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?
Normal:
mysql < dump.sql
gzip:
gunzip < dump.sql.gz | mysql
bzip2:
bunzip2 < dump.sql.bz2 | mysql
More details about mysqldump can be found on MySQL Online Documentation.
Thats all folks
