解决使用innobackupex备份mysql产生returned OS error 124
xtrabackup 报错Too many open files 故障处理
一、背景
客户反馈数据库备份失败。
二、环境描述
[root@mes-node1 ~]# mysql -V mysql Ver 14.14 Distrib 5.7.24, for Linux (x86_64) using EditLine wrapper [root@mes-node1 ~]# innobackupex --version xtrabackup: recognized server arguments: --server-id=21 --log_bin=mysql-bin --datadir=/home/lib/mysql innobackupex version 2.4.20 Linux (x86_64) (revision id: c8b4056)
三、故障排查
1. 检查备份脚本
[root@mes-node1 xtrabackup_full]# crontab -l 17 2 * * * /home/mysql_bak/backup.sh >>/home/mysql_bak/backup.log 0 4 * * * /home/xtra_backup.sh [root@mes-node1 ~]# cat /home/xtra_backup.sh /usr/local/xtrabackup/bin/innobackupex -uroot -phzjs1234 -S /home/lib/mysql/mysql.sock /home/xtrabackup_full find /home/xtrabackup_full -mtime +1 -exec rm -rf {} \;
2. 检查备份结果
[root@mes-node1 xtrabackup_full]# ls -lrth total 0 drwxr-x--- 2 root root 32 Mar 21 04:00 2023-03-21_04-00-01 drwxr-x--- 2 root root 32 Mar 22 04:00 2023-03-22_04-00-02 [root@mes-node1 xtrabackup_full]# du -sh * 200K 2023-03-21_04-00-01 200K 2023-03-22_04-00-02 [root@mes-node1 xtrabackup_full]# ll 2023-03-21_04-00-01 total 200 -rw-r----- 1 root root 201216 Mar 21 04:00 xtrabackup_logfile
3. 手动执行备份
[root@mes-node1 ~]# /usr/local/xtrabackup/bin/innobackupex -uroot -phzjs1234 -S /home/lib/mysql/mysql.sock /home/xtrabackup_full
InnoDB: Number of pools: 1 230322 13:38:14
>> log scanned up to (427884628939) xtrabackup: Generating a list of tablespaces InnoDB: Allocated tablespace ID 3463 for mysql/servers, old maximum was 0 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 ./quality@002dserver/spc_process_trace_data_T15.ibd: 'open' returned OS error 124. Cannot continue operation InnoDB: Cannot continue operation.
分析： 备份报错，抛出错误信息InnoDB: Error number 24 means ‘Too many open files’.
4.检查数据库open_files_limit参数
mysql> show variables like 'open_files_limit';
+------------------+-------+ | Variable_name | Value | +------------------+-------+ | open_files_limit | 5000 |
+------------------+-------+ 1 row in set (0.01 sec)
5.统计下目前数据库需要打开多少文件
[root@mes-node1 ~]# find /home/lib/mysql -name "*.ibd" |wc -l 1114
分析： 实例的 open_files_limit 的运行参数值为5000，大于备份时需要打开的文件数量，参数设置没有问题。
6.查看OS当前的 open files 数量
[root@mes-node1 xtrabackup_full]# ulimit -a
分析： open files为1024，小于备份时需要打开的文件数量，数量不够，需要修改。
7.修改OS open files 数量
[root@mes-node1 ~]# vi /etc/security/limits.conf
* soft nproc 65536
* hard nproc 65536
* soft nofile 65536
* hard nofile 65536
-- 重新登录session
[root@mes-node1 ~]# ulimit -a|grep open open files (-n) 65536
8.再次手动执行备份
[root@mes-node1 ~]# /usr/local/xtrabackup/bin/innobackupex -uroot -phzjs1234 -S /home/lib/mysql/mysql.sock /home/xtrabackup_full
结论：备份成功，问题解决。
