Summary: In this article, we will learn how to check if a database is corrupted or not in MySQL. We will explain different options to check corruption in MySQL database. In addition, we will mention a third-party MySQL database repair tool that can easily repair corrupt database.
Contents
You can use the error log files to check if a MySQL database is corrupt or not. Usually, if your database is installed with the default values, the location of the file is:
- In Windows : C:\ProgramData\MySQL\MySQL Server 8.0\data\hostname.err ( here, replace 8.0 with your MySQL version).
- In Linux: /var/log/mysql/error.log or the /var/log/mysqld.log
If the file is not at the default location, you can use the command line to find the file. The following example shows how to find the file in Windows or Linux:
SHOW VARIABLES LIKE ‘log_error’
Also, if a database is corrupt, you encounter different types of errors. Some typical errors that indicate that the database is corrupt are:
- Table ‘stellarTable’ is marked as crashed and should be repaired. This message indicates that the table is corrupted. You can use the “REPAIR TABLE” command to repair the table in this scenario.
- Error reading file ‘xx’ (Errcode: yy). Here xx is the file name and yy is the error number. When you receive this error, it means that there is corruption in the file.
- Corrupted page XX in block YY. The error identifies the exact block and page corrupted. If you are using MyISAM storage, you can use the myisamcheck command to repair the tables.
- Got error xxx from storage engine. Here xxx is the error number. This error means that the MySQL storage engine found problems while accessing data due to corruption.
- InnoDB: Database page corruption on disk or a failed file read. The error means that a page problem occurred in the InnoDB storage pages. If you have this problem, you can use the innodb_force_recovery option in recovery mode and repair the database.
How to check database corruption in MySQL using the myisamchk command?
You can use the myisamchk command to check corruption in databases using MyISAM storage. The following example shows how to run this command:
myisamchk Stellartable.MYI
The command checks if the table is fine. If not, some errors will be displayed, such as:
- Table is marked as crashed and should be repaired. This error means that the database is corrupted and needs to be repaired.
- Can?t create new tempfile. This error means that you do not have enough disk space to run the command that creates a tempfile.
- Table is already up to date. This message means that there are no errors.
If you want to repair it, the myisamchk command can be used. The following example shows how to do it.
myisamchk -r StellarTable
Here, StellarTable is the name of the table and -r means to repair.
If the command fails due to some errors, you can force the repair by using -o option.
How to check database corruption in MySQL using the mysqlcheck command?
Another command that can be used to check the database corruption is the mysqlcheck command. This command can be used by the MyISAM and InnoDB storages.
The following example shows how to use this command.
mysqlcheck -c stellardb
Here, -c means to check and stellardb is the name of the database.
If you want to check a specific table, you can use the following command:
mysqlcheck –databases stellardb –table stellarTable –check
This will check the table named stellarTable in the database stellarDB.
Finally, if you want to verify all the databases, the following command will be used:
mysqlcheck –all-databases –check
Note: It is not recommended to check all the databases unless they are really small because it may take a long time to execute the command.
Repair corrupt MySQL database using Stellar Repair for MySQL
You can use a third-party software, such as Stellar Repair for MySQL to repair the corrupt database. This software is compatible with the Windows Desktop editions (Windows 10, 8, 7, 11, and lower versions) and also for the Windows Server versions (2022, 2019, 2012, 2008, and earlier).
This software can repair MyISAM and InnoDB databases. It requires you to stop the MySQL Service. Once stopped, you can copy the file and repair the copy. The software can restore the database. You can also export your data to Excel, CSV, or HTML file.
Conclusion
In this article, we have explained different options to check corruption in MySQL database. You can check error log files, use the myisamchk command, or the mysqlcheck command to check corruption in database. In addition, we have mentioned a third-party tool named Stellar Repair for MySQL to repair the corrupt database.
