mysql compress backup

By | 12 March 2019

There are several strategies for backup, snapshot, dump, dump binlog, it all depends on your need and the size of your database. I indicate this tool for databases that have 10G or more than that, the probability that you have problems to restore a backup and long delay, make it unfeasible. I’ll explain a little about the tool provides by MySQL, which is the mysqldump.

Making backup all databases:
mysqldump --all-databases > dump.sql

Making backup only one database:
mysqldump --databases db1 > dump.sql

Making backup many databases:
mysqldump --databases db1 db2 db... > dump.sql

Making backup with triggers:
mysqldump --triggers --all-databases > dump.sql

Making backup with procedures and functions:
mysqldump --routines --all-databases > dump.sql

Now let’s compress our dump in real time with gzip:
mysqldump --all-databases | gzip > dump.sql.gz

We can still reach a higher compression ratio using the bzip2:
mysqldump --all-databases | bzip2 > dump.sql.bz2

And how do I restore the dump?

mysql < dump.sql

gunzip < dump.sql.gz | mysql

bunzip2 < dump.sql.bz2 | mysql

More details about mysqldump can be found on MySQL Online Documentation.

Thats all folks