Mac OS X,  Mac OS X Server,  SQL,  Xsan

Fix Table Corruption In MySQL

Corruption happens. Sometimes, it’s little things that cause problems. With MySQL, the mysql command line tool has long held the answer for easy corruption issues. There are a number of tools to repair corruption, but the place to start is the REPAIR command within that trusty mysql command line tool.

To start, let’s try a backup. In this case, I’m going to use a tool those of us who deal with Media Assets frequently tinker with, CatDV. I’m going to backup the databases with a simple mysqldump command, defining the user and then piping the data out to some backup file, which in this case is catdvbak on the desktop:

mysqldump -u catdvadmin -pcatdv catdv > ~/Desktop/catdvbak

If this fails due to corruption then I personally like to stop my databases and back it up flat before I make any changes to it, which a repair command will of course do. Then, we’ll need to tap into mysql:

mysql -P 1099 -p

Then, we will be in an interactive mysql environment. Let’s just say the auditLog in the catdv database is corrupt. First, select the database:

use catdv;

Then, repair that table:

REPAIR TABLE auditLog;

Note: You’ll need to quote things if the name of your table isn’t quite so simple and has special characters.

Then try and re-run your backup if it didn’t complete and you should be good to go! If the repair doesn’t go swimmingly, check out myisamchk for more detailed options.