通过my.cnf修改最大连接数max_connections没有生效

【问题描述】：

mysql 通过my.cnf修改最大连接数没有生效。

【配置文件】：

#BEGIN CONFIG INFO
#DESCR: 4GB RAM, InnoDB only, ACID, few connections, heavy queries
#TYPE: SYSTEM
#END CONFIG INFO

[mysqld]
bind-address    = 0.0.0.0
user            = dr
datadir         = /home/dr/server/mysql/3306/data
basedir     = /usr
port            = 3306
socket          = /home/dr/server/mysql/3306/mysql.sock
pid-file        = /home/dr/server/mysql/3306/mysql.pid
back_log        = 600
max_connections = 1000
max_connect_errors = 10
max_allowed_packet = 16M
max_heap_table_size = 64M
read_buffer_size = 2M
read_rnd_buffer_size = 16M
sort_buffer_size = 8M
join_buffer_size = 8M
thread_cache_size = 8
query_cache_size = 64M
query_cache_limit = 2M
ft_min_word_len = 4
default-storage-engine = MYISAM
thread_stack = 240K
transaction_isolation = REPEATABLE-READ
tmp_table_size = 64M
server-id = 1
key_buffer_size = 32M
performance_schema_max_table_instances = 2048
table_definition_cache = 2048
table_open_cache = 2048
bulk_insert_buffer_size = 64M
myisam_sort_buffer_size = 128M
myisam_max_sort_file_size = 10G
myisam_repair_threads = 1
innodb_buffer_pool_size = 2G
innodb_data_file_path = ibdata1:10M:autoextend
innodb_data_home_dir = /home/dr/server/mysql/3306/data
innodb_write_io_threads = 8
innodb_read_io_threads = 8
innodb_thread_concurrency = 16
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size = 8M
innodb_log_file_size = 256M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 120
explicit_defaults_for_timestamp=true
skip-name-resolve

#log = /home/dr/server/mysql/3306/log/mysql.log

log-bin =/home/dr/server/mysql/3306/binlog/mysql-bin.log 
binlog_format=mixed
expire_logs_days = 7

slow_query_log
long_query_time = 2
binlog_cache_size = 10M
log_slave_updates=1

log-error=/home/dr/server/mysql/3306/log/err.log
long_query_time=2

slow_query_log = 1
long_query_time = 1
slow_query_log_file = /home/dr/server/mysql/3306/slowlog/slow.log

general_log = 0
general_log_file = /home/dr/server/mysql/3306/general_log/general.log

#skip-networking
#external-locking
#memlock
#innodb_flush_method=O_DSYNC
#innodb_log_group_home_dir
#innodb_fast_shutdown
#innodb_force_recovery=1
#skip-innodb
#tmpdir = /tmp
#log_slave_updates
#log
#log_warnings

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash

[myisamchk]
key_buffer_size = 512M
sort_buffer_size = 512M
read_buffer = 8M
write_buffer = 8M

[mysqlhotcopy]
interactive-timeout

[mysqld_safe]
open-files-limit = 8192

[client]
#password       = [your_password]
port            = 3306
socket          = /home/dr/server/mysql/3306/mysql.sock

重启服务：

systemctl restart mysqld

日志警告：

180801 16:15:08 mysqld_safe mysqld from pid file /home/dr/server/mysql/3306/mysql.pid ended
180801 16:15:08 mysqld_safe Logging to '/home/dr/server/mysql/3306/log/err.log'.
180801 16:15:08 mysqld_safe Starting mysqld daemon with databases from /home/dr/server/mysql/3306/data
2018-08-01 16:15:08 0 [Note] /usr/sbin/mysqld (mysqld 5.6.36-log) starting as process 16407 ...
2018-08-01 16:15:08 16407 [Warning] Buffered warning: Could not increase number of max_open_files to more than 1024 (request: 8192)

2018-08-01 16:15:08 16407 [Warning] Buffered warning: Changed limits: max_connections: 214 (requested 1000)

2018-08-01 16:15:08 16407 [Warning] Buffered warning: Changed limits: table_open_cache: 400 (requested 2048)

2018-08-01 16:15:08 16407 [Note] Plugin 'FEDERATED' is disabled.
2018-08-01 16:15:08 16407 [Note] InnoDB: Using atomics to ref count buffer pool pages
2018-08-01 16:15:08 16407 [Note] InnoDB: The InnoDB memory heap is disabled
2018-08-01 16:15:08 16407 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtinsq

通过show variables like '%connect%'; 查看

mysql> show variables like '%connect%';
+-----------------------------------------------+-----------------+
| Variable_name                                 | Value           |
+-----------------------------------------------+-----------------+
| character_set_connection                      | utf8            |
| collation_connection                          | utf8_general_ci |
| connect_timeout                               | 10              |
| disconnect_on_expired_password                | ON              |
| init_connect                                  |                 |
| max_connect_errors                            | 10              |
| max_connections                               | 214             |
| max_user_connections                          | 0               |
| performance_schema_session_connect_attrs_size | 512             |
+-----------------------------------------------+-----------------+
9 rows in set (0.00 sec)

如果修改后生效，但被限制在214，无法再大
1、修改/etc/security/limits.conf，添加
root soft nofile 65535
root hard nofile 65535
* soft nofile 65535
* hard nofile 65535

2、修改/lib/systemd/system/mysql.service，添加
LimitNOFILE=65535
LimitNPROC=65535

保存后，执行以下命令
systemctl daemon-reload
systemctl restart mysql.service

再次查看

mysql> show variables like '%connect%';
+-----------------------------------------------+-----------------+
| Variable_name                                 | Value           |
+-----------------------------------------------+-----------------+
| character_set_connection                      | utf8            |
| collation_connection                          | utf8_general_ci |
| connect_timeout                               | 10              |
| disconnect_on_expired_password                | ON              |
| init_connect                                  |                 |
| max_connect_errors                            | 10              |
| max_connections                               | 1000            |
| max_user_connections                          | 0               |
| performance_schema_session_connect_attrs_size | 512             |
+-----------------------------------------------+-----------------+
9 rows in set (0.00 sec)