Find solution
How to completely backup all mysql databases?

How can I backup multi-gigabyte databases in MySQL? Bonus points for speedier restores

  • I have a 4GB database in MySQL. How can I back it up? Furthermore, is there a way that I can speed up the restoration process in the event that I need to recover? I currently use a cron job every night that calls mysqldump to dump everything and I keep 3 days of backups.

  • Answer:

    We have around 90 GB in a MySQL, so here are some suggestions: You can try the following call: mysqldump -u USER -pPASS --single-transaction -Q --databases foo bar (Use the docs to check if those switches work for you.) If you use MYISAM-tables only, then copying the database to another drive and tar'ing it, might also be an alternative. This will not work with INNODB though, since it keeps an additional file. Also, you have to test this carefully. Enable binary logs, and back them up. (This is probably my favorite!) Setup replication and to do the backup on one of your slaves, and then let it catch up with the rest. It's pretty nice when it runs, but the problem is getting it running. Replicating is no fun with MySQL. :( Does your filesystem support snapshots? If it does, then that is a great opportunity to use them. Use a commercial solution, such as http://mysqlbackup.zmanda.com/.

nicudotro at Server Fault Visit the source

Was this solution helpful to you?

Other answers

The best way would be without interrupting normal operation. For high-reliability, fault-tolerant systems, you have 2 DBs that are kept in sync, and you backup the secondary. (eg http://www.howtoforge.com/back_up_mysql_dbs_without_interruptions) Otherwise, read the http://dev.mysql.com/doc/refman/5.0/en/backup-and-recovery.html: use myseldump as you currently do, or use the mysqlhotcopy script (use just like mysqldump), or stop the DB and simply copy the frm, MID, MYI files (using rsync). I think the stop+copy files mechanism is the fastest you're going to get.

gbjbaanb

If some of your databases are read-only or modified rarely single it out as a less frequent cron.

J.J.

To answer your question - http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html#option_mysqldump_extended-insert option works good for me. If you have enough resources for restoring, extended_insert will insert huge chunks of data in a single query, making the restore faster.

Gaurav

You should really consider Percona's http://www.percona.com/docs/wiki/percona-xtrabackup%3astart The wrapper script "innobackupex" can handle MyISAM but for a non-locking/online backup your tables should all be InnoDB. Cheers

HTTP500

Related Q & A:

Just Added Q & A:

Find solution

For every problem there is a solution! Proved by Solucija.

  • Got an issue and looking for advice?

  • Ask Solucija to search every corner of the Web for help.

  • Get workable solutions and helpful tips in a moment.

Just ask Solucija about an issue you face and immediately get a list of ready solutions, answers and tips from other Internet users. We always provide the most suitable and complete answer to your question at the top, along with a few good alternatives below.