Changing from local to remote database

  • I am trying to figure out how to transfer an already created database in wordpress from the local wordpress server to a remote server.

    After doing the changes the wordpress site gives “Error establishing a database connection”.
    I can access the database from the server with the credentials without a problem.

    Both servers are in the same local network.
    Wordpress server : 10.172.16.5
    Database server : 10.172.16.6

    On the SQL server :
    mysql -u wordpressuser -p

    mysql  Ver 15.1 Distrib 10.3.12-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

Connection id:		39
Current database:	
Current user:		wordpressuser@
SSL:			Not in use
Current pager:		stdout
Using outfile:		''
Using delimiter:	;
Server:			MariaDB
Server version:		10.3.12-MariaDB-1:10.3.12+maria~bionic mariadb.org binary distribution
Protocol version:	10
Connection:		Localhost via UNIX socket
Server characterset:	utf8mb4
Db     characterset:	utf8mb4
Client characterset:	utf8mb4
Conn.  characterset:	utf8mb4
TCP port:		/var/run/mysqld/mysqld.sock
Uptime:			8 min 13 sec

    On the wordpress server :
    mysql -u wordpressuser -p -h 10.172.16.6

    mysql  Ver 15.1 Distrib 10.3.12-MariaDB, for Linux (x86_64) using readline 5.1

Connection id:		40
Current database:	
Current user:		wordpressuser@
SSL:			Cipher in use is ECDHE-RSA-AES256-GCM-SHA384
Current pager:		stdout
Using outfile:		''
Using delimiter:	;
Server:			MariaDB
Server version:		10.3.12-MariaDB-1:10.3.12+maria~bionic mariadb.org binary distribution
Protocol version:	10
Connection:		10.172.16.6 via TCP/IP
Server characterset:	utf8mb4
Db     characterset:	utf8mb4
Client characterset:	utf8
Conn.  characterset:	utf8
TCP port:		3306
Uptime:			8 min 34 sec

    I exported the database from the working wordpress installation with :
    mysqldump -u wordpressuser -p wordpress > dbbackup.sql

    And imported it to the new server with :
    mysql -p -u wordpressuser wordpress < dbbackup.sql

    config.php

    /** The name of the database for WordPress */
define('DB_NAME', 'wordpress');

/** MySQL database username */
define('DB_USER', 'wordpressuser');

/** MySQL database password */
define('DB_PASSWORD', 'password');

/** MySQL hostname */
define('DB_HOST', '10.172.16.6');

define( 'MYSQL_CLIENT_FLAGS', MYSQLI_CLIENT_SSL );
Viewing 5 replies - 1 through 5 (of 5 total)
  • Moderator Steven Stern (sterndata)

    (@sterndata)

    Volunteer Forum Moderator

    What happens if you try: define('DB_HOST', '10.172.16.6:3306'); ?

    Also, try adding a user 'wordpressuser'@'10.172.16.5' for the database in case it’s not happy using a wildcard.

    Most days I hate local hosts or rather the problems they seem to cause when the local site is moved to the actual home server.

    And from what you’ve said, it appears the WordPress install on the ‘actual home server’ is having db connection issues. Those are usually credential or configuration issues or else whatever was done clobbered the database there.

    If your local host exactly matches the install on the new host ‘directory wise’, and your local host’s database is reachable from the Internet you might just tell your home server’s WordPress to use that database for a bit.

    That still leaves you with the issue of the home server’s database being broken though. I’d fix that first then save a copy of the working wp-config.php (on the new home server) and then setup the existing wp-config to point at the database on the local host. Keep good notes, too.

    Now your site’s data should be there but you’ll need to tweak things to make all that work on the new host since your domain name probably changed.

    Once it’s working, install a backup plugin on the new home site and backup the database. Change your config back and restore the database… You should be ‘good to go’ now.

    IF YOUR LOCAL DATABASE ISN’T INTERNET REACHABLE you’ll need to backup the database on the local server and install the same plugin used on the New Home Server Install and then copy the database to the new home. Restore it from there and fix as needed.

    If instead you want to pursue the database manipulations as you have been doing you might ask for some help from your host provider or else at Stack Exchange.

    The MySQL manipulations you want to do are outside the scope of normal WordPress Support.

    Thread Starter carobell

    (@carobell)

    @sterndata
    Changing the DB_HOST did not change anything, I also tried “10.172.16.6:/var/run/mysqld/mysqld.sock”.
    I did not notice that it was on a wildcard. I am very certain that it had the hostname of the database server at some point, but now I cannot seem to make it go back.
    I am remaking the database server to check that out~

    @jnashhawkins
    I’m sorry if that was not clear, I am my own “host provider”.

    Both machines are on my own server. WordPress server is available on the internet but not the database. I do not use any plugin to backup anything since I can just copy the files directly in the CLI.

    But you might be right about the broken database, I will have to try with a completely new install.

    Yeah, I host my own sites, too.

    But I still lean on what I outlined in my post letting WordPress and a backup plugin do the work.

    And since you host your own sites you might want to just build the WordPress first and let your local host use that database the next time. Then when you go to bring up the actual site live your database will be sitting there waiting on you.

    Just a thought!

    Thread Starter carobell

    (@carobell)

    I feel I do not understand what you are trying to say.

    Like, instead of moving the database server I move wordpress instead ?

Viewing 5 replies - 1 through 5 (of 5 total)
  • The topic ‘Changing from local to remote database’ is closed to new replies.