MYSQL: Steps to repair and recover innodb db

In this post, I will explain how to recover an innodb crash in a Linux server.

STEP 1:

To confirm innodb crash, first of all add the following parameter to mysql configuration file "/etc/my.cnf" and try to restart mysql.
--------
skip-innodb
-------

If it starts fine now, then we can confirm  that it is a innodb crash.

Now, remove the parameter "skip-innodb"  from my.cnf and follow the steps below to recover innodb:

STEP 1:

Add this line to your /etc/my.cnf configuration file: [mysqld] innodb_force_recovery = 4

STEP 2:

Restart Mysql now. Database should start, but with innodb_force_recovery in my.cnf. All insert and update operation will be ignored.

STEP 3:

Mysqldump all databases.

    mysqldump -A > dump.sql

STEP 4:

 Dump the user and db table from mysql databae

    mysqldump mysql user > user.sql
    mysqldump mysql db > db.sql

if the dump fails create a copy of current /var/lib/mysql directory

    cp -pr /var/lib/mysql /var/lib/mysql_bak

STEP 5:

Shutdown database and delete the data directory (better if you move it) and execute mysql_install_db from shell to create MySQL default tables.

    mysql_install_db

STEP 6:

Remove the "innodb_force_recovery" line from your /etc/my.cnf file and restart the database.

STEP 7:

Restore everything from your backup except default 'mysql' database. For restoring the previous user db privileges do the following.

    cp -rp /var/lib/mysql_bak/mysql /var/lib/mysql/oldmsqldb
    mysqldump oldmysqldb user > oldmysqldb_user.sql
    mysql mysql user < /var/lib/mysql/oldmysqldb_user.sql
    mysql mysql db < /var/lib/mysql/oldmysqldb_db.sql

Keep reading :)