Tuesday, August 30, 2011

The recovery of crashed InnoDB

If the database crashes, InnoDB will attempt to fix everything by running the log file from the last timestamp. Unfortunately if InnoDB fails to repair itself, the -entire- database will not start. MySQL will exit with an error message and your entire database will be offline.

Once you have corrupt InnoDB tables that are preventing your database from starting, you should follow this five step process:

Step 1: Add this line to your /etc/my.cnf configuration file:

innodb_force_recovery = 4

Step 2: Restart MySQL. Your database will now start, but with innodb_force_recovery, all INSERTs and UPDATEs will be ignored.

Step 3: Dump all tables

Step 4: Shutdown database and delete the data directory. Run mysql_install_db to create MySQL default tables

Step 5: Remove the innodb_force_recovery line from your /etc/my.cnf file and restart the database. (It should start normally now)

Step 6: Restore everything from your backup

*** This is copied from How to Fix MySQL Database (MyISAM / InnoDB). The command mysql_install_db is only available since MySQL 5. The steps I took are slightly differently since we are using MySQL 4.
1. Set innod_force_recovery=6
2. Start MySQL, drop databases
3. Stop MySQL, delete ibdata and ib_logfiles, and unset innodb_force_recovery
4. Start MySQL again, the tablespace and log files will be recreated
5. Restore the data with the backup file

No comments: