How to resolve cannot set max_connections through my.cnf

I have some problem with MySql/MariaDB configuration to set max_connections parameter in /etc/my.cnf but MariaDB does not seem to read the parameter from the file. This appears to be some bug in the new mysqld. Setting max_connections to 1000, it was  reset to 214 during service restart/startup.

# grep -i 'max_connections' /etc/my.cnfmax_connections=1000

# systemctl restart mariadb

MariaDB [(none)]> show variables like 'max_connections';+-----------------+-------+| Variable_name   | Value |+-----------------+-------+| max_connections | 214   |+-----------------+-------+1 row in set (0.00 sec)

If we see 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

It is caused because of open_files_limit for MySQL has been reached.

To resolve it, you can use following steps:

Find out whether MySQL or MariaDB installed:

# systemctl list-unit-files | grep -E 'mysql|mariadb'mariadb.service enabled

Create an override for the service file from the previous step:

# systemctl edit mariadb.service

Note: In case mysql.service, or mysqld.service was displayed on the previous step, replace “mariadb.service” with it.

Add the following content to the open text editor and save the file:

[Service]LimitNOFILE=4096

Then, restart MariaDB server:

# systemctl restart mysql mysqld mariadb 2>/dev/null

References: https://support.plesk.com/hc/en-us/articles/213393029-MySQL-values-open-files-limit-and-max-connections-are-not-applied

Originally posted 2019-04-13 14:31:14.