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
or
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.