Moving Your MySQL Database — The Clever Way

This article explains how one could migrate a MySQL database of any size from one remote server to another in the most efficient and simplest way.

So efficient, even I still cannot believe how an 850 gigabyte database has shrunk to an ultra-compressed 70 gigabytes (that’s almost 92% less), without any intermediary files! And while there are myriad of techniques on how you could transfer the same data from one server to another, none of them came close to the technique explained below.

Prerequisites: You’ll need SSH and MySQL access on both servers.

Starting from the source server, run the mysqldump process and pipe the output over to gzip, which ultra-compresses the data before dumping it into a file dump.sql.gz.

$ mysqldump -u <user> --password=<password> <database> | \ 
  gzip -9 > dump.sql.gz

Next: let’s upload! rsync is easy, super fast, and resumable–just in case something goes wrong. The P flag is optional, but helps you see the upload’s progress.

$ rsync -P dump.sql.gz <username>@<destination IP>:<target directory>

Once the upload is complete you are ready to access the destination server and run the below. It’s a reciprocal of the first command: uncompress the data and pass the output over to mysql to evaluate (and store your data).

$ gunzip -c dump.sql.gz | \
  mysql -u <user> --password=<password> <database>

And you’re done–you can now pat yourself on the back.

No one likes another outdated article. If you feel it can be improved or keep it up-to-date, I would very much appreciate getting in touch with me over twitter @mcac0006.