How to Fix InnoDB Tablespace Corruption in MySQL¶
The goal is to be able to successfully export your databases into .sql files. Once this has been completed you will be able to remove the databases and import your .sql file to get back to normal.
When attempting to restart MySQL you receive a message similar to:
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:
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 will 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=1 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. Once you have made the modification to your my.cnf file, attempt to start MySQL.
innodb_force_recovery may be set as high as 6 but we should attempt recovery with 1 first. I don’t recall this ever working until I get to at least 4 but you should still start with 1. Corruption becomes possible at 4 so it is worth trying 1-3 first. There is a lot more detailed information in MySQL’s innodb_force_recovery documentation should you want more in-depth information.
If you have not done so already, 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"`
# 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