How to Fix MySQL Database Corruption?

Corruption in database is not an uncommon scenario in MySQL. It can get corrupted due to various reasons. When this happens, you may fail to open the tables in the MySQL database or even fail to run queries. Just like a locked file, you can’t open or access the database file, until the corruption is resolved. For developers, this can mean downtime – being unable to access the data from the important tables, and extra work to recover everything.

Here are some of the errors reported by the MySQL users when their database gets corrupted:

• Error “Index for table ‘global_priv’ is corrupt; try to repair it” has occurred when trying to add user account.
• While using DELETE command in an InnoDB table, MySQL Server created a temp table in ibdata1, filled the disk and crashed. I deleted the 30 GB ibdata1 file to restart MySQL, but now all tables show as corrupt with error “Unknown table engine ‘InnoDB’.”

Such corruption-related errors can occur when you try to access or perform any operation on the MySQL database. In this article, we will discuss the causes of MySQL database corruption and the methods to fix it. To repair MySQL database quickly, we will mention a professional MySQL repair tool. This tool can repair InnoDB and MyISAM tables while keeping their data intact.

How to Check Corruption in MySQL Database?

First, check the affected MySQL database for corruption. To check this, the best method is to check your MySQL error log. Server adds all the information related to corruption in the MySQL database in this log. It will mention the list of threads related to the affected tables. To find this error log, you can go the MySQL configuration file. Alternatively, you can run the commands in MySQL to check for MySQL database corruption, such as:

• CHECK TABLE
• mysqlcheck command

You can use the CHECK TABLE command if you want to check for corruption in a specific table in MySQL database. Otherwise, you can use the mysqlcheck utility to check for corruption in multiple MySQL databases at once. You can use these commands only if your MySQL is running.

Methods to Repair MySQL Database

Now that you have confirmed the corruption in your database, the next step is to repair it. Follow the methods below:

Method 1: Restore your Backup File

If you have an updated backup of the MySQL database, then a simple option is to recover the backup file. You can use the mysqldump utility to restore the backup file in MySQL. To restore the dump file using the mysqldump utility, the steps are as follows:

Note: Ensure that you have all the privileges and roles granted to your MySQL account using the SHOW GRANTS statement. Also, ensure that you have the ALTER, CREATE, RELOAD, and SELECT privileges for the backup file.

• Drop and recreate your database using the following command:

mysql > drop db_namemysql > create db_name

•  Next, restore the database by executing the below command:

mysql -u root -p db_name < dump.sql

•   Now, verify if the restored database contains the correct data in the tables.

‘mysql > use db_name;mysql > show tables;’

Method 2: Use PhpMyAdmin Tool

PhpMyAdmin tool is also one of the repair tools that most of the MySQL developers use to resolve corruption-related issues. You can use it to run the REPAIR TABLE command to repair the MySQL tables. To know how to access and use the PhpMyAdmin to repair the MySQL tables.

Ghi chú: You may face time-out issues when trying to restore large-sized database file using PhpMyAdmin. 

Method 3: Use Dump and Reload Method

This method is more helpful when the corruption in MySQL database, especially in InnoDB tables, leads to MySQL Server crash. In such scenarios, you need to use the InnoDB force recovery option from the MySQL configuration file to restart MySQL. Once you’re able to start the server and access the tables, then use dump and reload method to rebuild MySQL InnoDB tables. To know the stepwise instructions to use force recovery in MySQL to repair the MySQL database.

Method 4: Use Alter Table Command

You can even force the InnoDB tables to recreate them and rebuild their data to resolve corruption issues in MySQL InnoDB tables. You can perform this by just renaming the affected InnoDB table using the ALTER TABLE command. First check the privileges for the InnoDB table and then run this command:

ALTER TABLE tbl_name ENGINE=INNODB

Method 5: Use REPAIR TABLE Command

If the tables created in the MyISAM storage engine are corrupted, then you can use the REPAIR TABLE command. Here’s how to use this command:

REPAIR [NO_WRITE_TO_BINLOG | LOCAL]

TABLE tbl_name [, tbl_name] …

[QUICK] [EXTENDED] [USE_FRM]

Ghi chú: Sometimes, you need to execute the command multiple times to prevent data overwrites, especially when the server suddenly closes down while processing this query.

You can even use the myisamchk Command to resolve the error message indicating corruption in the MyISAM table. Here’s how to use this command:

• First, stop the MySQL Server.
• Next, run the below command:

myisamchk –recover TABLE

• Now, restart the MySQL Server.

Note: It does not repair unique keys in the corrupt tables. Also, in some cases, it causes data loss as it may delete corrupted rows while performing the repair operation.

Method 6: Using a Professional MySQL Repair Tool

If the above repair methods fail and you have no backup file to restore, then you can use a professional MySQL repair tool, like Stellar Repair for MySQL. This tool is a one-stop solution to repair databases created in both InnoDB and MyISAM engines and save them to a new MySQL database file. It can even repair and restore indexes, keys, partitioned tables, etc. from the corrupt MySQL database with no data loss. It can easily repair large-sized corrupt MySQL databases quickly and in just a few simple steps.

Kết luận

Resolving corruption in MySQL database is simpler than ever. You can implement the methods outlined in this post to repair corrupt InnoDB/MyISAM tables. To repair your MySQL database without hassle, using a professional MySQL repair tool is worth the try. Stellar Repair for MySQL is one such user-friendly tool that can help you repair MySQL tables in any condition. It can repair InnoDB and MyISAM tables, including partitioned tables and foreign keys, with complete integrity.