When a MySQL database turns corrupt, you may wonder why the database got corrupted in the first place. This post will outline possible reasons behind database corruption before answering the question: how to repair a corrupt MySQL database?
Reasons behind MySQL Database Corruption
A MySQL database can get corrupt due to any of these reasons:
- Sudden server shutdown
- Defect in the hardware used to store and retrieve data like disk subsystem, disk controllers, drivers, etc.
- ‘mysqld’ process is killed while an ongoing update is in process
- Software bug
It can be challenging to pinpoint the cause of a corrupted database. Whatever may be the cause, the concern you should have is how to repair the corrupt MySQL database.
How to Repair Corrupt MySQL Database?
The first thing you need to look at is the error message you get. Following are some error messages you may encounter when MySQL database gets corrupted:
Error 144: Table is crashed, and the last repair failed
Error 141: Duplicate unique key or constraint on write or update
Error 136: No more room in the index file
Error 134: Record was already deleted
Error 126: Index file is crashed/ wrong file format
Error: Could not find file ‘wtlicensemanager.dll’
If you want to do the repairing free of cost, you have two options:
- Do it manually.
- Do it with the help of software using the trial period.
A database recovery software can do a better and faster job at repairing the corrupted database.
Here is one software application that you can use to repair the corrupted MySQL database:
This MySQL database repair tool repairs database created using both InnoDB and MyISAM database engines of MySQL. You can download the demo version of the SQL repair tool to preview recoverable database before saving them.
Key Features:
- Can recover MySQL database on Windows and Linux systems.
- Recovers InnoDB files (.ibdata, .ibd and .frm) and MyISAM files (myd, .myi and .frm)
- Restores database objects like tables, table properties, data types, views, triggers, etc.
- Allows selective recovery of database components
How to Repair Corrupt MySQL Database Manually?
There are two types of storage engines in MySQL: InnoDB and MyISAM. The InnoDB table contains FRM, IBDATA, and IDB files, while the MyISAM table contains FRM, MYD, and MYI files.
Before We Proceed
Here are a few things you should consider before attempting manual recovery:
- Do not reboot the server
When you reboot the server, the server can put the corrupted database in suspect mode. This will turn off the database. It can also create errors while connecting to the database. You should attempt recovery without rebooting the server.
- Use Single User mode
Make sure you are the only user connected to the database. The single-user mode gives you the entire control, eliminating other users’ interference.
- Do not upgrade MySQL
You may think of updating the MySQL database to repair the corrupted files. But this is not the case. Upgrade can cause permanent data loss.
- Take Backup of the corrupted MySQL database
It is important to have a backup of the corrupted file as there can be errors that can cause you to lose data. There is no guarantee that your manual steps will not affect the healthy files. So it is safe to have a backup before attempting manual recovery.
To backup, use the following command:
mysqldump db_name > database_name.sql
Just enter your database name in place of database_name.
If the above does not work, try the following:
mysqldump –u[username] –p[password] [database_name] > [dump_file].sql
There is another way to back up the database:
service mysqld stop
cp -r /var/lib/mysql /var/lib/mysql_bkp
Now that we have the backup, we can attempt to repair the corrupt MySQL database.
How to Recover MySQL Database Manually?
Repairing MyISAM Database
If you are using MyISAM database storage engine for your database table, you can utilize the following commands to repair the table:
Mysqlcheck
If your MySQL server is running, run the following mysqlcheck command to repair your table:
mysqlcheck -r [database_name]
Make sure to replace ‘database_name’ with the name of your database.
Myisamchk
If the server is not running, try repairing the database table by using the myisamchk command:
Note: Before running this command, make sure that the server does not have any tables open.
myisamchk table_name
Repair the table name with the table name in your case. This command automatically fixes your table.
To restart the server, type the following:
service mysqld start
Force InnoDB Recovery
You will need to perform InnoDB force recovery to extract data from a corrupted InnoDB database table.
Step 1: First, you need to open my.cnf file.
In this file, locate the [mysqld] section.
Add the following line to this section:
innodb_force_recovery=1
Now save the configuration file and restart the MySQL server. You can set the value of innodb_force_recovery from 1 to 6. However, a value beyond 4 is not recommended, as it can corrupt the data.
Step 2: Dump the table data into a new file.
To do so, use the following command:
mysqldump –u user –p db_name table_name > single_dbtable_dump.sql
Step 3: Drop the corrupt table from the database.
mysql –u user –p –execute=”DROP TABLE db_name.table_name”
Step 4: Restore the table from the dump file.
mysql –u user –p < single_dbtable_dump.sql
Step 5: Once the files are repaired, disable the force recovery mode with the following command:
#innodb_force_recovery=…
Now you can restart the MySQL service in the normal mode.
We hope that the above methods will help you repair the corrupt MySQL database.