MySQL中统计各个IP的连接数的方法总结
<div id="navCategory"><h5 class="catalogue">目录</h5><ul class="first_class_ul"><li><a href="#_label0">MySQL中统计各个IP的连接数</a></li><ul class="second_class_ul"><li><a href="#_lab2_0_0">方法1：查询 INFORMATION_SCHEMA.PROCESSLIST</a></li><li><a href="#_lab2_0_1">方法2：使用 SHOW PROCESSLIST 命令</a></li><li><a href="#_lab2_0_2">方法3：更详细的连接信息统计</a></li><li><a href="#_lab2_0_3">方法4：实时监控脚本（Shell）</a></li><li><a href="#_lab2_0_4">方法5：定期统计并记录到表</a></li><li><a href="#_lab2_0_5">方法6：查看当前连接详情</a></li></ul><li><a href="#_label1">实用技巧</a></li><ul class="second_class_ul"><li><a href="#_lab2_1_6">1. 查找连接数过多的IP</a></li><li><a href="#_lab2_1_7">2. 杀死特定IP的所有连接</a></li></ul></ul></div><p class="maodian"><a name="_label0"></a></p><h2>MySQL中统计各个IP的连接数</h2><p>在MySQL中统计各个IP的连接数，可以使用以下几种方法：</p>
<p class="maodian"><a name="_lab2_0_0"></a></p><h3>方法1：查询 INFORMATION_SCHEMA.PROCESSLIST</h3>
<div class="jb51code"><pre class="brush:sql;">SELECT
SUBSTRING_INDEX(host, ':', 1) AS client_ip,
COUNT(*) AS connection_count
FROM information_schema.processlist
WHERE host != ''
GROUP BY client_ip
ORDER BY connection_count DESC;
</pre></div>
<p class="maodian"><a name="_lab2_0_1"></a></p><h3>方法2：使用 SHOW PROCESSLIST 命令</h3>
<div class="jb51code"><pre class="brush:sql;">-- 先创建临时表存储结果
CREATE TEMPORARY TABLE temp_processlist AS
SHOW PROCESSLIST;
-- 然后查询统计
SELECT
SUBSTRING_INDEX(host, ':', 1) AS client_ip,
COUNT(*) AS connection_count
FROM temp_processlist
WHERE host != ''
GROUP BY client_ip
ORDER BY connection_count DESC;
</pre></div>
<p class="maodian"><a name="_lab2_0_2"></a></p><h3>方法3：更详细的连接信息统计</h3>
<div class="jb51code"><pre class="brush:sql;">SELECT
SUBSTRING_INDEX(host, ':', 1) AS client_ip,
COUNT(*) AS total_connections,
SUM(CASE WHEN command = 'Sleep' THEN 1 ELSE 0 END) AS sleep_connections,
SUM(CASE WHEN command != 'Sleep' THEN 1 ELSE 0 END) AS active_connections,
GROUP_CONCAT(DISTINCT user) AS connected_users
FROM information_schema.processlist
WHERE host != ''
GROUP BY client_ip
ORDER BY total_connections DESC;
</pre></div>
<p class="maodian"><a name="_lab2_0_3"></a></p><h3>方法4：实时监控脚本（Shell）</h3>
<div class="jb51code"><pre class="brush:sql;">#!/bin/bash
# 保存为 monitor_connections.sh
mysql -u root -p -e "
SELECT
SUBSTRING_INDEX(host, ':', 1) AS '客户端IP',
COUNT(*) AS '连接数',
GROUP_CONCAT(DISTINCT USER) AS '用户列表'
FROM information_schema.processlist
WHERE host != ''
GROUP BY SUBSTRING_INDEX(host, ':', 1)
ORDER BY COUNT(*) DESC;"
</pre></div>
<p class="maodian"><a name="_lab2_0_4"></a></p><h3>方法5：定期统计并记录到表</h3>
<div class="jb51code"><pre class="brush:sql;">-- 创建统计记录表
CREATE TABLE IF NOT EXISTS connection_stats (
id INT AUTO_INCREMENT PRIMARY KEY,
stat_time DATETIME DEFAULT CURRENT_TIMESTAMP,
client_ip VARCHAR(45),
connection_count INT,
unique_users INT
);
-- 插入统计数据的存储过程
DELIMITER //
CREATE PROCEDURE log_connection_stats()
BEGIN
INSERT INTO connection_stats (client_ip, connection_count, unique_users)
SELECT
SUBSTRING_INDEX(host, ':', 1) AS client_ip,
COUNT(*) AS connection_count,
COUNT(DISTINCT user) AS unique_users
FROM information_schema.processlist
WHERE host != ''
GROUP BY SUBSTRING_INDEX(host, ':', 1);
END //
DELIMITER ;
-- 执行统计
CALL log_connection_stats();
</pre></div>
<p class="maodian"><a name="_lab2_0_5"></a></p><h3>方法6：查看当前连接详情</h3>
<div class="jb51code"><pre class="brush:sql;">SELECT
ID as connection_id,
USER as mysql_user,
SUBSTRING_INDEX(host, ':', 1) AS client_ip,
DB as database_name,
COMMAND as command_type,
TIME as time_seconds,
STATE as connection_state,
INFO as current_query
FROM information_schema.processlist
WHERE host != ''
ORDER BY client_ip, TIME DESC;
</pre></div>
<p class="maodian"><a name="_label1"></a></p><h2>实用技巧</h2>
<p class="maodian"><a name="_lab2_1_6"></a></p><h3>1. 查找连接数过多的IP</h3>
<div class="jb51code"><pre class="brush:sql;">SELECT
SUBSTRING_INDEX(host, ':', 1) AS client_ip,
COUNT(*) AS connection_count
FROM information_schema.processlist
WHERE host != ''
GROUP BY client_ip
HAVING connection_count > 10-- 设置阈值
ORDER BY connection_count DESC;
</pre></div>
<p class="maodian"><a name="_lab2_1_7"></a></p><h3>2. 杀死特定IP的所有连接</h3>
<div class="jb51code"><pre class="brush:sql;">-- 先生成kill语句预览
SELECT CONCAT('KILL ', ID, ';') AS kill_command
FROM information_schema.processlist
WHERE SUBSTRING_INDEX(host, ':', 1) = '192.168.1.100';-- 替换为目标IP
-- 确认无误后执行kill
</pre></div>
頁:
[1]