Copy MySQL Data Across The Server

If you move or change your hosting server, beside the files you also need to move your MySQL data as well. MySQL database holds all your website data including content, configuration, user account and other things. So please be careful with this data — you need to back it up regularly. See my other post about how to regularly backup your MySQL.

But when you move to another server you can do it with 2 ways:

1. Backup it from the old server and upload it to another server

You can do this with mysqldump function or use export from PhpMyAdmin, then upload to your new server. See the following code sample:

mysqldump -h localhost -u [username] -p[password] --complete-insert [database-name] > [filename].sql

Then you upload the [filename].sql to your new server and insert it to your new MySQL server. This needs at least a 3-step process: dump, transfer, and insert.

2. Copy your MySQL data across the server

The second way is to copy your MySQL data directly from server to server. This can be done with 2 options:

a. Dump it across the server (not secure connection)

Before you can dump your MySQL data from another host you need to add the permission on the old MySQL server. Add the “%” wildcard to your user, meaning the user can connect from any host. Then you can backup your data with this command:

mysqldump -h [hostname/domain name] -u [username] -p[password] --complete-insert [database-name] > [filename].sql

Your dump file will be stored on your current server. Then you just insert it to your new MySQL server.

b. Use SSH pipeline (use secure connection)

With this method you need to have SSH access to both your servers. Connect from your old server then do this:

mysqldump -h localhost -u [username] -p[password] --complete-insert [database-name] | ssh [remote-user]@[remote-server] mysql -u [remote-db-username] -p[remote-db-password] [remote-db-database]

Explanation: the server will dump your MySQL data as the details given, then connect to your remote server and insert the MySQL dump data to remote MySQL database as given. One step command only.

So which one is easier for you? You choose!