MYSQL OS errno 24 - Too many open files

OS  errno 24 - Too many open files

使用xtrabackup备份时候报错

210716 08:49:48 [01] Copying ./ecology/workrelate_share_permissions.ibd to /backupmysql/data/ecology/workrelate_share_permissions.ibd
xtrabackup: Can't create/write to file '/backupmysql/data/ecology/docwatermarksecsetting.ibd' (OS errno 24 - Too many open files)
[02] xtrabackup: error: cannot open the destination stream for ecology/docwatermarksecsetting.ibd
[02] xtrabackup: Error: xtrabackup_copy_datafile() failed.
[02] xtrabackup: Error: failed to copy datafile.
210716 08:49:48 [01]        ...done
210716 08:49:48 [03]        ...done
210716 08:49:49 >> log scanned up to (515003610)


1.使用ulimit -a 查看shell资源限制，发现open files  限制为1024

[root@eslave01 ~]# 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) 96272
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) 96272
virtual memory          (kbytes, -v) unlimited
file locks                      (-x) unlimited


[root@eslave01 ~]# ulimit  -n
1024


2.登录mysql 查看最大连接数，也被限定在214,配置文件设定的是5120但没有生效

[root@slave01 ~]# mysql -uroot -p

mysql>  show  global  variables like 'max_connec%';
+--------------------+---------+
| Variable_name      | Value   |
+--------------------+---------+
| max_connect_errors | 1000000 |
| max_connections    | 214     |
+--------------------+---------+
4 rows in set (0.01 sec)


mysql> system grep -i max_connections /etc/my.cnf
max_connections = 5120


3.分别修改limits.conf 和my.cnf 打开的文件描述符的限制

[root@eslave01 ~]# grep -i nofile  /etc/security/limits.conf 
#        - nofile - max number of open file descriptors
[root@eslave01 ~]# vi /etc/security/limits.con

* hard nofile 65535
* soft nofile 65535

[root@slave01]# vi /etc/my.cnf

添加

[Service]

LimitNOFILE = 65535
LimitNPROC  = 65535


4.重新登录会话

[root@slave01]# ulimit  -n
65535
[root@slave01]# su - mysql
上一次登录：五 7月 16 08:50:22 CST 2021pts/1 上
[mysql@emaster ~]$ ulimit  -n
65535


5.重启mysql
[root@slave01]#  mysqld_safe --defaults-file=/etc/my.cnf > /mysql/errorlog/error.log 2>&1 &

6.再次查看最大打开连接数和文件限制
mysql>  show  global  variables like 'max_connec%';

+--------------------+---------+
| Variable_name      | Value   |
+--------------------+---------+
| max_connect_errors | 1000000 |
| max_connections    | 5120    |
+--------------------+---------+

mysql> show global variables like "%open_files_limit%";
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| open_files_limit | 65535 |
+------------------+-------+

7.重新开始备份,备份成功
[root@slave01 data]# xtrabackup --defaults-file=/etc/my.cnf --host=192.168.200.147 --user=root --password=mysql123 --port=3306 --backup --parallel=3 --target-dir=/backupmysql/data

MySQL binlog position: filename 'mysql-bin.000019', position '272', GTID of the last change '11408e8c-e297-11eb-b003-0050568d7647:1-666,4597305d-e297-11eb-989c-0050568d551d:1-47401,dbcabeff-e296-11eb-8c0f-0050568d200a:1-54670'
210716 09:05:08 [00] Writing /backupmysql/data/backup-my.cnf
210716 09:05:08 [00]        ...done
210716 09:05:08 [00] Writing /backupmysql/data/xtrabackup_info
210716 09:05:08 [00]        ...done
xtrabackup: Transaction log of lsn (515535895) to (515565651) was copied.
210716 09:05:09 completed OK!