存在大量表的MySQL系统中，出现xtrabackup 问题“Too many open files”
In this blog post, we’ll find out how to use Percona XtraBackup on a MySQL instance with a large number of tables.
As of Percona Xtrabackup 2.4.5, you are required to have enough open files to open every single InnoDB tablespace in the instance you’re trying to back up. So if you’re running innodb_file_per_table=1, and have a large number of tables, you’re very likely to see Percona XtraBackup fail with the following error message:
InnoDB: Operating system error number 24 in a file operation.
InnoDB: Error number 24 means ‘Too many open files’
InnoDB: Some operating system error numbers are described at http://dev.mysql.com/doc/refman/5.7/en/operating-system-error-codes.html
InnoDB: File ./sbtest/sbtest132841.ibd: ‘open’ returned OS error 124. Cannot continue operation
InnoDB: Cannot continue operation.
InnoDB: Operating system error number 24 in a file operation.
InnoDB: Error number 24 means ‘Too many open files’
InnoDB: Some operating system error numbers are described at http://dev.mysql.com/doc/refman/5.7/en/operating-system-error-codes.html
InnoDB: File ./sbtest/sbtest132841.ibd: ‘open’ returned OS error 124. Cannot continue operation
InnoDB: Cannot continue operation.
If you run into this issue, here is what you need to do:
1 Find out how many files you need:
root@ts140i:~# find /var/lib/mysql/ -name “*.ibd” | wc -l
1000005
I would add at least another 1000 to this number for system tablespace and other miscellaneous open file needs. You might want to go even higher to accommodate for a growing number of tables.
对于系统表空间和其他杂项打开文件需求，我将至少再添加 1000 到这个数字。您可能想要更高以容纳越来越多的表。
- Check the maximum number of files you can keep open in the system. If this number is too small Percona Xtrabackup might monopolize the open files in the system, causing other processes to fail when they try to open files. This can cause MySQL Server to crash, and other processes to fail.
检查您可以在系统中保持打开的最大文件数。如果这个数字太小，Percona Xtrabackup 可能会独占系统中打开的文件，导致其他进程在尝试打开文件时失败。这可能会导致 MySQL 服务器崩溃，以及其他进程失败。
root@ts140i:/mnt/data/backup# cat /proc/sys/fs/file-max
3262006
增加的方法：
sysctl -w fs.file-max=5000000
echo "fs.file-max=5000000" >> /etc/sysctl.conf
- Increase the limit on the number of files the Percona XtraBackup process can open:
The best way to do this is using —open-files-limit option. For example, you can specify the following in your my.cnf:
增加 Percona XtraBackup 进程可以打开的文件数量限制：
要做到这一点，最好的办法是使用 —open-files-limit 选项。例如，您可以在 my.cnf 中指定以下内容：
[xtrabackup]
open-files-limit=2000000
或者，您可以将其作为命令行选项传递，或在运行备份命令之前运行ulimit -n 2000000 。
您需要确保您的用户帐户具有将打开文件限制设置为如此高的权限。如果您在“root”用户下进行备份，那应该没有问题。否则，您可能需要调整*/etc/security/limits.conf 中*的限制：
mysql hard nofile 2000000
mysql soft nofile 2000000
在此文件中指定“软”限制消除了在 Percona XtraBackup 之前运行 ulimit 或在配置中指定它的需要。
- 还有一个可能的限制需要克服。即使以 root 用户身份运行，您也可能会收到以下错误消息：
root@ts140i:/mnt/data/backup# ulimit -n 2000000
-su: ulimit: open files: cannot modify limit: Operation not permitted
如果发生这种情况，您可能需要增加内核对任何可以拥有的进程数的限制：
pz@ts140i:~$ cat /proc/sys/fs/nr_open
1048576
我对这个系统的限制略高于 100 万。您可以使用以下方法增加它：
sysctl -w fs.nr_open=2000000
echo “fs.nr_open=2000000” >> /etc/sysctl.conf
通过这些配置调整，您应该能够使用 Percona XtraBackup 来备份包含数百万表的 MySQL 实例而不会出现问题。
如果您不允许 Percona XtraBackup 打开那么多文件怎么办？然后是选项 –close-files 通常不需要增加打开文件数的限制。但是，如果您在备份期间执行 DDL 操作，则使用此选项可能会导致备份损坏。
这个要求你保持所有表空间打开的奇怪限制来自哪里？它来自 这个问题 。在某些情况下，RENAME TABLE 等 DDL 操作可能会导致复制错误的文件，无法通过回复 InnoDB 重做日志来赶上。保持文件打开会清楚地显示在备份过程开始时哪个文件对应于给定的表空间，并得到正确处理。
这个问题并不是 Percona XtraBackup 独有的。如果有的话，Percona Xtrabackup 会加倍努力确保数据库备份的安全。为了进行比较，MySQL Enterprise Backup 4.0 简单 说明 ：
“不要在备份操作正在进行时运行 DDL 操作ALTER TABLE 、TRUNCATE TABLE 、OPTIMIZE TABLE 、REPAIR TABLE 、RESTORE TABLE或CREATE INDEX 。生成的备份可能会损坏。”
With these configuration adjustments, you should be able to use Percona XtraBackup to backup MySQL instances containing millions of tables without problems.
What if you can’t allow Percona XtraBackup to open that many files? Then there is the option –close-files that won’t normally require increasing the limit to the number of open files. Using this option, however, might cause the backup corruption if you’re doing DDL operations during the backup.
From where does this strange limitation requiring you to keep all tablespaces open come? It comes from this issue . In some cases, DDL operations such as RENAME TABLE might cause the wrong file to be copied, and unable to be caught up by replying to InnoDB redo logs. Keeping the file open clearly shows which file corresponds to a given tablespace at the start of a backup process, and gets handled correctly.
This problem is not unique to Percona XtraBackup. If anything, Percona Xtrabackup goes the extra mile to ensure database backups are safe. For comparison, MySQL Enterprise Backup 4.0 simply states :
“Do not run the DDL operations ALTER TABLE, TRUNCATE TABLE, OPTIMIZE TABLE, REPAIR TABLE, RESTORE TABLE or CREATE INDEX while a backup operation is going on. The resulting backup might become corrupted.”
xtrabackup 参数选项参考：
The innobackupex Option Reference
