Using Backups for Data Recovery

Database damage occurs for a number of reasons and varies in extent. If you're lucky, you may simply have minor damage to a table or two (for example, if your machine goes down briefly due to a power outage). If you're not so lucky, you may have to replace your entire data directory (for example, if a disk died and took your data directory with it). Recovery is also needed under other circumstances, such as when users mistakenly drop databases or tables or delete a table's contents. Whatever the reason for these unfortunate events, you'll need to perform some sort of recovery.

If tables are damaged but not lost, try to repair them using myisamchk or isamchk. You may not need to use backup files at all if the damage is such that the repair utilities can fix it. The procedure for table repair is described in Chapter 13. If tables are lost or irreparable, you'll need to restore them.

Recovery procedures involve two sources of information: your backup files and your update logs. The backup files restore tables to the state they were in at the time the backup was performed. However, tables typically will have been modified between the time of the dump and the time at which problems occurred. The update logs contain the queries used to make those changes. You can repeat the queries by using the update logs as input to mysql. (This is why you should enable update logging. If you haven't yet done so, you should do so right now and generate a new backup before reading further.)

The recovery procedure varies depending on how much information you must restore. In fact, it may be easier to restore an entire database than a single table because it's easier to apply the update logs for a database than for a table.

Recovering an Entire Database

First of all, if the database you want to recover is the mysql database that contains the grant tables, you'll need to run the server using the --skip-grant-tables option. Otherwise, it will complain about not being able to find the grant tables. After you've restored the tables, execute mysqladmin flush-privileges to tell the server to load the grant tables and start using them.

• Copy the contents of the database directory somewhere else. You may want them later—for example, to perform post-mortem examination of the corpses of crashed tables.

• Reload the database using your most recent backup files. If you're using files generated by mysqldump, use them as input to mysql. If you're using files that were directly copied from the database (for example, with tar or cp), copy them directly back into the database directory. In this case, however, you should bring down the server before copying the files and restart it afterward.

• Use the update logs to repeat the queries that modified database tables subsequent to the time at which the backup was made. For any applicable update log, use it as input to mysql. Specify the --one-database option so that mysql executes queries only for the database you're interested in recovering. If you know you need to apply all the update log files, you can use this command in the directory containing the logs:

% ls -t -r -1 update.[0-9]*| xargs cat | mysql --one-database
							db_name
						

The ls command produces a single-column list of update log files, sorted according to the order in which they were generated by the server. (Be aware that if you modify any of the files, you will change the sort order, which will result in the update logs being applied in the wrong order.)

It's more likely that you'll have to apply just some update logs. For example, if the logs made since the time of your backup are named update.392, update.393, and so forth, you can rerun the commands in them like this:

% mysql --one-databasedb_name< update.392
% mysql --one-databasedb_name < update.393
…

If you're performing recovery and using update logs to restore information that was lost due to an ill-advised DROP DATABASE, DROP TABLE, or DELETE statement, be sure to remove that statement from the update log in which it appears before applying the log!

Recovering Individual Tables

Recovering individual tables is more difficult. If you have a backup file generated by mysqldump and it doesn't contain the data just for the table in which you're interested, you'll need to extract the relevant lines and use them as input to mysql. That's the easy part. The hard part is pulling out the pieces of the update log that apply to just that table. You may find the mysql_find_rows utility helpful for this; it can extract multiple-line queries from update logs.

Another possibility is to restore the entire database using another server, then copy the files for the table you want to the original database. This may actually be easier! Make sure the server for the original database is down when you copy the files back into the database directory.