Recovering a difficult tmpfs database with innodb corrupted.

I had to recover a database after the tmpfs full scenario today.

Normally the instructions for recovery from this on the testbot project apge are adequate (stop mysql; rm -rf /tmpfs/mysql/drupaltesbotmysql/simpletest*; restart mysql; drop database drupaltestbotmysql; create database drupaltestbotmysql).

However, on occasion, this causes some kind of disaster, and mysql's system tables are out of date.

You might get messages like

Mar 10 08:01:35 drupaltestbot659-mysql mysqld: 120310  8:01:35  InnoDB: Error: table 'drupaltestbotmysql/authmap'
Mar 10 08:01:35 drupaltestbot659-mysql mysqld: InnoDB: in InnoDB data dictionary has tablespace id 355290538,
Mar 10 08:01:35 drupaltestbot659-mysql mysqld: InnoDB: but tablespace with that id or name does not exist. Have
Mar 10 08:01:35 drupaltestbot659-mysql mysqld: InnoDB: you deleted or moved .ibd files?
Mar 10 08:01:35 drupaltestbot659-mysql mysqld: InnoDB: This may also be a table created with CREATE TEMPORARY TABLE
Mar 10 08:01:35 drupaltestbot659-mysql mysqld: InnoDB: whose .ibd and .frm files MySQL automatically removed, but the
Mar 10 08:01:35 drupaltestbot659-mysql mysqld: InnoDB: table still exists in the InnoDB internal data dictionary.

and like

Mar 10 08:01:35 drupaltestbot659-mysql mysqld: 120310  8:01:35  InnoDB: Error: page 13618 log sequence number 21588276757294
Mar 10 08:01:35 drupaltestbot659-mysql mysqld: InnoDB: is in the future! Current system log sequence number 21588276444999.
Mar 10 08:01:35 drupaltestbot659-mysql mysqld: InnoDB: Your database may be corrupt or you may have copied the InnoDB
Mar 10 08:01:35 drupaltestbot659-mysql mysqld: InnoDB: tablespace but not the InnoDB log files. See
Mar 10 08:01:35 drupaltestbot659-mysql mysqld: InnoDB: http://dev.mysql.com/doc/refman/5.1/en/forcing-innodb-recovery.html

In this case, there's a pretty easy recovery without completely reinstalling mysql. Following the ideas in http://www.softwareprojects.com/resources/programming/t-how-to-fix-mysql..., I did this and it worked (finally)

1. Stop mysql (service mysql stop). If ps -ef | grep mysql still shows running processes, killall mysqld_safe; killall mysqld; killall -9 mysqld_safe; killall -9 mysqld
2. Edit /etc/mysql/my.cnf to add
   

   [mysqld]
   innodb_force_recovery = 6

3. Restart mysql service mysql start
4. mysqldump drupaltestbot | gzip >~/tmp/drupaltestbot.sql.gz
5. Stop mysqld again as in the first step, but you may have to force it with killall again
6. Remove or comment out the innodb_force_recovery line in my.cnf

7. cd /tmpfs/mysql
   rm -rf drupaltestbot*
   rm ib_log* ibdata*
   

8. Start mysqld again (service mysql start)
9. echo "create database drupaltestbot; create database drupaltestbotmysql;" | mysql
10. gzip -dc ~/tmp/drupaltestbot.sql.gz | mysql drupaltestbot

You may have a working testbot at this point. You'll want to reconfirm it.