Resolving Xtrabackup Backup Failures Caused by Too Many Open Files in MySQL

Background

When using the xtrabackup tool to back up a MySQL 5.7 instance that contains a large number of .ibd files, the backup may fail because the number of files opened during the backup exceeds the open_files_limit allowed for the backup user, resulting in the error "Too many open files".

220330 08:28:47 >> log scanned up to (328701072168) InnoDB: Operating system error number 24 in a file operation. InnoDB: Error number 24 means 'Too many open files' InnoDB: File ./stage/ts_cg_inteltaxtochanges #P#P_20220322.ibd: 'open' returned OSerror 124. Cannot continue operation InnoDB: Cannot continue operation.

Simulated Fault Scenario

1. Environment

MySQL version: 5.7.35

Xtrabackup version: 2.4.24

2. Check current open‑files parameters

Operating system limit:

# ulimit -a
... 
open files (-n) 1024
...

MySQL configuration ( my.cnf ) values:

innodb_file_per_table = 1
open_files_limit = 20000

Backup user ( actiontech-mysql ) limits in /etc/security/limits.d/actiontech-mysql.conf :

actiontech-mysql soft nofile 10240
actiontech-mysql hard nofile 10240
actiontech-mysql soft nproc 65535
actiontech-mysql hard nproc 65535

Generate many tables with sysbench:

# /usr/share/sysbench/oltp_read_write.lua --table-size=1000 --tables=12000 ... prepare

3. Reproduce the error

• Non‑root user ( actiontech-mysql ) backup:

# su -s $(which bash) actiontech-mysql
bash-4.2$ xtrabackup --defaults-file=/opt/mysql/etc/3306/my.cnf --user=root --password=1 \
--socket=/opt/mysql/data/3306/mysqld.sock --backup --target-dir=/opt/mysql/backup/3306
xtrabackup: open files limit requested 20000, set to 10240
... InnoDB: Operating system error number 24 ... 'Too many open files' ...

• Root user backup (after lowering open_files_limit to 10000 in my.cnf and restarting MySQL):

# xtrabackup --defaults-file=/opt/mysql/etc/3306/my.cnf --user=root --password=1 \
--socket=/opt/mysql/data/3306/mysqld.sock --backup --target-dir=/opt/mysql/backup/3306
xtrabackup: open files limit requested 10000, set to 10000
... InnoDB: Operating system error number 24 ... 'Too many open files' ...

Both cases successfully reproduce the failure.

Fault Analysis

Counting the required open files:

# find /opt/mysql/data/3306 -name "*.ibd" | wc -l
12945

Although the MySQL open_files_limit is set to 20000 (greater than 12945), the backup still fails because the effective limit for the backup user is 10240, which is lower than the number of files that need to be opened.

For the non‑root user, the limit is taken from the user’s nofile settings (10240). For the root user, there is no per‑user restriction, so the limit comes directly from the MySQL configuration (10000 in the example).

Summary and Recommendations

When using xtrabackup as a non‑root user, ensure the user’s open files limit exceeds the number of files that will be opened during backup. Increase the nofile soft and hard values in /etc/security/limits.d or temporarily raise it with ulimit -n .

When using xtrabackup as root, verify that the open_files_limit value in my.cnf is sufficient, or pass a larger value via the --open-files-limit option, or set ulimit -n before running the backup.

Make sure the system-wide maximum file descriptor count ( fs.file-max ) is large enough; otherwise the backup process may exhaust the global limit and cause MySQL or other services to fail.

Example commands to adjust limits:

# Permanently set user limits
* soft nofile 20000
* hard nofile 20000
# Or temporarily for the session
ulimit -n 20000

# Increase MySQL config
[mysqld]
open_files_limit=20000

# Pass via command line
xtrabackup ... --open-files-limit=20000

# Increase system file‑max
sysctl -w fs.file-max=5000000
echo "fs.file-max=5000000" >> /etc/sysctl.conf

Following these adjustments prevents the "Too many open files" error and allows successful backups of MySQL instances with a large number of tables.