Mysql连接数和状态

连接数查看

show processlist: 列出当前用户的前100条连接信息；
show full processlist: 列出当前用户的所有连接信息；
当root用户登陆后，能够查看所有用户的连接数；

mysql> show processlist;
+----+------+---------------------+------+---------+-------+----------+------------------+
| Id | User | Host                | db   | Command | Time  | State    | Info             |
+----+------+---------------------+------+---------+-------+----------+------------------+
|  4 | root | 192.168.8.133:57398 | sys  | Sleep   |  7973 |          | NULL             |
|  5 | root | 192.168.8.133:57400 | NULL | Sleep   | 23704 |          | NULL             |
| 17 | root | localhost           | NULL | Query   |     0 | starting | show processlist |
+----+------+---------------------+------+---------+-------+----------+------------------+
3 rows in set (0.00 sec)

mysql> show full processlist;
+----+------+---------------------+------+---------+-------+----------+-----------------------+
| Id | User | Host                | db   | Command | Time  | State    | Info                  |
+----+------+---------------------+------+---------+-------+----------+-----------------------+
|  4 | root | 192.168.8.133:57398 | sys  | Sleep   |  7982 |          | NULL                  |
|  5 | root | 192.168.8.133:57400 | NULL | Sleep   | 23713 |          | NULL                  |
| 17 | root | localhost           | NULL | Query   |     0 | starting | show full processlist |
+----+------+---------------------+------+---------+-------+----------+-----------------------+
3 rows in set (0.00 sec)

最大连接数查看

mysql> show variables like '%max_connections%';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 151   |
+-----------------+-------+
1 row in set (0.01 sec)

mysql> show status like '%Threads%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| Delayed_insert_threads | 0     |
| Slow_launch_threads    | 0     |
| Threads_cached         | 1     |
| Threads_connected      | 3     |     // 连接数
| Threads_created        | 4     |     //为处理连接而创建的线程数
| Threads_running        | 1     |     // 并发数
+------------------------+-------+
6 rows in set (0.01 sec)

max_connections，允许同时连接DB的客户端的最大线程数。如果客户端的连接数超过了max_connections,应用就会收到“too many connections”的错误。

修改最大连接数

打开my.ini，修改max_connections=100(默认为100)。在linux中为/etc/my.cnf文件