Set Limit for open files for MySQL on Ubuntu 16.04 with systemd

Sometimes you get the error

Out of resources when opening file './database/table.MYD' (Errcode: 24 - Too many open files)

To fix this on Ubuntu 16.04 you have to change the limits and to change the systemd-config:

1. Change your limits

vi /etc/security/limits.d/99-openfiles.conf

#<domain>      <type>  <item>         <value>
mysql          soft    nofile         8192
mysql          hard    nofile         8192

If you want to set the limits for all users and for root use this:

#<domain>      <type>  <item>         <value>
*              soft    nofile         8192
*              hard    nofile         8192
root           soft    nofile         8192
root           hard    nofile         8192

2. Change systemd-config

To create new override-file use this command:

systemctl edit mysql

Now a new file will be created and you are in your default editor (e.g. vim):

[Service]
LimitNOFILE=8192

(Leave editor with :x)

systemctl daemon-reload
service mysql restart

3. Control MySQL-Settings

By default for MySQL the variable open_files_limit is set to 5000. To adjust this parameter:

vi /etc/mysql/mysql.conf.d/xx-openfiles.cnf

[mysqld]
open_files_limit        = 20000

Or you can add this variable in your /etc/mysql/my.cnf in section [mysqld].