The Community Forums

Interact with an entire community of cPanel & WHM users!
  1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

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

Discussion in 'E-mail Discussions' started by cPanelNick, Dec 17, 2008.

Page 1 of 2
  1. cPanelNick

    cPanelNick Administrator
    Staff Member

    Joined:
    Mar 9, 2015
    Messages:
    3,451
    Likes Received:
    12
    Trophy Points:
    148
    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.
     
    #1 cPanelNick, Dec 17, 2008
  2. cPanelNick

    cPanelNick Administrator
    Staff Member

    Joined:
    Mar 9, 2015
    Messages:
    3,451
    Likes Received:
    12
    Trophy Points:
    148
    cPanel Access Level:
    DataCenter Provider
    This space held for future posts
     
    #2 cPanelNick, Dec 17, 2008
  3. cPanelNick

    cPanelNick Administrator
    Staff Member

    Joined:
    Mar 9, 2015
    Messages:
    3,451
    Likes Received:
    12
    Trophy Points:
    148
    cPanel Access Level:
    DataCenter Provider
    This space held for future posts (2)
     
    #3 cPanelNick, Dec 17, 2008
  4. dxm

    dxm Active Member

    Joined:
    Aug 1, 2007
    Messages:
    25
    Likes Received:
    0
    Trophy Points:
    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.
     
    #4 dxm, Dec 17, 2008
  5. Tigran

    Tigran Member

    Joined:
    Apr 20, 2005
    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    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?
     
    #5 Tigran, Dec 18, 2008
  6. phoenixweb

    phoenixweb Well-Known Member

    Joined:
    Jun 3, 2004
    Messages:
    70
    Likes Received:
    0
    Trophy Points:
    156
    cPanel Access Level:
    DataCenter Provider
    ... ahi ahi

    More than 20 accounts with data losted!!!!!

    How can we restore theme?

    :eek:
     
    #6 phoenixweb, Dec 18, 2008
  7. mohit

    mohit Well-Known Member

    Joined:
    Jul 12, 2005
    Messages:
    553
    Likes Received:
    0
    Trophy Points:
    166
    Location:
    Sticky On Internet
    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.
     
    #7 mohit, Dec 18, 2008
  8. cPanelNick

    cPanelNick Administrator
    Staff Member

    Joined:
    Mar 9, 2015
    Messages:
    3,451
    Likes Received:
    12
    Trophy Points:
    148
    cPanel Access Level:
    DataCenter Provider
    If there are any problems or you are uncomfortable doing this procedure, do not proceed and open a ticket @
    https://tickets.cpanel.net/submit/
     
    #8 cPanelNick, Dec 18, 2008
  9. phoenixweb

    phoenixweb Well-Known Member

    Joined:
    Jun 3, 2004
    Messages:
    70
    Likes Received:
    0
    Trophy Points:
    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 :(
     
    #9 phoenixweb, Dec 18, 2008
  10. movielad

    movielad Well-Known Member
    PartnerNOC

    Joined:
    May 14, 2003
    Messages:
    107
    Likes Received:
    2
    Trophy Points:
    168
    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)...
     
    #10 movielad, Dec 19, 2008
    Last edited: Dec 19, 2008
  11. phoenixweb

    phoenixweb Well-Known Member

    Joined:
    Jun 3, 2004
    Messages:
    70
    Likes Received:
    0
    Trophy Points:
    156
    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
     
    #11 phoenixweb, Dec 19, 2008
  12. cPanelNick

    cPanelNick Administrator
    Staff Member

    Joined:
    Mar 9, 2015
    Messages:
    3,451
    Likes Received:
    12
    Trophy Points:
    148
    cPanel Access Level:
    DataCenter Provider
    Max,

    If you can just upgrade to MySQL 5.0.x as its InnoDB support is much more reliable.
     
    #12 cPanelNick, Dec 19, 2008
  13. phoenixweb

    phoenixweb Well-Known Member

    Joined:
    Jun 3, 2004
    Messages:
    70
    Likes Received:
    0
    Trophy Points:
    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.
     
    #13 phoenixweb, Dec 19, 2008
  14. LiNUxG0d

    LiNUxG0d Well-Known Member

    Joined:
    Jun 25, 2003
    Messages:
    206
    Likes Received:
    1
    Trophy Points:
    168
    Location:
    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.
     
    #14 LiNUxG0d, Dec 19, 2008
  15. cPanelDavidG

    cPanelDavidG Technical Product Specialist

    Joined:
    Nov 29, 2006
    Messages:
    11,277
    Likes Received:
    8
    Trophy Points:
    313
    Location:
    Houston, TX
    cPanel Access Level:
    Root Administrator
    Do not proceed. Immediately submit a support ticket so our technical analysts can assist you with this: http://tickets.cPanel.net/submit
     
    #15 cPanelDavidG, Dec 19, 2008
  16. StevenC

    StevenC Well-Known Member

    Joined:
    Jan 1, 2004
    Messages:
    252
    Likes Received:
    0
    Trophy Points:
    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.
     
    #16 StevenC, Dec 21, 2008
  17. mhkstr

    mhkstr Member

    Joined:
    Feb 29, 2008
    Messages:
    8
    Likes Received:
    0
    Trophy Points:
    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.
     
    #17 mhkstr, Sep 3, 2009
  18. JeffP.

    JeffP. Well-Known Member

    Joined:
    Sep 28, 2010
    Messages:
    164
    Likes Received:
    13
    Trophy Points:
    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.
     
    #18 JeffP., Sep 20, 2012
  19. xeonex

    xeonex Well-Known Member

    Joined:
    Mar 20, 2005
    Messages:
    56
    Likes Received:
    0
    Trophy Points:
    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
     
    #19 xeonex, Nov 5, 2012
  20. xeonex

    xeonex Well-Known Member

    Joined:
    Mar 20, 2005
    Messages:
    56
    Likes Received:
    0
    Trophy Points:
    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!
     
    #20 xeonex, Nov 5, 2012
(You must log in or sign up to post here.)
Page 1 of 2
Loading...
Similar Threads - MySQL InnoDB crash
  1. Alaa M

    Mysql Fail - InnoDB corruption

    Alaa M, Mar 12, 2016, in forum: Database Discussions
    Replies:
    2
    Views:
    1,051
    cPanelMichael
    Mar 14, 2016
  2. ladydi711

    default storage engine is not InnoDB for MySQL 5.5?

    ladydi711, Feb 24, 2016, in forum: Database Discussions
    Replies:
    4
    Views:
    1,136
    cPanelMichael
    Feb 25, 2016
  3. robertjw

    MySQL restarts without InnoDB

    robertjw, Aug 21, 2015, in forum: Database Discussions
    Replies:
    6
    Views:
    739
    cPanelMichael
    Sep 10, 2015
  4. burn1024

    New Thread Munin mysql2 plugin isn't working

    burn1024, Jun 23, 2017 at 5:41 PM, in forum: General Discussion
    Replies:
    0
    Views:
    26
    burn1024
    Jun 23, 2017 at 5:41 PM
  5. Souther

    MySQL Profiles: user app migration to using remote server

    Souther, Jun 21, 2017 at 11:30 PM, in forum: Database Discussions
    Replies:
    6
    Views:
    63
    Souther
    Jun 22, 2017 at 12:43 PM

Share This Page