• Home
  • About Us
  • Contact
  • Privacy Policy

LinuxAdmin.io

  • Home
  • Tutorials
    • System Administration
    • Linux Tutorials
    • Web Hosting
    • WordPress
    • Virtualization
    • Troubleshooting
    • OpenStack

How To Check And Repair MyISAM Tables In MySQL

How To Repair MyISAM tables in mysql

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
0 0 vote
Article Rating
Mod_Expires Configuration In ApacheConfigure ProFTPd for SFTP on CentOS
You Might Also Like
 
Block Countries With Nginx and GeoIP
 
Laravel Installation Guide For CentOS
Subscribe
Notify of
guest
guest
0 Comments
Inline Feedbacks
View all comments
5 years ago Linux System Administration, MySQL 3,247
Recent Posts
  • Laravel Installation Guide For CentOS
  • Openstack Services Explanation And Overview
  • OSSEC Intrusion Detection Installation On Centos 7
  • Configure ProFTPd for SFTP on CentOS
  • How To Check And Repair MyISAM Tables In MySQL
Most Commented
Hot Clone Linux Server with Rsync
Hot Clone A CentOS Server With Rsync
14 Comments
ngx_cache_purge module
Install The ngx_cache_purge Module In Nginx
8 Comments
piwik-nginx
Piwik Analytics on Nginx
8 Comments
Tags
linuxcentosLinux Performance Tuningsysadminkvmnetworkingmemcachedanalyticssystemd
About

We love Linux and are dedicated to creating Linux administration tutorials for System Administrators since 2016.

Most Viewed
Default Gateway
How To Configure A Default Gateway on CentOS
62,983 views
Zend Opcache
Setup and Optimize Zend OpCache
46,714 views
Install ffmpeg
FFMpeg Install On CentOS 7
20,909 views
Archives
Email subscription

Sign up for our newsletter to receive the latest news and event postings.

2018 © LinuxAdmin.io
wpDiscuz