mysqldump Too many open files

By Paulus, 20 December, 2016

When runing the mysqldump command there's a possibility that you will receive the error message:

mysqldump: Couldn't execute 'show create table `table`': Out of resources when opening file './database/table.MYD' (Errcode: 24 "Too many open files") (23)

There are several possibilities as to why this error is occuring. Before changing the number of open files allowed, you can try using the --single-transaction command line option.

# mysqldump --all-databases --single-transaction -p > databases.sql

Unfortunately, this didn't work in my case and I had to try several fixes before I was able to do a full database dump.

open_files_limit

The open_files_limit configuration variable and --open-files-limit command line option sets the number of file descriptors available to MariaDB and MySQL.

# mysqld --open-files-limit=4096 --basedir=/usr

Instead of having to specify the number of open files allowed to MariaDB/MySQL open_files_limit can be added to the appropiate configuration file under different sections. For example, If you just want to change the open file limit for mysqldump, then add the open_files_limit setting under the [mysqldump] section.  

[mysqld]
open_files_limit = 4096

[mariadb]
open_files_limit = 4096

[mysqldump]
open_files_limit = 4096
# systemctl restart mariadb

ulimit

The number of files opened by a process may be limited by the OS:

1024
1024
# su -l -s /bin/bash mysql
1024
1024

The command on line 5 logs into the the mysql account so you can verify the file limits for mysql user. The -l and -s /bin/bash are necessary if the mysql user is a system account. To change the number of files being open permanent edit the following file:

mysql soft nofile 4096
mysql hard nofile 4096

systemctl

The above methods will not work if MariaDB/MySQL is being started via systemd. LimitNOFILE and LimitMEMLOCK needs to be added to the [Service] section of the systemd service file for MariaDB/MySQL.

[Unit]
Description=MySQL database server
After=syslog.target
After=network.target
Conflicts=mariadb.service

[Service]
Type=simple
User=mysql
Group=mysql

# Add the following lines and set the value accordingly.
LimitNOFILE = infinity
LimitMEMLOCK = infinity

# Note: we set --basedir to prevent probes that might trigger SELinux alarms,
# https://bugzilla.redhat.com/show_bug.cgi?id=547485
ExecStart=/usr/sbin/mysqld --basedir=/usr
ExecStartPost=/usr/libexec/mysqld-wait-ready $MAINPID

# Give a reasonable amount of time for the server to start up/shut down
TimeoutSec=300

# We rely on systemd, not mysqld_safe, to restart mysqld if it dies
# Restart crashed server only, on-failure would also restart, for example, when
# my.cnf contains unknown option
Restart=on-abort
RestartSec=5s

# Place temp files in a secure directory, not /tmp
PrivateTmp=true

# To allow memlock to be used as non-root user if set in configuration
CapabilityBoundingSet=CAP_IPC_LOCK

# Prevent writes to /usr, /boot, and /etc
ProtectSystem=full

# Currently has issues with SELinux https://jira.mariadb.org/browse/MDEV-10404
# This is safe to uncomment when not using SELinux
#NoNewPrivileges=true

PrivateDevices=true

# Prevent accessing /home, /root and /run/user
ProtectHome=true

UMask=007

[Install]
WantedBy=multi-user.target

Any time a change is made to a systemd file, the daemon needs to be reloaded so it can apply the changes.

# systemctl daemon-reload
# systemctl restart mariadb
Enter password:
MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE 'open%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| open_files_limit | 4162  |
+------------------+-------+
1 row in set (0.00 sec)

The SHOW GLOBAL VARIABLES LIKE 'open%'; is a way of verifying that the changes took. In my case, prior to updating the mariadb.service file, the open_files_limit's value was 1024.