Recover Crashed innodb tables

Lost valuable database ? Hire us we will recover it for you Server Management plan

Recover crashed Innodb tables process will help you to recover any critical tables during innodb crash. One of the dangerous issue you will face on a MySQL server is innodb crash, no matter how important the data is and what tweaks you have on the database server crash can happen sometimes. So you need to Recover crashed Innodb tables if you don’t have a recent backup. It can happen on cPanel server, Plesk server, Directadmin server, Percona server, Madiarb server.

mysql crashed innodb recovery process

How Innodb crash can happen ?

• Innodb table can crash if there is powerlose for your dedicated server.
• Force shutdown of MySQL service can end up in Innodb crash.
• Space issue on the serve can case Innodb crash, if the server space is 100% filled and mysql not able to write on the disk.
• High load on the server can cause Innodb crash.
• High I/O wait on the disk can also cause MySQL innodb crash.

How to determine if innodb is crashed.?

You can determine this by checking MySQL logs, On most of the server location of the Log is /var/lib/mysql/server.hostname.com.err sometimes it can also found under /var/lib/mysql/mysql.log this can be changed under /etc/my.cnf file.

You can see logs like below if the table is Innodb table is crashed. refer InnoDB recovery

The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.
180406 13:45:02 [Note] Plugin 'FEDERATED' is disabled.
180406 13:45:02 InnoDB: The InnoDB memory heap is disabled
180406 13:45:02 InnoDB: Mutexes and rw_locks use GCC atomic builtins
180406 13:45:02 InnoDB: Compressed tables use zlib 1.2.3
180406 13:45:02 InnoDB: Using Linux native AIO
180406 13:45:02 InnoDB: Initializing buffer pool, size = 128.0M
180406 13:45:02 InnoDB: Completed initialization of buffer pool
180406 13:45:02 InnoDB: highest supported file format is Barracuda.
InnoDB: Log scan progressed past the checkpoint lsn 16040747881
180406 13:45:02 InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
InnoDB: Doing recovery: scanned up to log sequence number 16040751132
180406 13:45:02 InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percents: 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99
InnoDB: Apply batch completed
180406 13:45:03 InnoDB: Waiting for the background threads to start
180406 13:45:04 InnoDB: 5.5.58 started; log sequence number 16040751132

Version: '5.5.58-cll' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server (GPL)
180406 13:48:06 InnoDB: Assertion failure in thread 140428055471872 in file fut0lst.ic line 83
InnoDB: Failing assertion: addr.page == FIL_NULL || addr.boffset >= FIL_PAGE_DATA
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.5/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.
18:48:06 UTC - mysqld got signal 6 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help
diagnose the problem, but since we have already crashed,
something is definitely wrong and this may fail.

Mostly you will face the issue on cPanel MySQL server 5.X

Recover crashed Innodb tables on MySQL

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.

Recovered crashed innodb mysql service