Please whitelist cPanel in your adblocker so that you’re able to see our version release promotions, thanks!

The Community Forums

Interact with an entire community of cPanel & WHM users!

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

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

Page 1 of 2
  1. cPanelNick

    cPanelNick Administrator
    Staff Member

    Joined:
    Mar 9, 2015
    Messages:
    3,483
    Likes Received:
    31
    Trophy Points:
    158
    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,483
    Likes Received:
    31
    Trophy Points:
    158
    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,483
    Likes Received:
    31
    Trophy Points:
    158
    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:
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
    #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.
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
    #7 mohit, Dec 18, 2008
  8. cPanelNick

    cPanelNick Administrator
    Staff Member

    Joined:
    Mar 9, 2015
    Messages:
    3,483
    Likes Received:
    31
    Trophy Points:
    158
    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 :(
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
    #9 phoenixweb, Dec 18, 2008
  10. movielad

    movielad Well-Known Member

    Joined:
    May 14, 2003
    Messages:
    109
    Likes Received:
    2
    Trophy Points:
    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)...
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
    #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
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
    #11 phoenixweb, Dec 19, 2008
  12. cPanelNick

    cPanelNick Administrator
    Staff Member

    Joined:
    Mar 9, 2015
    Messages:
    3,483
    Likes Received:
    31
    Trophy Points:
    158
    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.
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
    #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.
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
    #14 LiNUxG0d, Dec 19, 2008
  15. cPanelDavidG

    cPanelDavidG Technical Product Specialist

    Joined:
    Nov 29, 2006
    Messages:
    11,216
    Likes Received:
    10
    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.
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
    #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:
    15
    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
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
    #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!
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
    #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. Peter Horvath

    InnoDB crashed, no MySQL service available

    Peter Horvath, Apr 17, 2018, in forum: Database Discussion
    Replies:
    4
    Views:
    444
    cPanelMichael
    May 15, 2018
  2. kernow

    SOLVED [CPANEL-22320] Table mysql/innodb_index_stats has length mismatch

    kernow, Nov 11, 2018, in forum: Database Discussion
    Replies:
    5
    Views:
    357
    kernow
    Nov 13, 2018
  3. cosmin

    MySQL InnoDB errors after upgrade to 5.7

    cosmin, Jun 20, 2018, in forum: Database Discussion
    Replies:
    2
    Views:
    498
    cPanelMichael
    Jun 21, 2018
  4. Mayc

    Null Mysql Daemon Innodb Process

    Mayc, Feb 7, 2018, in forum: Database Discussion
    Replies:
    5
    Views:
    470
    cPanelMichael
    Feb 12, 2018
  5. MarceloKonrath

    mysql.innodb_index_stats are not present

    MarceloKonrath, Nov 3, 2017, in forum: Database Discussion
    Replies:
    3
    Views:
    1,523
    cPanelMichael
    Nov 7, 2017

Share This Page

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Accept Learn More...
    Dismiss Notice