scrambled mysql databases for breakfast

I really don't like corrupt databases. They just ruin my day and I really hate scrambled mysql databases for breakfast. However, sometimes corrupt databases are really just the result of problematic indexes. To check those indices... Stop the mysql database daemon...

/etc/init.d/mysqld stop

Don't forget to lsof and check for any hanging processes or inode access.

lsof |grep mysql

Then go to the directory in which you have your mysql database files, say

/var/lib/mysql

Within the directory, check the tables:

myisamchk *.MYI

Then repair the tables:

myisamchk -r *.MYI

After all is said and done, restart mysql...

/etc/init.d/mysqld start

A caveat, however, you need to have as much disk space free on the disk on which you have your database that you're doing your repairs. So, if you have a 50GB database, you'll need to have at least 50GB free. Oh yes. However, if you want to play it fast and loose and do a live check, use mysqlcheck.

mysqlcheck db

Then repair the database tables:

mysqlcheck -r db