mysql中innodb_open_files限制导致数据库异常重启

问题描述：收到监控软件告警，提示数据库发生重启，进去查看，截止到6/27 10:00 之前，作为主节点的orch1先重启，然后故障转移到orch2和orch3节点上。在持续到6/27 9:00 左右，orch2又接着重启，到现在只剩下一个orch3节点单独支撑业务。

环境：

redhat7.6+orchestrator+MySQL8.0.26+一主两从的物理机集群

orch1 主库

orch2 从库

orch3 从库

orch1宕机，vip会漂移到orch2，选举为新的主库，orch2宕机，故障转移到orch3，此时会变成单节点

1.数据库提示重启，首先需要查看节点orch1现在的状态，在1d 22h之前发生了重启，此时状态正常

mysql> status;
--------------
/soft/mysql/bin/mysql  Ver 8.0.26 for Linux on x86_64 (MySQL Community Server - GPL)

Connection id:        1929
Current database:    
Current user:        root@localhost
SSL:            Not in use
Current pager:        stdout
Using outfile:        ''
Using delimiter:    ;
Server version:        8.0.26 MySQL Community Server - GPL
Protocol version:    10
Connection:        Localhost via UNIX socket
Server characterset:    utf8mb4
Db     characterset:    utf8mb4
Client characterset:    utf8mb4
Conn.  characterset:    utf8mb4
UNIX socket:        /home/mysql/db_orch1/mysql.sock
Binary data as:        Hexadecimal
Uptime:            1 day 22 hours 39 min 34 sec

Threads: 11  Questions: 1071929  Slow queries: 0  Opens: 770  Flush tables: 3  Open tables: 355  Queries per second avg: 6.381
--------------

查看错误日志，有一个重要的ERROR信息，提示too many open files0

2022-06-25T10:29:05.298867+08:00 0 [ERROR] [MY-012592] [InnoDB] Operating system error number 24 in a file operation.
2022-06-25T10:29:05.298916+08:00 0 [ERROR] [MY-012596] [InnoDB] Error number 24 means 'Too many open files'
2022-06-25T10:29:05.298937+08:00 0 [ERROR] [MY-012646] [InnoDB] File ./bznaqgk/sc_task_position.ibd: 'open' returned OS error 124. Cannot continue operation
2022-06-25T10:29:05.298950+08:00 0 [ERROR] [MY-012981] [InnoDB] Cannot continue operation.
2022-06-25T02:29:06.331542Z mysqld_safe Number of processes running now: 0

2.查看orch2节点的状态以及错误日志，二节点在55min之前发生了重启　　

mysql> status;
--------------
/soft/mysql/bin/mysql  Ver 8.0.26 for Linux on x86_64 (MySQL Community Server - GPL)

Connection id:        53
Current database:    
Current user:        root@10.157.200.8
SSL:            Cipher in use is TLS_AES_256_GCM_SHA384
Current pager:        stdout
Using outfile:        ''
Using delimiter:    ;
Server version:        8.0.26 MySQL Community Server - GPL
Protocol version:    10
Connection:        10.157.200.8 via TCP/IP
Server characterset:    utf8mb4
Db     characterset:    utf8mb4
Client characterset:    utf8mb4
Conn.  characterset:    utf8mb4
TCP port:        13306
Binary data as:        Hexadecimal
Uptime:            55 min 27 sec

Threads: 11  Questions: 20655  Slow queries: 0  Opens: 326  Flush tables: 3  Open tables: 232  Queries per second avg: 6.208

查看二节点的错误日志，这次就比较清晰，得到的错误 [InnoDB] Open files 406 exceeds the limit 400，innodb打开文件数受到了限制，所以下面就要查看一下数据库有关innodb有关文件数的限制

2022-06-26T12:56:32.140572+08:00 0 [Warning] [MY-012152] [InnoDB] Open files 406 exceeds the limit 400
2022-06-26T13:18:19.851180+08:00 0 [Warning] [MY-012152] [InnoDB] Open files 406 exceeds the limit 400
2022-06-26T19:09:27.950568+08:00 0 [Warning] [MY-012152] [InnoDB] Open files 406 exceeds the limit 400
2022-06-26T19:17:09.227432+08:00 0 [Warning] [MY-012152] [InnoDB] Open files 407 exceeds the limit 400
2022-06-26T19:17:09.227462+08:00 0 [Warning] [MY-012152] [InnoDB] Open files 407 exceeds the limit 400
2022-06-26T23:46:27.098079+08:00 0 [Warning] [MY-012152] [InnoDB] Open files 406 exceeds the limit 400
2022-06-27T06:50:13.584841+08:00 0 [Warning] [MY-012152] [InnoDB] Open files 406 exceeds the limit 400
2022-06-27T07:50:18.753974+08:00 0 [Warning] [MY-012152] [InnoDB] Open files 406 exceeds the limit 400
2022-06-27T08:03:56.200996+08:00 0 [Warning] [MY-012152] [InnoDB] Open files 407 exceeds the limit 400
2022-06-27T08:03:56.201006+08:00 0 [Warning] [MY-012152] [InnoDB] Open files 407 exceeds the limit 400
2022-06-27T08:03:56.201011+08:00 0 [Warning] [MY-012152] [InnoDB] Open files 407 exceeds the limit 400
2022-06-27T08:13:20.471561+08:00 0 [ERROR] [MY-012592] [InnoDB] Operating system error number 24 in a file operation.
2022-06-27T08:13:20.471608+08:00 0 [ERROR] [MY-012596] [InnoDB] Error number 24 means 'Too many open files'
2022-06-27T08:13:20.471631+08:00 0 [ERROR] [MY-012646] [InnoDB] File ./bznaqgk/drp_synlog_detail.ibd: 'open' returned OS error 124. Cannot continue operation
2022-06-27T08:13:20.471644+08:00 0 [ERROR] [MY-012981] [InnoDB] Cannot continue operation.
2022-06-27T00:13:21.227135Z mysqld_safe Number of processes running now: 0
2022-06-27T00:13:21.233845Z mysqld_safe mysqld restarted

3.根本原因，在orch1和orch2上分别查看到有关文件数的限制分别是400和1024，那么在mysql8上直接修改这个参数是不是就行了呢

mysql> show variables like '%open_file%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| innodb_open_files | 400   |
| open_files_limit  | 1024  |
+-------------------+-------+
2 rows in set (0.00 sec)

4.调整表打开数量，修改失败，只读参数，需要写进配置文件进行重启

mysql> set persist open_files_limit=60000;
ERROR 1238 (HY000): Variable 'open_files_limit' is a read only variable

mysql> set persist innodb_open_files=60000;
ERROR 1238 (HY000): Variable 'innodb_open_files' is a read only variable
mysql>

5.然后进行数据库重启，但是参数没有生效，这又是怎么回事呢。有意思的是之前这三台数据库的服务器就因为/etc/security/limits.conf 系统最大文件数不够导致连接失败，当时也并没有数据发生重启或者宕机，只是VIP连接不上，然后就进行了limits.conf的修改，此时的conf大小

这时的操作系统的limits.conf跟数据库的最大打开文件数有什么关系么？在配置文件中修改了参数，但是重启完并没有生效。图为数据库配置文件

查询资料，centos7使用了systemd管理进程，安装mysql时mysqld_safe不再被安装。若要配置open_file_limit可通过systemctl edit mysqld,编辑service模块。只需要使用root用户启动mysqld_safe进程可使参数生效，mysql在启动时，这个参数参考的是操作系统的limits.conf的配置