Resolve the difference between max_connections and max_user_connections in mysql

  • 2020-05-24 06:22:12
  • OfStack

In the manual of mysql, there is already 1 point about max_user_connections. It is used to limit user resources. How to limit user resources? Here's a little test.
First look at the value of the global variable
mysql > select @@max_user_connections;
+------------------------+
| @@max_user_connections |
+------------------------+
| 0 |
+------------------------+
1 row in set (0.00 sec)
The default is 0
What does it mean to be zero? It does not limit user resources.
Here I change the value of 1, and in the query, note that this change is invalid after the server restart, want to save 1 straight in the options file!
mysql > set @@global.max_user_connections=1;
Query OK, 0 rows affected (0.00 sec)
mysql > select @@max_user_connections;
+------------------------+
| @@max_user_connections |
+------------------------+
| 1 |
+------------------------+
1 row in set (0.00 sec)
Now I'm going to re-open a connection
C:\Windows\System32 > mysql -uroot -pweb
ERROR 1203 (42000): User root already has more than 'max_user_connections' activ
e connections
This means that the user has reached the maximum number of connections, can no longer connect to own resources!
This parameter can only restrict the resource as a whole, and cannot restrict a certain user. If you need to restrict a certain user, you can see it in the permission table
max_questions: 0
max_updates: 0
max_connections: 0
max_user_connections: 0
These parameters can restrict a user.
max_connections means user limit on the entire server!

Related articles: