Data Management

Checking and repairing MySQL tables

There's nothing quite like coming to work one morning, and finding that four years of corporate data has just been corrupted. If your database is MySQL, though, there's still hope.

Data corruption is every DBA's worst nightmare. There's nothing quite like the experience of coming to work one morning, finding that four years of hard-earned corporate data has just been corrupted, and the entire organization is looking to you to recover it. It sure beats coffee for a wake-up call.

If your database is MySQL, though, there's still hope. Over the course of this article, I will show you how to use MySQL's built-in crash recovery tools to check your database, and hopefully recover all the data you just lost.

Built-in tools
When it comes to repairing and checking tables, MySQL offers two options:
  1. The MySQL distribution ships with a tool called "myisamchk," designed specifically to check and repair MyISAM tables (the default table type used by MySQL). This tool can scan your databases, identify problems, optimize tables for faster lookup, and optionally repair corrupted tables. The myisamchk tool is invoked from the command line.
  2. In addition to the tool above, MySQL also allows you check and repair tables using SQL commands. The CHECK TABLE, REPAIR TABLE, and OPTIMIZE TABLE commands can be used on a running MySQL server and need to be entered through a MySQL client.

Most of the time, it's preferable to use myisamchk because it is significantly faster than using SQL commands.

Checking tables
If you're having trouble accessing a table, first try checking it for errors. To check a table, shut down the server and type myisamchk at the command prompt, followed by the table's file name, as shown below:
$ myisamchk /usr/local/mysql/data/db2/payroll.myi
Checking MyISAM file: /usr/local/mysql/data/db2/payroll.MYI
Data records:     1153   Deleted blocks:       0
- check file-size
- check key delete-chain
- check record delete-chain
- check index reference
- check data record references index: 1
- check record links

Use the complete path to the table file instead of just the table name. Remember to include the file extension as well.

You can pass myisamchk command line arguments, including those shown in Table A, to control how intensively it checks the table.
Table A
Argument Description
myisamchk —fast Perform a quick check, only verifying if the table handler closed successfully
myisamchk —medium-check Perform a faster check, by calculating a checksum for the indexes in each record and verifying that checksum against the index tree
myisamchk —extend-check Perform a thorough check of the table, verifying the data in each record
Command line arguments for myisamchk

Repairing tables
If your check reveals errors, or if you see cryptic error messages about table handlers or corrupt indexes, then you need to try repairing the table. Unplug the phone, turn off the Blackberry, and cancel your appointments, because you're going to be busy for a while.

The first—and most important—thing you can do before repairing a corrupted table is to back it up. Never try running a repair operation on a table without backing it up; if you fail, your data might be in worse shape than originally and may even become unsalvageable. Backing up the original table file allows you the option of trying a different technique if your first attempt doesn't work.

Next, shut down the MySQL server. This is because myisamchk makes changes to the table file itself, so you don't want other users accessing or otherwise manipulating the file during the repair operation. Remember, don't just terminate the server with a call to "kill"; instead, use the mysqladmin SHUTDOWN command to ensure that MySQL closes all open tables gracefully and doesn't further compound your problem.

Once the table file is backed up and the server down, you can run myisamchk with the —recover option, as shown below:
$ myisamchk —recover /usr/local/mysql/data/db2/payroll.myi
- recovering (with sort) MyISAM-table '/usr/local/mysql/data/db2/payroll.MYI'Data records: 1153
- Fixing index 1

The —recover option reconstructs the MySQL table index file after weeding the corrupted table of invalid or previously deleted records that could be causing a problem.

In the unlikely event that the —recover option fails, revert to the original table file and try the —safe-recover option. This is slower, because MySQL needs to scan through the records in the data file one by one and them restore the index, but it can sometimes work better than a regular repair operation.
$ myisamchk —safe-recover /usr/local/mysql/data/db2/payroll.myi
- recovering (with keycache) MyISAM-table '/usr/local/mysql/data/db2/payroll.MYI'
Data records: 4

If you still have no luck, it usually means that the table's index and/or description files are corrupt. Typically, the only way around this is to create new, empty files and con MySQL into using the new "good" files instead of the original "bad" ones. This process is quite complex and thoroughly documented in the MySQL manual and mailing lists, so I won't repeat it here—suffice it to say that it's worked like a charm on the few occasions I've had to use it. Most of the time, you won't need to go this far, as the regular —recover option will fix most common corruption problems.

Quick tip: If your MySQL tables get corrupted because of frequent server crashes, it could be because of insufficient disk space available to the server. Try increasing the disk quota limits for the "mysql" user and see if the problem goes away.

As noted previously, you can also run check and repair operations from a MySQL prompt, via the CHECK TABLE and REPAIR TABLE commands. In this case, however, the server must not be taken down. Instead, since these commands can only be sent to the server via a client, the server must be running throughout the process. To avoid other users accessing the table from another client on the network, you can shut down and restart mysqld with the —skip-networking option, which tells MySQL not to accept any TCP/IP connections from the network, so you can work on it alone.

Consider the following example of running a CHECK TABLE command:
mysql> CHECK TABLE articles;
| Table        | Op    | Msg_type | Msg_text |
| db1.articles | check | status   | OK       |
1 row in set (0.02 sec)

You can add the FAST, MEDIUM, and EXTENDED keywords to the command to obtain behavior similar to that available in myisamchk. For example:
mysql> CHECK TABLE articles EXTENDED;
| Table        | Op    | Msg_type | Msg_text |
| db1.articles | check | status   | OK       |
1 row in set (0.02 sec)

When it comes to repairing tables, too, MySQL offers the REPAIR TABLE option, as below:
mysql> REPAIR TABLE articles;
| Table        | Op     | Msg_type | Msg_text |
| db1.articles | repair | status   | OK       |
1 row in set (0.01 sec)

As with the myisamchk tool, the REPAIR TABLE command can take one of two additional options: QUICK, which tries a quick repair, and EXTENDED, which rebuilds the index after reading each record in the table.

An option here is to use the mysqlcheck utility, which provides a command line front end to the CHECK TABLE and REPAIR TABLE commands, allowing you to check and repair tables without first taking the server down. Everything that you can do with CHECK TABLE and REPAIR TABLE can be done with mysqlcheck, by passing appropriate command line options to the program.

Final thoughts
Since MySQL's tools are quite sophisticated and will do most of the work for you, there isn't too much else for you to do when it comes to salvaging data from your tables. Most of the time, the myisamchk tool works very well and will get your data back; for the more extreme cases, the manual suggests some last-ditch options.

What you should take away from this tutorial, though, is one important note: While crash recovery is fine and MySQL's tools are sophisticated enough to get you out of most jams, prevention is always better than a cure. So create a backup schedule for your data, and follow it rigorously. Backing up your data on a daily, weekly, or monthly basis (depending on how often it changes) is the first step to insuring yourself against data loss.

Editor's Picks