Symptoms
Newly Configured MySQL directives do not take effect when configured within /etc/my.cnf file.
The following output describes the globally defined value for connection limiting directives:
mysql> show variables like 'max%connection%'\G
*************************** 1. row ***************************
Variable_name: max_connections
Value: 151
*************************** 2. row ***************************
Variable_name: max_user_connections
Value: 0
2 rows in set (0.00 sec)
The following command shows the per-user defined connections directives:
mysql -e "select distinct user,max_user_connections from mysql.user where user='cptest'"
+----------+----------------------+
| user | max_user_connections |
+----------+----------------------+
| cptest | 0 |
Description
MySQL supports global variables/directives and user-defined variables/directives.
Global variables apply to the MySQL/MariaDB running instance at the start of the database server process for all clients.
Clients do not configure User-defined variables. These are directives that apply directly to MySQL users but are set by the database administrator.
In the example above the "cptest" user has max_user_connections set to 0, because it does not have a specific value configured.
Workaround
MySQL-user directives/variables can be set to control users. For example, the following command configures MySQL-user directive max_user_connections to 50:
mysql> show variables like 'max%connection%'\G
*************************** 1. row ***************************
Variable_name: max_connections
Value: 151
*************************** 2. row ***************************
Variable_name: max_user_connections
Value: 10
2 rows in set (0.00 sec)
The above was configured in the "/etc/my.cnf" configuration file under the [mysqld] section:
grep -B1 max_user_connection /etc/my.cnf
[mysqld]
max_user_connections=10
Detail information can be found in the MySQL official documentation:
https://dev.mysql.com/doc/refman/8.0/en/server-udfs.html