How to check and repair myisam tables in mysql

[RhetoricalJudo]

MySQL tables can become corrupt for a variety for reasons such as incomplete writes, running out of space, the MySQL daemon being killed or crashing, power failures. If MySQL detects a crashed or corrupt table it will need to be repaired before it can be used again. This guide will walk you through detecting crashed tables and how to repair MyISAM tables.



FIND CRASHED MYISAM TABLES IN MYSQL


Usually a table will show as corrupt in the mysql log, to locate the location of the log, you will be able to find it in my.cnf or you can view it directly in mysql by the following:

MariaDB [(none)]> show variables like '%log_error%';
 +---------------+--------------------------------+
 | Variable_name | Value |
 +---------------+--------------------------------+
 | log_error | /var/lib/mysql/centos7-vm2.err |
 +---------------+--------------------------------+
 1 row in set (0.01 sec)

You can then cat that log

cat /var/lib/mysql/centos7-vm2.err|grep -i crashed

This will return any crashed tables that have been logged. Another way to check all of the tables is to used the mysqlcheck binary

mysqlcheck -A

will check for all crashed tables

# mysqlcheck -A
 mysql.columns_priv OK
 mysql.db OK
 mysql.event OK
 mysql.func OK
 mysql.help_category OK
 mysql.help_keyword OK
 mysql.help_relation OK
 mysql.help_topic OK
 mysql.host OK
 mysql.ndb_binlog_index OK
 mysql.plugin OK
 mysql.proc OK
 mysql.procs_priv OK
 mysql.proxies_priv OK
 mysql.servers OK
 mysql.tables_priv OK
 mysql.time_zone OK
 mysql.time_zone_leap_second OK
 mysql.time_zone_name OK
 mysql.time_zone_transition OK
 mysql.time_zone_transition_type OK
 mysql.user OK
 test.Persons OK
 test.tablename OK
 test.testtable OK

Lastly you can check a table directly through MySQL as well:

MariaDB [test]> check table testtable;
 +----------------+-------+----------+----------+
 | Table | Op | Msg_type | Msg_text |
 +----------------+-------+----------+----------+
 | test.testtable | check | status | OK |
 +----------------+-------+----------+----------+
 1 row in set (0.00 sec)

REPAIR A SINGLE MYISAM TABLE


Once you have located the table in need of repair you can repair it directly through MySQL. Once connected type ‘use databasename’ substituting the real database name that contains the crashed table:

MariaDB [(none)]> use test
 Database changed

After that all you need to do is type ‘repair table tablename’ substituting ‘tablename’ with the name of the crashed table:

MariaDB [test]> repair table tablename
 -> ;
 +----------------+--------+----------+----------+
 | Table | Op | Msg_type | Msg_text |
 +----------------+--------+----------+----------+
 | test.tablename | repair | status | OK |
 +----------------+--------+----------+----------+
 1 row in set (0.00 sec)

CHECK AND REPAIR ALL MYISAM TABLES


You can do this quickly by using mysqlcheck with the following command

mysqlcheck -A --auto-repair

You will see each table followed by a status

# mysqlcheck -A --auto-repair
 mysql.columns_priv OK
 mysql.db OK
 mysql.event OK
 mysql.func OK
 mysql.help_category OK
 mysql.help_keyword OK
 mysql.help_relation OK
 mysql.help_topic OK
 mysql.host OK
 mysql.ndb_binlog_index OK
 mysql.plugin OK
 mysql.proc OK
 mysql.procs_priv OK
 mysql.proxies_priv OK
 mysql.servers OK
 mysql.tables_priv OK
 mysql.time_zone OK
 mysql.time_zone_leap_second OK
 mysql.time_zone_name OK
 mysql.time_zone_transition OK
 mysql.time_zone_transition_type OK
 mysql.user OK
 test.Persons OK
 test.tablename OK
 test.testtable OK

This command will attempt to check and repair all MySQL tables in every database on the server. That is it for repairing MyISAM tables in MySQL.