Check and repair corrupted tables on MySQL database

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