ulimits不生效导致数据库启动失败和相关设置说明

-- 正常启动中
2022-07-16T09:30:27.428609+08:00 0 [Note] [MY-010252] [Server] Server hostname (bind-address): '127.0.0.1'; port: 33062
2022-07-16T09:30:27.429134+08:00 0 [Note] [MY-010264] [Server]   - '127.0.0.1' resolves to '127.0.0.1';
2022-07-16T09:30:27.429792+08:00 0 [Note] [MY-010251] [Server] Server socket created on IP: '127.0.0.1'.
2022-07-16T09:30:27.430296+08:00 0 [Note] [MY-010252] [Server] Server hostname (bind-address): '*'; port: 3306
2022-07-16T09:30:27.430816+08:00 0 [Note] [MY-010254] [Server] IPv6 is not available.
2022-07-16T09:30:27.431308+08:00 0 [Note] [MY-010264] [Server]   - '0.0.0.0' resolves to '0.0.0.0';
2022-07-16T09:30:27.431991+08:00 0 [ERROR] [MY-010250] [Server] Failed to create a socket for IPv4 '0.0.0.0': errno: 24.
2022-07-16T09:30:27.432466+08:00 0 [ERROR] [MY-010255] [Server] Can't create IP socket: Too many open files
-- 报错Can't create IP socket: Too many open files
2022-07-16T09:30:27.433711+08:00 0 [ERROR] [MY-010119] [Server] Aborting
2022-07-16T09:30:27.435690+08:00 0 [Note] [MY-012330] [InnoDB] FTS optimize thread exiting.
2022-07-16T09:30:28.164281+08:00 0 [Note] [MY-010120] [Server] Binlog end
2022-07-16T09:30:28.165714+08:00 0 [Note] [MY-000000] [Server] Plugin GreatSQL reported: 'Gdb_job_thread stopped!'
2022-07-16T09:30:28.165960+08:00 0 [Note] [MY-000000] [Server] Plugin GreatSQL reported: 'Job manager local thread stopped!'
-- 接下来开始走shutdown流程
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.

[GreatSQL@GDB02-DB01 ~]$ ulimit -a
...
open files                      (-n) 1024
...
[GreatSQL@GDB02-DB01 ~]$
1.
2.
3.
4.
5.

[GreatSQL@GDB02-DB01 ~]$ tail -10 /etc/security/limits.conf 
#@faculty        soft    nproc           20
#@faculty        hard    nproc           50
#ftp             hard    nproc           0
#@student        -       maxlogins       4

* soft nofile 65535
* hard nofile 65535


# End of file
[GreatSQL@GDB02-DB01 ~]$
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.

-- 堡垒机直接登录GreatSQL用户，有open files未修改成功的提示信息
-bash: ulimit: open files: cannot modify limit: Operation not permitted
-- 此时的open files配置确实没生效
[GreatSQL@GDB02-DB01 ~]$ ulimit -a |grep 'open files'
open files                      (-n) 1024
-- su切换到root，root的open files是正常的
[GreatSQL@GDB02-DB01 ~]$ sudo su - root
Last login: Tue Jul 19 11:40:45 CST 2022 on pts/5
[root@GDB02-DB01 ~]# ulimit -a |grep 'open files'
open files                      (-n) 65535
-- su切换到GreatSQL，open files也是正常的
[root@GDB02-DB01 ~]# su - GreatSQL
Last login: Tue Jul 26 14:23:56 CST 2022 from XXXXXX on pts/8
[GreatSQL@GDB02-DB01 ~]$ ulimit -a |grep 'open files'
open files                      (-n) 65535
[GreatSQL@GDB02-DB01 ~]$ 
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.

[GreatSQL@GDB02-DB02 ~]$ ulimit -n 1026
-bash: ulimit: open files: cannot modify limit: Operation not permitted
[GreatSQL@GDB02-DB02 ~]$ ulimit -Hn
1024   -- 可以发现这里使用的硬件资源限制1024
1.
2.
3.
4.

Connecting to XXXXXX...
Connection established.
To escape to local shell, press 'Ctrl+Alt+]'.

Last login: Tue Jul 26 14:32:32 2022 from XXXXXX

Prepare to login to the target device, Please wait a second.

Last login: Tue Jul 26 14:31:21 2022 from XXXXXX
-bash: ulimit: open files: cannot modify limit: Operation not permitted
[GreatSQL@GDB02-DB01 ~]$ ulimit -a
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.

cat /etc/ssh/sshd_config
.......
# Set this to 'yes' to enable PAM authentication, account processing,
# and session processing. If this is enabled, PAM authentication will
# be allowed through the ChallengeResponseAuthentication and
# PasswordAuthentication.  Depending on your PAM configuration,
# PAM authentication via ChallengeResponseAuthentication may bypass
# the setting of "PermitRootLogin without-password".
# If you just want the PAM account and session checks to run without
# PAM authentication, then enable this but set PasswordAuthentication
# and ChallengeResponseAuthentication to 'no'.
# UsePAM no
......
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.

