Symptoms

• The value of max_connections changes to 214 automatically after some time, although the value itself is defined in my.cnf:

  # grep -i 'max_connections' /etc/my.cnf
  max_connections=2048
  
  # plesk db "SHOW GLOBAL VARIABLES LIKE 'max_connections'"
  | max_connections                                   | 214

• The value of open_files_limit is reset after system updates are installed.

• In the log file /var/log/mysqld.log the following records can be found:

  [Warning] Changed limits: max_open_files: 1024  max_connections: 214  table_cache: 400

• When restoring a MySQL backup, the following error is shown:

  Warning: mysql "databasename"
  Not all the data was backed up into [...]/databasename_1 successfully. mysqldump: Got error: 23: Out of resources when opening file '[...]' (Errcode: 24 - Too many open files) when using LOCK TABLES 

Cause

open_files_limit for MySQL has been reached.

Resolution

Adjust open_files_limit for the MySQL service. For that, check the current value:

# plesk db "SHOW GLOBAL VARIABLES LIKE 'open_files_limit'"
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| open_files_limit | 1024  |
+------------------+-------+

On systemd systems:

1. Create limit_nofile.conf file:

   • For Debian-based distributions:

     Create /lib/systemd/system/mysql.service.d/limit_nofile.conf file with the following content:

     # cat /lib/systemd/system/mysql.service.d/limit_nofile.conf
     [Service]
     LimitNOFILE=4096

   • For RHEL-based distributions:

     Create /usr/lib/systemd/system/mariadb.service.d/limit_nofile.conf file with the following content:

     # cat /usr/lib/systemd/system/mariadb.service.d/limit_nofile.conf
     [Service]
     LimitNOFILE=4096

2. Reload daemons and restart MySQL service:

   # systemctl daemon-reload
   # systemctl restart mysql

On System V systems:

• For Debian-based distributions:

  1. Add the following lines in /etc/security/limits.conf:

     mysql             soft    nofile           4096
     mysql             hard    nofile           4096

  2. Add the following to the end of file into /etc/pam.d/common-session :

     session required pam_limits.so

  3. Add the following to the end of file into /etc/pam.d/common-session-noninteractive:

     session required pam_limits.so

  4. Add the following line in /etc/mysql/my.cnf and under the [mysqld] section:

     open_files_limit = 4096

  5. Restart the MySQL service.

• For RHEL-based distributions:

  1. Add the following line in /etc/sysctl.conf:

     fs.file-max = 65536

  2. Edit the file /etc/security/limits.conf and add lines:

     *          soft     nproc          40960
     *          hard     nproc          40960
     *          soft     nofile         40960
     *          hard     nofile         40960

  3. Edit the file /etc/security/limits.d/90-nproc.conf and add lines:

     *          soft     nproc          40960
     *          hard     nproc          40960
     *          soft     nofile         40960
     *          hard     nofile         40960

  4. Add the following line in /etc/my.cnf and under the [mysqld] section:

     open_files_limit = 4096

  5. Restart the MySQL service.