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.
Symptoms:
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"`
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