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

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:

• 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.

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

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.

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

Press the Save icon to save the objects.

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?

YES0

NO

About The Author

Daniel Calbimonte

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