4.5 Recovery

Individual recovery scenarios vary widely, ranging from disk hardware failures to corrupted data files to accidentally dropped tables, and many points in between. In this section, we provide an overview of recovery procedures.

In general, you need two things to perform a database recovery: your backup files and your binary logs. Performing a recovery consists of:

• Restoring the database from the last backup

• Applying the binary logs to bring the system completely up to date

If you do not have binary logging enabled, the best you can do is restore the system to the last full backup.

4.5.1 mysqldump Recovery

This example assumes we are recovering the database named test that we dumped earlier.

The following command reloads the database:

$ cat test.dump | mysql

The command runs the SQL commands produced by mysqldump and brings the database back to the state it was at the last backup.

Once the system is back to its state at the time of the last backup, it is time to rerun the transactions that have taken place since the last backup using the binary log. If your log includes entries from multiple databases, and you want to recover only one of them, use the -- one-database mysql option to filter out SQL commands that apply to other databases. You should then rerun only the binary logs that were created since your last backup. For each binary log file, type:

$ mysqlbinlog host-bin.xxx | mysql  -- one-database=testdb

Sometimes you will need to massage the log output from the mysqlbinlog program before sending it to MySQL. If you are recovering from a mistaken DROP TABLE statement, for example, you will need to remove this command from the output of mysqlbinlog; otherwise, you will drop the table again! If such intervention is necessary, you need send the output from mysqlbinlog to a text file and edit it before sending it to MySQL.

4.5.2 mysqlhotcopy Recovery

To recover from a mysqlhotcopy backup, reload the database by copying the database files from the backup location to the mysql data location while the server is not running. Assuming the database is backed up in /var/backup/test and the mysql data location is /usr/local/mysql/data:

$ cp -R /var/backup/test /usr/local/mysql/data

This command brings the database back to the state of the last backup.

It is now safe to restart the mysql server and apply the binary logs as described in the previous section to bring the system up to date.