Published on: July 18, 2014 by George K.
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 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: 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.
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 &gt;&gt; /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
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
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.
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