Summary: In this blog, we will learn why database page corruption in MySQL occurs. Also, we will learn how to check database page corruption and how to use different commands to fix database corruption. We will also mention a third-party MySQL repair tool that can easily and quickly repair corrupt databases.
Contents
One of the important tasks of IT administrators is to constantly verify and check the hard disks. If there is any issue, they need to replace the hard drives to make sure that the database is fine.
If the hard disk gets damaged, it may affect the pages in MySQL. The pages in MySQL are units to store data in databases. Other factors that can affect the hard drive are software problems and malware attacks on the system.
Verify MySQL Database Page using the CHECKSUM Command
CHECKSUM is a command in MySQL that can be used to verify if the database is fine. This command checks the integrity of the database.
The following example shows how to use the CHECKSUM command:
CHECKSUM TABLE StellarTable
Note: This function is slow because it works row-by-row.
Verify MySQL Database Page using the Mysqlcheck Command
Mysqlcheck command not only works with a single table but also with the entire database. With this command, you can not only check but also repair a database. The following example shows how to check a database named stellardb using this command.
mysqlcheck --check stellardb
Optionally, you can use the -c option (see the below example).
mysqlcheck -c stellardb
If you want to check multiple databases, you can use the following command:
mysqlcheck --check --all-databases
Note: If you have huge database or several databases, this command may take a long time.
How to repair database page corruption using the mysqlcheck command?
The following example shows how to repair a MySQL database named stellardb using the mysqlcheck command.
mysqlcheck --repair stellardb
How to repair database page corruption using the repair command?
In addition, you can use the REPAIR command to repair a specific table in MySQL. The following example illustrates how to repair a table named StellarTable using this command:
REPAIR TABLE StellarTable;
The REPAIR TABLE command has multiple options. The first option is to run Quick. This is a fast repair option. The following example shows the faster way to repair the table using the REPAIR TABLE command:
REPAIR TABLE StellarTable quick;
You can also use the EXTENDED option. This option is slower. Use this option, if the damage is severe and the QUICK option did not work.
REPAIR TABLE StellarTable EXTENDED;
In addition, there is recover option which is used for crashed databases.
REPAIR TABLE StellarTable RECOVER;
The USE_FRM option can be used to use the file with frm extension to rebuild the table structure. If your table is corrupted and the frm file is fine, this option will work.
The frm files contain the table definition of a table.
REPAIR TABLE StellarTable USE_FRM;
Finally, there is FORCE option that continues repairing even if there are errors during the execution. Some errors that are ignored by the FORCE command are duplicated keys, record-size errors, problems with the index merge, keys out of order, and connection loss.
REPAIR TABLE StellarTable FORCE;
The Log File
The error.log can be used to verify database corruption problems.
The following command will show you the location of the error.log.
SHOW VARIABLES LIKE 'log_error';
Once you are there, search for phrases like page corruption and page wrong in the file.
Use Stellar Repair for MySQL
If the above options fail to fix the corruption, then you can use a third-party software, such as Stellar Repair for MySQL – a software specialized in repairing corrupt databases.
This software works for MySQL and MariaDB. It is compatible with Windows OS (servers and workstations) and also the following Linux versions:
- CentOS Red Hat Linux
- Ubuntu
The software requires an offline copy of the database file. You may need to stop the MySQL Service and create a copy. After that, the software scans and repairs the file. It can repair InnoDB and MyISAM databases. The software can rebuild the database or you can store the recovered information in different formats, like XLS, CSV, and HTML. Optionally, you can generate scripts.
Conclusion
In this article, we explained different options to check database page corruption. In addition, we learned different commands to repair the database, like mysqlcheck, checksumtable, and REPAIR TABLE. We also mentioned a third-party MySQL repair tool that can be used to repair the database in case the other options fail.
