How to compress a mysqldump backup

By using Gzip, you can considerably reduce the size of the mysqldump output. Of course, this comes with a tradeoff: the duration of the backup. Gzip has 9 leveles of compression, 1 being the fastest with the smallest compression ratio and 9 being the slowest but with the highest ratio of compression.

Dump and compress a single database

# Dump and compress the output using the default level (6)
mysqldump my_app | gzip > my_backup.sql.gz

# Dump and compress the output using a different level
mysqldump my_app | gzip -8 > my_backup.sql.gz

Dump and compress multiple databases

# Dump and compress the output using the default level (6)
mysqldump --databases my_app1 my_app2 | gzip > my_backups.sql.gz

# Dump and compress the output using a different level
mysqldump --databases my_app1 my_app2 | gzip -8 > my_backups.sql.gz

Dump and compress all databases

# Dump and compress the output using the default level (6)
mysqldump --all-databases | gzip > my_backups.sql.gz

# Dump and compress the output using a different level
mysqldump --all-databases | gzip -8 > my_backups.sql.gz

What compression level should you use?

Our recommendation is to stick with the default one (level 6) because it offers the best compression/speed ratio.