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.


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

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!