MySQL Won’t Start – InnoDB Corruption and Recovery

Databases get corrupted for many reasons. In my case, our SAN (or your hard drive) went down during writes to the database from a power failure. InnoDB corruption can cause all of the databases running on that server to become inaccessible.

I have to say what everyone else out there says, “Backup, Backup, and Backup”. Make sure that you maintain a good backup schedule by running a dump script to get your dbs back in order in case of serious corruption or data loss (which is inevitable). (Automysqlbackup)
MySQL Won’t Start: How do I restore an InnoDB database?

Prerequisites:
My server is Centos 5.x, MySQL 5.5 running cPanel 11.34. Ubuntu and Debian flavors will differ and good command-line knowledge is helpful here. You will not be able to do any of these steps without shell access to your server via SSH.  You also will not be able to repair or check your tables via phpmyadmin, WHM or cPanel.

InnoDB corruption can cause all of the databases running on that server to be inaccessible.  Without going into the technical reasons as to why that is, you will find that your databases are unavailable and your MySQL server just won’t start.  You may get a simple response from the MySQL server like:

Starting MySQL..The server quit without updating PID file (/var/lib/mysql/my.server.com.pid).
[FAILED] or MySQL server PID file could not be found!

These errors can only usually be found when trying to re-start the MySQL server. You must now dig deeper, this is where checking the MySQL error log for “my.server.com” will somewhat give you an idea as to what to do next.

First, we have to get to the server and this is where command-line experience comes into play. You should have sudo or root access to your server running MySQL. Let’s take a look at the MySQL error log for my.server.com:

ssh user@my.server.com
tail -500 /var/lib/mysql/my.server.com.err

130306 22:02:18 mysqld_safe Number of processes running now: 0
130306 22:02:18 mysqld_safe mysqld restarted
130306 22:02:18 [Note] Plugin 'FEDERATED' is disabled.
130306 22:02:18 InnoDB: The InnoDB memory heap is disabled
130306 22:02:18 InnoDB: Mutexes and rw_locks use GCC atomic builtins
130306 22:02:18 InnoDB: Compressed tables use zlib 1.2.3
130306 22:02:18 InnoDB: Using Linux native AIO
130306 22:02:18 InnoDB: Initializing buffer pool, size = 128.0M
130306 22:02:18 InnoDB: Completed initialization of buffer pool
130306 22:02:18 InnoDB: highest supported file format is Barracuda.
130306 22:02:18 InnoDB: 5.5.30 started; log sequence number 1629186928
130306 22:02:18 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306
130306 22:02:18 [Note] - '0.0.0.0' resolves to '0.0.0.0';
130306 22:02:18 [Note] Server socket created on IP: '0.0.0.0'.
130306 22:02:18 [Note] Event Scheduler: Loaded 0 events
130306 22:02:18 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.5.30-cll' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server (GPL)
130306 22:02:19 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.
03:02:19 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.

We will try our 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.

Steps to get it back up.

1. Stop mysqld.
2. Backup /var/lib/mysql/ib*
3. Add the following line into /etc/my.cnf:

innodb_force_recovery = 4

4. Restart mysqld.
5. Dump all tables:# mysqldump -A > dump.sql
6. Drop all databases which need recovery.
7. Stop mysqld.
8. Remove /var/lib/mysql/ib*
9. Comment out or remove innodb_force_recovery = 4 in /etc/my.cnf
10. Restart mysqld. Look at mysql error log. By default it should be /var/lib/mysql/server/hostname.com.err to see how it creates new ib* files.
11. Restore databases from the dump:mysql < dump.sql

**Hint : A simple query for finding all of your InnoDB tables in case you want to specifically target the corruption:

SELECT table_schema, table_name
FROM INFORMATION_SCHEMA.TABLES
WHERE engine = 'innodb';