Compressing mysqldump output

The result of mysqldump is a flat text file containing the sql commands used to restore the mysql databases/tables that were dumped. Normally the result of mysqldump is compressed (in regular backups) because the size of the resulted file is normally big and good compression rates are achieved on such text files. To do this manually using gzip we just run:

gzip -v outputfile.sql

This quick tip shows how you can run the same thing on the fly with only one command directly compressing the resulted file. This might be useful in situations where space is a problem and the full dump can’t be saved on the available storage directly because of its size. Also this might be useful to not run it in 2 commands and have one compact command (maybe used also in some backup scripts, etc.)

mysqldump < mysqldump options> | gzip > outputfile.sql.gz

When restoring from a compressed file we can manually uncompress it first:

gunzip -v outputfile.sql.gz

or again we can run in the same command line mysqldump and gunzip:

gunzip < outputfile.sql.gz | mysql < mysql options>

The same can be done using bzip2 compression:

mysqldump < mysqldump options> | bzip2 > outputfile.sql.bz2

and to restore

bunzip2 < outputfile.sql.bz2 | mysql < mysql options>

From my own experience I would not recommend using bzip2 for this because even if the compression is better the time to run it on large files will be much longer than gzip (6-10 times longer). But if you are in need for every bit of space and don’t care that this will take much longer then bzip2 is the solution for you.