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.


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.  

open_files_limit = 4096

open_files_limit = 4096

open_files_limit = 4096
# systemctl restart mariadb


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

# su -l -s /bin/bash mysql

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


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.

Description=MySQL database server


# 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,
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

# 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

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

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

# Prevent writes to /usr, /boot, and /etc

# Currently has issues with SELinux
# This is safe to uncomment when not using SELinux


# Prevent accessing /home, /root and /run/user



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.