How to recover MySQL from InnoDB corruption


Symptoms:  When attempting to restart MySQL you receive a message similar to:

 
# service mysqld restart
Starting MySQL..The server quit without updating PID file (/var/lib/mysql/server.domain.com.pid).[FAILED] or MySQL server PID file could not be found!
 
Checking the MySQL error log reveals something like:
tail -n 200 /var/lib/mysql/server.domain.com.err
 
129096 13:45:01 mysqld_safe Number of processes running now: 0
129096 13:45:01 mysqld_safe mysqld restarted
129096 13:45:01 [Note] Plugin 'FEDERATED' is disabled.
129096 13:45:01 InnoDB: The InnoDB memory heap is disabled
129096 13:45:01 InnoDB: Mutexes and rw_locks use GCC atomic builtins
129096 13:45:01 InnoDB: Compressed tables use zlib 1.2.3
129096 13:45:01 InnoDB: Using Linux native AIO
129096 13:45:01 InnoDB: Initializing buffer pool, size = 128.0M
129096 13:45:01 InnoDB: Completed initialization of buffer pool
129096 13:45:01 InnoDB: highest supported file format is Barracuda.
129096 13:45:01 InnoDB: 5.5.30 started; log sequence number 1584752745
129096 13:45:01 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306
129096 13:45:01 [Note] - '0.0.0.0' resolves to '0.0.0.0';
129096 13:45:01 [Note] Server socket created on IP: '0.0.0.0'.
129096 13:45:01 [Note] Event Scheduler: Loaded 0 events
129096 13:45:01 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.5.30-cll' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server (GPL)
129096 13:45:02 InnoDB: Assertion failure in thread 47204348393792 in file trx0purge.c line 840
InnoDB: Failing assertion: purge_sys->purge_trx_no <= purge_sys->rseg->last_trx_no
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:45:02 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.
 
This is typical of InnoDB corruption. To recover from this, perform the following steps:
 
MySQL should already be stopped but let's make sure.
# service mysqld stop
 
Back up your current MySQL data directory
# cp -r /var/lib/mysql/* /root/mysql-recovery
 
Add innodb_force_recovery=4 in the my.cnf file. Make sure this option is added in the [mysqld] section of my.cnf. In CentOS, this is file is usually located in /etc/my.cnf. For Debian and related distributions, it will typically be found in /etc/mysql/my.cnf
 
innodb_force_recovery may be set as high as 6 but we should attempt recovery with 4 first.
 
Now attempt to restart MySQL.
# server mysqld start
 
If MySQL started successfully, dump all databases to a file:
 
mysqldump -uroot -p -A | sed '1i\SET FOREIGN_KEY_CHECKS = 0;' > /root/mysql-dump.sql
 
If you are running Plesk, databases may be dumped with the following command:
 
mysqldump -uadmin -p´cat /etc/psa/.psa.shadow´ -A | sed '1i\SET FOREIGN_KEY_CHECKS = 0;' > /root/mysql-dump.sql
 
In the event the dump fails with an error such as:
Incorrect information in file: './database/table.frm' when using LOCK TABLES"´
You will need to increase innodb_force_recovery in your my.cnf file, restart MySQL and try again.
 
Once the dump has been completed, stop MySQL
# service mysqld stop
 
Now delete your MySQL data files:
rm -rf ´ls -d /var/lib/mysql/* | grep -v "/var/lib/mysql/mysql"´
 
Restart MySQL:
# service mysqld restart
 
Import the databases back into MySQL:
mysql -uroot -p < /root/mysql-dump.sql
 
If running Plesk, all databases may be imported with the following command:
mysql -uadmin -p´cat /etc/psa/.psa.shadow´ < /root/mysql-dump.sql
 
Assuming the import went ok, you can remove the innodb_force_recovery line from your my.cnf file and restart MySQL.
# service mysqld restart

 

Posted in
Last update:
2016-07-07 10:56
Average rating:0 (0 Votes)