How to set max connections in mysql permanently?

Intrepid Web Studio picture Intrepid Web Studio · Jun 1, 2014 · Viewed 25.1k times · Source

I need some help to set MAX connections value permanently in MySql. I have tried but I can't find a permanent solution. Now, I am using temporary solution by changing in command prompt like in this article.

Answer

Nav picture Nav · Feb 1, 2017

Since this question shows up on the search results when people search for a solution, and now that the solution has changed, I felt it necessary to post an answer.
Ubuntu 15.04+ no longer respects the limits set in /etc/security/limits.conf. So if you set max connections and you don't see any effect, have a look at the log file at sudo vim /var/log/mysql/error.log and you'll see Changed limits: max_connections: 214 (requested 5000).

Solution:
Copy the limits for mysql from the systemd config file to /etc/systemd using:

sudo cp /lib/systemd/system/mysql.service /etc/systemd/system/
sudo vim /etc/systemd/system/mysql.service  

Then add the following lines to the bottom of the file:

LimitNOFILE=infinity
LimitMEMLOCK=infinity 

You could also use a finite number instead of infinity.

Now reload systemd config:

sudo systemctl daemon-reload