MySQL InnoDB crash recovery guide for 11.23,11.24 (mysql will not start)

cPanelNick

Administrator
Staff member
Mar 9, 2015
3,488
35
208
cPanel Access Level
DataCenter Provider
Mysql 4.1 INNODB crash bug recovery guide
---------------------------------------------------------------

Affected Systems:
Machines that are running mysql 4.1 that had no root password set previously,
or have changed root's homedir,
or have run out of disk space during the update
or an automated process killer has terminated the update process (because of the size of the update this appears to be much more likely to happen)
or the system runs out of memory and the kernel kills mysqld


Document Version: 2.5

WARNINGS:
STOP : If there are any problems or you are uncomfortable doing this procedure, do not proceed and open a ticket @ https://tickets.cpanel.net/submit/


Make sure you have free disk space in /home and /var/lib/mysql or where ever your mysql databases are stored. This guide assumes they are in /var/lib/mysql.

Step 1:
Disable tailwatchd (chkservd on 11.23) to prevent mysql from being restarted. Stop any other processes that may access mysql including munin, backups, and the webserver if possible.
Code:
killall -TERM tailwatchd
Step 2:
Add
innodb_force_recovery = 3
to /etc/my.cnf

Step 3:
Run
Code:
/scripts/restartsrv_mysql
Step 4:
Verify mysql is up

Step 6:
Set a mysql root password
Code:
echo "root RANDOMPASSHERE" | /scripts/mysqlpasswd
Check to see if they have moved root's homedir. If they have copy the ~root/.my.cnf file to /root/.my.cnf
Support for checking this will be added ASAP.


Step 7:
If you do not have any innodb data you care about you can skip this step.


Download http://koston.org/dumpdbs.txt
Code:
wget -O dumpdbs.pl [url]http://koston.org/dumpdbs.txt[/url]
Step 8:
If you do not have any innodb data you care about you can skip this step.

Run the script. It should dump the databases using innodb into /home/innodb_dumps/

Step 9:
Stop Mysql

Step 10:
Code:
mkdir /var/lib/mysql/INNODB_BACKUPS
	mv /var/lib/mysql/ib* /var/lib/mysql/INNODB_BACKUPS
Step 11:
Remove
innodb_force_recovery = 3
from /etc/my.cnf

Step 12:
If you do not have any innodb data you care about you can skip this step.

Restart mysql (
Code:
/scripts/restartsrv_mysql
)


Step 13:
If you do not have any innodb data you care about you can skip this step.

For every database that was dummped into /home/innodb_dumps/ we need to move away the databases so they can be restored from the dumps
Code:
mv /var/lib/mysql/MYSQLDBNAME /var/lib/mysql/INNODB_BACKUPS
Next create empty database dirs for the .sql files to be restored into
Code:
mkdir /var/lib/mysql/MYSQLDBNAME
	  chown mysql:mysql /var/lib/mysql/MYSQLDBNAME
Step 14:
If you do not have any innodb data you care about you can skip this step.

Code:
cd /home/innodb_dumps
For every database that was dummped into /home/innodb_dumps/

Code:
mysql DBNAME < DBNAME.sql
Step 15:
If you do not have any innodb data you care about you can skip this step.

Verify mysql did not crash during the restore

Step 16:
Code:
Restart mysql and tailwatchd (chkservd on 11.23) and any other process you may have stopped.
  /scripts/restartsrv_mysql
  /scripts/restartsrv_tailwatchd


==========

Note: cPanel keeps backups of your horde and roundcube databases in /var/cpanel/roundcube and /var/cpanel/horde.

Code:
/usr/local/cpanel/bin/update-roundcube
and
Code:
/usr/local/cpanel/bin/update-horde
May be able to be used to get horde or roundcube working in a pinch.
 

dxm

Active Member
Aug 1, 2007
25
0
51
This worked for me.
mysql 4.1.22-standard,
cPanel 11.24.4-R32470 - WHM 11.24.2 - X 3.9
CENTOS 4.7 i686 on virtuozzo

Latest update ran and mysql wouldn't start, giving innodb errors.

This process has allowed me to get my innodb databases back up and running.
Sigh of relief all round!

Thanks.
 

Tigran

Member
Apr 20, 2005
6
0
151
But if I using InnoDB?

Everywhere You write
If you do not have any innodb data you care about you can skip this step.
But I have Innodb? how can enable it?
 

mohit

Well-Known Member
Jul 12, 2005
553
0
166
Sticky On Internet
Everywhere You write
If you do not have any innodb data you care about you can skip this step.
But I have Innodb? how can enable it?
If you do not have any innodb data you care about you can skip this step.
If you DO NOT have any innodb data you care about, you can skip this step.

so if you have, make sure to follow the steps and advice.
 

phoenixweb

Well-Known Member
Jun 3, 2004
70
0
156
cPanel Access Level
DataCenter Provider
... this procedure was right to restart MySql.

However we advise of the problem when was too late, MySql run out of space on his partition due to too many restart / logs / shits, and this beginning to corrupt tables over tables.

So after this recovery we had to recovery also the database from the last backup (15 of december)....

not good.

This is the FIRST time that we loose costumer's data since 2003.

Guys... that was a very bad joke for us... i hope this is the last time that you release a major upgrade without make acid test over several machines and configurations.

Very bad.
Please test it before provide the upgrades.

