今天下午开会,公司同事提到如何获取MySQL并发用户连接数目的问题。
MySQL并发连接数,简单说来就是客户端与服务器3306端口(本地除外)之间的连接数目总和。MySQL的连接数至少可以从这么两个方面获取:
- Netstat 调用
netstat -an | grep "tcp 0 0 192.168.0.250:3306" | wc -l
这里面的IP 192.168.0.250:3306是我的服务器上MySQL监听的IP和端口。
netstat的显示日志如下:
tcp 0 0 192.168.0.250:3306 192.168.0.250:35868 ESTABLISHED
tcp 0 0 192.168.0.250:3306 192.168.0.250:35869 ESTABLISHED
tcp 0 0 192.168.0.250:3306 192.168.0.250:35870 ESTABLISHED
tcp 0 0 192.168.0.250:3306 192.168.0.250:35871 ESTABLISHED
tcp 0 0 192.168.0.250:3306 192.168.0.250:35864 ESTABLISHED
tcp 0 0 192.168.0.250:3306 192.168.0.250:35865 ESTABLISHED
tcp 0 0 192.168.0.250:3306 192.168.0.250:35866 ESTABLISHED
tcp 0 0 192.168.0.250:3306 192.168.0.250:35867 ESTABLISHED
tcp 0 0 192.168.0.250:3306 192.168.0.250:35860 ESTABLISHED
tcp 0 0 192.168.0.250:3306 192.168.0.250:35861 ESTABLISHED
tcp 0 0 192.168.0.250:3306 192.168.0.250:35862 ESTABLISHED
tcp 0 0 192.168.0.250:3306 192.168.0.250:35863 ESTABLISHED
tcp 0 0 192.168.0.250:3306 192.168.0.250:35884 ESTABLISHED
tcp 0 0 192.168.0.250:3306 192.168.0.250:35885 ESTABLISHED
tcp 0 0 192.168.0.250:3306 192.168.0.250:35886 ESTABLISHED
tcp 0 0 192.168.0.250:3306 192.168.0.250:35887 ESTABLISHED
tcp 0 0 192.168.0.250:3306 192.168.0.250:35881 ESTABLISHED
tcp 0 0 192.168.0.250:3306 192.168.0.250:35882 ESTABLISHED
tcp 0 0 192.168.0.250:3306 192.168.0.250:35883 ESTABLISHED
tcp 0 0 192.168.0.250:3306 192.168.0.250:35876 ESTABLISHED
tcp 0 0 192.168.0.250:3306 192.168.0.250:35877 ESTABLISHED
tcp 0 0 192.168.0.250:3306 192.168.0.250:35878 ESTABLISHED
tcp 0 0 192.168.0.250:3306 192.168.0.250:35879 ESTABLISHED
tcp 0 0 192.168.0.250:3306 192.168.0.250:35872 ESTABLISHED
tcp 0 0 192.168.0.250:3306 192.168.0.250:35873 ESTABLISHED
tcp 0 0 192.168.0.250:3306 192.168.0.250:35874 ESTABLISHED
tcp 0 0 192.168.0.250:3306 192.168.0.250:35875 ESTABLISHED
tcp 0 0 192.168.0.250:3306 192.168.0.250:35900 ESTABLISHED
tcp 0 0 192.168.0.250:3306 192.168.0.250:35901 ESTABLISHED
tcp 0 0 192.168.0.250:3306 192.168.0.250:35902 ESTABLISHED
tcp 0 0 192.168.0.250:3306 192.168.0.250:35903 ESTABLISHED
tcp 0 0 192.168.0.250:3306 192.168.0.250:35896 ESTABLISHED
tcp 0 0 192.168.0.250:3306 192.168.0.250:35897 ESTABLISHED
tcp 0 0 192.168.0.250:3306 192.168.0.250:35898 ESTABLISHED
tcp 0 0 192.168.0.250:3306 192.168.0.250:35899 ESTABLISHED
tcp 0 0 192.168.0.250:3306 192.168.0.250:35892 ESTABLISHED
tcp 0 0 192.168.0.250:3306 192.168.0.250:35893 ESTABLISHED
tcp 0 0 192.168.0.250:3306 192.168.0.250:35894 ESTABLISHED
tcp 0 0 192.168.0.250:3306 192.168.0.250:35895 ESTABLISHED
tcp 0 0 192.168.0.250:3306 192.168.0.250:35888 ESTABLISHED
tcp 0 0 192.168.0.250:3306 192.168.0.250:35889 ESTABLISHED
tcp 0 0 192.168.0.250:3306 192.168.0.250:35890 ESTABLISHED
tcp 0 0 192.168.0.250:3306 192.168.0.250:35891 ESTABLISHED
tcp 0 0 192.168.0.250:3306 192.168.0.250:35908 ESTABLISHED
tcp 0 0 192.168.0.250:3306 192.168.0.250:35909 ESTABLISHED
tcp 0 0 192.168.0.250:3306 192.168.0.250:35904 ESTABLISHED
tcp 0 0 192.168.0.250:3306 192.168.0.250:35905 ESTABLISHED
tcp 0 0 192.168.0.250:3306 192.168.0.250:35906 ESTABLISHED
tcp 0 0 192.168.0.250:3306 192.168.0.250:35907 ESTABLISHED
tcp 0 0 192.168.0.250:3306 192.168.0.250:35924 ESTABLISHED
tcp 0 0 192.168.0.250:3306 192.168.0.110:1813 ESTABLISHED
tcp 0 0 192.168.0.250:3306 192.168.0.254:2685 ESTABLISHED
tcp 0 0 192.168.0.250:3306 192.168.0.110:1729 ESTABLISHED
tcp 0 0 192.168.0.250:3306 192.168.0.254:3002 ESTABLISHED
tcp 0 0 192.168.0.250:3306 192.168.0.254:3016 ESTABLISHED
tcp 0 0 192.168.0.250:3306 192.168.0.254:3009 ESTABLISHED
tcp 0 0 192.168.0.250:3306 192.168.0.254:3010 ESTABLISHED
tcp 0 0 192.168.0.250:3306 192.168.0.254:3013 ESTABLISHED
tcp 0 0 192.168.0.250:3306 192.168.0.254:3014 ESTABLISHED
tcp 0 0 192.168.0.250:3306 192.168.0.254:3015 ESTABLISHED
tcp 0 0 192.168.0.250:3306 192.168.0.110:1156 ESTABLISHED
tcp 0 0 192.168.0.250:3306 192.168.0.110:1687 ESTABLISHED
tcp 0 0 192.168.0.250:3306 192.168.0.110:1692 ESTABLISHED
tcp 0 0 192.168.0.250:3306 192.168.0.110:1690 ESTABLISHED
tcp 0 0 192.168.0.250:3306 192.168.0.110:1689 ESTABLISHED
tcp 0 0 192.168.0.250:3306 192.168.0.111:2618 ESTABLISHED
tcp 0 0 192.168.0.250:3306 192.168.0.111:3095 ESTABLISHED
tcp 0 0 192.168.0.250:3306 192.168.0.111:3040 ESTABLISHED
tcp 0 0 192.168.0.250:3306 192.168.0.111:3039 ESTABLISHED
tcp 0 0 192.168.0.250:3306 192.168.0.111:3034 ESTABLISHED
tcp 0 0 192.168.0.250:3306 192.168.0.111:3035 ESTABLISHED
tcp 0 0 192.168.0.250:3306 192.168.0.111:3032 ESTABLISHED
tcp 0 0 192.168.0.250:3306 192.168.0.111:3033 ESTABLISHED
tcp 0 0 192.168.0.250:3306 192.168.0.167:2960 ESTABLISHED
tcp 0 0 192.168.0.250:3306 192.168.0.167:2962 ESTABLISHED
tcp 0 0 192.168.0.250:3306 192.168.0.167:2964 ESTABLISHED
tcp 0 0 192.168.0.250:3306 192.168.0.167:2968 ESTABLISHED
tcp 0 0 192.168.0.250:3306 192.168.0.167:2957 ESTABLISHED
tcp 0 0 192.168.0.250:3306 192.168.0.167:2956 ESTABLISHED
tcp 0 0 192.168.0.250:3306 192.168.0.63:2027 ESTABLISHED
tcp 0 0 192.168.0.250:3306 192.168.0.167:2959 ESTABLISHED
tcp 0 0 192.168.0.250:3306 192.168.0.128:3932 ESTABLISHED
- MySQLAdmin调用
mysqladmin processlist -u root 的结果如下:
+-------+-------------+--------------------+--------------+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-------+-------------+--------------------+--------------+---------+------+-------+------------------+
| 7795 | development | 192.168.0.110:1156 | mc_portal_db | Sleep | 4 | | |
| 20301 | development | 192.168.0.63:2027 | mc_portal_db | Sleep | 8970 | | |
| 21378 | root | 192.168.0.128:3932 | mc_portal_db | Sleep | 4501 | | |
| 29236 | development | 192.168.0.110:1687 | mc_portal_db | Sleep | 0 | | |
| 29264 | development | 192.168.0.110:1689 | mc_portal_db | Sleep | 0 | | |
| 29294 | development | 192.168.0.110:1690 | mc_portal_db | Sleep | 0 | | |
| 29323 | development | 192.168.0.110:1692 | mc_portal_db | Sleep | 0 | | |
| 30082 | development | 192.168.0.111:2618 | mc_portal_db | Sleep | 5884 | | |
| 30965 | development | 192.168.0.110:1729 | mc_portal_db | Sleep | 0 | | |
| 32641 | development | 192.168.0.254:2685 | mc_portal_db | Sleep | 4 | | |
| 35065 | development | 192.168.0.110:1813 | mc_portal_db | Sleep | 0 | | |
| 36336 | development | 192.168.0.167:2956 | mc_portal_db | Sleep | 8 | | |
| 36352 | development | 192.168.0.167:2957 | mc_portal_db | Sleep | 8 | | |
| 36367 | development | 192.168.0.167:2959 | mc_portal_db | Sleep | 8 | | |
| 36383 | development | 192.168.0.167:2960 | mc_portal_db | Sleep | 8 | | |
| 36399 | development | 192.168.0.167:2962 | mc_portal_db | Sleep | 8 | | |
| 36412 | development | 192.168.0.167:2964 | mc_portal_db | Sleep | 3 | | |
| 36421 | development | 192.168.0.167:2968 | mc_portal_db | Sleep | 8 | | |
| 36506 | development | 192.168.0.254:3002 | mc_portal_db | Sleep | 0 | | |
| 36522 | development | 192.168.0.254:3009 | mc_portal_db | Sleep | 0 | | |
| 36537 | development | 192.168.0.254:3010 | mc_portal_db | Sleep | 0 | | |
| 36552 | development | 192.168.0.254:3013 | mc_portal_db | Sleep | 0 | | |
| 36568 | development | 192.168.0.254:3014 | mc_portal_db | Sleep | 0 | | |
| 36583 | development | 192.168.0.254:3015 | mc_portal_db | Sleep | 0 | | |
| 36598 | development | 192.168.0.254:3016 | mc_portal_db | Sleep | 0 | | |
| 39625 | development | 192.168.0.111:3032 | mc_portal_db | Sleep | 8 | | |
| 39648 | development | 192.168.0.111:3033 | mc_portal_db | Sleep | 8 | | |
| 39671 | development | 192.168.0.111:3034 | mc_portal_db | Sleep | 8 | | |
| 39686 | development | 192.168.0.111:3035 | mc_portal_db | Sleep | 8 | | |
| 39700 | development | 192.168.0.111:3039 | mc_portal_db | Sleep | 8 | | |
| 39720 | development | 192.168.0.111:3040 | mc_portal_db | Sleep | 4 | | |
| 39822 | root | localhost | | Sleep | 1847 | | |
| 40187 | development | 192.168.0.111:3095 | mc_portal_db | Sleep | 8 | | |
| 41832 | development | 192.168.0.250:35860 | mc_portal_db | Sleep | 5 | | |
| 41833 | development | 192.168.0.250:35861 | mc_portal_db | Sleep | 5 | | |
| 41834 | development | 192.168.0.250:35862 | mc_portal_db | Sleep | 5 | | |
| 41835 | development | 192.168.0.250:35863 | mc_portal_db | Sleep | 5 | | |
| 41836 | development | 192.168.0.250:35864 | mc_portal_db | Sleep | 5 | | |
| 41837 | development | 192.168.0.250:35865 | mc_portal_db | Sleep | 5 | | |
| 41838 | development | 192.168.0.250:35866 | mc_portal_db | Sleep | 5 | | |
| 41839 | development | 192.168.0.250:35867 | mc_portal_db | Sleep | 5 | | |
| 41840 | development | 192.168.0.250:35868 | mc_portal_db | Sleep | 5 | | |
| 41841 | development | 192.168.0.250:35869 | mc_portal_db | Sleep | 5 | | |
| 41842 | development | 192.168.0.250:35870 | mc_portal_db | Sleep | 5 | | |
| 41843 | development | 192.168.0.250:35871 | mc_portal_db | Sleep | 5 | | |
| 41844 | development | 192.168.0.250:35872 | mc_portal_db | Sleep | 5 | | |
| 41845 | development | 192.168.0.250:35873 | mc_portal_db | Sleep | 5 | | |
| 41846 | development | 192.168.0.250:35874 | mc_portal_db | Sleep | 5 | | |
| 41847 | development | 192.168.0.250:35875 | mc_portal_db | Sleep | 5 | | |
| 41848 | development | 192.168.0.250:35876 | mc_portal_db | Sleep | 5 | | |
| 41849 | development | 192.168.0.250:35877 | mc_portal_db | Sleep | 2 | | |
| 41850 | development | 192.168.0.250:35878 | mc_portal_db | Sleep | 1 | | |
| 41851 | development | 192.168.0.250:35879 | mc_portal_db | Sleep | 1 | | |
| 41852 | development | 192.168.0.250:35880 | mc_portal_db | Sleep | 0 | | |
| 41853 | development | 192.168.0.250:35881 | mc_portal_db | Sleep | 0 | | |
| 41854 | development | 192.168.0.250:35882 | mc_portal_db | Sleep | 5 | | |
| 41855 | development | 192.168.0.250:35883 | mc_portal_db | Sleep | 5 | | |
| 41856 | development | 192.168.0.250:35884 | mc_portal_db | Sleep | 5 | | |
| 41857 | development | 192.168.0.250:35885 | mc_portal_db | Sleep | 5 | | |
| 41858 | development | 192.168.0.250:35886 | mc_portal_db | Sleep | 5 | | |
| 41859 | development | 192.168.0.250:35887 | mc_portal_db | Sleep | 5 | | |
| 41860 | development | 192.168.0.250:35888 | mc_portal_db | Sleep | 5 | | |
| 41861 | development | 192.168.0.250:35889 | mc_portal_db | Sleep | 5 | | |
| 41862 | development | 192.168.0.250:35890 | mc_portal_db | Sleep | 5 | | |
| 41863 | development | 192.168.0.250:35891 | mc_portal_db | Sleep | 5 | | |
| 41864 | development | 192.168.0.250:35892 | mc_portal_db | Sleep | 5 | | |
| 41865 | development | 192.168.0.250:35893 | mc_portal_db | Sleep | 5 | | |
| 41866 | development | 192.168.0.250:35894 | mc_portal_db | Sleep | 5 | | |
| 41867 | development | 192.168.0.250:35895 | mc_portal_db | Sleep | 5 | | |
| 41868 | development | 192.168.0.250:35896 | mc_portal_db | Sleep | 5 | | |
| 41869 | development | 192.168.0.250:35897 | mc_portal_db | Sleep | 5 | | |
| 41870 | development | 192.168.0.250:35898 | mc_portal_db | Sleep | 5 | | |
| 41871 | development | 192.168.0.250:35899 | mc_portal_db | Sleep | 5 | | |
| 41873 | development | 192.168.0.250:35900 | mc_portal_db | Sleep | 5 | | |
| 41874 | development | 192.168.0.250:35901 | mc_portal_db | Sleep | 5 | | |
| 41875 | development | 192.168.0.250:35902 | mc_portal_db | Sleep | 5 | | |
| 41876 | development | 192.168.0.250:35903 | mc_portal_db | Sleep | 5 | | |
| 41877 | development | 192.168.0.250:35904 | mc_portal_db | Sleep | 5 | | |
| 41878 | development | 192.168.0.250:35905 | mc_portal_db | Sleep | 5 | | |
| 41879 | development | 192.168.0.250:35906 | mc_portal_db | Sleep | 5 | | |
| 41880 | development | 192.168.0.250:35907 | mc_portal_db | Sleep | 5 | | |
| 41881 | development | 192.168.0.250:35908 | mc_portal_db | Sleep | 5 | | |
| 41882 | development | 192.168.0.250:35909 | mc_portal_db | Sleep | 5 | | |
| 41948 | development | 192.168.0.250:35924 | mc_portal_db | Sleep | 10 | | |
| 42397 | root | localhost | | Query | 0 | | show processlist |
+-------+-------------+--------------------+--------------+---------+------+-------+------------------+
由此可见, mysqladmin processlist -u root | wc -l 结果再减去5即可获取当前的数据库并发连接数目