How to Repair MySQL Databases and Tables?

When MySQL database and tables become damaged or corrupt, the first thing you should do is determine the possible reasons behind corruption. Following are some of the common reasons behind MySQL database corruption:

• Faulty hardware
• Bugs in MySQL software
• Abnormal termination of MySQL service
• Server crash due to insufficient memory or corrupted MySQL data files/index files

How to Identify Corruption in MySQL Database and Tables?

When corruption occurs in a MySQL database and its tables, you can find relevant information about different database issues you may encounter by checking the MySQL error log. You can find the error log in the “data directory specified in my.ini file”. The default location of data directory in Windows is as follows:

C:\Program Files\MySQL\MySQL Server 8.0\data or C:\ProgramData\MySQL

Note: By default, the “C:\ProgramData” directory is hidden. You’ll need to change the folder options to view the directory and contents.

To find error log in other platforms (such as Ubuntu), check variable log_error.

Note: For more details on MySQL error log, refer to this link.

To identify table corruption, run any of these commands:

• If your server is still running and you’re using MyISAM/InnoDB database engine, use the CHECK TABLE statement to check a single or multiple tables for errors. The syntax of CHECK TABLE command is:

CHECK TABLE tablename [,tablename2...] [option][,option2...]

For more in-depth checking of a table, use the following available options:

Table 1 - CHECK TABLE Checking Options

• Another command you can use to check a table for errors is mysqlcheck. The syntax of mysqlcheck command is as follows:

The options that pertain to checking a table are listed as below:

Table 2 - mysqlcheck Table Checking Options

• If your server is down or the database tables are inaccessible, run the myisamchk command. The syntax is:

The following table lists the options to check table:

Table 3 - myisamchk Table Checking Options

Note: If the status of the table is OK (as you can see in the code snippet below), there is no need to fix the table.

+--------------------+--------+-------------+--------------+

| Table                | Op     | Msg_type | Msg_text  |

+---------------------+--------+--------------+-------------+

| databasetable1 | check | status        | OK          |

+---------------------+--------+------------=-+-------------+

But, if the status is not OK, it means that the table is corrupted and needs to be repaired.

Repairing MySQL Database and Tables

When a MySQL database and its tables get corrupted, restore the database from the last known good backup. But you must use an updated backup to bring the db online.

Even if you have the latest backup available, you might want to attempt repair and recovery of MySQL database and tables, as it may take less time to bring the database online.

Manual Methods to Repair MySQL Database and Tables

Manual Methods to Repair MySQL Database

Method 1 – Use the REPAIR TABLE Method

Note: You can repair only MyISAM, ARCHIVE, and csv tables by using the REPAIR TABLE command.

If you have identified corruption in your MyISAM database table, try repairing the table by using the REPAIR TABLE statement. The syntax to repair a single table is:

Note: Replace tablename with the name of your corrupted database table.

Execute the following MySQL command to repair all tables in a database:

Refer to the following table to check REPAIR TABLE options:

Table 4 – REPAIR TABLE Options

You can also use the mysqlcheck command-line utility to repair MyISAM database tables. The utility provides command-line access to the REPAIR TABLE statement. Run the following command to repair a specific table from the command line:

 To repair all tables in a MySQL database, use the following command:

Method 2 – Use MySQL Built-in Repair Options

MySQL provides two built-in options to repair the database tables for corruption:

2.1. Use the myisamchk Command Line Utility

Note: Before running the myisamchk command, make sure to stop the MySQL server using the command “service mysqld stop”. This is important to prevent users from accessing the corrupt table(s) while you are working on them.

Run myisamchk with ‘--recover’ option to recover a MyISAM table:

The ‘--recover’ option is the default option to perform recovery of MyISAM table. If this option fails, try running the myisamchk command with --safe-recover option:

The --safe-recover option is slower than the default recovery option, as MySQL scans through each record in the data file one at a time and then restores the index. After repairing tables with myisamchk, restart your MySQL server.

2.2. InnoDB Crash Recovery

To repair MySQL InnoDB table, try running the InnoDB recovery process. Follow these steps to repair a corrupt InnoDB table:

Step 1: Find and open configuration file (my.cnf) on MySQL server.

Step 2: Find [mysqld] section, and then add the following statement:

Note: The value of innodb_force_recovery by default is ‘0’, but you may need to change the value to ‘1’ for starting the InnoDB db engine and dump the tables. You may need to increase the innodb_force_recovery value to 4 or higher to dump your tables; but it involves data loss risk.

Step 3: When you’re able to start your database, export all of the databases to the dump.sql file:

Step 4: Now restart your server and drop a single affected database or all the databases using the DROP DATABASE command.

Note: If the database cannot be dropped, stop your mysql server, and then try deleting the database manually by running these commands:

Step 5: Once the db is dropped, disable the InnoDB recovery mode. To do so, comment the following line in [mysqld]:

Step 6: Save the my.cnf file, and then start your MySQL server again.

Performing these steps will hopefully help you recover the InnoDB table. If you fail to perform the recovery, use a professional MySQL database repair software to repair your database and recover all its data.

Quick Solution to Repair Corrupt MySQL Database and Tables

Troubleshooting the corrupted database and tables can take significant time and may not yield expected results. You may encounter data inconsistency errors. Use Stellar Repair for MySQL software to quickly repair your corrupt MySQL database and recover all the tables and other objects, keeping the data intact. The software performs the repair in a few simple steps and doesn’t require writing multiple lines of code.

Some key features of MySQL repair software from Stellar® are as follows:

• Repairs both MySQL InnoDB and MyISAM database tables
• Repairs corrupt MySQL database on both Windows & Linux systems
• Recovers all database objects, including tables, keys, views, triggers, etc.
• Repair multiple corrupt MySQL databases files in a single process
• Previews all recoverable MySQL database objects

Steps to Repair MySQL Database and Tables using Stellar Repair for MySQL Software

Step 1: Run the Stellar Repair for MySQL software. The main interface opens with Select Data Folder dialog box. Select the MySQL version you are using, and then browse the database file you want to repair. Click OK.

Figure 1 - Select MySQL Version

Step 2: From the Select Database dialog box, select a single or all the databases you want to repair.

Figure 2 - Select Databases to be Repaired

Step 3: Click Repair to start repairing your corrupted MySQL db. 

Step 4: When the Repair Complete message box appears, hit the OK button.

Step 5: The preview window displays all the recoverable database tables and other components in the left pane. Click on a table to view its contents in the right pane.

Figure 3 - Preview of Recoverable MySQL Database Objects

Step 6: Select the tables you want to recover from the preview window, and then choose Save on the File menu.

Step 7: In the Save Database dialog box, do the following:

• Select MySQL under Save As
• For saving the repaired MySQL database online, connect to your MySQL server by specifying the required details under Connect to Server.
• Click Save.

Figure 4 - MySQL Database File Saving Options

Step 8: When the Save Complete message box pops-up, click OK.

Figure 5 - Save Complete Message Box

The repaired database and tables will now be saved.

Conclusion

You can manually repair corrupted MySQL database and tables by following the step-wise instructions explained in this article. However, troubleshooting the corrupted database manually may take significant time and result in data inconsistency. But using Stellar Repair for MySQL software can help you quickly repair corrupted database (InnoDB and MyISAM) tables in their original format.    

Was this article helpful?

YES0

NO0

About The Author

Charanjeet Kaur

Technical writer with over 7 years of experience