MySQL Database Repair

How to Use MySQL’s Inbuilt Tools to Repair MySQL Database?


Table of Content

    Summary: MySQL database may get damaged or corrupted due to different reasons. In this post, we will see how to use inbuilt tools in MySQL to repair the corrupt or damaged MySQL databases. We will also mention a third-party MySQL repair tool that can easily repair damaged or corrupted databases. Sometimes, problems with hardware or software can damage or corrupt the MySQL databases. However, you can use the MySQL built-in tools to fix the corrupt or damaged databases. Below, we will see how to use Mysqlcheck - a common inbuilt tool in MySQL - to repair damaged databases.

    How to Use Mysqlcheck Command in MySQL to Repair Databases?

    Mysqlcheck is a command used to maintain tables. With this command, you can check tables in a database, repair the database, optimize the performance of the tables, or analyze the tables.

    It is to be noted that this command locks the tables. Also, you need to have the MySQL Server running to use the command.

    The following command will show the status of the server in Windows:

    sc query mysqlXX

    Here XX is the MySQL version.

    The command will provide the following information:

    MYSQL Command
    • Service name is the name of the service.
    • Type WIN32_OWN_PROCESS means that this service does not depend on other ones.
    • The state of the service. In this case, it is RUNNING. It could be STOPPED or PAUSED.
    • If the WIN32_EXIT_CODE is 0, it means that the service was stopped without problems.
    • CHECKPOINT equal to 0 means that there is no checkpoint. This value is for internal use.
    • WAIT_HINT estimates the time in ms that the service will take to finish the operation.

    Let’s take a look at some examples to run the command.

    In the MySQL bin folder, in the command line, run this command:

    Mysqlcheck –repair stellardb

    This command will repair the database, named stellardb.

    The following command will compress the connection used by the command.

    mysqlcheck -r –compress stellardb

    Note: The options –repair and -r are the same.

    The below example will auto-repair the database if necessary. The auto-repair option checks the status of the tables. If there are any errors, they will be fixed.

    mysqlcheck -r –auto-repair stellardb

    The following option is used to check only the tables that were not closed in a proper way.

    mysqlcheck –fast stellardb

    If you want to debug, you can use the –debug option. The following example will create a log in the c:\log\debug.log file.

    mysqlcheck –debug=d:t:o,c:\log\debug.log stellardb

    In the above example:

    • d means to debug.
    • t means to include the timestamp in the log file.
    • o means to log into a file name.

    Finally, you have the analyze option, which checks the statistics and key distribution of a table. The following example shows how to use it.

    mysqlcheck –analyze stellardb

    An Alternate Solution

    Sometimes, it is not enough to use the mysqlcheck command. You need to use other tools to repair the corrupt MySQL database or databases. One such tool is Stellar Repair for MySQL.

    To repair the database, the software requires you to stop the MySQL service and create a copy of the folder with the database.

    You can stop the services by using the Windows services.

    Windows services

    Type services in Windows Search bar and click the Services app. On the Services page, right-click the service and select the Stop option.

    windows search bar

    Once stopped, look for the databases. They are usually stored at the following location:

    C:\ProgramData\MySQL\MySQL Server X.X\Data

    Here X.X is the version number.

    Copy the data folder to another place. You can do it manually in Windows or use the command line:

    xcopy /s /e /i “C:\ProgramData\MySQL\MySQL Server 8.0\Data” “c:\backup\data”

    Once the data is copied, open Stellar Repair for MySQL, press the Browse button, and select the folder in the c:\backup\data.

    selecting database in repair tool

    Select the database or databases that you want to repair and press the Repair button.

    selecting database in repair tool

    Press the Save icon to save the objects.

    saving databases

    You can save the information in a database and restore it or export it to different formats, like Excel, HTML, and CSV.

    Conclusion

    In this article, we discussed different options provided by the mysqlcheck command to check and repair MySQL database. In case the command fails to repair the database, you can use Stellar Repair for MySQL. It is a powerful software to fix corrupt MySQL database and to restore all the database objects.

    Was this article helpful?

    No NO

    About The Author

    Daniel Calbimonte linkdin

    Daniel Calbimonte is a Microsoft Most Valuable Professional, Microsoft Certified Trainer, and Microsoft Certified IT Professional for SQL Server. He is an accomplished SSIS author, teacher at IT Academies and has over 10 years of experience as a QE and developer for SQL Server related software. He has worked for the government, oil companies, web sites, magazines and universities around the world. Daniel also regularly speaks at SQL Servers conferences and blogs. Read more

    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