One of the frequently faced problems in MySQL database is corruption in the Tables. Hence, it is not enough to install the database alone. It must have a sound backup application, and this application must back up the data at regular intervals. A systematic disaster recovery plan must also be in place.
Despite taking all the due measures, it sometimes becomes difficult to keep the database up and running. This is exactly what happened with our storage engines in the MySQL application. An error occurred in collation on the organization’s MySQL server and it resulted in InnoDB database corruption issue. Our database server was rendered inaccessible and it started displaying the following message:
The following Server configuration was in use: Ubuntu 16.04; MySQL 5.7
The need of the hour was to correctly identify the database page corruption. For that, we dumped the Tables from the database with the help of SELECT….INTO OUTFILE command, but using this command caused the InnoDB background operations to crash, which further resulted in InnoDB roll-forward recovery to crash. A series of crashes ultimately led to serious corruption issue in the MySQL database.
We tried correcting the error with the help of ‘Table Rebuild’. This process involved updating the indexes for those character columns, which were utilizing the collation.
The process resulted in MySQL server becoming inaccessible and ultimately, database users were not able to fetch data from the storage system.
A high priority ticket was raised by the Helpdesk Manager, which alerted our IT operations team – the database, backup, monitoring and maintenance administrators. The inaccessible database had resulted in a major crisis and we as database administrators had to get the system up and running, ASAP.
We formulated a plan of action to get the MySQL database online, which included the following major activities:
- Restore MySQL database through backup
- Forced Recovery of MySQL Table
- Repair MySQL Table by using MySQL repair software
- Restore MySQL database through backup
As restoring MySQL database through backup is considered the most effective process to retrieve the inaccessible database, we tried to restore the inaccessible MySQL data through backup. To our dismay, the backup was not up-to-date, hence, restoring MySQL database through obsolete backup could have incurred data loss.
- Forced Recovery of MySQL Table
Now the option was to force recover MySQL InnoDB Table.
Note: For the normal startup without forced recovery, the innodb_forced_recovery is set at 0, by default. Hence, recovering the database in an emergency situation required the value of InnoDB_force_recovery to be set at 1 and increasing it incrementally, if necessary, where the permissible values may vary between 1 and 6.
We were aware that we could afford to dump the tables with an innodb_forced_recovery as 3 or lesser. Here, only some data on corrupt individual pages was lost. With 4 or greater value, the situation could have proved to be fatal because of permanent corruption in the data files, so much so that the value of 6 may leave the database pages in an obsolete state and corruption may be introduced in B-trees and other database structures.
Note: InnoDB does not allow “Insert, Update or Delete” during innodb_forced_recovery greater than 0.
Recovering the InnoDB tables forcefully was not a feasible option as it could have resulted in the loss of MySQL database.
Our last resort was to try and set up an effective MySQL database repair solution to repair the corrupted elements and restore the complete database.
- Repair MySQL Table with the help of a repair utility
Trying an effective MySQL repair solution needed some research. Any issue with the software’s repair and recover feature meant starting from scratch with another software.
Thankfully, we came across software which provided a preview of the repairable and recoverable database items prior to final recovery. This was Stellar Repair for MySQL software. We got the final confirmation from the customer support.
Next, we installed the software and submitted .ibdata file of InnoDB.
It was a moment of great joy as we could access the repaired database in the preview section. Since the database was humongous and it was difficult to verify each component, we had to go for random verification and found that none of the table- items were amiss.
The entire MySQL database was available on the screen in the form of a preview. Without waiting any longer, we activated the software and saved the database.
It was a total win-win situation with no training, no lengthy processes, no coding, and above all no fear of data loss during the repair process. The software recovered all the database components in original format!
Final Analysis
Table corruption is one of the key reasons for the inaccessible MySQL database. Such Table corruption can be repaired by using the codes. Applying codes would prolong downtime, and during this time period, the database would have remained inaccessible. Also, using the codes might have resulted in deleting the MySQL database.
Creating a healthy and updated backup is a necessity and all administrators including our team are aware of this fact. Still, this crisis was prolonged for lack of backup.
During the downtime, our primary concern was to get back a complete and accessible database and the best way to repair MySQL database Tables was through an effective software solution. Here, Stellar Repair for MySQL came to our rescue. We could repair and recover the entire MySQL database. Our database was up and running once again. One of the major reasons we chose this software was that it repairs both InnoDB and MyISAM Tables and recovers all Table-properties.
Word of caution:
Regular practicing of the following tips will help in avoiding the time and effort spent on repairing corrupt MySQL:
- Make sure that the device on which MySQL database is available has adequate UPS support
- Always test MySQL kernel by using MySQLid command. This command runs in the backend and is great to start the server on a machine.
- Perform a regular BACKUP – maintain it and update it.
- Keep the viruses and malware at bay by installing and updating an antivirus.
- Keep a MySQL Repair software handy to repair the database when it gets corrupted.