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.