Menu Close

MySQL compress backup – mysqldump – gzip – bzip2

Today let’s talk about a very important issue for database administrators, BACKUP.
There are several strategies for backup, snapshot, dump, dump binlog, it all depends on your need and the size of your database. I’ll explain a little about the tool provides by MySQL, which is the mysqldump , I indicate this tool for databases that have 15gb/20gb even more than that, the probability that you have problems to restore a backup and long delay, make it unfeasible.
How mysqldump works? He does nothing but create sql commands to our structure and data and put them in the file specified.
First, I will explain some of the most important options mysqldump then, for those who have MySQL installed on a Linux server, I will teach how to compress the dump in real time.

    Making backup all databases:

mysqldump -u usuario -p --all-databases > dump.sql

    Making backup only one database:

mysqldump -u usuario -p --databases db1 > dump.sql

    Making backup many databases:

mysqldump -u usuario -p --databases db1 db2 db... > dump.sql

    Making backup with triggers:

mysqldump -u usuario -p --triggers --all-databases > dump.sql

    Making backup with

procedures and functions:

mysqldump -u usuario -p --routines --all-databases > dump.sql

    Now let’s compress our dump in real time with

gzip:

mysqldump -u usuario -p --all-databases | gzip > dump.sql.gz

    We can still reach a higher compression ratio using the

bzip2:

mysqldump -u usuario -p --all-databases | bzip2 > dump.sql.bz2

Making a comparison between the dump with three compression options (none, gzip and bzip2) had the following result:

    Dump normal – 947k
    Dump com gzip – 297k
    Dump com bzip2 – 205k

And how do I restore the dump?

Normal:

mysql -u usuario -p < dump.sql

gzip:

gunzip < dump.sql.gz | mysql -u usuario -p

bzip2:

bunzip2 < dump.sql.bz2 | mysql -u usuario -p

More details about mysqldump can be found on MySQL Online Documentation, see you!!!

MySQL by Marcelo Altmann
MySQL by Marcelo Altmann

Liked? Help and Share!