MySQL Phrasebook

Now that you have a comprehensive backup strategy, let's examine how you would go about restoring your database from the backup files should a catastrophe happen.

Restoring a Full Backup

As the output from mysqldump is simply a series of SQL commands, you can restore a database in a single operation by passing the contents of the backup file into the mysql program.

If you restore to the same dbname from which the dump was taken, the existing tables are overwritten. A DROP TABLE statement is included in the output from mysqldump for each table that was backed up.

If the backup file is for a single database, it does not contain a CREATE DATABASE statement, so you could actually restore the entire database file to a different database name by specifying a different database name in the mysql command.

If mysqldump was run with the --all-databases or --databases option, the CREATE DATABASE command is automatically included for each database in the backup.

Restoring an Incremental Backup

Before restoring an incremental backup, you must restore your database to a known point using a full backup, and then use a binary log fileor series of filesthat contains the database changes from that point onward.

The mysqlbinlog utility converts the binary log format to a text format containing the SQL commands that have been recorded. The output from mysqlbinlog can then be passed into a mysql command just like the output from mysqldump.

The preceding phrase uses a pipe to feed the output from mysqlbinlog into mysql, but you could also save the output to a file and then pass it to mysql using file redirection.

The following command uses a regular expression to restore a database using every binary log file in the current directory. The shell reads the list of matching files and passes each one to mysql.

mysqlbinlog hostname-bin.[0-9]* | \ mysql --options dbname

Because the numbers in the filenames are padded with leading zeros, the shell reads the files in the correct sequence. If you restore manually from more than one binary log file, you must use the backup files in the correct order.