mysql中max_connections与max_user_connections使用区别

问题描述：把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参数的所有用户。