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 [1].

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 [2], but 8 worked).

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

# 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 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