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

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

  1. mahdy_sharifi

    mahdy_sharifi Well-Known Member

    Joined:
    Feb 26, 2012
    Messages:
    175
    Likes Received:
    0
    Trophy Points:
    16
    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:
    175
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    How can resolve this issue ?
     
  3. vanessa

    vanessa Well-Known Member
    PartnerNOC

    Joined:
    Sep 26, 2006
    Messages:
    817
    Likes Received:
    22
    Trophy Points:
    18
    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/
     
  4. mahdy_sharifi

    mahdy_sharifi Well-Known Member

    Joined:
    Feb 26, 2012
    Messages:
    175
    Likes Received:
    0
    Trophy Points:
    16
    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:
    817
    Likes Received:
    22
    Trophy Points:
    18
    Location:
    Virginia Beach, VA
    cPanel Access Level:
    DataCenter Provider
    then kill the query.
     
  6. mahdy_sharifi

    mahdy_sharifi Well-Known Member

    Joined:
    Feb 26, 2012
    Messages:
    175
    Likes Received:
    0
    Trophy Points:
    16
    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 Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,678
    Likes Received:
    651
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
    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.
     
    #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:
    175
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    Hello :

    even after disabling problem not resolved.
     
  9. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,678
    Likes Received:
    651
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
    Could you elaborate? For instance, do you still see the same output when running the "mysqladmin processlist" command?

    Thank you.
     
Loading...

Share This Page