Occasionally, you may need to repair corruption in MySQL tables. Corruption in a database and its tables typically happens when a mysqld process gets killed in the middle of an update or write operation. Also, events like unexpected system shutdown, hardware errors, etc. could easily corrupt a database table.
A corrupt table can render the data unreadable. To regain access to the data, you must first try restoring the table from an updated database backup. If it’s not possible, you must repair the corrupted table. If your server is running, use the ‘mysqlcheck’ command to repair all or a specific table.
Note: The mysqlcheck command works when the server is running. If the server is no longer running or has crashed, you may use the myisamcheck command to repair a corrupt MyISAM table. For recovery of a corrupted InnoDB table, try forcing InnoDB recovery. If nothing works, you may consider repairing the corrupted MySQL tables using a MySQL repair software.
Before repairing the database tables, you must take backup of the tables to prevent data damage.
Backing up MySQL Tables
To take backup of all the files of your databases, do the following:
- Log in to your MySQL server and stop the server using the command:
- Copy all the files of your database into a new directory by running the below command:
| cp -r /var/lib/mysql /var/lib/mysql_backup |
Steps to Repair MySQL Tables using Mysqlcheck
After backing up the data, begin troubleshooting the corrupted MySQL tables by running the mysqlcheck command. The procedure to repair tables in MySQL database using mysqlcheck is as follows:
Note: In the following steps, replace ‘db_name’ and ‘tbl_name’ with the name of your database and table to be repaired.
Step 1: Check the MySQL Tables for Corruption
Before you repair the table, run the mysqlcheck command with ‘-c’ option to identify corrupted tables:
This command will check for errors in all the tables of a specified database.
Alternatively, run the below command to check a specific table for corruption:
| mysqlcheck –c db_name tbl_name |
If the output of this command returns OK, it means that the table is not corrupted. But, if it reports table errors, you must repair it.
Step 2: Repair the Tables
To repair a table, specify the database name and the table name along with ‘-r’ option as shown below:
| mysqlcheck -r db_name tbl_name |
This command only repairs MyISAM tables. To repair corrupted InnoDB tables, read this: Best Ways to Repair InnoDB Table Corruption in MySQL.
Alternatives to Repairing MySQL Tables
If using mysqlcheck with the repair option fails to fix corrupted tables, try alternative methods to rebuild or repair a table recommended by Microsoft. However, these methods may result in data integrity issues. Also, you may lose some data.
One of the easiest methods to repair database tables without data loss is to use a third-party MySQL repair tool. Stellar Repair for MySQL is an efficient tool you can use to fix corruption in MyISAM and InnoDB tables.
Download and install the SQL repair tool. After installation, launch the tool and follow these steps:
Step 1: Click OK to proceed with the repair process.
Step 2: Select MySQL version and data folder containing the database with corrupted tables.
Step 3: After making the appropriate selection, click OK.
Step 4: Select all or a specific database containing tables you want to repair, and then click Repair.
Step 5: After completion of the repair process, click OK.
Step 6: A window opens with a list of all the recoverable tables and other objects on the left pane. Click on the table to view its data on the right pane. Select the tables you want to repair, click Save from the File menu.
Step 7: On the Save Database dialog box, select ‘MySQL’ file format to save the repaired tables. Next, enter the server details for saving the file with restored tables. Click Save.
You will get a message after the file gets saved.
EndNote
Whether you’re having trouble opening MySQL tables or accessing the records, using mysqlcheck can help check corruption in MySQL tables and repair them. If this didn’t work, try the alternative methods suggested in this blog to restore the table. For further support or any queries, leave a comment below.
