How to Repair MySQL Tables Using Mysqlcheck

Sometimes, users are looking for a solution to repairing their MySQL tables due to corruption or damage. Generally, users face this corruption while the data held within them is unreadable and when a mysqld process gets stopped in the middle of an update or writing operation. It also happens when one can unexpectedly shut down the system, hardware issue, etc. So, because of all this, there would be a chance that the server gets crashed.

In this blog, we will cover the efficient steps of MySQLcheck to repair a corrupted database table. But, before moving on to the solution, let us check out some common reasons behind the corruption of MySQL tables.

Why Do Users Face MySQL Tables Corruption Issue?

• The MySQL server gets stopped within the writing process.
• Chances that the external program has modified the table, and it has been modified simultaneously by the server.
• Unexpectedly, the shutdown of the system.
• Possibility of the hardware failure.
• There is a presence of a bug somewhere in MySQL code.

These are the several factors of the corrupted table, and due to this, your data is rendered and unreadable. Now, to get back your data and access, you can try the process to update the database backup. If you can not get data back, you must have to repair the corrupted MySQL table.

Step-by-Step Guide to Repair MYSQL Tables Using Mysqlcheck

While your MySQL table gets corrupted or damaged, you can use the Mysqlcheck command to repair/restore it. There are different commands provided by Mysqlcheck to know about the specific table like checks, repairs, optimizes, and analyzes. Now, let us move to the solution part to fix the corruption issue as soon as possible.

Note:- Before you begin the repairing process of database tables, I recommend you take a backup of the tables to prevent them from being lost.

To take the backup of Database tables, implement the steps mentioned below:

Step 1: First of all, Login into your MySQL server and stop it using this command:

• For CentOS and Fedora, enter: service mysqld start
• For Debian and Ubuntu, enter: service mysql stop

Step 2: Now, use this command to store the copy of all database data into a new directory: cp -r /var/lib/mysql /var/lib/mysql_backup

Step 3: Finally, restart the server using:

• For CentOS and Fedora, enter: service mysqld start
• For Debian and Ubuntu, enter: service mysql start

After taking the backup of the data, initiate the troubleshooting of the corrupted MySQL tables steps by checking the table in the database:

Part 1: Inspect the Specific table in a Database

If your application showing the error message that this particular table gets corrupted, then by implementing the below command, you can check that one table:

Just take an example to check the employee table in the classstudents database.

# mysqlcheck -c classstudents employee -u root -p

Enter password:

classstudents.employee   OK

Enter the username and password for the mysqlcheck command. Otherwise, you get an error listed below:

# mysqlcheck -c classstudents employee

mysqlcheck: Got error: 1045: Access denied for user ‘root’@’localhost’ (using password: NO) when trying to connect

The benefit of the mysqlcheck command is that one can run it while the MySQL daemon is in running mode. It facilitates the feature to check and repair the corrupted table when the database is running.

Part 2: Inspect all Tables in a Database

To inspect all the tables in an individual database, don’t enter the table name. You will have to define the name of the database.

This example provides you an option to check the tables present in the collegedepartments database.

# mysqlcheck -c collegedepartments -u root -p

Enter password:

collegedepartments.JBPM_ACTION                                OK

collegedepartments.JBPM_BYTEARRAY                        OK

collegedepartments.JBPM_BYTEBLOCK                        OK

collegedepartments.JBPM_COMMENT                           OK

collegedepartmentsJBPM_DECISIONCONDITIONS      OK

collegedepartments.JBPM_DELEGATION                       OK

collegedepartments.JBPM_EVENT                                  OK

..

Part 3: Inspect all Tables and All Database

Now, to check all the tables and databases apply the all-databases command along with the -c options:

# mysqlcheck -c  -u root -p –all-databases

Enter password:

classstudents.employee                                                        OK

collegedepartments.JBPM_ACTION                                    OK

collegedepartments.JBPM_BYTEARRAY                            OK

collegedepartments.JBPM_BYTEBLOCK                            OK

..

..

Additionally, if you need to check the tables of a few databases, then check the database names using –databases.

Below mentioned command, check the tables present in the classstudents and collegedepartments database.

# mysqlcheck -c  -u root -p –databases classstudents collegedepartments

Enter password:

 classstudents.employee                                                        OK

 collegedepartments.JBPM_ACTION                                    OK

 collegedepartments.JBPM_BYTEARRAY                             OK

 collegedepartments.JBPM_BYTEBLOCK                             OK

..

Part 4: Analyze Tables Using Mysqlcheck

The below-mentioned analyzes the employee table that is present in the classstudents database.

# mysqlcheck -a classstudents employee -u root -p

Enter password:

classstudents.employee   Table is already up to date

Here, the internally mysqlcheck command uses the ANALYZE TABLE command.

Note:- When the mysqlcheck analyze command is in running mode, the table is locked and available for read-only.

Part 5: Optimize Tables using Mysqlcheck

The below-mentioned optimize the employee table that is present in the classstudents database.

# mysqlcheck -o classstudents employee -u root -p

Enter password:

classstudents.employee         OK

Here, the internally mysqlcheck command uses the OPTIMIZE TABLE command.

Note:- After deletion of the multiple rows from a table, it will help you to get back the unused space and defragment the data file. Moreover, because of all this, the performance of big tables increases.

Part 6: Repair MYSQL Tables Using Mysqlcheck

The below-mentioned repairs the employee table that is present in the classstudents database.

# mysqlcheck -r classstudents employee -u root -p

Enter password:

classstudents.employee        OK

Here, the internally mysqlcheck command uses the REPAIR TABLE command. It will automatically repair and fix corrupted/damaged MyISAM and archive tables completely.

Note:- Instead of performing the repairing process separately, you can perform this all functionality check, optimize and repair together. Use the auto-repair command to do.

The below-mentioned checks, optimize, and repair the corrupted table that is present in the classstudents database.

# mysqlcheck -u root -p –auto-repair -c -o classstudents 

Alternate Method to Repair MYSQL Tables

If you fail, after performing the mysqlcheck process steps to fix the corruption of the MySQL tables. Then you can opt with the expert solution Aryson MySQL Database Repair to fix the corruption or damage of MySQL database and tables completely.

MySQL Database Recovery is a safe and 100% risk-free software to recover/restore data from the corrupted database tables without losing any data. This tool is efficiently designed to troubleshoot any type of issue present in the MySQL database. In addition, one can restore all the database objects such as keys, tables, table properties, data types, views, triggers without any hassle.

Conclusion

Whenever you are facing any issue while opening or accessing the MySQL tables because of corruption, use mysqlcheck, it will help you to troubleshoot your error successfully. Furthermore, if it did not work, then try the automated third-party solution to repair MySQL tables.

Realted Post