MySQL Global variables max_connections & max_user_connections Detailed

I've seen a lot of explanations for max_conections & max_user_conenctions variables on the internet before, but it's a little bit inaccurate, or it's very vague, so read the official MySQL documentation 6.3.4 Setting Account Resource Limits chapters and validates them with experiments.

The experimental steps are as follows:

1. Start MySQL

2. Users required to create a test environment

3. View and verify the meaning of the max_connections variable

4. View and verify the meaning of the max_user_connections variable

To start MySQL, the command is as follows:

Windows:c:\net start MySQL

Linux: #service mysqld start

Users required to create a test environment

[Windows | linux]: mysql-uroot-p

Enter password:******

mysql> create user [email protected] ' 127.0.0.1 ' identified by ' Keyman ';

mysql> create user [email protected] ' 127.0.0.1 ' identified by ' Harry ';

mysql> flush Privileges;

View and verify the meaning of the max_connections variable

1) Open 4 command windows [Window1 & Window2 & window3 & Window4]

2) Execute the following command on the Window1

[Windows | linux]: mysql-uroot-p

Enter password:******

Mysql> SELECT @ @max_connections;

+-------------------+

| max_connections |

+-------------------+

| 0 |

+-------------------+

You can see that the value at this time (the default) is 0, that is, no limit

Mysql> SET @ @global. max_connections=2;

Mysql> SELECT @ @max_connections;

+-------------------+

| max_connections |

+-------------------+

| 2 |

+-------------------+

Mysql> exit

[Windows | linux]: mysql-uroot-p

Enter password:******

3) Execute the following command on the Window2

[Windows | linux]: mysql-ukeyman-p

Enter password:******

Mysql>

4) Execute the following command on the WINDOW3

[Windows | linux]: mysql-uharry-p

Enter password:*****

ERROR 1040 (08004): Many connections

Because the value of Max_connections is set to 2, the connection cannot be made at this time, but it is still unclear

Why you cannot connect, continue testing to further verify why you cannot connect.

5) Execute the following command on the WINDOW3

Note: 4) steps cannot log on to MySQL, this step can be directly on the basis of 4 to execute the following command, you can also open a new command window.

[Windows | linux]: mysql-ukeyman-p

Enter password:******

Mysql>

The miracle happened, the Keyman user actually connected.

6) Execute the following command on the WINDOW4

[Windows | linux]: mysql-ukeyman-p

Enter password:******

Mysql>

Miracles happen again, and the third connection to the Keyman user is connected.

In 6 steps, the significance of the max_connections variable is obvious.

View and verify the meaning of the max_user_connections variable

1) Open three command windows [Window1 & Window2 & window3 & Window4]

2) Execute the following command on the Window1

[Windows | linux]: mysql-uroot-p

Enter password:******

Mysql> SELECT @ @max_user_connections;

+------------------------+

| max_user_connections |

+------------------------+

| 0 |

+------------------------+

You can see that the value at this time (the default) is 0, that is, no limit

Mysql> SET @ @global. max_user_connections=2;

Mysql> SELECT @ @max_user_connections;

+------------------------+

| max_user_connections |

+------------------------+

| 2 |

+------------------------+

Mysql> exit

[Windows | linux]: mysql-uroot-p

Enter password:******

3) Execute the following command on the Window2

[Windows | linux]: mysql-uroot-p

Enter password:******

Mysql>

4) Execute the following command on the WINDOW3

[Windows | linux]: mysql-uroot-p

Enter password:******

ERROR 1203 (42000): User Root already have more then ' max_user_connections ' active connections

Because the value of Max_connections is set to 2, the connection cannot be made at this time, but it is still unclear

Why you cannot connect, continue testing to further verify why you cannot connect.

5) Execute the following command on the WINDOW3

Note: 4) steps cannot log on to MySQL, this step can be directly on the basis of 4 to execute the following command, you can also open a new command window.

[Windows | linux]: mysql-ukeyman-p

Enter password:******

Mysql>

The miracle happened, the Keyman user actually connected to the

6) Execute the following command on the WINDOW4

[Windows | linux]: mysql-uharry-p

Enter password:******

Mysql>

The miracle happened again, and Harry's user connected to the

In 6 steps, the significance of the max_user_connections variable is obvious.

Associating the value of a max_user_connections variable with a user

1) Open three command windows [Window1 & Window2 & window3 & Window4]

2) Execute the following command on the Window1

[Windows | linux]: mysql-uroot-p

Enter password:******

Mysql> SELECT @ @max_user_connections;

+------------------------+

| max_user_connections |

+------------------------+

| 0 |

+------------------------+

Mysql> show grants; commands can also be viewed.

You can see that the value at this time (the default) is 0, that is, no limit

Mysql> Grant Usage on * * to [e-mail protected] ' 127.0.0.1 ' with max_user_connections 2;

Mysql> SELECT @ @max_user_connections;

+------------------------+

| max_user_connections |

+------------------------+

| 2 |

+------------------------+

Mysql> exit

[Windows | linux]: mysql-ukeyman-p

Enter password:******

3) Execute the following command on the Window2

[Windows | linux]: mysql-ukeyman-p

Enter password:******

Mysql>

4) Execute the following command on the WINDOW3

[Windows | linux]: mysql-ukeyman-p

Enter password:******

ERROR 1203 (42000): User Keyman already have more then ' max_user_connections ' active connections

Because the value of Max_connections is set to 2, the connection cannot be made at this time, but it is still unclear

Why you cannot connect, continue testing to further verify why you cannot connect.

5) Execute the following command on the WINDOW3

Note: 4) steps cannot log on to MySQL, this step can be directly on the basis of 4 to execute the following command, you can also open a new command window.

[Windows | linux]: mysql-uroot-p

Enter password:******

Mysql> SELECT @ @max_user_connections;

+------------------------+

| max_user_connections |

+------------------------+

| 0 |

+------------------------+

Verified to log in to the root account at the same time in three windows without any problems.

If you insist on reading this article, then it is an honor to share the final conclusion with you. Time To witness miracles:

Conclusion the meaning of the 1:max_connections variable is to restrict the different uses of the current MySQL server to allow simultaneous connections

Number of users, and does not limit multiple connections to the same user

Conclusion the meaning of the 2:max_user_connections variable is to restrict the phase that the current MySQL server allows simultaneous connection

Number of connections to the user, no limit to the number of different users connected

Conclusion 3: The setting of the max_connections variable is due to the full local variables, so they are all valid for all users

Conclusion 4: The setting of max_user_connections is divided into the following two kinds of cases;

1. Valid for all users

Mysql> SET @ @global. max_user_connections=2;

2. Effective for individual users

Mysql> Grant Usage on * * to [e-mail protected] with max_user_connections 2;

The settings at this time will be valid only for Keyman users.

This article is from the "Keyman" blog, make sure to keep this source http://keyman.blog.51cto.com/9807984/1652220

MySQL Global variables max_connections & max_user_connections Detailed