Critical Skill 8.3 Recover from a Disaster

No one likes the sound of the phrase disaster recovery, but the best thing to do is focus on the recovery part. If you have been faithfully making backups and synchronizing your binary update log each time, MySQL makes it fairly easy to quickly and cleanly get your database back to where it was before your particular disaster struck.

Once you have ascertained the cause of your disaster, you can take steps to recover from it. Obviously, if it is a hardware failure, you will need to replace the hardware and reload the appropriate programs onto your machine. If it was human error in the form of unfortunate queries that resulted in table damage, you need to locate those queries in your binary update log so that you do not execute them again during the recovery process and duplicate the error. If it was something uncontrollable, like a power outage , then weigh what your downtime is costing you compared to the price of a suitable UPS, and if the downtime is more costly, put one in the loop before recovering your database.

Knowing the Restore Procedure

The following is the basic procedure for recovering from a database failure:

Make a copy of the damaged database.
Put your most current backup in the data directory.
Use the binary update log to execute the queries that had been run since the backup was taken.

The following sections describe how to accomplish each of these steps.

Copying the Damaged Database

If you still have a current but damaged copy of your database, make a copy of it that is clearly marked as damaged. It is possible that you may want to examine the damage during the restoration process. It may be that the causes of the disaster were more complex than you first realized, so in the unlikely event that the recovery fails, you will still need the damaged database to examine.

Putting Your Backup in the Data Directory

Next , you need to put your most current backup in the data directory. How you do this depends on how the data was backed up.

If you used the direct-copy data file method, copy the backup files into the data directory using the same OS-specific file-copy techniques.

If you used the BACKUP TABLE command, use the reciprocal RESTORE TABLE command. Like BACKUP TABLE , RESTORE TABLE can be used only on MyISAM tables. When you use the RESTORE TABLE command, it copies the .MYD and .frm files into the data directory and re-creates the index (.MYI), if there was one in the original table. The syntax for the RESTORE TABLE command is as follows :

 RESTORE TABLE <  table_name or list of names  > FROM <  path to backup files  >

Note	The RESTORE TABLE command will not allow you to write over any already existing files. The old files must be removed from the data directory, or the command will return an error.

If you used either of the methods that result in a file of SQL commands, run those commands, preferably using a GUI to import the file or to paste the file information into an SQL editor window to run.

Executing Queries with the Binary Update Log

Once the backup is installed into MySQL, use the binary update log to execute the queries that had been run after the backup was taken. mysqlbinlog is another script that must be run from the command line, and its syntax is as follows:

 MYSQLBINLOG <  update_log_name  >  MYSQL --ONE-DATABASE <  database_name  >

The --ONE_DATABASE option allows MySQL to ignore any SQL statements in the update files for any but the specified database.

Dealing with Damaged Tables

If you have a damaged table, the logical reaction would seem to be restoring just that table; however, restoring a table can be much more complicated than restoring an entire database. When you restore a database, MySQL searches through the binary update file for all queries pertaining to that database. If you have multiple databases, that can be a complicated search. If you are restoring one table within a specific database, the binary update file must be searched one level deeper, looking for only the queries pertaining to that specified table.

It is often easier to restore the entire database than take the time to restore just one table within it. Remember that the mysql and test databases are installed with MySQL, so even if you store all your data within one other database, there are still queries to more than one database happening within the binary update log. The more databases you have, the more complex a mixture of queries will reside in your binary update file. That being said, you may still decide to deal with database damage on a table level.

There are several ways a table can become damaged. During the operation of a mysqld process, the interruption of a write will result in a damaged table. Such an interruption can be caused by a power failure, a crash of the OS, a hardware error, or a kill command issued on the process. Using a script to interface with MySQL without locking the tables first can also result in table damage.

The classic signs of table damage are if MySQL suddenly stops working for no apparent reason or if one process thread begins to use up all of your CPU activity. When either of those events occurs, it may be advisable to use the CHECK TABLE command.

Using the CHECK TABLE Command

The CHECK TABLE command works on MyISAM and InnoDB tables. It operates by first locking tables, and then checking for errors in the data or table structure before unlocking the tables again. On MyISAM tables, it corresponds with the myisamchk script when using the -m option, as discussed in the next section.

The syntax for the CHECK TABLE command is as follows:

 CHECK TABLE <  table_name or list of names  > [  option  ]

You can run CHECK TABLE with any of the five options shown in Table 8-2.

Table 8-2: Options for the CHECK TABLES Command
Option	Description
QUICK	Does not scan rows for broken links
FAST	Does not scan tables that have closed properly
CHANGED	Does not scan unaltered or properly closed tables
MEDIUM	Scans rows to verify deleted links are correct and runs a checksum verification on rows (default value)
EXTENDED	Does a full key lookup for all keys on all rows( time-consuming )

Specifying no options in the CHECK TABLE command causes the command to use the default value, MEDIUM . You may also specify multiple options, if desired.

If the command line returns anything other than OK or Table is already up to date , you need to repair the table.

Diagnosing and Repairing with the myisamchk Script

The myisamchk script works only with MyISAM tables. It can check a table for errors, and it can also repair, optimize, and gather information about a table. Including options in the command syntax controls the various levels of operation.

The myisamchk script does not communicate with the MySQL Client, however; so running it without locking the database tables is dangerous and can lead to damage. Therefore, you must take the following steps when using myisamchk :

Open two command-line windows and log in to the MySQL Client in one of them.
Perform the following commands in the MySQL Client window:
```
 LOCK TABLE <  table_name  >  READ;FLUSH TABLES; 
```
In the other command-line window, change to the data directory of the database holding the table you wish to check and execute the myisamchk command, using the following syntax:
```
 MYISAMCHK [  option  ] <  table_name  > 
```
When you have completed using myisamchk , return to the window where you are still logged in to the MySQL Client and execute the following command:
```
 UNLOCK TABLES; 
```

You can perform this task from one command-line window by logging in and out of the MySQL Client when necessary, but the dual-window method makes it less likely that you will forget to unlock the tables after you are finished checking them.

There are many options for the myisamchk script, which can be listed by running myisamchk --help from the command line. The most commonly used options for checking tables are shown in Table 8-3.

Table 8-3: Commonly Used Checking and Repairing Options for myisamchk
Option	Description	Level/Use
No option	Default table check	Finds most errors
-m or --medium-check	Corresponds to CHECK TABLE MEDIUM	More thorough check
-e or --extend-check	Corresponds to CHECK TABLE EXTENDED	Most thorough check
-s or --silent	Returns only error messages	Handy for use in scripts
-r or --recover	Default table repair	Fixes most errors
-o or “safe-recover	Slower but more thorough table repair	Fixes errors that “r does not fix.
-q or --quick	Faster but limited table repair	Fixes a table but not a table's data

The myisamchk script will also repair tables using the -r and the -o options. The -r option is the basic way to repair a table and fixes most errors; the -o option does a more thorough check and can sometimes repair errors that the -r option does not. The -q option can be used to speed the repair by not modifying the data.

As when you use the myisamchk script to check tables, you must remember to lock, flush, and unlock the tables, or the process may result in a false result or even damage the tables.

Note	The easiest way to avoid difficulties with unlocked tables when performing checks or repairs is to use the CHECK TABLE or REPAIR TABLE command instead of the myisamchk script, because it handles the locking and unlocking steps for you.

Repairing on the Table Level

If your database has multiple, large tables, it may be more efficient to repair damage on the table level. The REPAIR TABLE command, like the BACKUP TABLE and CHECK TABLE commands, works only on MyISAM tables. It also handles the locking and unlocking steps automatically. The command corresponds to the myisamchk -r script option.

The basic syntax for the REPAIR TABLE command is as follows:

 REPAIR TABLE <  table_name  > [  option  ];

REPAIR TABLE has two options: QUICK and EXTENDED . The QUICK option attempts to repair the table by examining and repairing its index only. The EXTENDED option repairs the index by examining the table a row at a time.

