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.
Nov 9, 2017LinuxAdmin.io