InnoDB is a robust storage engine for MySQL, but like any system, it can encounter issues such as corruption. Understanding how to address these problems is crucial for database administrators. This guide will outline steps to recover from an InnoDB corruption using the innodb_force_recovery option.
Understanding the Error
The error log indicates an assertion failure within InnoDB, suggesting data corruption. Typical causes include hardware faults, sudden shutdowns, or bugs. The error message suggests steps for recovery and forces InnoDB into a special "recovery" mode.
130306 22:02:18 mysqld_safe Number of processes running now: 0
130306 22:02:18 mysqld_safe mysqld restarted
130306 22:02:18 [Note] Plugin 'FEDERATED' is disabled.
130306 22:02:18 InnoDB: The InnoDB memory heap is disabled
130306 22:02:18 InnoDB: Mutexes and rw_locks use GCC atomic builtins
130306 22:02:18 InnoDB: Compressed tables use zlib 1.2.3
130306 22:02:18 InnoDB: Using Linux native AIO
130306 22:02:18 InnoDB: Initializing buffer pool, size = 128.0M
130306 22:02:18 InnoDB: Completed initialization of buffer pool
130306 22:02:18 InnoDB: highest supported file format is Barracuda.
130306 22:02:18 InnoDB: 5.5.30 started; log sequence number 1629186928
130306 22:02:18 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306
130306 22:02:18 [Note] - '0.0.0.0' resolves to '0.0.0.0';
130306 22:02:18 [Note] Server socket created on IP: '0.0.0.0'.
130306 22:02:18 [Note] Event Scheduler: Loaded 0 events
130306 22:02:18 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.5.30-cll' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server (GPL)
130306 22:02:19 InnoDB: Assertion failure in thread 47204348393792 in file trx0purge.c line 840
InnoDB: Failing assertion: purge_sys->purge_trx_no <= purge_sys->rseg->last_trx_no
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.5/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.
03:02:19 UTC - mysqld got signal 6 ;This could be because you hit a bug. It is also possible that this binaryor one of the libraries it was linked against is corrupt, improperly built,or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail.
Steps to Recovery
1. Stop mysqld:
First, ensure that MySQL is not running to prevent further data corruption.
service mysqld stop
2. Backup Critical Files:
Before proceeding with any recovery steps, back up the existing InnoDB data files.
mkdir /root/mysql_backup
cp /var/lib/mysql/ib* /root/mysql_backup/
3. Configure MySQL for Recovery:
Edit the MySQL configuration file (/etc/my.cnf) and add the following under the [mysqld] section:
innodb_force_recovery = 4
There are different levels of innodb_force_recovery you can use, from 1 to 6. Levels 1-4 are generally considered safe, while 5 and 6 are used as a last resort.
4. Restart mysqld:
Restart the MySQL server. It will start in recovery mode due to the innodb_force_recovery setting.
service mysqld start
5. Dump All Tables:
Create a dump of all databases. This might take some time depending on the size of your databases.
mysqldump -A > /root/mysql_backup/dump.sql
6. Drop Corrupted Databases:
Identify and drop the corrupted databases. If you're unsure which ones are corrupted, you might need to drop all databases and restore them from the dump later.
mysql -u root -p -e "DROP DATABASE corrupted_db"
7. Stop mysqld:
Once again, stop the MySQL server to proceed with the next steps.
service mysqld stop
8. Remove InnoDB Data Files:
Delete the old InnoDB data files. Be cautious with this command – ensure you have backups!
rm /var/lib/mysql/ib*
9. Comment out innodb_force_recovery:
Edit /etc/my.cnf and comment out or remove the innodb_force_recovery option.
10. Restart mysqld:
Start the MySQL server. It should recreate the InnoDB data files.
service mysqld start
11. Restore Databases:
Restore the databases from the dump you created earlier.Copy code
mysql < /root/mysql_backup/dump.sql
12. Repair and Optimize:
Finally, check and repair all databases and tables.
innodb force recovery optionsmysqlcheck –all-databases –repairMode 1 - Doesn't crash MySQL when it sees a corrupt pageMode 2 - Doesn't run background operationsMode 3 - Doesn't attempt to roll back transactionsMode 4 - Doesn't calculate stats or apply stored/buffered changesMode 5 - Doesn't look at the undo logs during start-upMode 6 - Doesn't roll-forward from the redo logs (ib_logfiles) during start-up1 (SRV_FORCE_IGNORE_CORRUPT)Let the server run even if it detects a corrupt page. Try to make SELECT * FROM tbl_name jumpover corrupt index records and pages, which helps in dumping tables.2 (SRV_FORCE_NO_BACKGROUND)Prevent the main thread from running. If a crash would occur during the purge operation,this recovery value prevents it.3 (SRV_FORCE_NO_TRX_UNDO)Do not run transaction rollbacks after recovery.4 (SRV_FORCE_NO_IBUF_MERGE)Prevent insert buffer merge operations. If they would cause a crash, do not do them.Do not calculate table statistics.5 (SRV_FORCE_NO_UNDO_LOG_SCAN)Do not look at undo logs when starting the database: InnoDB treats even incomplete transactions as committed.6 (SRV_FORCE_NO_LOG_REDO)Do not do the log roll-forward in connection with recovery.The database must not otherwise be used with any nonzero value of innodb_force_recovery.As a safety measure, InnoDB prevents users from performing INSERT, UPDATE, orDELETE operations when innodb_force_recovery is greater than 0.**Hint : A simple query for finding all of your InnoDB tables in case you want to specifically target the corruption.SELECT table_schema, table_name
FROM INFORMATION_SCHEMA.TABLES
WHERE engine = 'innodb';\