MyISAM Repair all MySQL Databases & Tables
So you’ve probably not made the switch to InnoDB or XtraDB yet, shame on you! But tonight your server crashed, ran out of disk space or otherwise corrupted all of your active tables across various databases. Ouch! How are we going to fix this one?
Many admins try using the myisamchk tool from shell in an attempt to repair the MYI files. This may or may not work. I generally don’t recommend it as a primary means to a repair, especially if you are going to continue running mysqld and attempting to use the crashed tables.
First you will want to identify weather or not tables are marked as crashed. This will be evident in the mysqld log file, typically in /var/lib/mysql, /var/log/, /var/log/mysql or possibly another location depending on your my.cnf. You should look for messages like:
130422 17:43:34 [ERROR] /usr/sbin/mysqld: Incorrect key file for table './socialforum/wp_posts.MYI'; try to repair it 130422 17:43:47 [ERROR] /usr/sbin/mysqld: Incorrect key file for table './socialforum/wp_options.MYI'; try to repair it
Alternatively you can also look at table status via the mysql CLI.
So now that we know we have indeed experienced corruption, we need to do something about it. If you have just one corrupt table you could fix it via the mysql CLI like so:
root@server [/var/lib/mysql]# mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1691 Server version: 5.0.96-community-log MySQL Community Edition (GPL) Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> REPAIR TABLE socialforum.wp_posts; +-------------------------+--------+----------+----------+ | Table | Op | Msg_type | Msg_text | +-------------------------+--------+----------+----------+ | socialforum.wp_posts | repair | status | OK | +-------------------------+--------+----------+----------+ 1 row in set (0.00 sec) mysql> exit Bye root@server [/var/lib/mysql]#
That should repair the table and stop the errors. However, what if you have a TON of tables, let’s say hundreds across several databases? What do we do then? We can’t just sit around entering each repair command manually. No way!
What we will do instead is cycle through each MyISAM table across all database and repair those tables via a bash script.
First you will want to ‘cd’ into your mysql directory that has all the databases. Typically it’s /var/lib/mysql:
root@server [~]# cd /var/lib/mysql root@server [/var/lib/mysql]#
Now let’s run this one-liner, note that it finds all the files, greps for MYI (indicating a MyISAM table) figures out the db name ($1) and table name ($2). I recommend first removing the while loop at the end and making sure that each statement makes sense before running it with the while loop:
find . | grep MYI | cut -d / -f 2,3 | cut -d . -f 1 | awk -F "/" '{print "REPAIR TABLE " $1 "." $2 ";"}' | while read LINE; do mysql -e "$LINE"; done
Note it may take a while to run especially with very large tables. However once finished you will be error free. Make sure you have enough disk space for the repair, if you had corruption due to running out of disk space you need to free space first before repairing.