Recover Crashed innodb tables
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.
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
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
And try to start MySql using the following command
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
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
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.