ERROR 1040 (08004): Too many connections
ERROR 1040 (08004): Too many connections
长期一来，mysql低版本中，当面对连接数陡增而出现too many connections时，往往比较棘手。今天特意在mysql社版本的8.0.17中，操作试验并记录下了。以前percona版本中就有该功能了。以后再也不用重启服务了。
mysql> show variables like '%max_conn%'; +------------------------+---------+ | Variable_name | Value | +------------------------+---------+ | max_connect_errors | 1000000 | | max_connections | 1024 | | mysqlx_max_connections | 100 | +------------------------+---------+ 3 rows in set (0.00 sec) mysql> set global max_connections=2; Query OK, 0 rows affected (0.00 sec)
# 管理端口
mysql> show variables like '%port%'; +--------------------------+-------+ | Variable_name | Value | +--------------------------+-------+ | admin_port | 33062 | | large_files_support | ON | | mysqlx_port | 33060 | | mysqlx_port_open_timeout | 0 | | port | 3306 | | report_host | | | report_password | | | report_port | 3306 | | report_user | | | require_secure_transport | OFF | +--------------------------+-------+ 10 rows in set (0.02 sec)
注意：admin_port该参数不需要明确指定，你的mysql端口是3306的话，这里就显示33062.
# 打开第一个连接
[root@fudao_db_cluster_001 ~]# mysql -utest_r -h 10.192.30.53 -p'f9p%VSfXxcY3kHLJmKIi' mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 26 Server version: 8.0.17 MySQL Community Server - GPL Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql>
# 打开第二个连接
[root@fudao_db_cluster_001 local]# mysql -utest_r -h 10.192.30.53 -p'f9p%VSfXxcY3kHLJmKIi' mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 27 Server version: 8.0.17 MySQL Community Server - GPL Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql>
# 打开第三个连接
[root@fudao_db_cluster_001 local]# mysql -utest_r -h 10.192.30.53 -p'f9p%VSfXxcY3kHLJmKIi' mysql: [Warning] Using a password on the command line interface can be insecure. ERROR 1040 (08004): Too many connections [root@fudao_db_cluster_001 local]#
# 用管理端口本地登录
[root@fudao_db_cluster_001 local]# mysql -h 127.0.0.1 -uadmin_m -p'rA75MQy*R*y@KO4z%LZe' -P 33062 mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 29 Server version: 8.0.17 MySQL Community Server - GPL Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql>
从上面可以看出，当普通用户连接数满后，只能用超级用户本地127.0.0.1或者localhost登录。
[root@fudao_db_cluster_001 local]# mysql -h 10.192.30.53 -uadmin_m -p'rA75MQy*R*y@KO4z%LZe' -P 33062 mysql: [Warning] Using a password on the command line interface can be insecure. ERROR 2003 (HY000): Can't connect to MySQL server on '10.192.30.53' (111) [root@fudao_db_cluster_001 local]#
