How to compress mysqldump output with gzip/bzip2

mysqldump exports MySQL data in SQL format and is commonly used to back up MySQL databases. Normally, mysqldump backups are not compressed, so large databases produce large dump files. This article shows how to back up and restore while compressing or decompressing data, saving backup storage space.

Compressing with gzip

To back up a MySQL database while compressing it with gzip, run:

# mysqldump [mysqldump options] | gzip > outputfile.sql.gz

To restore a MySQL database while decompressing gzip data, run:

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

Compressing with bzip2

To back up a MySQL database while compressing it with gzip, run:

# mysqldump [mysqldump options] | bzip2 > outputfile.sql.bz2

To restore a MySQL database while decompressing gzip data, run:

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

Note: compressing data while backing it up takes longer (time required: uncompressed < gzip < bzip2), so use this with care.