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;

-- 先創(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;

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;

#!/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;"

-- 創(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();

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;

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;

-- 先生成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