一、设置参数
mysql> set global max_connections=1;Query OK, 0 rows affected (0.00 sec)
二、测试登录情况
--在root用户已经登录的情况下，普通用户无法继续登录
root@sky-a@/root # /mysql/mysql-8.0.22/bin/mysql -usky --socket=/mydata/test/test.sock -psky
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1040 (08004): Too many connections
--root可以再登录一次
root@sky-a@/root # 3306
mysql> show processlist;
+----+-----------------+-----------+------+---------+------+------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-----------------+-----------+------+---------+------+------------------------+------------------+
| 5 | event_scheduler | localhost | NULL | Daemon | 331 | Waiting on empty queue | NULL |
| 12 | root | localhost | NULL | Sleep | 22 | | NULL |
| 15 | root | localhost | NULL | Query | 0 | init | show processlist |
+----+-----------------+-----------+------+---------+------+------------------------+------------------+
3 rows in set (0.00 sec)
--root再次登录报错
root@sky-a@/root # 3306
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1040 (HY000): Too many connections
结论就是超级用户可以额外登录一次，查看官网确认：
mysqld actually permits max_connections + 1 client connections. The extra connection is reserved for use by accounts that have the CONNECTION_ADMIN privilege (or the deprecated SUPER privilege)
三、其他方案
（1）使用gdb调整【生产尽量避免使用】
root@sky-a@/root # gdb -p 77472 -ex "set max_connections=5000" -batch--其中77472是指mysqld的进程id--查看确认已调整mysql> show variables like '%max_connections%';+------------------------+-------+| Variable_name | Value |+------------------------+-------+| max_connections | 5000 || mysqlx_max_connections | 100 |+------------------------+-------+2 rows in set (0.01 sec)
（2）使用admin_port连接
mysql> show variables like '%admin%';+---------------------------------+-----------------+| Variable_name | Value |+---------------------------------+-----------------+| admin_address | 127.0.0.1 || admin_port | 33062
--此时已经登录2个会话
mysql> show processlist;
+----+-----------------+-----------+------+---------+------+------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-----------------+-----------+------+---------+------+------------------------+------------------+
| 5 | event_scheduler | localhost | NULL | Daemon | 331 | Waiting on empty queue | NULL |
| 12 | root | localhost | NULL | Sleep | 22 | | NULL |
| 15 | root | localhost | NULL | Query | 0 | init | show processlist |
+----+-----------------+-----------+------+---------+------+------------------------+------------------+
3 rows in set (0.00 sec)
--root再登录会报错
root@sky-a@/root # 3306
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1040 (HY000): Too many connections
--用root@'%'登录admin_port/admin_address，未赋权
root@sky-a@/root # /mysql/mysql-8.0.22/bin/mysql -uroot -P33062 -h127.0.0.1 -proot1234
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1227 (42000): Access denied; you need (at least one of) the SERVICE_CONNECTION_ADMIN privilege(s) for this operation
--赋权和localhost一样的权限，可以登录
root@sky-a@/root # /mysql/mysql-8.0.22/bin/mysql -uroot -P33062 -h127.0.0.1 -proot1234
mysql> show processlist;
+----+-----------------+-----------------+------+---------+------+------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-----------------+-----------------+------+---------+------+------------------------+------------------+
| 5 | event_scheduler | localhost | NULL | Daemon | 560 | Waiting on empty queue | NULL |
| 12 | root | localhost | NULL | Sleep | 11 | | NULL |
| 15 | root | localhost | NULL | Sleep | 229 | | NULL |
| 17 | root | 127.0.0.1:53594 | NULL | Query | 0 | init | show processlist |
+----+-----------------+-----------------+------+---------+------+------------------------+------------------+
4 rows in set (0.00 sec)
即可通过具有SERVICE_CONNECTION_ADMIN权限的用户通过TCP/IP的方式连接至数据库。
PS：这两个参数都是静态参数，重启数据库才有效。
Setting admin_port has no effect if admin_address is not specified because in that case the server maintains no administrative network interface.
