Backing Up and Recovering MySQL

Even with reliable software and competent users, there are still countless ways to lose or corrupt your MySQL data from your database server. Fortunately, recovery is actually a straightforward process when you have reliable backups.

InnoDB has an automatic internal recovery system, which much of the time operates transparently.  In the case of database hosting failure, InnoDB attempts to fix it by running the log file from the last timestamp.  In many cases, it succeeds during power failure or an operating system crash, for example, InnoDB itself takes care of recovering all the data.  But when InnoDB fails this automatic repair process, the entire database will not start.

It is important to schedule regular backups of your database.  The mysqldump utility is a helpful tool to make a backup of your data as a snapshot in a point of time.  Use this command to create a “dump file” of your MySQL database.

shell>  mysqldump --single-transaction --flush-logs --master data=2 \
            --all-databases > dump.sql

You can name the output file anything you want – here, it is dump.sql.  This file consists of the data, tables, and structures of all databases backed up into a SQL text file, dump.sql.

The option “--single-transaction” in the command above is specifically for InnoDB tables; it performs an online backup that takes no locks on tables.

To make incremental backups, however, you need to save incremental changes, a process best accomplished using the binary logs.  MySQL should always be started with the --log-bin option to enable that binary log.  In the mysqldump command above, the “--flush-logs” option causes the server to flush its logs.  You can flush the logs incrementally between dump backups to contain all the data changes made since that backup.

Now, suppose your database has a catastrophic crash.  It is simple to restore when you have regular backups as well as binary logs.  First, restore to the last full backup that you have.

shell>  mysql < dump.sql

The data is restored to the state that it was at your last mysqldump backup.  To restore the changes since then, use the incremental backups, from the binary log files which will be listed in the data directory of your MySQL server (here, logfilename-bin.000001 and logfilename-bin.000002).

shell>  mysqlbinlog logfilename-bin.000001 logfilename-bin.000002 | mysql