لطفا منتظر باشید ...
Checking and Repairing MySQL Data Files
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.
