How to repair a crashed MySQL table?

Causes of crashed MySQL table:

The MySQL tables fail to respond or crash due to a number of reasons like

- Your system becomes out of space.
- Storage devices where the MySQL database is saved have some issues.
- The system shut down while working on the tables in MySQL.
- Software bugs on your MySQL Server.
- Your application suddenly stopped.

Check Crashed MySQL table for corruption

If you face any scenarios which indicate the crashed table in the error message, first check that MySQL table for corruption. You can use the SQL-based command CHECK TABLE command or a command-line utility, mysqlcheck, to check tables for corruption. These commands can help you check the crashed MySQL tables for corruption errors. The CHECK TABLE command checks only specific tables in the MySQL database. To check the partitioned tables using CHECK TABLE, you are required to add an Alter statement in it. Here is the syntax:

CHECK TABLE tbl_name [, tbl_name] ... [option] ...

option: {

    FOR UPGRADE

  | QUICK

  | FAST

  | MEDIUM

  | EXTENDED

  | CHANGED

}

To run mysqlcheck Utility to check MyISAM table, here is a syntax:

mysqlcheck [options] db_name [tbl_name ...]

Insert –check in place of options.

Methods to repair a crashed MySQL Table:

1-Use the mysqlcheck utility

If your server is running, then you can use the mysqlcheck utility to repair the MySQL table. It uses the REPAIR TABLE statement. The syntax is

mysqlcheck [options] db_name [tbl_name ...]

Write --repair to repair the tables using mysqlcheck utility.

2-Use Dump and Reload method

If your server is not running and your tables are created in InnoDB engines, then use the force-recovery option to enable the server. Here are the steps to do so:

- Go to the configuration file on your system. The configuration file (my.cnf) is usually present in this /etc/mysql/my.cnf. Path on your windows. Once you locate to it, then change the value of innodb_force_recovery=1 and then restart your MySQL. After this, you would be able to access your crashed or corrupted table. Next, you can utilize the dump and reload method to rebuild the corrupt MySQL tables.

- The command to dump the table is below:

mysqldump -u user -p database_name table_name > single_dbtable_dump.sql

- Next, export all the databases to the dump.sql file by executing the below command.

mysqldump --all-databases --add-drop-database --add-drop-table > dump.sql

- Restart the MySQL Server and then use the DROP DATABASE command to drop the database.

Once you are done with this, go to the same configuration file and then comment the innodb_force_recovery value to #. Save and restart the application and your system.

3-Use Myisamchk command

If the corrupt table is created in MyISAM engine then repair the MySQL table using myisamchk command. First, stop the MySQL Server and execute the below command:

myisamchk –recover TABLE-Name

Once the table is recovered, restart the server.

Easy Method to repair crashed MySQL Table-Use a Professional MySQL Repair tool

To ease the process of recovering MySQL tables, you can use Stellar Repair for MySQL. It is an advanced MySQL data recovery tool that helps you repair and recover all data from MySQL tables in various situations like hard-drive failure, system crash, etc. Apart from this, it can even recover deleted data from both InnoDB and MyIsam tables. The tool also supports data repair in the Linux operating system. It can help resolve various issues associated with corruption in any of the objects in MySQL database.

Conclusion

When the MySQL tables are corrupted, damaged, or inaccessible after any disaster, you can run the repair methods discussed in the above article. Innodb_force_recovery mode can help you open the database while the MySQL server is not responding. However, if you want a single reliable tool to repair both InnoDB and MyISAM tables with complete integrity, then use a professional MySQL repair tool. The Stellar Repair for MySQL is one of the expert choices for repairing and recovering data in MySQL tables quickly and accurately.