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.
Nifty.
And a nice example for copy and paste purposes:
ssh #REMOTEUSERNAME#@#HOSTNAME#
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
exit
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