vi /etc/ssh/sshd_config
.......
# Set this to 'yes' to enable PAM authentication, account processing,
# and session processing. If this is enabled, PAM authentication will
# be allowed through the ChallengeResponseAuthentication and
# PasswordAuthentication.  Depending on your PAM configuration,
# PAM authentication via ChallengeResponseAuthentication may bypass
# the setting of "PermitRootLogin without-password".
# If you just want the PAM account and session checks to run without
# PAM authentication, then enable this but set PasswordAuthentication
# and ChallengeResponseAuthentication to 'no'.
UsePAM yes
......
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.

[root@GDB02-DB01 ~]# systemctl restart sshd
[root@GDB02-DB01 ~]# systemctl status sshd
● sshd.service - SYSV: OpenSSH server daemon
 Loaded: loaded (/etc/rc.d/init.d/sshd; bad; vendor preset: enabled)
 Active: active (running) since Tue 2022-07-26 10:28:30 CST; 2s ago
 Docs: man:systemd-sysv-generator(8)
 Process: 46808 ExecStop=/etc/rc.d/init.d/sshd stop (code=exited, status=0/SUCCESS)
 Process: 46815 ExecStart=/etc/rc.d/init.d/sshd start (code=exited, status=0/SUCCESS)
 Main PID: 46823 (sshd)
 Tasks: 14
 Memory: 85.8M
......
[root@GDB02-DB01 ~]# 
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.

Connection established.
To escape to local shell, press 'Ctrl+Alt+]'.

Last login: Tue Jul 26 10:28:11 2022 from XXXXXX

Prepare to login to the target device, Please wait a second.

Last login: Tue Jul 26 10:28:17 2022 from XXXXXX
[GreatSQL@GDB02-DB01 ~]$ ulimit -a
...
open files                      (-n) 65535
...
[GreatSQL@GDB02-DB01 ~]$
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.

* soft nofile 655350  #任何用户的每个进程可以打开的最大的文件描述符数量，默认1024，这里的数值会限制tcp连接
* hard nofile 655350
* soft nproc  655350  #任何用户可以打开的最大进程数
* hard nproc  650000

@student hard nofile 65535
@student soft nofile 4096
@student hard nproc 50  #学生组中的任何人不能拥有超过50个进程，并且会在拥有30个进程时发出警告
@student soft nproc 30
1.
2.
3.
4.
5.
6.
7.
8.
9.

$ ps h -Led -o user | sort | uniq -c | sort -n
      1 chrony
      1 dbus
      2 postfix
      7 polkitd
    129 root
   1326 GreatSQL
1.
2.
3.
4.
5.
6.
7.

-- 查看
$ cat /proc/sys/fs/file-max
6553600
-- 设置
$ vim /etc/sysctl.conf
fs.file-max = 6553600
1.
2.
3.
4.
5.
6.

-- 查看soft limit，ulimit -n默认查看的是soft limit
$ ulimit -n
65535
-- 查看hard limit
$ ulimit -Hn
65535

-- 临时设置
-- 通过ulimit -Sn设置最大打开文件描述符数的soft limit，注意soft limit必须小于hard limit
$ ulimit -Sn 65535
-- 同时设置soft limit和hard limit。对于非root用户只能设置比原来小的hard limit。
$ ulimit -n 65535

永久设置
#root权限下，在/etc/security/limits.conf中添加如下两行，表示所有用户最大打开文件描述符数的soft limit为102400，hard limit为104800。重启生效
* soft nofile 655350
* hard nofile 655350
复制
注意：设置nofile的hard limit还有一点要注意的就是hard limit不能大于/proc/sys/fs/nr_open，假如hard limit大于nr_open，注销后将无法正常登录。
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.

$ cat /proc/sys/fs/file-nr
5664        0        186405
其中第一个数表示当前系统已分配使用的打开文件描述符数，
第二个数为分配后已释放的（目前已不再使用），
第三个数等于file-max。
1.
2.
3.
4.
5.

使用ulimt -n命令进行测试，如果小于系统允许的最大值，设置成功；大于最大值，系统会报错提示。

$ ulimit -n 1100000
-bash: ulimit: open files: cannot modify limit: Operation not permitted
$ ulimit -n 1048576
$ ulimit -n 1048577
-bash: ulimit: open files: cannot modify limit: Operation not permitted
$ ulimit -n 1048575
$ ulimit -n 1048576
1.
2.
3.
4.
5.
6.
7.
8.
9.

ulimit -a    显示当前所有的资源限制
 ulimit -n    设置进程最大打开文件描述符数
 ulimit -H    设置硬件资源限制
 ulimit -S    设置软件资源限制述符数
 ulimit -u   用户最多可开启的程序数目
1.
2.
3.
4.
5.