Check and repair corrupted tables on MySQL database

Jack Wallen
Feb 4, 2010
Updated • Nov 28, 2012
Development
|
5

This morning I woke up to find one of the sites I administer down. The error on the page was very clear in that it could not connect to the database due to a corrupt table. I had two choices, recover from a backup or attempt to repair the corrupted tables. I opted to try the repair. In the end, it worked. And now, you will get to benefit from that success.

The initial problem was trying to figure out which table was corrupt. The database in question wasn't HUGE, by any stretch of the imagination; but it was big enough that an attempt to repair all tables would have taken quite some time. Fortunately, when I went to restart MySQL, the output of the command even helped me narrow down the corrupted table. In this article I will show you my process for repairing this corruption.

As I said, the error message I received only pointed me in the right direction. What the error message indicated was that there was corruption in the cache. This wound up only be part of the issue. But I had a starting point. So the first steps were to locate the exact name of the cache table in the database. Here's how this is done.

  1. Log into the MySQL server with the command mysql -u USER -p (Where USER is the name of the database admin user).
  2. Enter the user password when prompted.
  3. Switch to the database in question with the command use DATABASE; (Where DATABASE is the name of the database containing the corrupted table).
  4. List the tables on the database with the command show tables;
  5. Search for the table. In my case the table was actually called cache.
  6. Exit out of the MySQL command prompt with the command quite.
  7. Check the table in question for errors with the following command myisamchk /var/lib/mysql/DATABASENAME/TABLE_NAME.myi (Where DATABASENAME is the name of the database you are working with and TABLE_NAME is the name of the table to be checked.)
  8. The above command should indicate there are errors on the table. If this is the case you will need to repair them. In my case the output actually pointed me to another table mod_layout_config, so I knew I actually had to re-run the check command (show above) on the file mod_layout_config.myi.
  9. When the myisamchk command report errors, it's time to repair the table. But before you run this next command, make sure you back up that database! You do not want to run the risk of losing your entire database. To repair the table in question issue the command myisamchk --recover /var/lib/mysql/DATABASENAME/TABLE_NAME.myi (Where DATABASENAME is the name of the database you are working with and TABLE_NAME is the name of the table to be checked.)
  10. If this does not fully recover the table, you can re-run the command replacing --recover with --safe-recover which is slower and more thorough.

That should do it. You should now be able to restart your MySQL database server and your web site should be able to connect to its database. 

Advertisement

Previous Post: «
Next Post: «

Comments

  1. Santa Firr said on January 29, 2014 at 8:39 am
    Reply

    I am fully sure about that Kernel for MySQL database repair tool is a best software to repair and recover corrupt or deleted MySQL table. Software show you the preview of recovered data items such as Tables, Triggers, Views and Primary Keys in a tree like structure after restoring the corrupt MySQL database file.

  2. Castrovincino said on February 5, 2010 at 11:14 am
    Reply

    Well, myisamchk is only useful if you have MyISAM tables, if your db is built with InnoDB tables there is no use for this tool.

  3. philoSurfer said on February 5, 2010 at 8:56 am
    Reply

    Sounds like bad sectors on a drive……
    I would take a preventative look at that, before your tables go corrupt again [;-)

    They only take a dump like that if there was some serious conflict during a row level lock, or bad write (bad sector).

    thanks for the post… a reminder that i should use MySql command line more often.. im getting too reliant on PHPmySql.

Leave a Reply

Check the box to consent to your data being stored in line with the guidelines set out in our privacy policy

We love comments and welcome thoughtful and civilized discussion. Rudeness and personal attacks will not be tolerated. Please stay on-topic.
Please note that your comment may not appear immediately after you post it.