MySQL 連接 IP 統計與監控實戰：information_schema、show processlist、Shell 腳本三種方案對比

生產環境需監控每個客戶端 IP 的連接數、識別佔用過高的應用、配合限流熔斷。本文提供三種獲取 IP 連接統計的方案：SQL 查詢、命令行管道、監控腳本，並給出生產級告警閾值與自動化收集架構。

方案一：SQL 查詢 (推薦、最準確)

SELECT 
    SUBSTRING_INDEX(host, ':', 1) AS client_ip,
    COUNT(*) AS connection_count,
    GROUP_CONCAT(DISTINCT user) AS users,
    GROUP_CONCAT(DISTINCT db) AS databases,
    MAX(TIME) AS max_time_sec,
    SUM(TIME) AS total_time_sec
FROM information_schema.processlist
WHERE command != 'Sleep'  -- 可選：排除空閒連接
GROUP BY client_ip
ORDER BY connection_count DESC;

輸出示例

+-------------+-----------------+-------+------------+--------------+---------------+
| client_ip   | connection_count| users | databases  | max_time_sec | total_time_sec|
+-------------+-----------------+-------+------------+--------------+---------------+
| 10.0.1.50   | 45              | app1  | order_db   | 120          | 3400          |
| 10.0.2.100  | 30              | etl   | dw_db      | 3600         | 54000         |
| 192.168.1.20| 5               | admin | mysql      | 10           | 25            |
+-------------+-----------------+-------+------------+--------------+---------------+

方案二：命令行管道 (適合 Shell 整合)

# 方案 A: show processlist + awk
mysql -uroot -p -h127.0.0.1 -e "show processlist\G" | \
awk '/Host:/ {split($2,a,":"); print a[1]}' | sort | uniq -c | sort -nr

# 方案 B: 表格模式 + awk
mysql -uroot -p -h127.0.0.1 --skip-column-names -e "show processlist" | \
awk '{print $3}' | awk -F: '{print $1}' | sort | uniq -c | sort -nr

方案三：監控腳本 (Prometheus Exporter 模式)

#!/bin/bash
# mysql_connection_exporter.sh - 每分鐘由 Cron 執行，推送到 Pushgateway

MYSQL_HOST="127.0.0.1"
MYSQL_USER="monitor"
MYSQL_PASS="StrongMonitorPass"
PUSHGATEWAY="https://pushgateway:9091"
JOB="mysql_connections"
INSTANCE=$(hostname -f)

# 獲取統計
stats=$(mysql -h"$MYSQL_HOST" -u"$MYSQL_USER" -p"$MYSQL_PASS" -N -e "
SELECT CONCAT('mysql_connections_per_ip{ip=\"', SUBSTRING_INDEX(host, ':', 1), '\"} ', COUNT(*))
FROM information_schema.processlist
GROUP BY SUBSTRING_INDEX(host, ':', 1)
")

# 推送
echo "$stats" | curl --data-binary @- "$PUSHGATEWAY/metrics/job/$JOB/instance/$INSTANCE"

生產級告警規則

自動化收集架構

[MySQL] → [Node Exporter + 自定義腳本] → [Prometheus] → [Alertmanager] → [Webhook/Email/Slack]
                                    ↓
                              [Grafana Dashboard]
   - 實時連接數熱力圖 (IP × 時間)
   - 慢查詢關聯 IP 排行
   - 連接池洩漏趨勢分析

進階：識別具體應用進程

MySQL 5.7+ performance_schema.threads 關聯 processlist_info：

SELECT 
    t.processlist_id,
    t.processlist_user,
    t.processlist_host,
    t.processlist_db,
    t.processlist_command,
    t.processlist_time,
    t.processlist_state,
    LEFT(t.processlist_info, 100) AS sql_preview,
    p.thread_os_id  -- Linux 線程 ID，可關聯 pid/應用進程
FROM performance_schema.threads t
JOIN performance_schema.processlist p ON t.processlist_id = p.id
WHERE t.type = 'FOREGROUND';

避坑指南

坑 1：show processlist` 權限不足

需 PROCESS` 權限：`GRANT PROCESS ON *.* TO 'monitor'@'localhost';`

坑 2：`information_schema.processlist` 瞬時快照，高併發下有抖動

多次採樣取中位數，或用 `performance_schema` 更精確

坑 3：代理/連接池導致 IP 相同無法區分應用

應用端在連接字符串攜帶 `application_name` (MySQL 8.0+ `MYSQL_ATTR_INIT_COMMAND`)

坑 4：IPv6 地址格式 `::ffff:10.0.1.50` 解析異常

`SUBSTRING_INDEX(REPLACE(host, '::ffff:', ''), ':', 1)`