Diamond Notes

In preparation for disaster recovery and for training purposes I asked our Junior DBA to document the innodb_force_recovery options in our internal Wiki.  I am modestly familiar with the command having used it a few times because of data corruption.  However, somehow, I completely missed something that I discovered when discussing his findings with the other DBA.  I am passing it along since some of you might not know of it.

If you are doing a large operation that is modifying a transactional table and something goes wrong and you end up aborting the operation mysql will normally roll back the operation.  Because this is a disk-bound operation it will be many times slower than the original transaction.

Solution is this: if you abort a large operation and it begins to roll back you can kill the entire server process, add the  innodb_force_recovery=3 line to your my.cnf file (the mysqld section) and restart the mysql server.  You can then do any cleanup necessary because the server doesn’t begin transaction rollback as it normally would.  If you were doing a large import that you cancled you could drop the table for example.   Then just remove the innodb_force_recovery line from my.cnf and restart the mysql server.  You should tail the error log as it there will be lots of complaining by the server because of what you did, but in ten or fifteen minutes you should be back online.  I would restart the server again just to make sure everything begins as normal but at that point you can consider yourself in business.  If you can restart the server (which might not always be possible) this is much faster than waiting for a long transaction to roll back.

You should test this in a controlled test environment before having to use it “in the field”.

Resource:   http://dev.mysql.com/doc/refman/5.0/en/forcing-recovery.html