if you read my previous writeup on dumping all mysql databases you will recognize some of this information. I wanted to pay some specific attention to some of the different methods for how to compress mysqldump output.
Obviously compressing your mysql databased exports can have some major benefits. The biggest benefit is the smallness of the file size. Mysql databases and really all databases have the tendency to grow to large sizes. Even small websites can quickly find hundreds of megabytes worth of data in their database. Storing large database export files in your backup can eat up disk space pretty rapidly. Compressing your mysql output can reduce the size of your export file by seven or more times.
If you need to keep individual database backups then compression really makes sense. But if you are using something like rdiff-backup then it makes more sense to skip the compression. Rdiff-backup is unable to do a diff on the compressed data, so it won’t save the space you expect.
Basic Mysqldump Compression Commands
Here are a couple different variations of mysqldump piped compression commands which we will breakdown.
1: mysqldump -u dbUser -p DBName > OutputFile.sql 2: mysqldump -u dbUser -p DBName | gzip > OutputFile.sql.gz 3: mysqldump -u dbUser -p DBName | gzip -9 > OutputFile.sql.gz 4: mysqldump -u dbUser -p DBName | zip > OutputFile.sql.zip 5: mysqldump -u dbUser -p DBName | bzip2 > OutputFile.sql.bz2
In these examples we see the same database being exported in each command. But there are a couple differences, in #1 we are employing no compression. Command #2 is using gzip with its default settings. Then command #3 is utilizing gzip with maximum compression. Command #4 is using zip to perform its compression. And finally command #4 is using bzip2 to perform its compression.
Compression Commands Comparison
Testing the commands above on the same database and on the same hardware yielded the following results.
The table above shows the effectiveness of each compression method on the same dataset. The first command sets the baseline for data export with no compression. Gzip applies basic compression and gives a significant size reduction with a very small speed hit. It comes in just a hair faster than zip with about the same compression results.
Adding the -9 to the Gzip command in #3 doubles the output time, and only provides 2MB of space savings. But then Bzip2 weighs in on command #5 taking an extra minute over Gzip or Zip. That extra minute was required to pack the file small enough to rescue another 13MB of space.
Compress Mysqldump Output Conclusions
If you can compress your database output, then you will see significant space savings in your backup storage. Even if backup speed is essential, gzip or zip offer a major reduction in size for minimal extra time. And if time is not a major issue then going with bzip2 will give you much larger space savings in exchange.
Understanding and utilizing compression as part of your backup methodology is an essential element for storage success. Proper implementation can ensure that you save the needed space and reduce backup transfer time. Especially in the event that you need to transfer your backup over a slow network connection. Compression will come to your aid and save the day. So don’t hesitate to compress mysqldump output, it might be just what the doctor ordered.
For additional details and info check out this post which talks more about Compressing Mysqldump Output