MyISAM is the default storage engine coming with MySQL installations and commonly used. We can encounter the crash issue due to many reasons such as memory, server crash, improper development etc. We can detect it with the following error getting in MySQL log files:
[ERROR] /usr/sbin/mysqld: Table 'table_name' is marked as crashed and should be repaired
You won’t be able to run any database queries and error log gets flooded at "/var/lib/mysql/" by default. We can resolve it by using the following commands:
1. Login to MySQL and check the table with:
mysql> CHECK TABLE table_name;
here you need to replace the table_name with the actual name
If there are no crash issues, you will be getting an OK status in the output.
2. If there is a crash problem, attempt to repair it with the following command:
mysql> REPAIR TABLE table_name;
3. In most of the cases, this will work without any additional parameters. Sometimes, we get an error that the index file (.MYI) is missing or too corrupted to repair. In such cases, you can use the table definition file to repair it as described below:
mysql> REPAIR TABLE table_name USE_FRM;
4. If this does not fix the problem, we can repair the table with an external tool. Exit from the MySQL client and stop the MySQL server service:
#service mysqld stop
5. Now run the myisamchk command as follows:
#myisamchk /var/lib/mysql/mydatabase/table_name.MYI
If the data_dir variable in “/etc/my.cnf” is changed, point the command parameter as per the changes are done.
6. If you are getting errors, repair the index file by:
#myisamchk --recover /var/lib/mysql/mydatabase/table_name.MYI
Depending on the extent of the corruption, the time for the process varies, so please wait until its done.
7. Now restart the MySQL server service:
#service mysqld start
We can resolve most of the DB corrupt issues by using these methods.
