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> exit Bye C:\Users\ArcerZhang>mysql -uroot -parcerzhang Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 Server version: 5.5.28 MySQL Community Server (GPL) Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input 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> use arcerzhangdb; Database changed 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 Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 4 Server version: 5.5.28 MySQL Community Server (GPL) Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> use arcerzhangdb; Database changed mysql> show tables; +------------------------+ | Tables_in_arcerzhangdb | +------------------------+ | inno_tab | | t | | t_myisam | | tablespace_test | | tt | | ttt | +------------------------+ 6 rows in set (0.00 sec) mysql> desc t; +-------+-------------+------+-----+---------+-------+ | 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 Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 5 Server version: 5.5.28 MySQL Community Server (GPL) Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> use arcerzhangdb; Database changed mysql> select * from tt; +------+------+ | id | msg | +------+------+ | 1 | a | | 2 | b | | 3 | c | | 4 | d | | 5 | e | | 1000 | f | +------+------+ 6 rows in set (0.06 sec) 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 Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 6 Server version: 5.5.28 MySQL Community Server (GPL) Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> use arcerzhangdb; Database changed mysql> show tables; +------------------------+ | Tables_in_arcerzhangdb | +------------------------+ | inno_tab | | t | | t_myisam | | tablespace_test | | tt | | ttt | +------------------------+ 6 rows in set (0.00 sec) mysql> select * from ttt; +----+------+ | id | msg | +----+------+ | 1 | a | +----+------+ 1 row in set (0.07 sec) 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) 2015 Microsoft Corporation。保留所有权利。 C:\Users\ArcerZhang>mysql -uroot -parcerzhang ERROR 1040 (HY000): Too many connections C:\Users\ArcerZhang>
四、结论
第一个连接窗口为初始窗口，或者说叫做监控窗口.
第二个连接窗口应该算是我们设置max_connections=3后第一个真正意义上的连接用户.
第三个连接窗口应该算是我们设置max_connections=3后第二个真正意义上的连接用户.
第四个连接窗口应该算是我们设置max_connections=3后第三个真正意义上的连接用户.
所以当打开第五个窗口的时候，会报如下错误:
C:\Users\ArcerZhang>mysql -uroot -parcerzhang ERROR 1040 (HY000): Too many connections
