It is, unfortunately, not rare to experience MySQL table corruption on our production server. Fortunately the tools included with MySQL should be more than capable of correcting any problems. You’ll need a MySQL account with full access. You may also additionally need an account on the system and the ability to run as root (sudo).
Fix MyISAM tables through PHPMyAdmin
If you know which tables are corrupt you can repair them easily through PHPMyAdmin (PMA). There are a couple of methods of checking tables through PMA. If you’re in the database view check the box next to the tables and select “Check Tables” or “Repair Tables” from the drop down below the table list. If you’re in the table view, click on the “Operations” tab and scroll to the bottom of the page where you will find links for checking and repairing the current table.
Fix MyISAM tables from within MySQL
PMA uses certain SQL statements to check and repair tables. These can also be run directly from the MySQL command prompt, the MySQL GUI tools, or as SQL statements from within PMA.
I don’t have any quick advice yet for this method. See the MySQL documentation for now.
Fix MyISAM tables from the command line (online utility)
First I recommend that you stop Apache so that there is no attempt to access MySQL while you are working on it.
sudo /etc/init.d/apachectl stop
Next run the MySQL online table checker. It’ll tell you the status of the tables and, with the options specified below, fix any problems. (See the MySQL documentation for information about the options specified.)
/usr/local/mysql/bin/mysqlcheck --all-databases --auto-repair --force --password
In some cases this function can cause MySQL to crash. I’ve found that when this occurs the problem is not with MySQL but with the underlying file system. See the last section of this post for more information.
Fix MyISAM tables from the command line (offline utility)
You can also use the offline table checker. To do so you first have to stop MySQL
sudo /etc/init.d/mysql.server stop
Then run the offline checker as root. I recommend using su to switch to root prior to running this command (I found that when using sudo I was having problem with table access).
/usr/local/mysql/bin/myisamchk --backup --update-state --key_buffer_size=64M --sort_buffer_size=64M --read_buffer_size=1M --write_buffer_size=1M /inet/db/*/*.MYI
Next start up the database and run the online checker again to ensure the table corruption was fixed.
sudo /etc/init.d/mysql.server start
/usr/local/mysql/bin/mysqlcheck --all-databases --force --password
Fix InnoDB Tables from the command line
While you can use the CHECK TABLES command to check the status of InnoDB tables you can not use the REPAIR TABLES command to fix any problems.
As a matter of fact, the CHECK TABLES documentations states the following: “If CHECK TABLE finds a problem for an InnoDB table, the server shuts down to prevent error propagation. Details of the error will be written to the error log.”
I don’t have any advice for fixing corrupt InnoDB tables as this is not an issue we’ve had to deal with as of yet. If a need arises a good place to start is Backing Up and Recovering an InnoDB Database in the MySQL Reference Manual.
When MySQL is not the problem
I’ve found that on occasion what appears to be table corruption may actually be file system corruption. This is most apparent when MySQL crashes as you attempt to fix a table. In these instances you must first fix the file system corruption before fixing the MySQL tables.
The quickest way to correct file system corruption is to restart the server, forcing a file system check.
sudo /sbin/shutdown -rF now
When the server comes back up check MySQL using the procedures above.