In power outages, situations where you need to kill -9 the MySQL server process, or when Jess's friend idiot@%.host.net kicks the plug out of the wall, there is a risk that the MySQL data files may be damaged. This situation can arise if the server is in the middle of making changes to the files at the time of the disturbance, as the files may be left in a corrupt or inconsistent state. Since this type of damage can be subtle, it can go undetected for days, weeks, or even months. As a result, by the time you do finally discover the problem, all your backups may contain the same corruption.
Chapter 4 of the MySQL Reference Manual describes the myisamchk utility that comes with MySQL, and how to use it to check and repair your MySQL data files. While that chapter is recommended reading for anyone who wants to set up a heavy-duty preventative maintenance schedule for their MySQL server, we'll cover all the essentials here.
Before we go any further, though, it's important to realize that the myisamchk program expects to have sole access to the MySQL data files that it checks and modifies. If the MySQL server works with the files at the same time, and makes a modification to a file that myisamchk is in the middle of checking, myisamchk might incorrectly detect an error and try to fix it—which in turn could trip up the MySQL server! Thus, to avoid making things worse instead of better, it's usually a good idea to shut down the MySQL server while you're working on the data files. Alternatively, shut down the server just long enough to make a copy of the files, and then do the work on the copies. When you're done, shut down the server again briefly to replace the files with the new ones, and perhaps apply any update logs that were made in the interim.
The MySQL data directory isn't too difficult to understand. It contains a sub-directory for each database, and each of these sub-directories contains the data files for the tables in the corresponding database. Each table is represented by three files, which have the same name as the table, but three different extensions. The tblName.frm file is the table definition, which keeps track of which columns are contained in the table, and their type. The tblName.MYD file contains all the table data. The tblName.MYI file contains any indexes for the table. For example, it might contain the look-up table that helps the table's primary key column speed up queries that are based on this table.
To check a table for errors, just run myisamchk (in the MySQL bin directory) and provide either the location of these files and the name of the table, or the name of the table index file:
shell%myisamchk /usr/local/mysql/var/dbName/tblName shell%myisamchk /usr/local/mysql/var/dbName/tblName.MYI
Either of the above will perform a check of the specified table. To check all tables in the database, use a wild card:
shell%myisamchk /usr/local/mysql/var/dbName/*.MYI
And to check all databases in all tables, use two:
shell%myisamchk /usr/local/mysql/var/*/*.MYI
Without any options, myisamchk performs a normal check of the table files. If you suspect problems with a table and a normal check fails to turn up anything, you can perform a much more thorough (but also much slower!) check using the --extend-check option:
shell%myisamchk --extend-check /path/to/tblName
Checking for errors is non-destructive, which means that you don't have to worry that you might make an existing problem worse if you perform a check on your data files. Repair operations, on the other hand, while usually safe, will make changes to your data files that cannot be undone. For this reason, I strongly recommend that you make a copy of any damaged table files before you attempt to repair them. As usual, make sure your MySQL server is shut down before you make copies of live data files.
There are three types of repair that you can use to fix a problem with a damaged table. These should be tried in order with fresh copies of the data files each time (i.e. don't try the second recovery method on a set of files that result from a failed attempt of the first recovery method). If at any point you get an error message that indicates that a temporary file can't be created, delete the file to which the message refers and try again—the offending file is a remnant of a previous repair attempt.
The three repair methods can be executed as follows:
shell%myisamchk --recover --quick /path/to/tblName shell%myisamchk --recover /path/to/tblName shell%myisamchk --safe-recover /path/to/tblName
The first is the quickest, and fixes the most common problems; the last is the slowest, and fixes a few problems that the other methods do not.
If these methods fail to resurrect a damaged table, there are a couple more tricks you can try before you give up:
If you suspect that the table index file (*.MYI) is damaged beyond repair, or even missing entirely, it can be regenerated from scratch and used with your existing data (*.MYD) and table form (*.frm) files. To begin, make a copy of your table data (tblName.MYD) file. Restart your MySQL server and connect to it, then delete the contents of the table with the following command:
mysql>DELETE FROM tblName;
This command doesn't just delete the contents of your table; it also creates a brand new index file for that table. Log out and shut down the server again, then copy your saved data file (tblName.MYD) over the new (empty) data file. Finally, perform a standard repair (the second method above), and use myisamchk to regenerate the index data based on the contents of the data and table form files.
If your table form file (tblName.frm) is missing or damaged beyond repair, but you know the table well enough to reproduce the CREATE TABLE statement that defines it, you can generate a new .frm file and use it with your existing data file and index file. If the index file is no good, use the above method to generate a new one afterwards. First, make a copy of your data and index files, then delete the originals, and remove any record of the table from the data directory.
Start up the MySQL server and create a new table using the exact same CREATE TABLE statement. Log out and shut down the server, then copy your two saved files over top of the new, empty files. The new .frm file should work with them, but perform a standard table repair—the second method above—for good measure.