File Repair

How to Repair MySQL Databases and Tables?

Summary: This article outlines the common reasons leading to corruption in MySQL database. It also describes using MySQL error log to identify corruption in a database and tables. Further, it explains the manual methods on ‘how to repair MySQL databases and tables?’ The article also suggests using MySQL repair software for quickly and effectively repairing corrupt db and tables without downtime. Try the demo version of the software from the below link to verify its functionality.


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

Option Name

Description

QUICK

As the name implies, this is the quickest option to check a table for corruption. It avoids scanning the rows for incorrect links. This option applies to both InnoDB and MyISAM tables and views.

FAST

It only checks MyISAM tables that are not closed properly.

CHANGED

Check MyISAM tables that have not been closed properly or have been changed since the last check.

MEDIUM

The option applies only to MyISAM tables and views. It scans rows to validate if the deleted links are correct. It also verifies a calculated checksum for keys by calculating a key checksum for the rows.

EXTENDED

It is the slowest option as it performs a full key lookup for all keys for each row. Use this option if other checks do not report any errors, but you suspect corruption in database tables.

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

mysqlcheck [options] dbname tablename [tablename2... ]

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

Table 2 - mysqlcheck Table Checking Options

Option Name

Description

--check

Check for errors in the tables

--all-databases

Check all the database tables for errors

--check-only-changed

Only check the tables that have changed since the last check

--extended, -e

Takes a long time to check tables, but ensures that a table is 100% consistent

--fast

Only check tables that are not closed properly

--medium-check

Performs a check faster compared to an --extended operation

--quick

This is the fastest option to check tables for corruption

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

myisamchk [options] tablename.MYI

The following table lists the options to check table:

Table 3 - myisamchk Table Checking Options

Option Name

Description

--check, -c

Default option to check table for errors

--check-only-changed

Check only those tables that have been changed since the last check

--extend-check

Check the tables thoroughly. This process will take time if the table has many rows.

--fast, -f

Check only the tables that have not been closed properly

--information, -i

Print statistics of only the table that is checked

--medium-check

Check tables faster than an --extend-check operation

--read-only

Do not mark the table as checked. Use this option to check a table that is in use by some other application that doesn’t use locking

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

Notes:

  • Before repairing a corrupt MySQL database table, make sure to back it up. This will prevent further data corruption.
  • If you have identified corruption InnoDB table(s), skip to InnoDB Crash Recovery.
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:

REPAIR TABLE tablename [options]

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

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

mysqlcheck --repair --databases databasename ...

Refer to the following table to check REPAIR TABLE options:

Table 4 – REPAIR TABLE Options

Option Name

Description

QUICK

This is the quickest option that repairs only the data file and not the index file

EXTENDED

Tries recovering every possible row from the data file. Use this option as a last resort as it also finds a lot of garbage rows.

USE_FRM

Use this option if the .MYI index file is missing or has a corrupt header

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:

mysqlcheck --repair databasename tablename

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

mysqlcheck --repair --all-databases

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:

myisamchk --recover tablename

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:

myisamchk --safe-recover tablename

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:

[mysqld]

Innodb_force_recovery=1

service mysql restart

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:

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

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:

cd /var/lib/mysql

rm -rf db_name

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

#innodb_force_recovery=...

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.

Free download Stellar Repair for MySQL

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.

Select MySQL Version

Figure 1 - Select MySQL Version

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

Select MySQL Database for 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.

Preview Recoverable MySQL Objects

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.

Connect to MySQL Server

Figure 4 - MySQL Database File Saving Options

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

Saving Repaired MySQL Database Complete Message Box

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.    

 

76% of people found this article helpful