![\"How](\"https:\/\/linuxadmin.io\/wp-content\/uploads\/2017\/11\/repair-myisam-tables-mysql.png\")
<\/p>\n<\/p>\n
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.<\/p>\n
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:<\/p>\n
MariaDB [(none)]> show variables like ‘%log_error%’;
\n+—————+——————————–+
\n| Variable_name | Value |
\n+—————+——————————–+
\n| log_error | \/var\/lib\/mysql\/centos7-vm2.err |
\n+—————+——————————–+
\n1 row in set (0.01 sec)<\/p>\n
You can then cat that log<\/p>\n
cat \/var\/lib\/mysql\/centos7-vm2.err|grep -i crashed<\/p>\n
This will return any crashed tables that have been logged. Another way to check all of the tables is to used the mysqlcheck binary<\/p>\n
mysqlcheck -A<\/p>\n
will check for all crashed tables<\/p>\n
# mysqlcheck -A
\nmysql.columns_priv OK
\nmysql.db OK
\nmysql.event OK
\nmysql.func OK
\nmysql.help_category OK
\nmysql.help_keyword OK
\nmysql.help_relation OK
\nmysql.help_topic OK
\nmysql.host OK
\nmysql.ndb_binlog_index OK
\nmysql.plugin OK
\nmysql.proc OK
\nmysql.procs_priv OK
\nmysql.proxies_priv OK
\nmysql.servers OK
\nmysql.tables_priv OK
\nmysql.time_zone OK
\nmysql.time_zone_leap_second OK
\nmysql.time_zone_name OK
\nmysql.time_zone_transition OK
\nmysql.time_zone_transition_type OK
\nmysql.user OK
\ntest.Persons OK
\ntest.tablename OK
\ntest.testtable OK<\/p>\n
Lastly you can check a table directly through MySQL as well:<\/p>\n
MariaDB [test]> check table testtable;
\n+—————-+——-+———-+———-+
\n| Table | Op | Msg_type | Msg_text |
\n+—————-+——-+———-+———-+
\n| test.testtable | check | status | OK |
\n+—————-+——-+———-+———-+
\n1 row in set (0.00 sec)<\/p>\n
Once you have located the table in need of repair you can repair it directly through MySQL. Once connected type \u2018use databasename\u2019 substituting the real database name that contains the crashed table:<\/p>\n
MariaDB [(none)]> use test
\nDatabase changed<\/p>\n
After that all you need to do is type \u2018repair table tablename\u2019 substituting \u2018tablename\u2019 with the name of the crashed table:<\/p>\n
MariaDB [test]> repair table tablename
\n-> ;
\n+—————-+——–+———-+———-+
\n| Table | Op | Msg_type | Msg_text |
\n+—————-+——–+———-+———-+
\n| test.tablename | repair | status | OK |
\n+—————-+——–+———-+———-+
\n1 row in set (0.00 sec)<\/p>\n
You can do this quickly by using mysqlcheck with the following command<\/p>\n
mysqlcheck -A –auto-repair<\/p>\n
You will see each table followed by a status<\/p>\n
# mysqlcheck -A –auto-repair
\nmysql.columns_priv OK
\nmysql.db OK
\nmysql.event OK
\nmysql.func OK
\nmysql.help_category OK
\nmysql.help_keyword OK
\nmysql.help_relation OK
\nmysql.help_topic OK
\nmysql.host OK
\nmysql.ndb_binlog_index OK
\nmysql.plugin OK
\nmysql.proc OK
\nmysql.procs_priv OK
\nmysql.proxies_priv OK
\nmysql.servers OK
\nmysql.tables_priv OK
\nmysql.time_zone OK
\nmysql.time_zone_leap_second OK
\nmysql.time_zone_name OK
\nmysql.time_zone_transition OK
\nmysql.time_zone_transition_type OK
\nmysql.user OK
\ntest.Persons OK
\ntest.tablename OK
\ntest.testtable OK<\/p>\n
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.<\/p>\n
Nov 9, 2017LinuxAdmin.io<\/p>\n