MySQL global option vs user-directed options

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