MySQL Database Repair

Database Page Corruption on Disk or a Failed File Read of a Page in MySQL


Table of Content

    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.

    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.

    Was this article helpful?

    No NO

    About The Author

    Bharat Bhushan linkdin

    Bharat Bhushan is an experienced technical Marketer working at Stellar Data Recovery - expertise in data care. He is skilled in Microsoft Exchange Database, MSSQL Database troubleshooting & data warehousing. He is a Management Post Graduate having a strong grip in Technology & certified in SAP-SD, Oracle 10g & Informatica Powercenter 9.1.

    Leave a comment

    Your email address will not be published. Required fields are marked *

    Image Captcha
    Refresh Image Captcha

    Enter Captcha Here :

    Related Posts

    WHY STELLAR® IS GLOBAL LEADER

    Why Choose Stellar?

    • 0M+

      Customers

    • 0+

      Years of Excellence

    • 0+

      R&D Engineers

    • 0+

      Countries

    • 0+

      PARTNERS

    • 0+

      Awards Received