Blog

Here are the steps for InnoDB recovery:-
Step 1: Start the server in Recovery Mode

The MySQL server instance need to stopped first and restart in recovery mode.

The recovery mode can be enabled by adding the line  innodb_force_recovery in /etc/my.cnf. The recovery mode makes the dbs read only. Hence users can’t update, insert or alter the data, but can read the data. So the site would load instead of a MySQL error. Add innodb_force_recovery=1  to your my.cnf and check if the server kicks up. If not, you may further increase this number from 1 to 6, check MySQL manual to see what the differences are.

If the server fails, check the mysql log, there is possibility for a thread race-condition issue. In that case the error log will have entries like the following one

InnoDB: Waiting for the background threads to start

In such case, we need to specify the innodb_purge_threads=0 in your /etc/my.cnf file .

Levels 1-4 are pretty safe as most data is preserved. Setting the level to 5 or 6 gets a bit more risky as you could lose some data. In my case levels 1-5 did no good and MySQL refused to run.  On force recovery level 6 the MySQL did work and all databases were up and running. My /etc/my.cnf file had the following entries

innodb_purge_threads=0

innodb_force_recovery=6

Step 2: Create the DB dumps

This is the crucial step in the InnoDB recovery process. You need to list the dbs first, for that use the command from the shell

echo 'show databases;' | mysql | grep -v ^Database | grep -v information_schema

For easy administration, we are redirecting the output to a file. In this I used the file named “/home/mysql_recovery/db_list”

Now the command would look like this

echo 'show databases;' | mysql | grep -v ^Database | grep -v information_schema >> /home/mysql_recovery/db_list

The next step is to create the data base dumps

for i in `cat /home/mysql_recovery/db_list`; do mysqldump $i > /home/mysql_recovery/$i.sql; echo "$i" >> /home/mysql_recovery/Completed_dump.txt; done

Step 2: Remove the corrupted DBs and ibdata files

If you are unable to identify the corrupted db or the corruption occurred at the ibdate file, the dbs need to be deleted as well.

The db “mysql“ is critical and hence should be preserved. So you shouldn’t venture to delete it. Make sure to remove mysql from db_list file to avoid accidental deletion

Some admins believe that deletion of data directory for the db alone is sufficient. In my experience it can create tracking or follow up issues.  So I prefer to use the mysql command line to achieve this

for i in `cat /home/mysql_recovery/db_list`; do mysqladmin drop $i; done

Some dbs fails, especially if the file system is corrupted. In such cases delete the corresponding folder and drop the db again

cd /var/lib/mysql/

rm -rf dbname

mysqladmin drop dbname;

Once the dbs are cleared. Stop the server

/etc/init.d/mysql stop

Once the server is stopped, remove the ibdata files to another location

cd /var/lib/mysql

mv ib* /home/mysql_recovery/

Step3: Start the serverRemove or comment out  the  innodb_force_recovery in /etc/my.cnf and start the server.

/etc/init.d/mysql start

Check the server log and ensure that everything is working fine.

Step 4: Recreation and restore of DBs

Once the server is up, we need to recreate the dbs and restore them from the backups taken

cd /home/mysql_recovery

for i in `cat db_list`; do mysqladmin create $i; mysql $i < $i.sql; echo "$i" >>restored.txt ; done

Once the restore is complete, check the server logs and ensure that InnoDB recovery is successfull