Checking and Repairing Corrupted Tables
Problems such as running out of disk space or a power failure could cause your databases files to be corrupted; in these cases, the server will often not have written all transactions to disk. It’s a good idea to check the tables before you start to use them again. Repairing tables will not guarantee that no data will be lost, but it does allow you to use the database again without losing any more data.
One way to check and repair tables is to use the CHECK TABLE
and REPAIR TABLE
commands from the monitor. For
example, to check the artist
table
in the music
database, you would
write:
mysql>
CHECK TABLE music.artist;
+--------------+-------+----------+------------------------------+ | Table | Op | Msg_type | Msg_text | +--------------+-------+----------+------------------------------+ | music.artist | check | error | Checksum for key: 1 doesn't | | | | | match checksum for records | | music.artist | check | error | Corrupt | +--------------+-------+----------+------------------------------+ 2 rows in set (0.00 sec)
In this example, the table is damaged; you
can repair it using the REPAIR
TABLE
command:
mysql>
REPAIR TABLE music.artist;
+--------------+--------+----------+----------+ | Table | Op | Msg_type | Msg_text | +--------------+--------+----------+----------+ | music.artist | repair | status | OK | +--------------+--------+----------+----------+ 1 row in set (0.00 sec)
If the music
database was previously selected with
the USE music
Get Learning MySQL now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.