Like other databases, MySQL database is also susceptible to corruption and inconsistencies issues. The database can get corrupted due to several internal and external factors. In case of corruption in the database, you may experience random corruption-related errors, like “Table ‘.\mysql\user’ is marked as crashed and should be repaired”, “Index for table ‘.\mysql\user’ is corrupt; try to repair it,” etc. when you try to access the database. Sometimes, you even fail to open the database. In this article, we will discuss the causes behind MySQL database corruption and see how to fix corruption in the database.
What Causes Corruption in MySQL Database?
Following are some common reasons behind corruption in MySQL Server database:
- Issues in the hard disk where the database is saved.
- Sudden restart of MySQL server instance.
- Bugs in MySQL code.
- MySQL process gets killed in the middle of writing data to the hard disk.
- System crashes due to sudden power failure.
- Insufficient storage space on the hard disk.
- Malware infection in the system hosting the database
Solutions to Fix MySQL Server Database Corruption
Before proceeding to the repairing process, you need to ensure whether the database is corrupted or not. You can use the CHECK TABLE command to check corruption in MySQL database. This command supports databases created in both InnoDB and MyISAM engines. If there is corruption in the MySQL database, the first option you can try is to restore the database from the last known good backup. If the database backup is obsolete or not available, then follow the below methods to repair and restore the corrupt MySQL database.
Method 1 – Use mysqlcheck Utility
mysqlcheck is a command-line utility that is used to check, repair, optimize, and analyze tables in the MySQL database. To repair the corrupt table using mysqlcheck utility, open the command-line terminal on the system hosting the MySQL Server and execute the following command:
mysqlcheck --r database_name table_name
In above command, --r is the repair option. It will try to resolve all the issues in table, except the unique keys.
Note: If you run the above command without table_name, the entire database will be
repaired.
Method 2 – Rebuild your Database
If there is corruption in MySQL database, then you can also rebuild the database. The rebuilding methods vary depending on the search engine in which the database is created.
Rebuild InnoDB Database
If you are using InnoDB search engine, then you can use the Dump and Reload method to rebuild the tables in the MySQL database. This can help in rebuilding the InnoDB tables in MySQL database and also the indexes within the tables. Here’s how:
Step 1: Restart the MySQL Service
You need to first start the MySQL service by following these steps:
- In the Run window, type services.msc.
- In the Services window, search for the MySQL Service, and right-click on it.
- Click Restart service.
Step 2: Use Innodb_force_recovery Option
When you try to access the damaged or corrupt InnoDB tables, MySQL server may crash or fail to start. If you face trouble while restarting the MySQL service, you can use the InnoDB_force_recovery option to start the MySQL service. To enable the Innodb_force_recovery option from the configuration file, follow these steps:
- Search for the configuration file (
my.cnf).
Note: The my.cnf file’s location varies based on the operating system installed. In Windows system, the configuration file is located in ‘/etc’ directory. The default path is /etc/mysql/my.cnf.
- Once you found the my.cnf file, go to the
[mysqld] section and then add the below statements:
[mysqld]
Innodb_force_recovery=1
service mysql restart
- Save and close the MySQL configuration file and then try restarting the MySQL service again.
Note: The innodb_force_recovery option is set to ‘0’ by default. To start InnoDB and dump MySQL tables, you need to set the value to ‘1’ and increase the value incrementally (from 1 to 6). Dumping tables with value of 4 or higher can lead to data loss. So, take the database backup before proceeding.
Step 3 – Dump and Reload the Table Data
Once you enable the innodb_force_recovery, you will be able to access the corrupt table. Now, dump the table data by using the mysqldump command as given 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
Now, restart the MySQL Server and use the DROP DATABASE command to delete the database. This command requires DROP privileges. So, check and grant them.
If the above command fails to drop the database, then run the below commands to delete the database manually:
cd /var/lib/mysql
rm -rf db_name
Next, disable the InnoDB recovery mode by commenting on the following line in [mysqld]:
#innodb_force_recovery=...
Now, save the applied changes to the configuration (my.cnf) file and then restart the MySQL Server.
Rebuild MyISAM Database
If you are using MyISAM search engine, then you can use myisamchk command to repair or rebuild the MySQL database. The ‘myisamchk’ command checks all the MyISAM tables and repairs them. It supports .MYD and .MYI files in tables. It also repairs data and indexes in the tables. To run the myisamchk command, follow these steps:
- Stop the MySQL Server.
- Then, use the following command to repair MyISAM table:
myisamchk –recover TABLE
For detailed information, read ‘How to repair MyISAM table by using myisamchk?’
Alternatively, you can use the REPAIR TABLE command to rebuild the MyISAM table. This command supports MyISAM, ARCHIVE, and CSV tables. To repair the corrupt MyISAM table, run the below command:
REPAIR TABLE table name;
Method 3 – Use a Third-Party MySQL Database Repair Software
If the above repair methods fail, then you can use an advanced MySQL database repair software, such as Stellar Repair for MySQL to repair the corrupt database and restore all its objects. The software can repair tables of both InnoDB and MyISAM storage engines and save them in a new database file. It can help you recover all the data, including tables and indexes, from corrupt databases with complete integrity. The tool is compatible with Windows and Linux operating systems.
Some key features of Stellar Repair for MySQL:
- Repairs MySQL database tables created using InnoDB (.frm, .ibdata, or .idb) and MyISAM (.frm, .myd, or .myi) storage engines.
- Batch repairs multiple MySQL databases in a single process.
- Recovers all the database objects, including keys, tables, table properties, data types, views, and triggers.
- Previews recoverable database objects.
- Allows to save the repaired database in multiple file formats, such as MySQL, CSV, HTML, and XLS.
- Supports MySQL 8.x, 6.x, 5.x, 4.x, and 3.x.
End Notes
MySQL database can get corrupted due to several reasons. The repair methods discussed in this post can help you to easily and effectively repair the corrupted database. In case of severe corruption, you can use Stellar Repair for MySQL. It is a purpose-built MySQL repair software that can easily repair both InnoDB and MyISAM tables with complete precision and integrity. It can help you restore all the database objects, such as tables, indexes, log system tables, etc., in the original format. The software supports both Windows and Linux systems.
