问题描述：把max_connections和max_user_connections参数进行分析测试，顾名思义，max_connections就是负责数据库全局的连接数，max_user_connections可以限制单个用户的最大连接数。
1.临时修改max_user_connections全局参数，当max_user_connections为0时，对数据库下所有参数没有做限制，但不能说明数据库下的用户并没有开启这个参数
mysql> set global max_user_connections=1; Query OK, 0 rows affected (0.00 sec)
新开多个窗口测试连接，连接失败
[root@rhel7 ~]# /usr/local/mysql8/bin/mysql -utest01 -p123 -h192.168.163.21 -P33306 mysql: [Warning] Using a password on the command line interface can be insecure. ERROR 1203 (42000): User test01 already has more than 'max_user_connections' active connections
2.对单个用户进行连接限制
重启释放刚才设置临时设置的参数
mysql> restart -> ; Query OK, 0 rows affected (0.01 sec)
mysql> show variables like '%max_user_conn%';
+----------------------+-------+
| Variable_name | Value |
+----------------------+-------+
| max_user_connections | 0 |
+----------------------+-------+
1 row in set (0.01 sec)
设置单个用户的连接限制，官方文档给的语句，
mysql> CREATE USER 'francis'@'localhost' IDENTIFIED BY 'frank'
-> WITH MAX_QUERIES_PER_HOUR 20
-> MAX_UPDATES_PER_HOUR 10
-> MAX_CONNECTIONS_PER_HOUR 5
-> MAX_USER_CONNECTIONS 2;
进行用户连接测试，会看到提示最大连接数为3时，连接失败
[root@rhel7 ~]# /usr/local/mysql8/bin/mysql -ufrancis -pfrank -S /data/mysql8/db_dxpt08/mysql.sock mysql: [Warning] Using a password on the command line interface can be insecure. ERROR 1226 (42000): User 'francis' has exceeded the 'max_user_connections' resource (current value: 3)
测试成功的用户查看当前连接信息，这里连接用户可以看到最大用户连接数为2，但是root观察的全局max_user_connections还是0
mysql> show variables like '%max_user_connections%'; +----------------------+-------+ | Variable_name | Value | +----------------------+-------+ | max_user_connections | 2 | +----------------------+-------+ 1 row in set (0.00 sec)
3.如果想要查看修改参数在哪里设置，就需要user表中查看这些
mysql> select * from user where user='francis'\G *************************** 1. row *************************** Host: localhost User: francis Select_priv: N Insert_priv: N Update_priv: N Delete_priv: N Create_priv: N Drop_priv: N Reload_priv: N Shutdown_priv: N Process_priv: N File_priv: N Grant_priv: N References_priv: N Index_priv: N Alter_priv: N Show_db_priv: N Super_priv: N Create_tmp_table_priv: N Lock_tables_priv: N Execute_priv: N Repl_slave_priv: N Repl_client_priv: N Create_view_priv: N Show_view_priv: N Create_routine_priv: N Alter_routine_priv: N Create_user_priv: N Event_priv: N Trigger_priv: N Create_tablespace_priv: N ssl_type: ssl_cipher: 0x x509_issuer: 0x x509_subject: 0x max_questions: 20 max_updates: 10 max_connections: 5 max_user_connections: 2 plugin: mysql_native_password authentication_string: *63DAA25989C7E01EB96570FA4DBE154711BEB361 password_expired: N password_last_changed: 2022-06-13 10:46:16 password_lifetime: NULL account_locked: N Create_role_priv: N Drop_role_priv: N Password_reuse_history: NULL Password_reuse_time: NULL Password_require_current: NULL User_attributes: NULL 1 row in set (0.01 sec)
4.附上官方给的两种修改max_user_connections方式
mysql> CREATE USER 'francis'@'localhost' IDENTIFIED BY 'frank' -> WITH MAX_QUERIES_PER_HOUR 20 -> MAX_UPDATES_PER_HOUR 10 -> MAX_CONNECTIONS_PER_HOUR 5 -> MAX_USER_CONNECTIONS 2; mysql> ALTER USER 'francis'@'localhost' WITH MAX_QUERIES_PER_HOUR 100; mysql> ALTER USER 'francis'@'localhost' WITH MAX_QUERIES_PER_HOUR 100;
5.结论
1.max_connections可以负责全局最大连接数管理。
2.max_user_connections负责限制每个用户的最大连接数，设置数量不能超过max_connections。max_user_connections开启全局变量的时候，会影响所有用户，除外单独设置了max_user_connections参数的所有用户。