I often need to pull down a copy of a live MySQL database instance for development work on my local machine. Because my access to bandwidth is limited, I need to come up with the most efficient way of achieving this, and my method for achieving this is chronicled below – mainly so that I can refer to it when I forget!
Anyway, the process is simple enough to explain. First, we ssh into the target server and we use mysqldump to create a SQL dump text file of the required MySQL database. Next, we compress it using the powerful 7-Zip to create a nice and compact 7z file. Exit ssh. To pull it down from the server, we use plain old scp and do a secure copy down to our local machine. Next we uncompress the newly copied 7z archive and once extracted, import it into our local MySQL server using the mysql command.
And a nice example for copy and paste purposes:
mysqldump -u #USERNAME# -p#PASSWORD# -c --add-drop-table --all --quick #DATABASE# > dump.sql
7za a -t7z -m0=lzma -mx=9 -mfb=64 -md=32m -ms=on dump.7z dump.sql
scp -v -P #PORT# #REMOTEUSERNAME#@#HOSTNAME#:/home/server/dump.7z /home/craig/dumpscp.7z
7za e /home/craig/dumpscp.7z
mysql -u #USERNAME# -p#PASSWORD# database < /home/craig/dump.sql