Tag Archives: 7za

Ubuntu Terminal: Efficient Way to Copy Across a MySQL Database from One Server to Another CodeUnit 23 MAY 2011

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

Ubuntu Server: Maximum Compression of a file Using 7za CodeUnit 28 NOV 2010

Just a quick one-liner to remind me how to ultra compress a file using 7za instead of the bog standard gzip which I always seem to automatically turn to because its the only one I can remember out of my head!

Anyway, the command to ultra compress a single file is:

7za a -t7z -m0=lzma -mx=9 -mfb=64 -md=32m -ms=on archiveName.7z filetocompress.ext

where the -t7z indicates a 7z archive, -m0=lzma indicates lzma compression method, -mx=9 dicates compression level (9 is ultra), -mfb=64 is the number of fast bytes for LZMA, -md=32m allows for a 32mb dictionary size and -ms=on indicates a solid archive.

Using the above I managed to compress a 285mb SQL dump to a insignificant 7.4mb archived file!

Nifty.