mysql启动方式导致链接数max_connections查询的值不一致(Too many connections)

 

1.系统服务的方式启动
 

原来的启动配置文件

[root@localhost ~]# more /usr/lib/systemd/system/mysql.service
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target

[Install]
WantedBy=multi-user.target

[Service]
User=mysql
Group=mysql
ExecStart=/home/mysql57/bin/mysqld_safe --defaults-file=/home/mysql57/conf/my.cnf --user=mysql
##LimitNOFILE = 5000
#Restart=on-failure
#RestartPreventExitStatus=1
#PrivateTmp=false

启动发现参数值max_connections与配置文件中设置的不一样

systemctl stop mysql
systemctl start mysql
systemctl status mysql

 

mysql> select @@open_files_limit,@@table_open_cache,@@max_connections,@@table_definition_cache;
+--------------------+--------------------+-------------------+--------------------------+
| @@open_files_limit | @@table_open_cache | @@max_connections | @@table_definition_cache |
+--------------------+--------------------+-------------------+--------------------------+
|               1024 |                400 |               214 |                      600 |
+--------------------+--------------------+-------------------+--------------------------+
1 row in set (0.00 sec)

 

 

尝试使用手工方式启动
/home/mysql57/bin/mysqld_safe --defaults-file=/home/mysql57/conf/my.cnf --user=mysql &

 

mysql> select @@open_files_limit,@@table_open_cache,@@max_connections,@@table_definition_cache;
+--------------------+--------------------+-------------------+--------------------------+
| @@open_files_limit | @@table_open_cache | @@max_connections | @@table_definition_cache |
+--------------------+--------------------+-------------------+--------------------------+
|              65536 |               2000 |             10000 |                     1400 |
+--------------------+--------------------+-------------------+--------------------------+
1 row in set (0.00 sec)

 

手工启动的到的参数与配置文件中设置的一致

 

正确的系统自启动文件配置如下

/usr/lib/systemd/system/mysql.service

[Unit]
Description=mysql
[Service]
User=mysql
LimitNOFILE=100000
LimitNPROC=100000
ExecStart=/home/mysql57/bin/mysqld_safe --defaults-file=/home/mysql57/conf/my.cnf
[Install]
WantedBy=multi-user.target

 

若不设置LimitNOFILE和LimitNPROC的话，如下参数的默认值(my.cnf设置了也不管用)如下

mysql> select @@open_files_limit,@@table_open_cache,@@max_connections,@@table_definition_cache;
+--------------------+--------------------+-------------------+--------------------------+
| @@open_files_limit | @@table_open_cache | @@max_connections | @@table_definition_cache |
+--------------------+--------------------+-------------------+--------------------------+
|               1024 |                400 |               214 |                      600 |
+--------------------+--------------------+-------------------+--------------------------+
1 row in set (0.01 sec)