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.
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.
Thanks – you saved my a** 🙂
Thank you … this definitely worked for me and saved me a ton of time.
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
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
it’s not good idea because InnoDB dictionary will still keep old entries.
Fantastic, you save my b* too !
Really !
works well on percona on smartos
with
#recovery mesures
#innodb_force_recovery = 4
#innodb_purge_thread=0
thanks a ton, this guide worked for me.
Thanks Dude save my time 🙂
Great works! Save me …
DAMN! You saved my day! You are like so totally awesome! Woot woot!
“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?
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
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.
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/
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
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'”
It helped, thanks.
Saved all our corrupted databases. THANKS!!!!! I owe you a few dozen beers 😀
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.
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…
I agree… same thing here. Its impossible to dump databases since mysql is not starting!!!
Thanks! This was very helpful when trying to recover a corrupted db after an unexpected power outage.
Thanks soooo much! You saved my day too!
You saved my life – thank you!
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”
what happens when a system table (like mysql.proc) is corrupted and mysqlcheck can’t fix it?
Useful documentation. Thank you