This is always an interesting one, comes up from time to time especially if you run a server with heavy database traffic.

Possibly you’ve modified you my.cnf with various tweaked settings for ‘max_connections’ or ‘open_files_limit’ but your your ‘mysqld.log’ you start getting:

<span class="pun">[</span><span class="pln">Warning</span><span class="pun">]</span><span class="pln"> Buffered warning</span><span class="pun">:</span><span class="pln"> Changed limits</span><span class="pun">:</span><span class="pln"> max_open_files</span><span class="pun">:</span> <span class="lit">1024</span> <span class="pun">(</span><span class="pln">requested </span><span class="lit">5000</span><span class="pun">)</span><br />

<span class="pun">[</span><span class="pln">Warning</span><span class="pun">]</span><span class="pln"> Buffered warning</span><span class="pun">:</span><span class="pln"> Changed limits</span><span class="pun">:</span><span class="pln"> max_connections</span><span class="pun">:</span> <span class="lit">214</span> <span class="pun">(</span><span class="pln">requested </span><span class="lit">800</span><span class="pun">)</span><br />

<span class="pun">[</span><span class="pln">Warning</span><span class="pun">]</span><span class="pln"> Buffered warning</span><span class="pun">:</span><span class="pln"> Changed limits</span><span class="pun">:</span><span class="pln"> table_open_cache</span><span class="pun">:</span> <span class="lit">400</span> <span class="pun">(</span><span class="pln">requested </span><span class="lit">2000</span><span class="pun">)</span>

What gives ? Well the values of various settings get reset after system updates are installed.

So…

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</pre>
  
  3. Edit the file `/etc/security/limits.d/90-nproc.conf` and add lines: 
    <pre>*          soft     nproc          40960
  

•      hard     nproc          40960
  

•      soft     nofile         40960
  

•      hard     nofile         40960</pre>
  
  4. Add the following line in `/etc/my.cnf` and under the `[mysqld]` section: 
    <pre>open_files_limit = 4096</pre>
  
  5. Restart the MySQL service.