Open Tech blog

23 July 2014 by Fabio Pinto on Centos 6 | lbzip2 | mysqldump

Inline MySQLDump compression using lbzip2

We've all at some point in time used mysqldump to backup a database. Right?

And we all know that databases become extremly large. so in order to help with the size of databases after backup we can use inline bzip2 compression during the mysqldump process.

For example :

mysqldump -u <user> -p <database> | lbzip2 > <name of compressed backup.bz2>

Notice that I have used lbzip2 for my bzip2 compression. lbzip2 does exactly what normal bzip2 does except MULTI-THREADED! This makes it substantialy faster than the standard bzip2 package.

Another great trick is restoring directly from a .bz2 backup without extracting:

lbunzip2 < <name of compressed backup.bz2> | mysql -h localhost -u <user> -p <database>

Why is this cool? Well for disk space limited VPS's extracting the database from its .bz2 archive and then importing into MySQL may result in running out of available disk space (<-- This is a bad thing)

Hope you found this helpful!

WhyBackup?