March 7, 2013
MySQL Won’t Start – InnoDB Corruption and Recovery.
By DineEngine

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 [email protected]
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';

If you’re still have issues and would like some help, we have a technical support team that can help troubleshoot and fix your MySQL database issues.

DineEngine Staff members delight our subscribers and guests with interesting takes on what's going on in the restaurant industry with a focus on food technology and industry events.

28 Comments

  1. mansi

    Great post! Well what if all the above step fails and backup is also not available? In this case you can try a good MySQL database repair tool. I had used Stellar Phoenix MySQL Database recovery to recover my corrupt data.

    Reply
  2. b2un0

    Thanks – you saved my a** 🙂

    Reply
  3. asmythe2013

    Thank you … this definitely worked for me and saved me a ton of time.

    Reply
  4. Jovannie

    Hi,

    I have tried all steps, but it doesn’t work/start mysql back.
    I dropped all databases which use innodb.
    Comment out innodb_force_recovery, and still got this message:
    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!

    Please help? 😀

    With regards,

    Jovannie

    Reply
  5. Sam Stoelinga

    Just wanted to add, that in case dropping databases isn’t working you can manually remove them by doing:

    rm /var/lib/mysql/$DATABASENAME -r

    In my case mysqld just crashed as soon as I tried to delete the database using drop database $DATABASENAME

    Reply
    • Aleksandr Kuzminsky

      it’s not good idea because InnoDB dictionary will still keep old entries.

      Reply
  6. julienbijoux

    Fantastic, you save my b* too !
    Really !
    works well on percona on smartos
    with

    #recovery mesures
    #innodb_force_recovery = 4
    #innodb_purge_thread=0

    Reply
  7. hansi

    thanks a ton, this guide worked for me.

    Reply
  8. khaled

    Thanks Dude save my time 🙂

    Reply
  9. Dapeng

    Great works! Save me …

    Reply
  10. Jumoke

    DAMN! You saved my day! You are like so totally awesome! Woot woot!

    Reply
  11. arekm

    “A simple query for finding all of your InnoDB tables in case you want to specifically target ” – so all my tables are innodb.

    How to figure out which one is bad?

    Reply
  12. Nitesh

    My mysql not getting start
    Error

    [root@hosrvecom01 /]# service mysql start
    Starting MySQL. ERROR! The server quit without updating PID file (/var/lib/mysql/hosrvecom01.pid).

    PLease help to get out of it

    Reply
  13. Sinekt Alexe Emil

    Totally useless for me. MySQL is dead after a hard-drive cable problem:

    Starting MySQL. ERROR! The server quit without updating PID file (/var/lib/mysql/www.happymc.net.pid).

    No matter what’s in the conf, it won’t start. There is no such PID file, and if I create it, MySQL deletes it when I try to run the server.

    Reply
  14. TimothyOntiveros

    Based on what is in that log I suggest you run some disk checking, as it’s possible you have disk errors which are causing the corruption of the database. If the disk check out you may want to restore from your backup if you are unable to repair the database. Make sure the file /var/run/mysqld/mysqld.sock exists in your server. if not exist you can create it. this tuts will help you: http://www.filerepairforum.com/forum/microsoft/microsoft-aa/sql-server
    Or if you do not go, you can resort to extreme measures – install special software (I advise you to install only paid content, it will give a better chance of a successful recovery and a safer) to restore a damaged database: http://www.mysql.recoverytoolbox.com/

    Reply
  15. Matt H

    Quick little script to find all Innodb databases:

    # mysql -N mysql -e “SELECT table_schema, table_name FROM INFORMATION_SCHEMA.TABLES WHERE engine = ‘innodb’;” | awk ‘{print $1}’ | sort | uniq

    Reply
    • Mark Hahn

      struck me as amusing to use awk, sort *and* uniq rather than fixing the sql:

      mysql -N mysql -e “select distinct(table_schema) from INFORMATION_SCHEMA.TABLES where engine=’innodb'”

      Reply
  16. Jonathan Tremblay

    Saved all our corrupted databases. THANKS!!!!! I owe you a few dozen beers 😀

    Reply
  17. Vincent

    Thank you so much!
    Finding the table with errors was easy since mysqldump was disconnected with an error message while trying to dump a particular table. Fortunately the name of the table was unique amongst the databases and it was a mediawiki cache database which doesn’t need a backup, I did TRUNCATE table; and mysql restarted perfectly.

    Reply
  18. Simon

    4. Restart mysqld.
    5. Dump all tables:# mysqldump -A > dump.sql
    6. Drop all databases which need recovery.

    How do you restart mysqld since it son’t start (the name of the article). In my case, when I try to start it even with innodb force recovery, it keeps crashing…

    Reply
    • Getulio

      I agree… same thing here. Its impossible to dump databases since mysql is not starting!!!

      Reply
  19. Ed

    Thanks! This was very helpful when trying to recover a corrupted db after an unexpected power outage.

    Reply
  20. Poty

    Thanks soooo much! You saved my day too!

    Reply
  21. Ben

    You saved my life – thank you!

    Reply
  22. Brett

    This is great but what I cant understand is how other people have resolved this without actually knowing which databases need recovery…this guide doesn’t tell you “to determine which databases need recover,…do this”

    Reply
  23. tim

    what happens when a system table (like mysql.proc) is corrupted and mysqlcheck can’t fix it?

    Reply
  24. Shad Hasan

    Useful documentation. Thank you

    Reply

Submit a Comment

Your email address will not be published. Required fields are marked *

Get Started with DineEngine.

Contact Us Now Find Out How Chepri Can Help Your Team. Ask Us More About MySQL Won’t Start – InnoDB Corruption and Recovery..

(800) 338-8102

733-C Lakeview Plaza Blvd. Worthington, OH 43085.