In this post I’ll explain how to repair a corrupted MySQL database. Especially on large traffic database servers, data corruption can occur, for instance due to hard disk or network failures.
In a previous post I described how to perform a MySQL incremental backup that will keep you safe from data losses, but sometimes a backup is not enough.
Consider this scenario: your web application ran smoothly on MySQL until today. Running a simple query to read data from tables is crashing MySQL instance. The latest backup was taken yesterday, so you propose your client to perform a full restore of the database from yesterday’s backup. Backups are made exactly for that, right?
Nope! Your client says that today they worked many hours on the data, before the crash occurred. They cannot accept to repeat all the work and ask you to solve the problem quickly. The only solution here is trying to retrieve data from the corrupt tables.
1. How to repair a corrupted MySQL database using mysqlcheck
mysqlcheck is a tool distributed together with MySQL. As the documentation says:
the mysqlcheck client performs table maintenance: It checks, repairs, optimizes, or analyzes tables.
You can try to issue the following command to repair corrupted tables:
mysqlcheck --auto-repair -o --all-databases -uroot -p
Executing the above command started checking the tables (line-by-line), displaying OK at the end. Something like this:
[email protected]:/$ mysqlcheck --auto-repair -o --all-databases -uroot -p
Enter password:
mydb.accounting_table1 OK
mydb.accounting_table2 OK
mydb.accounting_table3 OK
mydb.accounting_table4 OK
Unfortunately mysqlcheck may hang when checking large databases for corrupt tables.
2. How to repair a corrupted MySQL database using InnoDB recovery mode
If your database uses InnoDB tables you can try to restart it in recovery mode, by stopping the mysql service and restarting it after adding the following options in my.cnf:
innodb_force_recovery=3
innodb_purge_threads=0
Please read the manual on starting the InnoDB engine in recovery mode to have more details on the meaning of these options. Using those options can help you in restarting the crashed database server and if this is the case you will be able to perform a dump of the data:
mysqldump --single-transaction --quick --skip-extended-insert \
--routines -umyuser -pmysecret dbname > /path/to/dumps/dir/dbname.dump;
If you cannot restart your MySQL server in recovery mode, the last resort is using a commercial MySQL repair tool.
3. How to repair a corrupted MySQL database using Stellar Repair for MySQL
Stellar Repair for MySQL is a commercial tool specifically designed to repair corrupted MySQL databases. As you can see from the product website it has the following benefits:
- Easy-to-use user interface (UI), with detailed instructions, makes the repair process straightforward.
- Repairs corrupt MySQL InnoDB and MyISAM tables.
- Recovers all major database components including tables, table properties, data types, keys, triggers, etc.
- Repairs and recovers corrupt database files created in MySQL version 8.x, 6.x, 5.x, and older versions.
The software is produced by Stellar Data Recovery Inc., which is specialized in data recovery solutions and has positive reviews from renowned sites.
The current price for the tool is $199, but I think that those money are well spent for a tool like this because you’ll save a lot of time in the long run. Click here to download the tool.
Unfortunately the software currently doesn’t run on Linux based Operating Systems, but you can indeed repair MySQL databases running on Linux servers by making a copying the data directory, usually located in /var/lib/mysql. In my case I used a Windows Server virtual machine to run the tool.
After purchasing a license of Stellar Repair and obtaining a copy of the software you can run it and it will present itself with a simple interface. On first run an Instruction window will give you some info on how to prepare the data directory for repair. Then a Select Data Folder window will appear:
here you have to choose the version of MySQL that produced the data directory you want to repair. In my case it was MySQL 5.7. Click Browse to select the data directory you copied from the MySQL server (this should be a copy of /var/lib/mysql if your DB server was running on Linux OS).
Then a list of your databases will be presented. Here you have to choose only the database(s) that you want to repair, then click “Repair”:
On the left pane of the window you’ll see all the databases together with the tables that have been repaired. You can click on each item to see the table data and properties on the right pane:
You can optionally filter the data you want to export by selecting the checkboxes appropriately. Then click on “Save” button in the toolbar:
You’ll be presented with a window to choose the destination of the restored data. Here I choose to export in CSV format:
You can also export the restored data directly on a running instance of MySQL by providing the necessary connection parameters:
In order to save MySQL database version 8, MySQL 8.0 ODBC driver 32-bit must be pre-installed on your system. To do this you can download the Windows version of MySQL community edition (GPL licensed).
4. Conclusion
While MySQL InnoDB storage engine is more resistant to corruption compared to other storage engines, InnoDB tables can still become corrupt and lead to server crash.
In this post I’ve discussed some methods to repair a corrupt MySQL table:
- mysqlcheck command can identify and repair corrupt tables;
- innodb_force_recovery option can be used to restart the server in recovery mode and bring database up for retrieving the data;
- Stellar’s MySQL repair tool can come in handy if all other methods failed.
Disclaimer: this fair review was kindly sponsored by Stellar Data Recovery Inc. who also gave me a license key for testing and reviewing their MySQL repair tool.