Bye :(
 

movielad

Well-Known Member
May 14, 2003
109
2
168
cPanel Access Level
Root Administrator
Twitter
While this is a serious issue, I am surprised that people still do not set root passwords on MySQL or keep vigilante for available disk space. These are common system administration tasks. cPanel cannot take full blame for this issue (although it would have been nice to have a bit more notice of the release in preparation for it)...
 
Last edited:

phoenixweb

Well-Known Member
Jun 3, 2004
70
0
156
cPanel Access Level
DataCenter Provider
While this is a serious issue, I am surprised that people still do not set root passwords on MySQL or keep vigilante for available disk space. These are common system administration tasks. cPanel cannot take full blame for this issue (although it would have been nice to have a bit more notice of the release in preparation for it)...

Dear Movielad,

we keep always monitored disk space, but when yesterday MySql crashed, it beginning to eat all disk space and end it in less than 3 hours... Since we run update in the night 'cause it's the time with minimum level of traffic over the server, we cannot notice this on time to fix the problem before it was too late.

We always take a look at the status of the server and read every email sended by the monitor of Cpanel (like DISK SPACE USAGE WARNING), and always take care about this things.

BUT, this time was strictly related to the update and happened too fast and in the night so it give us no a chance to be ready for this.

I'm planning to auto-stop the mysql server when our monitor agent track that the Mysql partition is running out of space. However this will be my way to avoid the problem but should be implemented directly by Cpanel system and not by a monitor selfdeveloped by our staff.

Bye
Max
 

cPanelNick

Administrator
Staff member
Mar 9, 2015
3,488
35
208
cPanel Access Level
DataCenter Provider
Dear Movielad,

we keep always monitored disk space, but when yesterday MySql crashed, it beginning to eat all disk space and end it in less than 3 hours... Since we run update in the night 'cause it's the time with minimum level of traffic over the server, we cannot notice this on time to fix the problem before it was too late.

We always take a look at the status of the server and read every email sended by the monitor of Cpanel (like DISK SPACE USAGE WARNING), and always take care about this things.

BUT, this time was strictly related to the update and happened too fast and in the night so it give us no a chance to be ready for this.

I'm planning to auto-stop the mysql server when our monitor agent track that the Mysql partition is running out of space. However this will be my way to avoid the problem but should be implemented directly by Cpanel system and not by a monitor selfdeveloped by our staff.

Bye
Max
Max,

If you can just upgrade to MySQL 5.0.x as its InnoDB support is much more reliable.
 

phoenixweb

Well-Known Member
Jun 3, 2004
70
0
156
cPanel Access Level
DataCenter Provider
But:

1) Not only INNODB crashed... running out of space destroyed also MyISAM tables. By the way INNODB was almost completely restored with your recovery guide... the problem was not the secondary cause-effect of MySql behaviour.

Upgrading to a better handling of INNODB should not save me from this kind of problem.

2) I cannot upgrade, since we noticed that old's script website doens't support the new query style of LEFT JOIN SYNTAX of the Mysql 5.2 version.

We have very few server with 4.1 installed over but cannot upgrade 'cause our costumer will kill our staff in less than 15 minuts.
 

LiNUxG0d

Well-Known Member
Jun 25, 2003
206
1
168
Gatineau, Quebec, Canada
Do we have any suggestions for when the fix doesn't fix?

I ran it word for word and when I get to step 3, mysql just doesn't start and it just loads my err log up with tons of garbage. :(

This is not very cool.
 

cPanelDavidG

Technical Product Specialist
Nov 29, 2006
11,216
13
313
Houston, TX
cPanel Access Level
Root Administrator
Do we have any suggestions for when the fix doesn't fix?

I ran it word for word and when I get to step 3, mysql just doesn't start and it just loads my err log up with tons of garbage. :(

This is not very cool.
Do not proceed. Immediately submit a support ticket so our technical analysts can assist you with this: http://tickets.cPanel.net/submit
 

StevenC

Well-Known Member
Jan 1, 2004
252
0
166
Should have looked here first! I went and resolved it on my own pretty quickly. This would have been useful. Thanks for the guide.
 

mhkstr

Member
Feb 29, 2008
8
0
51
hello,
i opened a ticket (id: 484322) about innodb databases recovery about 30Min ago.

hoping for a nice experience with the friendly cpanel support stuff :)

i will post update here about this issue.
 

JeffP.

Well-Known Member
Sep 28, 2010
164
15
68
Re: MySQL InnoDB crash recovery guide for 11.23,11.24 (mysql will not start

Yesterday I posted an update to the dumpdbs.txt script in this post, which allowed it to run on current versions of cPanel. Nick has since updated the dumpdbs.txt in the initial post of the thread, so I am replacing the content of this post from yesterday with this message.
 

xeonex

Well-Known Member
Mar 20, 2005
56
0
156
Re: MySQL InnoDB crash recovery guide for 11.23,11.24 (mysql will not start

hi i have upgraded Mysql from 5.1 to 5.5 and got onto the issue that all innodbs gone crashed i have oppened a ticket on my data center asking to escalate to cPanel but with no success and i am not very confident to perfom this fix by myself is there any chance that i can open a direct support ticket so i can get hekp with this
 

xeonex

Well-Known Member
Mar 20, 2005
56
0
156
Re: MySQL InnoDB crash recovery guide for 11.23,11.24 (mysql will not start

ok i have made all as indicated and everything has gone ok+

thumbs up thanks!