快捷導(dǎo)航

MySQL中統(tǒng)計各個IP的連接數(shù)的方法總結(jié)

更新時間：2025年12月19日 08:39:57 作者：「光與松果」

本文介紹了多種在MySQL中統(tǒng)計各個IP連接數(shù)的方法,包括查詢INFORMATION_SCHEMA.PROCESSLIST、使用SHOW PROCESSLIST命令、實(shí)時監(jiān)控腳本、定期統(tǒng)計并記錄到表等,此外,還提供了一些實(shí)用技巧,需要的朋友可以參考下

MySQL中統(tǒng)計各個IP的連接數(shù)

在MySQL中統(tǒng)計各個IP的連接數(shù)，可以使用以下幾種方法：

方法1：查詢 INFORMATION_SCHEMA.PROCESSLIST

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;

方法2：使用 SHOW PROCESSLIST 命令

-- 先創(chuàng)建臨時表存儲結(jié)果
CREATE TEMPORARY TABLE temp_processlist AS
SHOW PROCESSLIST;

-- 然后查詢統(tǒng)計
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;

方法3：更詳細(xì)的連接信息統(tǒng)計

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;

方法4：實(shí)時監(jiān)控腳本（Shell）

#!/bin/bash
# 保存為 monitor_connections.sh

mysql -u root -p -e "
SELECT 
    SUBSTRING_INDEX(host, ':', 1) AS '客戶端IP',
    COUNT(*) AS '連接數(shù)',
    GROUP_CONCAT(DISTINCT USER) AS '用戶列表'
FROM information_schema.processlist 
WHERE host != ''
GROUP BY SUBSTRING_INDEX(host, ':', 1)
ORDER BY COUNT(*) DESC;"

方法5：定期統(tǒng)計并記錄到表

-- 創(chuàng)建統(tǒng)計記錄表
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
);

-- 插入統(tǒng)計數(shù)據(jù)的存儲過程
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 ;

-- 執(zhí)行統(tǒng)計
CALL log_connection_stats();

方法6：查看當(dāng)前連接詳情

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;

實(shí)用技巧

1. 查找連接數(shù)過多的IP

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  -- 設(shè)置閾值
ORDER BY connection_count DESC;

2. 殺死特定IP的所有連接

-- 先生成kill語句預(yù)覽
SELECT CONCAT('KILL ', ID, ';') AS kill_command
FROM information_schema.processlist 
WHERE SUBSTRING_INDEX(host, ':', 1) = '192.168.1.100';  -- 替換為目標(biāo)IP

-- 確認(rèn)無誤后執(zhí)行kill

到此這篇關(guān)于MySQL中統(tǒng)計各個IP的連接數(shù)的方法總結(jié)的文章就介紹到這了,更多相關(guān)MySQL統(tǒng)計IP連接數(shù)內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: