MySQL-Recover Crashed innodb tables

Recover crashed Innodb tables on MySQL

As you can see from the log InnoDB starts crash recovery:

InnoDB: Starting crash recovery.

First think you need to do to recover the database is to stop mysql using

/etc/init.d/mysql stop

And make sure there are no running process of MySql you can check this using the command

ps -aux | grep mysql

If there are any running process kill the process id using the command kill -9 pid and take backup of Crashed MySQL database. If it is a cPanel server first make sure to disable MySQL monitoring from cPanel/WHM Home »Service Configuration »Service Manager to avoid automatic restart.

Backup /var/lib/mysql using below command

cp -rf /var/lib/mysql /var/lib/mysql_backup_data

It is important to take backup of the data folder because if something fail during recovery you can restore /var/lib/mysql and start the process again.

Once this is the done next step is to start the actual recovery process. You can do this by adding the parameter innodb_force_recovery, the value of the parameter is from 1 to 6, if you can recover the database using innodb_force_recovery value 1 you don’t lose any data and the condition of data will be very good. In any minor crash you can recover it using the value innodb_force_recovery = 1. If the intensity of the crash is high you might have to increase the valye up to 6 and the possibility of datalose is high.

So now we will try Forcing InnoDB Recovery first step is to add

innodb_force_recovery = 1

In the file

/etc/my.cnf

And try to start MySql using the following command

/etc/init.d/mysql start

If the issue is fixed MySQL will start with the message “Starting successful” or else you will get some error message like below.

Starting MySQL./usr/bin/mysqld_safe: line 182: 2208 Killed nohup /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --log-error=dasc-server.hosting.com.err --open-files-limit=10000 --pid-file=/var/lib/mysql/dasc-server.hosting.com.pid < /dev/null > /dev/null 2>&1 ERROR! The server quit without updating PID file (/var/lib/mysql/dasc-server.server.com.pid).

In that case try below value until MySQL starts

innodb_force_recovery = 2

innodb_force_recovery = 3

innodb_force_recovery = 4

innodb_force_recovery = 5

innodb_force_recovery = 6

Sometimes MySQL wont start even with the value 6 so you need to check the log file again which is under /var/lib/mysql/server.server.com.err

You might notice the error.

InnoDB: Waiting for the background threads to start

This can be fixed by adding the line

innodb_purge_threads=0

With the innodb_force_recovery 6 value.

So once MySQL is started in recovery mode we need to take backup of all the crashed databases and restore it with new ibdata1, ib_logfile0, ib_logfile1 files.

Next step is to create the database list into a file which can be done using the command.

mysql -e 'show databases;' | grep -v information_schema | grep -v Database > /home/mysql_innodb_recovery/database_list.txt

Once this is done take backup of each Individual databases including the database called “mysql” which is very important. You can take backup using below command.

for db in 'cat /home/mysql_innodb_recovery/database_list.txt'; do mysqldump $i > /home/mysql_innodb_recovery/database_backup/$db.sql;done

Now we need to drop current databases to make sure that they are removed from MySQL and innodb which can be done using the command. Make sure to remove the database name “mysql” from the file /home/mysql_innodb_recovery/database_list.txt before doing this.

for db in 'cat /home/mysql_innodb_recovery/database_list.txt'; do mysqladmin drop $db;done

Some database won’t get dropped which can be directly removed using the rm -rf command

cd /var/lib/mysql rm -rf database_name

Now move ib files to a new location and we force MySQL to create a new ibdata files.

mv /var/lib/mysql/ibdata1 /home/mysql_innodb_recovery/ mv /var/lib/mysql/ib_logfile0 /home/mysql_innodb_recovery/ mv /var/lib/mysql/ib_logfile1 /home/mysql_innodb_recovery/

Remove below values from /etc/my.cnf and start mysql

innodb_force_recovery = 1 innodb_purge_threads = 0

Start MySQL

/etc/init.d/mysql start

Once the MySQL service is started create all the database with the similar command we have used to drop database.

for db in 'cat /home/mysql_innodb_recovery/database_list.txt'; do mysqladmin create $db;done

Now we will restore the backups we have taken.

for db in 'cat /home/mysql_innodb_recovery/database_list.txt'; do mysqldump $db < /home/mysql_innodb_recovery/database_backup/$db.sql;done

Here we have completed the Innodb table repair process. Have a look at the log file /var/lib/mysql/server.server.com.err and make sure the repair process is successful.

Repair crashed innodb tables.

Sometimes tables will be marked as crashed this can be fixed quickly using the below command.

mysqlcheck -r -A

mysqlcheck --repair --all-databases

The above command will repair all the databases on the server. If there is only one database marked as crashed this can be fixed by running the command

mysqlcheck –repair DATABASE_NAME

Once everything is recovered you can test if the MySQL is up by simply running the command mysql from console.