How to Recover the Database When MySQL Cannot be Started After the Abnormal System Shutdown

1. Background

My VPS hosting provider informed me that there was a power disruption in their datacenter on June 20. And my VPS shutdown abnormally that morning. When the power supply turned normal, I found that most of my programs on the VPS works normally, except My Blog, which said “Error Establishing a Database Connection”. The further analysis suggested that MySQL couldn’t be started automatically. When I tried to start it manually, it failed with following error message:

I tried to find the answer by searching the error codes on Google. Although this seems to be a common problem, none of the solutions could help me start MySQL, including promoting the privilege of the folder /var/run/mysqld/, restarting MySQL, reinstalling MySQL without removing the configuration and data files, etc. However, If I removed the data files in /var/lib/mysql/, I found that MySQL could be restarted. When I move back the folder, the error came again.
So I decided to check the log file (/var/log/mysql/error.log).

Obviously, the error was caused by the mismatch of the log sequence number between ibdata and ib_logfiles. Since I had found the problem, what I should do it to force MySQL repaired itself.

2. Repair and Recovery the Database

Before I made any changes on the system, backup all the data, in case that the recovery failed and all the data was lost! This is very important! One can choose to copy the folder /var/lib/mysql/ to other directory or backup the whole system.
MySQL cannot be started. Thus I couldn’t simply use mysqldump to export the database. What to do is find a method to start MySQL. Luckily, the developers had added a function to force MySQL to run, repair itself, and neglect some errors. There are six levels of recovery. Level 0 means the normal startup without forced recovery. The higher level includes the functionality of lower ones. For example, level 3 includes all of the functionality of level 1 and 2. The levels below 3 (included level 3) should be considered safe for they have few change to the data, while the recovery above level 3 may be dangerous to the data and caused it lost permanently. Since I had backup the data in advance, I didn’t have to worry about it. Here is a detailed instruction from level 1 to 6: (more details can be found at here)

  • 1 (SRV_FORCE_IGNORE_CORRUPT)
    Lets the server run even if it detects a corrupt page. Tries to make SELECT * FROM tbl_name jump over corrupt index records and pages, which helps in dumping tables.
  • 2 (SRV_FORCE_NO_BACKGROUND)
    Prevents the master thread and any purge threads from running. If a crash would occur during the purge operation, this recovery value prevents it.
  • 3 (SRV_FORCE_NO_TRX_UNDO)
    Does not run transaction rollbacks after crash recovery.
  • 4 (SRV_FORCE_NO_IBUF_MERGE)
    Prevents insert buffer merge operations. If they would cause a crash, does not do them. Does not calculate table statistics. This value can permanently corrupt data files. After using this value, be prepared to drop and recreate all secondary indexes.
  • 5 (SRV_FORCE_NO_UNDO_LOG_SCAN)
    Does not look at undo logs when starting the database: InnoDB treats even incomplete transactions as committed. This value can permanently corrupt data files.
  • 6 (SRV_FORCE_NO_LOG_REDO)
    Does not do the redo log roll-forward in connection with recovery. This value can permanently corrupt data files. Leaves database pages in an obsolete state, which in turn may introduce more corruption into B-trees and other database structures.

Open and edit the MySQL configuration file (default: /etc/mysql/my.cnf). Add or modify the following code:

I tried to change the value of innodb_force_recovery from 1 to 6. After each change, I tried to start MySQL. In my case, when innodb_force_recovery = 2, MySQL can be started. But when I tried to export the database using mysqldump or phpmyadmin, it crashed. Thus I continued to increase the value of innodb_force_recovery. When innodb_force_recovery = 5, mysqldump worked successfully and I got the .sql file.
I deleted the folder /var/lib/mysql and all the files in it. Restart MySQL and check if it can run correctly. If there is no error, import the backup file.
If one is lucky enough, the database can be imported successfully and the recovery can be done. However, there are still errors which prevent me from importing. Here is my solution: I open the exported .sql file using vim to see it. I found the lost pages and times and copy it to the last backup file manually. These method is less efficient, but compared to the value of the data, the efforts is worth of taking.

3. Learn to Schedule Periodic Backups

This experience reminded me of the importance of the backup. Thanks to the fact that there were only two articles after my last backup. It didn’t take me too much effort to restore them. So I added the following codes to /etc/crontab to setup a schedule to export the database of MySQL periodically:

where

  • 15 4 * * * root” represents “minute hour day month dayofweek runasuser” (here it mean the following code will be run as root at 4:15 am everyday);
  • find /root/Backup/mysql -type f -mtime -1 -exec xz {}” means compressing the .sql file older than 1 day;
  • mysqldump --add-drop-table -u %USER% -%PASSWORD% WordPress > /root/Backup/mysql/WordPress_date +"\%m\%d\%Y".sql” means exporting the database named WordPress to /root/Backup/mysql and naming it as “WordPress_mmddyyyy.sql“;
  • “find /root/Backup/mysql -type f -mtime +14 -exec rm {} +” means deleting the backup files older than 14 days.

Ensure that service cron is running.

4. Conclusion

In this passage, I found that the abnormal shutdown of the machine might cause the severe data lost on the server. I tried to use the recover module of MySQL to repair the error. Though it didn’t word perfectly, I restored as much data as possible. It is a good habit to backup the data periodically.

Thank you for your reading.


If you think this passage is helpful, here is my Bitcoin Wallet:

Any questions or suggestions? Please contact me via Email.

Leave a Reply

Your email address will not be published. Required fields are marked *