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