Tudásbázis

Facing issues like the crashing of MySQL server and unable to restart due to connection timed out when restarting? This problem can occur when your website’s database is large and have high traffic. Due to this when you or WHM wants to restart the MySQL, it sticks there and throws the timeout error.

Above stated problems could happen due to any of the following –

• Low max_open_files limits on both MySQL server and Linux Server.
• Corrupted Database of the website.
• Faulty plugins creating numerous database connections.
• WHM conflicting while changing open file limits.

We will fix this problem using a series of changes that will be discussed in below sections.

Log Inspection

We will check the logs on your server and implement the fixes accordingly.

Log File Location – /var/log/chkservd.log

• Open this log file and search for “system failed”
  You can find the similar log as below after searching the above keywords

   

  Cpanel::RestartSrv::Lock::new(“Cpanel::RestartSrv::Lock”, “mysql”) called at /usr/local/cpanel/Cpanel/ServiceManager/Base.pm line 183
  Cpanel::SafeFile::safeopen(undef, “>>”, “/var/run/restartsrv_mysql”) called at /usr/local/cpanel/Cpanel/RestartSrv/Lock.pm line 25
  Cpanel::SafeFile::_safe_open(undef, “>>”, “/var/run/restartsrv_mysql”, CODE(0xb8ee48), “safeopen”) called at /usr/local/cpanel/Cpanel/SafeFile.pm line 115
  Cpanel::SafeFile::_safelock(“/var/run/restartsrv_mysql”) called at /usr/local/cpanel/Cpanel/SafeFile.pm line 558
  Cpanel::SafeFile::_lock_wait(“/var/run/restartsrv_mysql”) called at /usr/local/cpanel/Cpanel/SafeFile.pm line 355
  Cpanel::SafeFile::_die_if_file_is_flocked_cuz_already_waited_a_while(“/var/run/restartsrv_mysql”, 197) called at /usr/local/cpanel/Cpanel/SafeFile.pm line 829
  Cpanel::SafeFile::_timeout_exception(“/var/run/restartsrv_mysql”, 197) called at /usr/local/cpanel/Cpanel/SafeFile.pm line 761
  Cpanel::Exception::__ANON__(__CPANEL_HIDDEN__, __CPANEL_HIDDEN__…, ARRAY(0x14cce58)) called at /usr/local/cpanel/Cpanel/SafeFile.pm line 729
  Cpanel::Exception::create(“Timeout”, “The system failed to lock the file \x{e2}\x{80}\x{9c}[_1]\x{e2}\x{80}\x{9d} after [quant,_2″…, ARRAY(0x14cce58)) called at /usr/local/cpanel/Cpanel/Exception.pm line 61
  at /usr/local/cpanel/Cpanel/Exception/CORE.pm line 336.

  This means that WHM wants to restart MySQL server but its state is hanged and due to its state SQL server is unable to save the changes and restart safely. Hence it cannot create a lock file.

  For User-Friendly Logs for above problem you can fetch systemd log by running following command
  systemctl --no-pager -l status mysql > systemctl.log
  This saves the log file with name systemctl.log

  You can search for “operation timed out” after opening the saved log file
  You can find the similar log as below after searching the above keywords

  Jun 23 14:23:45 wp mysqld[31554]: 2018-06-23 14:23:45 139794412255424 [Note] /usr/sbin/mysqld (mysqld 10.2.15-MariaDB-log) starting as process 31554 …
  Jun 23 14:23:45 wp systemd[1]: Starting MariaDB 10.2.15 database server…
  Jun 23 14:20:55 wp systemd[1]: mariadb.service failed.
  Jun 23 14:20:55 wp systemd[1]: Unit mariadb.service entered failed state.
  Jun 23 14:20:55 wp systemd[1]: Failed to start MariaDB 10.2.15 database server.
  Jun 23 14:20:55 wp systemd[1]: mariadb.service: main process exited, code=exited, status=1/FAILURE
  Jun 23 14:20:14 wp systemd[1]: mariadb.service start operation timed out. Terminating.
  Jun 23 14:18:44 wp mysqld[30610]: 2018-06-23 14:18:44 140586726332608 [Note] /usr/sbin/mysqld (mysqld 10.2.15-MariaDB) starting as process 30610 …
  Jun 23 14:18:44 wp systemd[1]: Starting MariaDB 10.2.15 database server…

• Check for the open_limits warning on systemctl logs saved in the last section. You can search for “max_open_files” after opening the saved log file
  You can find the similar log as below after searching the above keywords

   

  Jun 23 14:33:47 wp mysqld[32608]: 2018-06-23 14:33:47 140276754827456 [Warning] Could not increase number of max_open_files to more than 10000 (request: 11031)
  Jun 23 14:33:47 wp mysqld[32608]: 2018-06-23 14:33:47 140276754827456 [Warning] Changed limits: max_open_files: 10000 max_connections: 1000 (was 1000) table_cache: 4485 (was 5000)
  Jun 23 14:35:17 wp systemd[1]: mariadb.service start operation timed out. Terminating.
  Jun 23 14:35:58 wp systemd[1]: mariadb.service: main process exited, code=exited, status=1/FAILURE
  Jun 23 14:35:58 wp systemd[1]: Failed to start MariaDB 10.2.15 database server.
  Jun 23 14:35:58 wp systemd[1]: Unit mariadb.service entered failed state.
  Jun 23 14:35:58 wp systemd[1]: mariadb.service failed.
  Jun 23 14:36:28 wp systemd[1]: Starting MariaDB 10.2.15 database server…
  Jun 23 14:36:29 wp mysqld[962]: 2018-06-23 14:36:29 140591034276032 [Note] /usr/sbin/mysqld (mysqld 10.2.15-MariaDB-log) starting as process 962 …
  Jun 23 14:36:29 wp mysqld[962]: 2018-06-23 14:36:29 140591034276032 [Warning] Could not increase number of max_open_files to more than 10000 (request: 11031)
  Jun 23 14:36:29 wp mysqld[962]: 2018-06-23 14:36:29 140591034276032 [Warning] Changed limits: max_open_files: 10000 max_connections: 1000 (was 1000) table_cache: 4485 (was 5000)

So with this, we finish the logs inspections. The further section of this guide will show how we can fix the above-found MySQL Server Problems

Solution Section

Dis-Allowing cPanel & WHM to change MySQL open_files_limit configuration

• Login to WHM and Search for Tweak Settings open it.
• Inside Tweak Settings goto SQL Tab and look toggle for Allow cPanel & WHM to determine the best value for your MySQL open_files_limit configuration?
  We will turn it off and press the save button below.

Increasing Linux open limits in CentOS

• Log in to the root of your Web Server.
• Edit File – /etc/security/limits.conf
  At the very bottom of this file, add the following lines and save it

   

  # Performance Tuning
  *       soft    nproc   32768
  *       hard    nproc   65535
  *       soft    nofile  32768
  *       hard    nofile  65535
  root    soft    nproc   32768
  root    hard    nproc   65535
  root    soft    nofile  32768
  root    hard    nofile  65535

• Now edit the file: /etc/sysctl.conf
  At the very bottom of this file, add the following lines and save it

   

  # Performance Tuning
  fs.file-max = 2097152
  net.core.netdev_max_backlog = 131070
  net.core.somaxconn = 131070
  net.ipv4.tcp_max_syn_backlog = 3240000
  net.ipv4.tcp_max_tw_buckets = 1440000
  net.ipv4.tcp_window_scaling = 1
  vm.swappiness = 30

• To apply these changes, simply type this command as root user via SSH:

  sysctl -p

  Note – If you error with “net.core.somaxconn” not being merged, change its value to “65535” and then re-merge with commandsysctl -p.

  The changes are now active and your system is able to handle more concurrent network connections.

• Finally, reboot your server.

Increasing MySQL open limits

• Log in to the root of your Web Server.
• Check whether you actually exceed the open_files limit by executing the following SQL queriesSHOW GLOBAL STATUS LIKE 'Open_files';
  The above command will give you the number of currently open files.

   

  Also, confirm the maximum value set for the open_files

  SHOW VARIABLES LIKE 'open_files_limit';

• Check if your server uses MariaDB or MySQL. Run the following command.mysql --version

  
  MySQL Output

  
  MariaDB Output

• Now we need to edit my.cnf setting config file and change LimitNOFILE value.
  We will change value from 10000 to 30000For MySQL Server – /etc/systemd/system/mysqld.service.d/limits.conf

   

  

  For MariaDB Server – /etc/systemd/system/mariadb.service.d/migrated-from-my.cnf-settings.conf
  
  

• Run the following command to apply the changes.systemctl daemon-reload && /scripts/restartsrv_mysql 

  The first part of that reloads the new limit data, and the second restart the service to start with the new limit data.

• Reboot your server.
• After the successful reboot of the server, we will again run below SQL Queries.SHOW GLOBAL STATUS LIKE 'Open_files';
SHOW VARIABLES LIKE 'open_files_limit';

   

  

With the solution above discussed your problems stated initially in this guide will be fixed and you will get a clean chkservd.log file without any MySQL failure Log Spam.