September 21, 2010 by Ivan Kristianto
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 hold all your website data including content, configuration, user account and other thing. So please be careful with this data you need to backup it 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 need at least 3 step process. Dump, Transfer and Insert it.
2. Copy you MySQL data across the server
The second way is 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 other host you need to add the permission on the old MySQL server. Add the “%” wildcard to your user, that means 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 store in your current server. And you just do insert to your new MySQL server.
b. Use SSH pipeline (use secure connection)
With this method you need to have SSH access to both your server.
Connect from you 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!