Wednesday, October 5, 2011

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
Post a Comment