Loading ...
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.sqlThis 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.gzWhen restoring from a compressed file we can manually uncompress it first:
gunzip -v outputfile.sql.gzor 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.bz2and 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.
Share This
31st October 2006, 11:14
gzip -v -9
-9 tells GZIP to attempt the highest possible levels of compression, which with 7 bit ASCII “text” can produce up to 90%+ compression.
so your example of:
gzip -v outputfile.sql
would reap far better disk space economies if you used:
gzip -v -9 outputfile.sql
but of course you will have a higher CPU load with the additional work required to gain the highest possible levels of compression GZIP can produce.
if CPU load is an issue, try lower levels of compression, test from -4 to -7 for example.
The nice thing about this is that GZIP doesn’t need any additional CLI options / arguments to be able to decompress a higher compression ratio .gz file, so your example of :
gunzip -v outputfile.sql.gz
will still work a treat, as GZIP figures it all out without having to be told gunzip -v -9 for example.
bz2 will give you even higher compression with 7 and 8 bit ASCII, but bz2 is not always on every box you might need to restore your .gz files to.
Then again GZIP may not be on all systems either.
A note for the safety nazi’s among you, if you’re ultra paranoid but still want to compress your MySQL dbdumps, consider using standard Unix “compress” - it’s on 100% of the available Unix and Unix like OS’s, or GNU/Linux distributions for example, and although it’s compression is not as good as GZIP, or BZ2, it is compatible accross all Unix platforms. It can even survive a little endian to big endian flip, from say x86 to PPC, without issue.
One wonders why dbdump doesn’t have the capability to compress the output - can someone ( I don’t have time ) perhaps write an additional “feature” for dbdump so you and do a “dbdump -v -z -9 > bigdbfile.gz and save a few steps ( did I really just say that? what am I thinking - pipe is your friend - tee bar’s are god! ).
Enjoy.
Cheers,
Dez
—
http://www.blanchfield.com.au/
http://www.websearch.com.au/
http://www.websearch.co.nz/
http://www.cradletechnologies.com/
http://www.thestorageforum.com/
31st October 2006, 14:46
Dez,
Thank you for your pertinent comment.
Here is just one quick addition:
The default level of compression on gzip is 6, that is normally a very good compromise between speed and the level of compression. But of course one can increase that up to 9 to get a better compression value. Also the –fast (=1) –best (=9) literal values can be used.
Cheers,
- Marius -
8th November 2006, 22:23
[...] Compressing mysqldump output | MDLog:/sysadmin КомпреÑÑÐ¸Ñ Ð²Ñ‹Ð²Ð¾Ð´Ð° результатов работы mysqldump (tags: mysql mysqldump компреÑÑÐ¸Ñ Ð±Ð°Ð·Ñ‹_данных) [...]
15th January 2007, 19:29
[...] References Compressing mysqldump output [...]
2nd October 2007, 23:23
Tektag / paddy…
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 b…