mysql 连接数测试_mysql最大连接数试验

一、试验基础环境配置

1、查看当前数据库的最大连接数

mysql> show global variables like '%connect%';+--------------------------+-----------------+

| Variable_name | Value |

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

| character_set_connection | utf8 |

| collation_connection | utf8_general_ci |

| connect_timeout | 10 |

| init_connect | |

| max_connect_errors | 10 |

| max_connections | 160 |

| max_user_connections | 0 |

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

7 rows in set (0.00 sec)

2、设置全局变量max_connections为3(基于试验目的，做测试)

mysql> set global max_connections=3;

Query OK,0 rows affected (0.01 sec)

3、查看设置是否生效.max_connections已经设置为3.

mysql> show global variables like '%connect%';+--------------------------+-----------------+

| Variable_name | Value |

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

| character_set_connection | utf8 |

| collation_connection | utf8_general_ci |

| connect_timeout | 10 |

| init_connect | |

| max_connect_errors | 10 |

| max_connections |3 |

| max_user_connections | 0 |

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

7 rows in set (0.00 sec)

4、退出当前登陆，再次登陆查看是否生效.

mysql> exitBye

C:\Users\ArcerZhang>mysql -uroot -parcerzhang

Welcometo the MySQL monitor. Commands end with ; or\g.

Your MySQL connection idis 3Server version:5.5.28MySQL Community Server (GPL)

Copyright (c)2000, 2012, Oracle and/or its affiliates. Allrights reserved.

Oracleis a registered trademark of Oracle Corporation and/orits

affiliates. Other names may be trademarksoftheir respective

owners.

Type'help;' or '\h' for help. Type '\c' to clear the currentinput statement.

mysql> show global variables like '%connect%';+--------------------------+-----------------+

| Variable_name | Value |

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

| character_set_connection | utf8 |

| collation_connection | utf8_general_ci |

| connect_timeout | 10 |

| init_connect | |

| max_connect_errors | 10 |

| max_connections | 3 |

| max_user_connections | 0 |

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

7 rows in set (0.00 sec)

二、查看当前数据库连接数

mysql> usearcerzhangdb;Databasechanged

mysql>show processlist;+----+------+-----------------+--------------+---------+------+-------+------------------+

| Id | User | Host | db | Command | Time | State | Info |

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

| 3 | root | localhost:53232 | arcerzhangdb | Query | 0 | NULL | show processlist |

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

1 row in set (0.00 sec)

三、分别打开第二个、第三个、第四个、第五个CMD窗口登录数据库

C:\Users\ArcerZhang>mysql -uroot -parcerzhang

Welcometo the MySQL monitor. Commands end with ; or\g.

Your MySQL connection idis 4Server version:5.5.28MySQL Community Server (GPL)

Copyright (c)2000, 2012, Oracle and/or its affiliates. Allrights reserved.

Oracleis a registered trademark of Oracle Corporation and/orits

affiliates. Other names may be trademarksoftheir respective

owners.

Type'help;' or '\h' for help. Type '\c' to clear the currentinput statement.

mysql> usearcerzhangdb;Databasechanged

mysql>show tables;+------------------------+

| Tables_in_arcerzhangdb |

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

| inno_tab |

| t |

| t_myisam |

| tablespace_test |

| tt |

| ttt |

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

6 rows in set (0.00sec)

mysql> desct;+-------+-------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

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

| id | int(11) | NO | PRI | 0 | |

| name | varchar(10) | YES | | NULL | |

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

2 rows in set (0.12 sec)

打开第二个窗口

mysql>show processlist;+----+------+-----------------+--------------+---------+------+-------+------------------+

| Id | User | Host | db | Command | Time | State | Info |

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

| 3 | root | localhost:53232 | arcerzhangdb | Query | 0 | NULL | show processlist |

| 4 | root | localhost:53251 | arcerzhangdb | Sleep | 6 | | NULL |

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

2 rows in set (0.00 sec)

查看数据库连接

C:\Users\ArcerZhang>mysql -uroot -parcerzhang

Welcometo the MySQL monitor. Commands end with ; or\g.

Your MySQL connection idis 5Server version:5.5.28MySQL Community Server (GPL)

Copyright (c)2000, 2012, Oracle and/or its affiliates. Allrights reserved.

Oracleis a registered trademark of Oracle Corporation and/orits

affiliates. Other names may be trademarksoftheir respective

owners.

Type'help;' or '\h' for help. Type '\c' to clear the currentinput statement.

mysql> usearcerzhangdb;Databasechanged

mysql> select * fromtt;+------+------+

| id | msg |

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

| 1 | a |

| 2 | b |

| 3 | c |

| 4 | d |

| 5 | e |

| 1000 | f |

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

6 rows in set (0.06sec)

mysql>

打开第三个窗口

mysql>show processlist;+----+------+-----------------+--------------+---------+------+-------+------------------+

| Id | User | Host | db | Command | Time | State | Info |

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

| 3 | root | localhost:53232 | arcerzhangdb | Query | 0 | NULL | show processlist |

| 4 | root | localhost:53251 | arcerzhangdb | Sleep | 43 | | NULL |

| 5 | root | localhost:53264 | arcerzhangdb | Sleep | 5 | | NULL |

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

3 rows in set (0.00 sec)

查看数据库连接

C:\Users\ArcerZhang>mysql -uroot -parcerzhang

Welcometo the MySQL monitor. Commands end with ; or\g.

Your MySQL connection idis 6Server version:5.5.28MySQL Community Server (GPL)

Copyright (c)2000, 2012, Oracle and/or its affiliates. Allrights reserved.

Oracleis a registered trademark of Oracle Corporation and/orits

affiliates. Other names may be trademarksoftheir respective

owners.

Type'help;' or '\h' for help. Type '\c' to clear the currentinput statement.

mysql> usearcerzhangdb;Databasechanged

mysql>show tables;+------------------------+

| Tables_in_arcerzhangdb |

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

| inno_tab |

| t |

| t_myisam |

| tablespace_test |

| tt |

| ttt |

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

6 rows in set (0.00sec)

mysql> select * fromttt;+----+------+

| id | msg |

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

| 1 | a |

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

1 row in set (0.07sec)

mysql>

打开第四个窗口

mysql>show processlist;+----+------+-----------------+--------------+---------+------+-------+------------------+

| Id | User | Host | db | Command | Time | State | Info |

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

| 3 | root | localhost:53232 | arcerzhangdb | Query | 0 | NULL | show processlist |

| 4 | root | localhost:53251 | arcerzhangdb | Sleep | 97 | | NULL |

| 5 | root | localhost:53264 | arcerzhangdb | Sleep | 59 | | NULL |

| 6 | root | localhost:53274 | arcerzhangdb | Sleep | 5 | | NULL |

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

4 rows in set (0.00 sec)

查看数据库连接

Microsoft Windows [版本 10.0.10586](c)2015Microsoft Corporation。保留所有权利。

C:\Users\ArcerZhang>mysql -uroot -parcerzhang

ERROR1040(HY000): Too many connections

C:\Users\ArcerZhang>

打开第五个窗口

四、结论

第一个连接窗口为初始窗口，或者说叫做监控窗口.

第二个连接窗口应该算是我们设置max_connections=3后第一个真正意义上的连接用户.

第三个连接窗口应该算是我们设置max_connections=3后第二个真正意义上的连接用户.

第四个连接窗口应该算是我们设置max_connections=3后第三个真正意义上的连接用户.

所以当打开第五个窗口的时候，会报如下错误:

C:\Users\ArcerZhang>mysql -uroot -parcerzhang

ERROR1040 (HY000): Too many connections