On an extremely large table, the REPAIR TABLE command with the EXTENDED option may never complete. If it continues interminably, you should abort the attempt and use the backup to recover the table.

The recommended procedure for repairing tables is to start with the fastest method and then proceed to the slower methods if the original attempt does not work. In most cases, the basic, fast method will result in the table being fixed.

Occasionally, the normal repair methods do not work. In that case, it may be because the index file (.MYI) is damaged beyond repair or missing entirely, or your table description file (.frm) could be damaged or missing. If it is the index file that is damaged or missing, and you are running MySQL version 4.0 or newer , you can create a new index file by using the following command syntax:

 REPAIR TABLE <  table_name  > USE_FRM;

If you are running a MySQL version prior to 4.0, you can make a safety copy of the data file in another directory, use the TRUNCATE < table_name > command to empty the data and index from the table, and then copy the safety copy of the data back into the original directory. Once that has been accomplished, run a REPAIR TABLE command to verify the table s condition.

If your description file is damaged or missing, you can either restore the table from the latest backup or create a new description file by making a safety copy of the data and index files in another directory, executing a CREATE TABLE command, and then copying the index and data file back into the original directory.

Ask the Expert

How can I recover from a failure as quickly as possible?

Often, it is faster and easier to rebuild from a backup than to fix a corrupt database. When performing backups always remember to run the flush logs command. Flushing the logs makes the MySQL server start a new binary log file. That means the most current log file contains only the transactions performed since the last backup, which allows you to apply it to the most current backup and re-create your database. Even though the default MySQL table type (MyISAM) does not have transactional capability, you should be able to bring the database back to its prefailure state in a matter of minutes if you have backed up the database and flushed the logs at regular intervals. If you write a script or create a batch file to perform this recovery process, the restoration may take only seconds. Once the process is semiautomated using a script or batch file, the key factors affecting restoration speed are file size and the number of statements that need to be rerun to bring the system up to date.

If both the index and description files are damaged or missing, you should first re-create the description file, and then re-create the index file.

Performing Preventative Maintenance: A Stitch in Time

As the preceding sections make clear, recovering your database from a disastrous failure is almost always possible, and MySQL gives you many tools to achieve those ends. However, prevention is always preferable to repair or recovery.

Corrupted tables can cause system failure. MySQL provides you with maintenance tools, whose regular use can help avoid that undesirable end. Checking your tables once a week is considered a reasonable interval. Linux users can set up a cron job to do it automatically, and Windows users can set up a process in the Windows Scheduler.

Caution

If the MySQL Server is running as usual (without locking the tables), it is possible to get an error result from checking a table, even when the table in question is actually okay.

Using Startup Table Check

If your MySQL Server is stopped and started regularly, you can place a myisam-recover option under the [mysqld] section in your .cnf or .ini configuration file, which will cause MySQL to check the tables automatically each time it starts up. When a table is opened, it will be checked and repaired if necessary. You can go one step further and change the syntax in the configuration file to read myisam-recover=BACKUP , which will cause backups of any changed data files to be saved under the default filename < table_name > _ < datetime function > .BAK .

Using the OPTIMIZE TABLE Command

The OPTIMIZE TABLE command allows you to clean up after the changes made in a table during routine operation. It accomplishes four basic tasks :

Unused space is made usable.
Data files are defragmented.
Index pages are sorted.
Statistics are updated.

Any time you delete large parts of any table, or you have many changes to a variable-length table, you should run OPTIMIZE TABLE to do the basic housekeeping that keeps your tables less vulnerable to damage.

The OPTIMIZE TABLE command works with MyISAM and BDB tables. Its syntax is as follows:

 OPTIMIZE TABLE <  table_name or list of names  >;

The OPTIMIZE TABLE command corresponds to running the myisamchk script with these options: --QUICK , --CHECK-CHANGE-TABLES , -- SORT -INDEX , and --ANALYSE .