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
xtrabackup: recognized server arguments: --server-id=21 --log_bin=mysql-bin --datadir=/home/lib/mysql 
xtrabackup: recognized client arguments: 
230322 13:38:14 innobackupex: Starting the backup operation

IMPORTANT: Please check that the backup run completes successfully.
           At the end of a successful backup run innobackupex
           prints "completed OK!".

230322 13:38:14  version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;port=3306;mysql_socket=/home/lib/mysql/mysql.sock' as 'root'  (using password: YES).
Failed to connect to MySQL server as DBD::mysql module is not installed at - line 1327.
230322 13:38:14 Connecting to MySQL server host: localhost, user: root, password: set, port: 3306, socket: /home/lib/mysql/mysql.sock
Using server version 5.7.24-log
/usr/local/xtrabackup/bin/innobackupex version 2.4.20 based on MySQL server 5.7.26 Linux (x86_64) (revision id: c8b4056)
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /home/lib/mysql
xtrabackup: open files limit requested 0, set to 1024
xtrabackup: using the following InnoDB configuration:
xtrabackup:   innodb_data_home_dir = .
xtrabackup:   innodb_data_file_path = ibdata1:12M:autoextend
xtrabackup:   innodb_log_group_home_dir = ./
xtrabackup:   innodb_log_files_in_group = 2
xtrabackup:   innodb_log_file_size = 50331648
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
core file size          (blocks, -c) 0
data seg size           (kbytes, -d) unlimited
scheduling priority             (-e) 0
file size               (blocks, -f) unlimited
pending signals                 (-i) 191192
max locked memory       (kbytes, -l) 64
max memory size         (kbytes, -m) unlimited
open files                      (-n) 1024
pipe size            (512 bytes, -p) 8
POSIX message queues     (bytes, -q) 819200
real-time priority              (-r) 0
stack size              (kbytes, -s) 8192
cpu time               (seconds, -t) unlimited
max user processes              (-u) 191192
virtual memory          (kbytes, -v) unlimited
file locks                      (-x) unlimited
复制

分析： 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
xtrabackup: recognized server arguments: --server-id=21 --log_bin=mysql-bin --datadir=/home/lib/mysql 
xtrabackup: recognized client arguments: 
.........
.........
230322 14:35:31 Executing UNLOCK TABLES
230322 14:35:31 All tables unlocked
230322 14:35:31 [00] Copying ib_buffer_pool to /home/xtrabackup_full/2023-03-22_13-57-10/ib_buffer_pool
230322 14:35:31 [00]        ...done
230322 14:35:31 Backup created in directory '/home/xtrabackup_full/2023-03-22_13-57-10/'
MySQL binlog position: filename 'mysql-bin.000297', position '620931201'
230322 14:35:31 [00] Writing /home/xtrabackup_full/2023-03-22_13-57-10/backup-my.cnf
230322 14:35:31 [00]        ...done
230322 14:35:31 [00] Writing /home/xtrabackup_full/2023-03-22_13-57-10/xtrabackup_info
230322 14:35:31 [00]        ...done
xtrabackup: Transaction log of lsn (427894252071) to (427913862331) was copied.
230322 14:35:31 completed OK!
复制

结论：备份成功，问题解决。