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 not restart

Discussion in 'Database Discussion' started by mahdy_sharifi, Jul 13, 2015.

  1. mahdy_sharifi

    mahdy_sharifi Well-Known Member

    Joined:
    Feb 26, 2012
    Messages:
    209
    Likes Received:
    0
    Trophy Points:
    66
    cPanel Access Level:
    Root Administrator
    Hello :

    I move all our user database to new mysql server (Remote mysql server)
    after start mysql server we can not restart it .

    please see following :

    Code:
    root@mysql [~]# service mysql restart
    Shutting down MySQL....................................................................................................................................................
    .......................................................................................................................................................................
    .......................................................................................................................................................................
    .......................................................................................................................................................................
    .......................................................................................................................................................................
    .................................................................................... ERROR!
    ERROR! Failed to stop running server, so refusing to try to start.
    
    and then mysql version not run :

    Code:
    root@mysql [~]# mysqladmin version
    mysqladmin: connect to server at 'localhost' failed
    error: 'Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)'
    Check that mysqld is running and that the socket: '/var/lib/mysql/mysql.sock' exists!
    and you can see mysql process exist :

    Code:
    root@mysql [~]# ps aux | grep mysql
    root      729300  0.0  0.0  11308  1504 ?        S    09:45   0:00 /bin/sh /usr/bin/mysqld_safe --datadir=/mysql/mysql/ --pid-file=/mysql/mysql//mysql.shosting.net.pid
    mysql     729861  100 22.4 42457756 11070156 ?   Sl   09:45 270:45 /usr/sbin/mysqld --basedir=/usr --datadir=/mysql/mysql/ --plugin-dir=/usr/lib64/mysql/plugin --user=
    mysql --log-error=/mysql/mysql//mysql.shosting.net.err --open-files-limit=1024000 --pid-file=/mysql/mysql//mysql.shosting.net.pid
    and check log :

    Code:
    2015-07-13 13:59:51 729861 [Note] /usr/sbin/mysqld: Normal shutdown
    
    2015-07-13 13:59:51 729861 [Note] Giving 43 client threads a chance to die gracefully
    2015-07-13 13:59:51 729861 [Note] Event Scheduler: Purging the queue. 0 events
    2015-07-13 13:59:51 729861 [Note] Shutting down slave threads
    2015-07-13 13:59:53 729861 [Note] Forcefully disconnecting 42 remaining clients
    2015-07-13 13:59:53 729861 [Warning] /usr/sbin/mysqld: Forcing close of thread 482  user: 'root'
    
    2015-07-13 13:59:53 729861 [Warning] /usr/sbin/mysqld: Forcing close of thread 499  user: 'root'
    
    2015-07-13 13:59:53 729861 [Warning] /usr/sbin/mysqld: Forcing close of thread 642  user: 'root'
    
    2015-07-13 13:59:53 729861 [Warning] /usr/sbin/mysqld: Forcing close of thread 661  user: 'root'
    
    2015-07-13 13:59:53 729861 [Warning] /usr/sbin/mysqld: Forcing close of thread 678  user: 'root'
    
    2015-07-13 13:59:53 729861 [Warning] /usr/sbin/mysqld: Forcing close of thread 713  user: 'root'
    
    2015-07-13 13:59:53 729861 [Warning] /usr/sbin/mysqld: Forcing close of thread 730  user: 'root'
    
    2015-07-13 13:59:53 729861 [Warning] /usr/sbin/mysqld: Forcing close of thread 747  user: 'root'
    
    2015-07-13 13:59:53 729861 [Warning] /usr/sbin/mysqld: Forcing close of thread 764  user: 'root'
    
    2015-07-13 13:59:53 729861 [Warning] /usr/sbin/mysqld: Forcing close of thread 784  user: 'root'
    
    2015-07-13 13:59:53 729861 [Warning] /usr/sbin/mysqld: Forcing close of thread 801  user: 'root'
    
    2015-07-13 13:59:53 729861 [Warning] /usr/sbin/mysqld: Forcing close of thread 870  user: 'root'
    
    2015-07-13 13:59:53 729861 [Warning] /usr/sbin/mysqld: Forcing close of thread 889  user: 'root'
    
    2015-07-13 13:59:53 729861 [Warning] /usr/sbin/mysqld: Forcing close of thread 836  user: 'root'
     
  2. mahdy_sharifi

    mahdy_sharifi Well-Known Member

    Joined:
    Feb 26, 2012
    Messages:
    209
    Likes Received:
    0
    Trophy Points:
    66
    cPanel Access Level:
    Root Administrator
    How can resolve this issue ?
     
  3. vanessa

    vanessa Well-Known Member
    PartnerNOC

    Joined:
    Sep 26, 2006
    Messages:
    834
    Likes Received:
    28
    Trophy Points:
    178
    Location:
    Virginia Beach, VA
    cPanel Access Level:
    DataCenter Provider
    The problem is that MySQL isn't shutting down in order to restart. The init script has a timeout so even though you see ERROR in the output, MySQL is still actually shutting down. Tail the log while it's shutting down and wait for it to finish, then initiate a start. If you get really impatient, you can kill the PID - but that can be dangerous so I don't recommend it. It's also unusual that it would take this long to stop unless you have an insanely busy MySQL server. Usually InnoDB is the culprit, so try this:

    https://www.percona.com/blog/2009/04/15/how-to-decrease-innodb-shutdown-times/
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
  4. mahdy_sharifi

    mahdy_sharifi Well-Known Member

    Joined:
    Feb 26, 2012
    Messages:
    209
    Likes Received:
    0
    Trophy Points:
    66
    cPanel Access Level:
    Root Administrator
    before restart i see following query in server :
    SELECT count(*) FROM tables WHERE ENGINE = 'InnoDB'

    with too much ruining time.
     
  5. vanessa

    vanessa Well-Known Member
    PartnerNOC

    Joined:
    Sep 26, 2006
    Messages:
    834
    Likes Received:
    28
    Trophy Points:
    178
    Location:
    Virginia Beach, VA
    cPanel Access Level:
    DataCenter Provider
    then kill the query.
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
  6. mahdy_sharifi

    mahdy_sharifi Well-Known Member

    Joined:
    Feb 26, 2012
    Messages:
    209
    Likes Received:
    0
    Trophy Points:
    66
    cPanel Access Level:
    Root Administrator
    what is this process ? this query cause mysql slow and problem to restart .

    Code:
    +------+------+-----------+--------------------+---------+------+----------------------+-----------------------------------------------------+
    | Id   | User | Host      | db                 | Command | Time | State                | Info                                                |
    +------+------+-----------+--------------------+---------+------+----------------------+-----------------------------------------------------+
    | 648  | root | localhost | information_schema | Query   | 6815 | checking permissions | SELECT count(*) FROM tables WHERE ENGINE = 'InnoDB' |
    | 767  | root | localhost | information_schema | Query   | 6514 | checking permissions | SELECT count(*) FROM tables WHERE ENGINE = 'InnoDB' |
    | 883  | root | localhost | information_schema | Query   | 6215 | checking permissions | SELECT count(*) FROM tables WHERE ENGINE = 'InnoDB' |
    | 1063 | root | localhost | information_schema | Query   | 5915 | checking permissions | SELECT count(*) FROM tables WHERE ENGINE = 'InnoDB' |
    | 1178 | root | localhost | information_schema | Query   | 5615 | checking permissions | SELECT count(*) FROM tables WHERE ENGINE = 'InnoDB' |
    | 1294 | root | localhost | information_schema | Query   | 5316 | checking permissions | SELECT count(*) FROM tables WHERE ENGINE = 'InnoDB' |
    | 1412 | root | localhost | information_schema | Query   | 5016 | checking permissions | SELECT count(*) FROM tables WHERE ENGINE = 'InnoDB' |
    | 1526 | root | localhost | information_schema | Query   | 4714 | checking permissions | SELECT count(*) FROM tables WHERE ENGINE = 'InnoDB' |
    | 1642 | root | localhost | information_schema | Query   | 4415 | checking permissions | SELECT count(*) FROM tables WHERE ENGINE = 'InnoDB' |
    | 1757 | root | localhost | information_schema | Query   | 4115 | checking permissions | SELECT count(*) FROM tables WHERE ENGINE = 'InnoDB' |
    | 1875 | root | localhost | information_schema | Query   | 3816 | checking permissions | SELECT count(*) FROM tables WHERE ENGINE = 'InnoDB' |
    | 2000 | root | localhost | information_schema | Query   | 3515 | checking permissions | SELECT count(*) FROM tables WHERE ENGINE = 'InnoDB' |
    | 2116 | root | localhost | information_schema | Query   | 3215 | checking permissions | SELECT count(*) FROM tables WHERE ENGINE = 'InnoDB' |
    | 2233 | root | localhost | information_schema | Query   | 2916 | checking permissions | SELECT count(*) FROM tables WHERE ENGINE = 'InnoDB' |
    | 2349 | root | localhost | information_schema | Query   | 2615 | checking permissions | SELECT count(*) FROM tables WHERE ENGINE = 'InnoDB' |
    | 2519 | root | localhost | information_schema | Query   | 2315 | checking permissions | SELECT count(*) FROM tables WHERE ENGINE = 'InnoDB' |
    | 2637 | root | localhost | information_schema | Query   | 2015 | checking permissions | SELECT count(*) FROM tables WHERE ENGINE = 'InnoDB' |
    | 2753 | root | localhost | information_schema | Query   | 1715 | checking permissions | SELECT count(*) FROM tables WHERE ENGINE = 'InnoDB' |
    | 2872 | root | localhost | information_schema | Query   | 1415 | checking permissions | SELECT count(*) FROM tables WHERE ENGINE = 'InnoDB' |
    | 2996 | root | localhost | information_schema | Query   | 1115 | checking permissions | SELECT count(*) FROM tables WHERE ENGINE = 'InnoDB' |
    | 3113 | root | localhost | information_schema | Query   | 815  | checking permissions | SELECT count(*) FROM tables WHERE ENGINE = 'InnoDB' |
    | 3229 | root | localhost | information_schema | Query   | 515  | checking permissions | SELECT count(*) FROM tables WHERE ENGINE = 'InnoDB' |
    | 3344 | root | localhost | information_schema | Query   | 215  | checking permissions | SELECT count(*) FROM tables WHERE ENGINE = 'InnoDB' |
    | 3420 | root | localhost | information_schema | Sleep   | 24   |                      |                                                     |
    | 3428 | root | localhost |                    | Query   | 0    | init                 | show processlist                                    |
    +------+------+-----------+--------------------+---------+------+----------------------+-----------------------------------------------------+
    
     
  7. cPanelMichael

    cPanelMichael Technical Support Community Manager
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    44,749
    Likes Received:
    1,885
    Trophy Points:
    363
    cPanel Access Level:
    Root Administrator
    Twitter:
    Hello :)

    You can try disabling the following option under the "SQL" tab in "WHM >> Tweak Settings" to see if it helps:

    "Use INFORMATION_SCHEMA to acquire MySQL disk usage"

    Per it's description: Quote

    Using INFORMATION_SCHEMA ensures that disk usage by MySQL tables is included in totals. However, enabling this option may cause a significant drop in performance as MySQL may become unresponsive until data collection is complete. Disabling this option causes the system to query the filesystem directly, potentially excluding disk space used by some database tables. Note: If you use a remote MySQL server, you must turn this setting On in order to calculate MySQL disk usage.

    Thank you.
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
    #7 cPanelMichael, Jul 15, 2015
    Last edited by a moderator: Jul 17, 2015
  8. mahdy_sharifi

    mahdy_sharifi Well-Known Member

    Joined:
    Feb 26, 2012
    Messages:
    209
    Likes Received:
    0
    Trophy Points:
    66
    cPanel Access Level:
    Root Administrator
    Hello :

    even after disabling problem not resolved.
     
  9. cPanelMichael

    cPanelMichael Technical Support Community Manager
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    44,749
    Likes Received:
    1,885
    Trophy Points:
    363
    cPanel Access Level:
    Root Administrator
    Twitter:
    Could you elaborate? For instance, do you still see the same output when running the "mysqladmin processlist" command?

    Thank you.
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
Loading...

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.
    Dismiss Notice