Check and Repair Corruption of MySQL Tables with Mysqlcheck CommandFile Repair Tool Blog
MySQL is a database system that is broadly used by several large and small companies across the globe for storing and managing large quantity of data. It comes with predefined queries which allow you to insert, restore and manage data that is stored in various tables in your MySQL database. So, if you want correct result, the syntax of the queries must be accurate, or else the database engine will display error informing the same.
However, despite of the accurate syntax of format of the queries, you may notice random error messages if there are some issues with the database. For example, a database may be corrupt due to random problems. Luckily, you can fix such errors with the help of inbuilt mysqlcheck command to check and fix corrupt MySQL database.
Checking Single Table
If you notice error message that inform the particular database table is corrupt, you can verify and repair that table with the use of mysqlcheck command. Follow the below to use command:
# mysqlcheck -c -u root -p
Enter password:
databasename.tablename OK
If the password not entered, you will notice the below error message:
# mysqlcheck -c
mysqlcheck: Got error: 1045: Access denied for user ‘root@localhost’ (using password: NO) when trying to connect
Checking All Tables
After fixing the corruption of particular table, if in case error still exist or any other error appears and stating corruption in the database, then run the below command to verify and repair the entire database:
# mysqlcheck -c -u root -p
Enter password:
newdatabasename.table1 OK
newdatabasename.table2 OK
newdatabasename.table3 OK
….
Important Note: Here OK notifies the status of the particular database table.
Checking All Databases as well as All Tables
You may also check all the databases and their respective tables for fixing corruption by following the below command. Just mention all-databases in the query.
#mysqlcheck -c -u root -p –all-databases
Enter password:
databasename.tablename OK
newdatabasename.table1 OK
newdatabasename.table2 OK
newdatabasename.table3 OK
….
….
mysql.help_category
error : Table upgrade required. Please do “REPAIR TABLE ‘help_category’ ” or dump/reload to fix it!
mysql.help_keyword
error : Table upgrade required. Please do “REPAIR TABLE ‘help_keyword’ ” or dump/reload to fix it!
….
Checking All Tables of Particular Databases
The mysqlcheck command let you to verify all tables of some particular databases in the database system. Hence, run the below command in this regard:
# mysqlcheck -c -u root -p –databases databasename newdatabasename
Enter password:
databasename.tablename OK
newdatabasename.table1 OK
newdatabasename.table2 OK
….
Analyzing Table with mysqlcheck Command
You can examine the tables in the database to identify its status with the mysqlcheck command using an option. Run the below command:
# mysqlcheck -a -u root -p
Enter password:
databasename.tablename Table is already up to date
The above mentioned command uses ANALYZE TABLE keyword within. In the meantime the specific table is available in Read-only mode for other processes.
Optimizing Table with mysqlcheck Command
You can also optimize the tables in the database with the same mysqlcheck command with -o option. Run the below command in this regard:
# mysqlcheck -a -u root -p
Enter password:
databasename.tablename OK
The abovementioned command uses OPTIMIZE TABLE keyword within. With this command, you regain the spaces vacated after deleting one or more rows in the table. The same also defragments the specific table.
Repairing Tables with mysqlcheck command
You can also repair the tables in the database by using the same mysqlcheck command with -r option. Run the below command in this regard:
# mysqlcheck -r -u root -p
Enter password:
databasename.tablename OK
The above mentioned command uses REPAIR TABLE keyword internally and repairs the problems creating corruption in the tables.
 |
 |
 |
Recommended Solution
You can also use MySQL Database Repair Tool as an alternative to all above commands. This tool is one of the best and excellent commercial tool that can easily detects and repair all types of corruption and related errors of MySQL Database. However, you can also install the free demo to check how the tool performs and work. It is compatible with almost all version of Windows operating system.
Steps to Repair Corruption of MySQL Tables
Step 1: Download, install and launch the MySQl Database repair tool.
Step 2: Click ‘Select’ button to find and add corrupt MySQL database to the application and then select OK. Here you have the option of ‘Repair corrupt database which is stored at default location of MySQL’ and ‘Manual option to selct your database from default location’.
Step 3: After the scanning process you can now see the preview of the file in the left panel of the software.
Step 4: Now you can start the reairing process, just click on Repair button. A dialoguie box will appear. Click Yes.
Step 5: After successful completion of the process, a message box is displayed as ‘repair process finished successfully’. Click ‘OK’ to close the message box.
