Managing & Using MySQL, 2nd Edition

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.