Can not increase max_open_files for Mysql max-connections in Ubuntu 15

I suggest you don't copy the existing mysql.service file as suggested, just create a file with only the changes you care about. So do:

mkdir /lib/systemd/system/mysql.service.d
vim /etc/systemd/system/mysql.service.d/limits.conf

And the contents of limits.conf is simply:

[Service]
LimitNOFILE=10000
LimitMEMLOCK=10000

or whatever limits you prefer.

Ubuntu has moved from Upstart to Systemd in version 15.04 and no longer respects the limits in /etc/security/limits.conf for system services. These limits now apply only to user sessions.

The limits for the MySQL service are defined in the Systemd configuration file, which you should copy from its default location into /etc/systemd and then edit the copy.

sudo cp /lib/systemd/system/mysql.service /etc/systemd/system/
sudo vim /etc/systemd/system/mysql.service # or your editor of choice

Add the following lines to the bottom of the file:

LimitNOFILE=infinity
LimitMEMLOCK=infinity

You could also set a numeric limit, eg LimitNOFILE=4510.

Now reload the Systemd configuration with:

sudo systemctl daemon-reload

Restart MySQL and it should now obey the max_connections directive.

I also had problems stopping MySQL cleanly after upgrading to 15.04. If this affects you (you'll know because it will take 300 seconds to time out when you do service mysql stop or service mysql restart) then adding the following line to the same /etc/systemd/system/mysql.service file fixed it for me:

ExecStop=/usr/bin/mysqladmin --defaults-file=/etc/mysql/debian.cnf shutdown

This latter problem seems to have been fixed by 16.04 and this line is no longer required, so before you do a distribution upgrade you'll want to stop MySQL and remove the ExecStop line from the config file.

With systemd the file override.conf does not need to be searched for or possibly created. Just use the following command:

sudo systemctl edit mysql

If the file override.conf exists, it is read in, otherwise it can be created now.

The rest, as Frederik wrote: Enter LimitNOFILE and restart the services.

As of MySQL 5.7.7, this is what the documentation recommends for Red Hat Enterprise Linux 7, Oracle Linux 7, CentOS 7, SUSE Linux Enterprise Server 12, Fedora 24 and 25:

https://dev.mysql.com/doc/refman/5.7/en/using-systemd.html

On Ubuntu 16.04 the service is called mysql, not mysqld, so this is what I did:

sudo mkdir /etc/systemd/system/mysql.service.d
sudo vi /etc/systemd/system/mysql.service.d/override.conf

Added this in the new file override.conf:

[Service]
LimitNOFILE=5000

Then restarted the service:

sudo systemctl daemon-reload
sudo systemctl restart mysql