MySQL – Corrupted InnoDB

After, for example, the restore of a bare-metal-backup it is possible that MySQL InnoDB is corrupted. How to fix this:

Backup all databases

Enable InnoDB recovery:

# vim /etc/my.cnf

Add:

innodb_force_recovery=1

Start MySQL:

# /etc/init.d/mysql start

Dump all databases:

# mysqldump --all-databases > full_backup.sql

Restore all databases

Disable InnoDB recovery:

# vim /etc/my.cnf

Remove or comment out:

innodb_force_recovery=1

Stop MySQL:

# /etc/init.d/mysql stop

Remove all databases from /var/lib/mysql, but copy over mysql users en settings:

# cd /var/lib
# mv mysql mysql_crash
# mkdir mysql
# chown --reference mysql_crash mysql
# chmod 700 mysql
# cp -av mysql_crash/mysql mysql Start MySQL: # /etc/init.d/mysql start

Restore all backups:

# mysql < full_backup.sql