ERROR 1040 (08004): Too many connections

ERROR 1040 (08004): Too many connections
In the long run, in the lower versions of mysql, too many connections can be tricky when the number of connections increases dramatically. Today, in mysql Community version 8.0.17, the operation is being tested and documented. This feature has been available in previous versions of Percona. You will never have to restart the service again.

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)

# Administration port

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)

Note: The admin_port parameter does not need to be explicitly specified; if your mysql port is 3306, 33062 is shown here.
# Open the first connection

[[email protected]_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> 

# Open the second connection

[[email protected]_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> 

Open the third connection

[[email protected]_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
[[email protected]_db_cluster_001 local]# 

Log in locally with the admin port

[[email protected]_db_cluster_001 local]# mysql -h 127.0.0.1 -uadmin_m -p'rA75MQy*R*[email protected]%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> 

As can be seen above, when the number of connections of ordinary users is full, the super user can only log in with local 127.0.0.1 or localhost.

[[email protected]_db_cluster_001 local]# mysql -h 10.192.30.53 -uadmin_m -p'rA75MQy*R*[email protected]%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)
[[email protected]_db_cluster_001 local]# 

 

Reproduced in: https://www.cnblogs.com/bjx2020/p/11339780.html

Read More: