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.

  1. cPanelNick

    cPanelNick Administrator
    Staff Member

    Joined:
    Mar 9, 2015
    Messages:
    3,426
    Likes Received:
    2
    Trophy Points:
    38
    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.
     
  2. cPanelNick

    cPanelNick Administrator
    Staff Member

    Joined:
    Mar 9, 2015
    Messages:
    3,426
    Likes Received:
    2
    Trophy Points:
    38
    cPanel Access Level:
    DataCenter Provider
    This space held for future posts
     
  3. cPanelNick

    cPanelNick Administrator
    Staff Member

    Joined:
    Mar 9, 2015
    Messages:
    3,426
    Likes Received:
    2
    Trophy Points:
    38
    cPanel Access Level:
    DataCenter Provider
    This space held for future posts (2)
     
  4. dxm

    dxm Active Member

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

    Tigran Member

    Joined:
    Apr 20, 2005
    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    1
    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?
     
  6. phoenixweb

    phoenixweb Well-Known Member

    Joined:
    Jun 3, 2004
    Messages:
    68
    Likes Received:
    0
    Trophy Points:
    6
    cPanel Access Level:
    DataCenter Provider
  7. mohit

    mohit Well-Known Member

    Joined:
    Jul 12, 2005
    Messages:
    553
    Likes Received:
    0
    Trophy Points:
    16
    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.
     
  8. cPanelNick

    cPanelNick Administrator
    Staff Member

    Joined:
    Mar 9, 2015
    Messages:
    3,426
    Likes Received:
    2
    Trophy Points:
    38
    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/
     
  9. phoenixweb

    phoenixweb Well-Known Member

    Joined:
    Jun 3, 2004
    Messages:
    68
    Likes Received:
    0
    Trophy Points:
    6
    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 :(
     
  10. movielad

    movielad Well-Known Member
    PartnerNOC

    Joined:
    May 14, 2003
    Messages:
    107
    Likes Received:
    2
    Trophy Points:
    18
    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:
    68
    Likes Received:
    0
    Trophy Points:
    6
    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
     
  12. cPanelNick

    cPanelNick Administrator
    Staff Member

    Joined:
    Mar 9, 2015
    Messages:
    3,426
    Likes Received:
    2
    Trophy Points:
    38
    cPanel Access Level:
    DataCenter Provider
    Max,

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

    phoenixweb Well-Known Member

    Joined:
    Jun 3, 2004
    Messages:
    68
    Likes Received:
    0
    Trophy Points:
    6
    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.
     
  14. LiNUxG0d

    LiNUxG0d Well-Known Member

    Joined:
    Jun 25, 2003
    Messages:
    206
    Likes Received:
    1
    Trophy Points:
    18
    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.
     
  15. cPanelDavidG

    cPanelDavidG Technical Product Specialist

    Joined:
    Nov 29, 2006
    Messages:
    11,279
    Likes Received:
    8
    Trophy Points:
    38
    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
     
  16. StevenC

    StevenC Well-Known Member

    Joined:
    Jan 1, 2004
    Messages:
    254
    Likes Received:
    0
    Trophy Points:
    16
    Should have looked here first! I went and resolved it on my own pretty quickly. This would have been useful. Thanks for the guide.
     
  17. mhkstr

    mhkstr Member

    Joined:
    Feb 29, 2008
    Messages:
    8
    Likes Received:
    0
    Trophy Points:
    1
    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.
     
  18. JeffP.

    JeffP. Well-Known Member

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

    xeonex Well-Known Member

    Joined:
    Mar 20, 2005
    Messages:
    56
    Likes Received:
    0
    Trophy Points:
    6
    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
     
  20. xeonex

    xeonex Well-Known Member

    Joined:
    Mar 20, 2005
    Messages:
    56
    Likes Received:
    0
    Trophy Points:
    6
    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!
     
Loading...

Share This Page