MySQL 查看连接数及状态信息

一、问题描述今天运维同事找到我问，有两台数据库MYSQL的连接数超过监控的阈值（700），当然他们的监控（zabbix）也是刚刚搭建起来的。表示该值已经从很低的数值一点一点调上来了，但是仍然报警。于是就找到我，做为Oracle’s DBA的我，准备变为双料DBA，这正是学习的好时机。马上来学习一下，如何在MYSQL中查询连接数。二、实验1.show status 查看所有状态参数，其中Threads_connected 当前的连接数，Connections 试图连接到（不管是否成功）MYSQL服务器的连接总数， Max_used_connections 服务器启动后已经同时使用过的连接最大数量（并发）。mysql> show status like "%connect%";+----------------------+---------+| Variable_name | Value |+----------------------+---------+| Aborted_connects | 163 || Connections | 1116123 || Max_used_connections | 266 || Threads_connected | 208 |+----------------------+---------+4 rows in set （0.00 sec）mysql>2.show processlist 显示当前正在执行的mysql连接mysql> show processlist;+---------+----------+--------------------+-----------+---------+-------+-------+------------------+| Id | User | Host | db | Command | Time | State | Info |+---------+----------+--------------------+-----------+---------+-------+-------+------------------+| 1105357 | tjuser | 10.10.100.30:36210 | testdb | Sleep | 1377 | | NULL || 1112435 | tjuser | 10.10.100.30:54112 | testdb | Sleep | 1616 | | NULL ||…… 略| 1116128 | tjuser | 10.10.100.21:47484 | testdb | Sleep | 1 | | NULL || 1116129 | tjuser | 10.10.100.21:47485 | testdb | Sleep | 64 | | NULL |+---------+----------+--------------------+-----------+---------+-------+-------+------------------+207 rows in set （0.00 sec）3.mysqladmin -u -p -h status 显示当前mysql状态[root@db ~]# mysqladmin -uroot -p -hlocalhost statusEnter password:Uptime: 14604445 Threads: 208 Questions: 34034734 Slow queries: 179 Opens: 12553 Flush tables: 3 Open tables: 977 Queries per second avg: 2.330[root@db ~]# 4.mysqladmin -u -p -h extended-status 显示mysql的其他状态[root@db ~]# mysqladmin -uroot -p -hlocalhost extended-statusEnter password: +------------------------------------------+--------------+| Variable_name | Value |+------------------------------------------+--------------+| Aborted_clients | 53041 || Aborted_connects | 163 || Connections | 1116157 |…… 略| Threads_connected | 206 || Threads_created | 633 || Threads_running | 1 || Uptime | 14604661 || Uptime_since_flush_status | 14604661 |+------------------------------------------+--------------+[root@db ~]# 三、总结此次查询只需要掌握mysql中show status，show process list命令，以及命令mysqladmin。通过上述命令可以快速得到MYSQL数据库连接参数与状态值。查询到数据库设置connect数为1000，告诉同事后修改了阈值后正常。 It’s never too late to be what you might have been.本文永久更新链接地址