Knowledge Base

MySQL values open_files_limit and max_connections are not applied

In 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

When restoring a MySQL backup, the following error is shown:

Warning: mysql “databasename”
Not all the data was backed up into […]/databasename_1 successfully. mysqldump: Got error: 23: Out of resources when opening file ‘[…]’ (Errcode: 24 – Too many open files) when using LOCK TABLES
Unable to export database in Plesk:
PLESK_ERROR: Unable to export a dump of databasename:
mysqldump: Got error: 1016: “Can’t open file: ‘./databasename/table.frm’ (errno: 24)” when using LOCK TABLES

Default open_files_limit for MySQL has been reached.

Connect to the server via SSH and adjust open_files_limit for the MySQL service as below.

Find out whether MySQL, or MariaDB service is used:

# 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 opened text editor and save the file:
```
[Service]
LimitNOFILE=4096
```
Note: Value might be increased if required, 4096 is only a suggested value.
Restart the MySQL/MariaDB server:

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

Add the following lines in /etc/security/limits.conf:
```
mysql soft nofile 4096
mysql hard nofile 4096
```
Add the following to the end of file into /etc/pam.d/common-session :
```
session required pam_limits.so
```
Add the following to the end of file into /etc/pam.d/common-session-noninteractive:
```
session required pam_limits.so
```
Add the following line in /etc/mysql/my.cnf and under the [mysqld] section:
```
open_files_limit = 4096
```
Restart the MySQL service:

# service mysql restart

Edit the file /etc/security/limits.conf and add lines:

* soft nproc 40960
* hard nproc 40960
* soft nofile 40960
* hard nofile 40960

Edit the file /etc/security/limits.d/90-nproc.conf and add lines:

* soft nproc 40960
* hard nproc 40960
* soft nofile 40960
* hard nofile 40960

Add the following line in /etc/my.cnf and under the [mysqld] section:
```
open_files_limit = 4096
```
Restart the MySQL service:

# service mariadb restart