View source for Recovering from mysql failures

==Recovering from InnoDB corruption==

Various procedures exist to recover from various kind of mysql database corruptions or crashes. A good resource for other situations may be [http://www.softwareprojects.com/resources/programming/t-how-to-fix-mysql-database-myisam-innodb-1634.html]. 

This presently documents a procedure that was needed after the system disk was completely filled and innodb ended in a non-recoverable state. The mysql ISAM structures were not affected.

Symptoms: innodb does not start, without error message. phpmyadmin shows all databases, but for the tables inside the database that use innodb, instead of size etc., at the end "in use" is displayed (MyISAM table show fully correctly).

When trying to dump the database, the dump does not start at all, or after setting set innodb_force_recovery = 4, the dump starts, but terminates with a message like: "mysqldump: Got error: 1033: Incorrect information in file: './enwiki/account_credentials.frm' when using LOCK TABLES"

'''Solution: increase innodb_force_recovery to 8''' (the actual max may only be 6 according to [http://dev.mysql.com/doc/refman/5.0/en/forcing-recovery.html], but 8 worked).

Recipe (may be partly incomplete, with respect to creating folders etc.):

<source lang="bash">
 # copy all sql data including innodb ibdata1 ib_logfile0 ib_logfile1 to a backup position:
 cp -pr ib* /mnt/storage/var-lib-mysql /mnt/dump/mysqlxxx 
 # change port to 8089 and set innodb_force_recovery = 2 (or 4, 6, 8) in
 nano /etc/mysql/my.cnf 
 /etc/init.d/mysql restart
 #then dump all databases
 mysqldump -u root -pTHEPASSWORD  --port=8089  --all-databases --opt > /mnt/dump/alldbs.sql
   # or file name versioned automatically with date and time:
   mysqldump -u root -pTHEPASSWORD  --port=8089  --all-databases --opt > /mnt/dump/alldbs_`date +"%Y-%m-%d_%H-%M"`.sql
 # or directly compressed:
 # mysqldump -u root -pTHEPASSWORD  --port=8089  --all-databases --opt | gzip > /mnt/dump/alldbs.sql.gz
 /etc/init.d/mysql stop
 nano /etc/mysql/my.cnf # remove innodb_force_recovery
 # remove innodb, copy exists, next restart will recreate:
 rm /mnt/storage/var-lib-mysql/ib*
 /etc/init.d/mysql start
 mysql -uroot -pTHEPASSWORD --port=8089  --compress < /mnt/dump/alldbs.sql
 /etc/init.d/mysql stop
 nano /etc/mysql/my.cnf # return port to 8088
 /etc/init.d/mysql start
</source>