Recovering MySQL After an Alibaba Cloud Snapshot Rollback

The startup failure

After an Alibaba Cloud snapshot was rolled back, MySQL could no longer start normally. The error log pointed to InnoDB, and the service failed during startup.

The log also suggested a recovery approach: set innodb_force_recovery=1. The configuration file was edited with:

vim /etc/my.cnf

The following line was added at the end of the file:

innodb_force_recovery=1

After saving the change and restarting the server, MySQL started successfully. However, application requests still failed when they attempted to process business data.

Why the service started but applications still failed

A second review of the MySQL log revealed the cause. Setting innodb_force_recovery to 1 allows InnoDB to start in recovery mode, but it also prevents data modification. The server may be available for inspection and data extraction, yet normal write operations are not permitted.

The configuration was therefore changed back to:

innodb_force_recovery=0

After restarting MySQL again, the service came up normally and the application resumed its business operations.

What innodb_force_recovery does

innodb_force_recovery is intended for situations in which a crash has damaged database tablespaces, InnoDB transaction logs, or other internal structures, leaving MySQL unable to start. Its default value is 0. When the error log indicates that an InnoDB-related failure is preventing startup, this parameter can be used to bring the server up far enough to inspect or export data.

The valid range is 0 through 6, with increasingly aggressive recovery behavior:

• 1 (SRV_FORCE_IGNORE_CORRUPT): Ignores corrupt pages detected by InnoDB and forces the server to continue running. This is usually the first value to try, after which the tables can be exported and rebuilt.
• 2 (SRV_FORCE_NO_BACKGROUND): Prevents the master thread and purge threads from running. It is useful when the crash occurs during a purge operation or when the main thread is attempting a full purge.
• 3 (SRV_FORCE_NO_TRX_UNDO): Skips transaction rollback.
• 4 (SRV_FORCE_NO_IBUF_MERGE): Disables insert-buffer merging and related statistical operations when they could trigger another crash. This setting can permanently damage data files; indexes should be removed and rebuilt after using it.
• 5 (SRV_FORCE_NO_UNDO_LOG_SCAN): Skips scanning the redo logs. InnoDB may treat uncommitted or incomplete transactions as committed, which can permanently damage data files.
• 6 (SRV_FORCE_NO_LOG_REDO): Disables redo log roll-forward during recovery. Database pages may remain in an obsolete state, potentially causing further damage to B-trees and other internal structures.

These modes are not normal operating settings. The higher the value, the greater the risk of additional data loss or corruption.

Practical precautions during recovery

Several precautions are important when using innodb_force_recovery:

• If a service restart fails because transaction rollback causes the problem, innodb_force_recovery can be set to 3 to skip the rollback phase.
• If corrupted data pages are suspected, use SELECT\+WHERE to identify readable data and export it with mysqldump.
• Once MySQL has been started with innodb_force_recovery greater than 0, block application access by redirecting the port or the domain/VIP elsewhere. Recovery mode is intended for inspection and extraction, not regular application traffic.
• After starting the service in recovery mode, mysqlcheck can be used to check, analyze, optimize, or repair tables where appropriate.
• Before restarting with a force-recovery value, back up all database files. Recovery operations can cause further damage, so a complete copy provides a way to return to the original state if necessary.

Lessons from the incident

A snapshot rollback is not necessarily equivalent to a consistent database backup. In this case, the rollback affected the integrity of the local database, leaving MySQL unable to start normally. A more robust backup design should use a dedicated database server or another backup approach that can preserve database consistency independently of the application host.

When a database service fails, the error log should be checked first. It often identifies the failing subsystem and, as in this case, may also indicate the recovery step needed to bring the service up temporarily.