Having your storage ripped from out from under your database is never good. I recently had to go through this where our iscsi storage backend powering our virtual machine vmdk’s magically died and even worse, its write cache was completely cleared out.
Upon things coming back, mysqld was in a reboot loop which could only be stopped by enabling a innodb_force_recovery option in my.conf.
Here’s some of the output starting up with recovery on:
150219 14:17:02 InnoDB: Started; log sequence number 111 1039202151
InnoDB: !!! innodb_force_recovery is set to 2 !!!
150219 14:17:02 [Note] Event Scheduler: Loaded 0 events
150219 14:17:02 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.1.35-community' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server (GPL)
Although I still encountered warnings like:
InnoDB: is in the future! Current system log sequence number 111 1039202151.
InnoDB: Your database may be corrupt or you may have copied the InnoDB
InnoDB: tablespace but not the InnoDB log files. See
InnoDB: http://dev.mysql.com/doc/refman/5.1/en/forcing-recovery.html
InnoDB: for more information.
150219 14:17:15 InnoDB: Error: page 360488 log sequence number 112 92120010
InnoDB: is in the future! Current system log sequence number 111 1039202151.
InnoDB: Your database may be corrupt or you may have copied the InnoDB
InnoDB: tablespace but not the InnoDB log files. See
InnoDB: http://dev.mysql.com/doc/refman/5.1/en/forcing-recovery.html
InnoDB: for more information.
At this point, my main priority was getting a database dump completed. Luckily, this completed successfully and importing it into another MySQL instance saw everything accessible without problems again :)
A couple things:
- You don’t want to try and use your database in production with the recovery flag enabled.
- I haven’t done a full post-mortem yet but I suspect I would need to completely clear out the old mysql instance and re-import from a database dump again.
- Ensure you take down whatever front-ends try to use your database while you are working with/dumping it in recovery mode.
