Recover Crashed InnoDB tables
Recover crashed Innodb tables process will help you to recover any critical tables during the InnoDB crash. One of the dangerous issues you will face on a MySQL server is the 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 the cPanel server, Plesk server, Directadmin server, Percona server, Madiarb server.
How Innodb crash can happen?
- InnoDB table can crash if it is powerless for your dedicated server.
- Force shutdown of MySQL service can end up in Innodb crash.
- Space issues on the server can cause an InnoDB crash if the server space is 100% filled and MySQL is not able to write on the disk.
- A high load on the server can cause an Innodb crash.
- High I/O wait on the disk can also cause a 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/srv1.domain.io.err sometimes it can also be 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
The first thing you need to do to recover the database is to stop MySQL using
systemctl stop mysql
And make sure there are no running processes of MySQL you can check this using the command
ps -aux | grep mysql
If there are any running processes 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 a backup of the data folder because if something fails during recovery you can restore /var/lib/mysql and start the process again.
Once this is 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 value up to 6 and the possibility of data loss 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:
systemctl start mysql
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=srv1.domain.io.err --open-files-limit=10000 --pid-file=/var/lib/mysql/dasc-srv1.domain.io.pid < /dev/null > /dev/null 2>&1
ERROR! The server quit without updating PID file (/var/lib/mysql/srv1.domain.io.pid).
In that case, try the 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 won't start even with the value 6 so you need to check the log file again which is under /var/lib/mysql/srv1.domain.io.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.
The 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 a backup of each Individual database including the database called “MySQL” which is very important. You can take backup using the 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 databases won’t get dropped which can be directly removed using the rm command:
cd /var/lib/mysql && rm -f database_name
Now move ib files to a new location and we force MySQL to create new data 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 using the following command line:
systemctl mysql start
Once the MySQL service is started create all the databases with the similar command we have used to drop the 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/srv1.domain.io.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 the console.
That's all!
