online prescription solutions
online discount medstore
pills online
buy lorazepam without prescription
xanax for sale
buy xanax without prescription
buy ambien without prescription
ambien for sale
buy modafinil without prescription
buy phentermine without prescription
modafinil for sale
phentermine for sale
lorazepam for sale
buy lexotan without prescription
bromazepam for sale
xenical for sale
buy stilnox without prescription
valium for sale
buy prosom without prescription
buy mefenorex without prescription
buy sildenafil citrate without prescription
buy adipex-p without prescription
librium for sale
buy restoril without prescription
buy halazepam without prescription
cephalexin for sale
buy zoloft without prescription
buy renova without prescription
renova for sale
terbinafine for sale
dalmane for sale
buy lormetazepam without prescription
nobrium for sale
buy klonopin without prescription
priligy dapoxetine for sale
buy prednisone without prescription
buy aleram without prescription
buy flomax without prescription
imovane for sale
adipex-p for sale
buy niravam without prescription
seroquel for sale
carisoprodol for sale
buy deltasone without prescription
buy diazepam without prescription
zopiclone for sale
buy imitrex without prescription
testosterone anadoil for sale
buy provigil without prescription
sonata for sale
nimetazepam for sale
buy temazepam without prescription
buy xenical without prescription
buy famvir without prescription
buy seroquel without prescription
rivotril for sale
acyclovir for sale
loprazolam for sale
buy nimetazepam without prescription
buy prozac without prescription
mogadon for sale
viagra for sale
buy valium without prescription
lamisil for sale
camazepam for sale
zithromax for sale
buy clobazam without prescription
buy diflucan without prescription
modalert for sale
diflucan for sale
buy alertec without prescription
buy zyban without prescription
buy serax without prescription
buy medazepam without prescription
buy imovane without prescription
mefenorex for sale
lormetazepam for sale
prednisone for sale
ativan for sale
buy alprazolam without prescription
buy camazepam without prescription
buy nobrium without prescription
mazindol for sale
buy mazindol without prescription
buy mogadon without prescription
buy terbinafine without prescription
diazepam for sale
buy topamax without prescription
cialis for sale
buy tafil-xanor without prescription
buy librium without prescription
buy zithromax without prescription
retin-a for sale
buy lunesta without prescription
serax for sale
restoril for sale
stilnox for sale
lamotrigine for sale

MySQL database backup file compression: gzip vs bzip2

In one of my previous posts: MySQL backups, I talked about using a script for automating backups. I show that we can use gzip to compress backup file to compress and save. Since then, our backup file has been growing meg or two a day which is causing our backup files to get bigger and bigger every day. We keep hourly backups for a week so you can imagine space usage is quite high.

I went and did few tests to see if its beneficial for us to use Bzip2 instead of gzip. I could’ve tried 7z also but that is not something installed on most linux machines I work on and I didn’t want use a solution which will require me to add more software. Here are the results:

Original sql file size: 584M
Using gzip: 166M
Time spent on compressing 1 minute 25 secs
Using bzip2: 125M
Time spent on compressing 1 minute 50 secs

As we can see that it takes longer to compress but file size is much smaller (adds up fast for multiple backups). As our database grows bigger and bigger, the size difference will matter quite a bit. Since we also ftp the file off the server to onsite/offsite location hourly as well.

Does anybody know of any backup techniques which we can use for free and we can do incrementals with? comments are always welcome.  To learn more about gzip or bzip2, see man gzip or man bzip2 respectively.

13 Responses to “ MySQL database backup file compression: gzip vs bzip2 ”

  1. dudus
    March 19th, 2007 | 7:45 pm

    You could keep a backup copy remote and locally. Then everytime you do a new backup you binary diff both and just submit the patch. So onsite you keep only the last one. And off site you keep an old one with several patches.
    Then you can apply some of this patches just to clenup once in a while.

    Just an idea

  2. March 19th, 2007 | 9:31 pm

    There’s always rsync :-)

    I’ve used rdiff-backup in the past, it’s pretty good albeit not very robust (fails with fatal error a lot in situations where it should have just retried or ignored the error). i’m now using rsync combined with some simple homebrewn script.

    Unix’s “cp -la” is also useful :-)

  3. March 19th, 2007 | 9:42 pm

    Steven: what am I rsync’ing? are you talking about rsync’ing the db files themselves? if so, it does make it hard for us to go back to certain date ad pull data out if needed.

    dudus: intresting idea. I never heard anybody do this… I will look into it. Thanks!

  4. Martin
    March 19th, 2007 | 11:00 pm

    For incremental backup I’d suggest using binary logs. Set max_binlog_size to something small and every time a new binlog file is started, copy the last one (I’m not sure if it is safe to copy the currently used binlog file as well).

    Even better would be a replication. Then you get a real-time copy of all transactions. That would also use less resources on the DB server, as all further copying and comression (hourly snapshots etc.) can take place on the backup server.

  5. March 19th, 2007 | 11:19 pm

    Have you tried with bzip2 -9 or -s ???

  6. March 20th, 2007 | 12:08 am

    Shin: -9 creates a same size compressed file. -s creates 135 meg file. -s is used so bzip doesn’t use too much ram. therefore its not worth using since ram is not an issue here.

    Martin: we will be looking at replication in near future. Thanks for binlog comment. another thing for me to try.

  7. Casey
    March 20th, 2007 | 6:55 am

    Crazytoon: I believe Steven is saying you can use rsync on the actual dumps. So, the basic process would be:

    1. Create a new SQL file using MySQLdump
    2. Rsync the SQL file to your backup server.

    Martin does have good advice for incremental backups. The nice thing about that is when you do a full-backup (once a week?), you can delete the old binary logs to save space.

  8. Samer El Sahn
    March 22nd, 2007 | 6:28 am

    Dar is a very good backup tool that could deliver diff backups

  9. November 26th, 2007 | 4:22 pm

    [...] MySQL database backup file compress gzip vs bzip2 [...]

  10. kindDBA
    April 2nd, 2008 | 2:54 am

    which one is the fastest way to compress database backup. I have a backup of 30G and is taking more than an hour to finish compressing

  11. April 3rd, 2008 | 2:00 pm

    gzip is definitely faster.

  12. January 21st, 2009 | 9:43 am

    Hey. Using MySQL’s archive storage engine can be good for things like logs I believe it uses gzip compression as well.

  13. July 9th, 2009 | 10:50 am

    Hey Everyone! Yeah using MySQL’s archive storage engine makes things small without the need to dump the .sql all the time. Tradeoff there is that the queries run slowly (Im not sure if it supports indexing)

Leave a reply

To prove that you're not a bot, enter this code
Anti-Spam Image