MySQL (InnoDB) server recovery

Tags: innodbMySQLrecovery

Published on: July 18, 2014 by George K.

MySQL (InnoDB) server recovery


InnoDB server rarely crashes. In majority of the cases, the corruption in the db can be fixed by repairing the table alone. Here we are discussing a severe table space corruption in ibdata1, ib_logfile0,ib_logfile1  files and the server fails to start even after multiple attempts. I shall try to explain the issue in view of recent issue we have had and the InnoDB recovery process

The server was failing to get restarted and the log file showed the following error

14051412:46:05InnoDB: Initializing buffer pool, size = 8.0M
14051412:46:05InnoDB: Completed initialization of buffer pool
InnoDB: Error: tried to read 1048576bytes at offset 02097152.
InnoDB: Was only able to read 839680.
InnoDB: Fatal error: cannot read from file. OS error number 17.
14051412:46:39InnoDB: Assertion failure in thread 139747413022464in file os/os0file.c line 2314
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to
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: about forcing recovery.
09:46:39UTC – mysqld got signal 6;
This could be because you hit a bug. It is also possible that thisbinary
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 tryour 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.
Now we are sure that some issues exists with the table space and InnoDB recovery needs to be done.

Here are the steps for InnoDB recovery:-
Step 1: Start the server in Recovery Mode

The MySQL server instance need to stopped first and restart in recovery mode.

The recovery mode can be enabled by adding the line  innodb_force_recovery in /etc/my.cnf. The recovery mode makes the dbs read only. Hence users can’t update, insert or alter the data, but can read the data. So the site would load instead of a MySQL error. Add innodb_force_recovery=1  to your my.cnf and check if the server kicks up. If not, you may further increase this number from 1 to 6, check MySQL manual to see what the differences are.

If the server fails, check the mysql log, there is possibility for a thread race-condition issue. In that case the error log will have entries like the following one

InnoDB: Waiting for the background threads to start

In such case, we need to specify the innodb_purge_threads=0 in your /etc/my.cnf file .

Levels 1-4 are pretty safe as most data is preserved. Setting the level to 5 or 6 gets a bit more risky as you could lose some data. In my case levels 1-5 did no good and MySQL refused to run.  On force recovery level 6 the MySQL did work and all databases were up and running. My /etc/my.cnf file had the following entries



Step 2: Create the DB dumps

This is the crucial step in the InnoDB recovery process. You need to list the dbs first, for that use the command from the shell

echo 'show databases;' | mysql | grep -v ^Database | grep -v information_schema

For easy administration, we are redirecting the output to a file. In this I used the file named “/home/mysql_recovery/db_list”

Now the command would look like this

echo 'show databases;' | mysql | grep -v ^Database | grep -v information_schema >> /home/mysql_recovery/db_list

The next step is to create the data base dumps

for i in `cat /home/mysql_recovery/db_list`; do mysqldump $i > /home/mysql_recovery/$i.sql; echo "$i" >> /home/mysql_recovery/Completed_dump.txt; done
This script will generate dumps of the dbs as  /home/mysql_recovery/dbname.sql, the list of completed dbs are available in /home/mysql_recovery/Completed_dump.txt . You can use these files for comparison.

Step 2: Remove the corrupted DBs and ibdata files

If you are unable to identify the corrupted db or the corruption occurred at the ibdate file, the dbs need to be deleted as well.

The db mysql is critical and hence should be preserved. So you shouldn’t venture to delete it. Make sure to remove mysql from db_list file to avoid accidental deletion

Some admins believe that deletion of data directory for the db alone is sufficient. In my experience it can create tracking or follow up issues.  So I prefer to use the mysql command line to achieve this

for i in `cat /home/mysql_recovery/db_list`; do mysqladmin drop $i; done

Some dbs fails, especially if the file system is corrupted. In such cases delete the corresponding folder and drop the db again

cd /var/lib/mysql/

rm -rf dbname

mysqladmin drop dbname;

Once the dbs are cleared. Stop the server

/etc/init.d/mysql stop

Once the server is stopped, remove the ibdata files to another location

cd /var/lib/mysql

mv ib* /home/mysql_recovery/

Step3: Start the serverRemove or comment out  the  innodb_force_recovery in /etc/my.cnf and start the server.

/etc/init.d/mysql start

Check the server log and ensure that everything is working fine.

Step 4: Recreation and restore of DBs

Once the server is up, we need to recreate the dbs and restore them from the backups taken

cd /home/mysql_recovery

for i in `cat db_list`; do mysqladmin create $i; mysql $i < $i.sql; echo "$i" >>restored.txt ; done

Once the restore is complete, check the server logs and ensure that InnoDB recovery is successfull





Category : Howtos, Linux, MySQL, Troubleshooting

George K.

George K.

George started his career in web hosting and Linux technical support in the year 2004 and is with SupportSages since 2009. He has keen interest in server optimizations, custom security solutions, hacked server recovery, cyber forensic and high availability fail over system design and implementation. George loves long drives and is passionate about art and literature.

You may also read:


Add new commentSIGN IN

Let's Connect


Your Cart

Cart is empty.

Send this to a friend