MySQL教程 / 第 260 节

第26章：MySQL监控体系

监控是保障MySQL稳定运行的第一道防线

26.1 监控概述

26.1.1 为什么需要监控？

问题场景：

❌ 数据库突然变慢，不知道原因
❌ 磁盘空间满了，业务中断
❌ 主从延迟严重，数据不一致
❌ 连接数耗尽，无法连接

监控的目标：

✅ 及时发现问题
✅ 快速定位问题
✅ 预防故障发生
✅ 优化性能

26.1.2 监控指标分类

系统层监控：

CPU使用率
内存使用率
磁盘I/O
网络流量

MySQL层监控：

QPS/TPS
连接数
慢查询
主从延迟
Buffer Pool命中率

业务层监控：

订单量
用户活跃度
响应时间

26.2 核心监控指标 ⭐⭐⭐⭐⭐

26.2.1 性能指标

1. QPS（每秒查询数）

-- 查看总查询数
SHOW GLOBAL STATUS LIKE 'Questions';

-- 查看运行时间
SHOW GLOBAL STATUS LIKE 'Uptime';

-- 计算QPS
QPS = Questions / Uptime

-- 实时监控QPS
mysqladmin -u root -p -i 1 status | grep Queries

2. TPS（每秒事务数）

-- 查看提交事务数
SHOW GLOBAL STATUS LIKE 'Com_commit';

-- 查看回滚事务数
SHOW GLOBAL STATUS LIKE 'Com_rollback';

-- 计算TPS
TPS = (Com_commit + Com_rollback) / Uptime

3. 响应时间

-- 查看慢查询
SHOW GLOBAL STATUS LIKE 'Slow_queries';

-- 查看平均查询时间（需要开启慢查询日志）
-- 使用pt-query-digest分析
pt-query-digest /var/lib/mysql/slow.log

26.2.2 连接指标

1. 当前连接数

-- 查看当前连接数
SHOW STATUS LIKE 'Threads_connected';

-- 查看正在运行的线程数
SHOW STATUS LIKE 'Threads_running';

-- 查看历史最大连接数
SHOW STATUS LIKE 'Max_used_connections';

-- 查看最大连接数配置
SHOW VARIABLES LIKE 'max_connections';

-- 连接使用率
连接使用率 = Threads_connected / max_connections * 100%
-- 建议：<80%

2. 连接错误

-- 查看连接错误数
SHOW STATUS LIKE 'Aborted_connects';

-- 查看客户端异常断开数
SHOW STATUS LIKE 'Aborted_clients';

-- 查看连接错误次数
SHOW STATUS LIKE 'Connection_errors%';

3. 线程缓存命中率

-- 查看创建的线程数
SHOW STATUS LIKE 'Threads_created';

-- 查看总连接数
SHOW STATUS LIKE 'Connections';

-- 线程缓存命中率
命中率 = (1 - Threads_created / Connections) * 100%
-- 建议：>90%

26.2.3 InnoDB指标

1. Buffer Pool命中率

-- 查看Buffer Pool读请求数
SHOW STATUS LIKE 'Innodb_buffer_pool_read_requests';

-- 查看从磁盘读取的次数
SHOW STATUS LIKE 'Innodb_buffer_pool_reads';

-- Buffer Pool命中率
命中率 = (1 - Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests) * 100%
-- 建议：>99%

2. Buffer Pool使用率

-- 查看Buffer Pool总页数
SHOW STATUS LIKE 'Innodb_buffer_pool_pages_total';

-- 查看空闲页数
SHOW STATUS LIKE 'Innodb_buffer_pool_pages_free';

-- 使用率
使用率 = (1 - pages_free / pages_total) * 100%

3. InnoDB行操作

-- 查看行读取数
SHOW STATUS LIKE 'Innodb_rows_read';

-- 查看行插入数
SHOW STATUS LIKE 'Innodb_rows_inserted';

-- 查看行更新数
SHOW STATUS LIKE 'Innodb_rows_updated';

-- 查看行删除数
SHOW STATUS LIKE 'Innodb_rows_deleted';

4. InnoDB日志

-- 查看日志写入次数
SHOW STATUS LIKE 'Innodb_log_writes';

-- 查看日志等待次数
SHOW STATUS LIKE 'Innodb_log_waits';
-- 如果>0，说明日志缓冲区太小

26.2.4 复制指标

1. 主从延迟

-- 在从库上执行
SHOW SLAVE STATUS\G

-- 关注字段：
-- Seconds_Behind_Master：延迟秒数
--   0：无延迟
--   NULL：复制未运行
--   >0：有延迟

-- 建议：<5秒

2. 复制状态

SHOW SLAVE STATUS\G

-- 关注字段：
-- Slave_IO_Running: Yes  -- IO线程运行
-- Slave_SQL_Running: Yes -- SQL线程运行
-- Last_Error: 空         -- 无错误

3. 复制位置

-- 主库binlog位置
SHOW MASTER STATUS;

-- 从库读取位置
SHOW SLAVE STATUS\G
-- Read_Master_Log_Pos
-- Exec_Master_Log_Pos

26.2.5 表和索引指标

1. 表锁

-- 查看表锁等待次数
SHOW STATUS LIKE 'Table_locks_waited';

-- 查看表锁立即获取次数
SHOW STATUS LIKE 'Table_locks_immediate';

-- 表锁等待率
等待率 = Table_locks_waited / (Table_locks_waited + Table_locks_immediate) * 100%
-- 建议：<1%

2. 临时表

-- 查看创建的临时表数
SHOW STATUS LIKE 'Created_tmp_tables';

-- 查看磁盘临时表数
SHOW STATUS LIKE 'Created_tmp_disk_tables';

-- 磁盘临时表比例
比例 = Created_tmp_disk_tables / Created_tmp_tables * 100%
-- 建议：<25%

3. 表缓存

-- 查看打开的表数
SHOW STATUS LIKE 'Open_tables';

-- 查看打开表的次数
SHOW STATUS LIKE 'Opened_tables';

-- 查看表缓存大小
SHOW VARIABLES LIKE 'table_open_cache';

-- 如果Opened_tables持续增长，说明缓存不足

26.2.6 磁盘指标

1. 磁盘空间

# 查看磁盘使用情况
df -h

# 查看MySQL数据目录大小
du -sh /var/lib/mysql

# 查看各数据库大小
SELECT 
    table_schema AS 'Database',
    ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS 'Size (MB)'
FROM information_schema.TABLES
GROUP BY table_schema;

# 查看各表大小
SELECT 
    table_name AS 'Table',
    ROUND(((data_length + index_length) / 1024 / 1024), 2) AS 'Size (MB)'
FROM information_schema.TABLES
WHERE table_schema = 'mydb'
ORDER BY (data_length + index_length) DESC;

2. 磁盘I/O

# 使用iostat监控
iostat -x 1

# 关注指标：
# %util：磁盘使用率（建议<80%）
# await：平均等待时间（SSD<1ms，HDD<10ms）
# r/s, w/s：每秒读写次数

26.3 监控工具

26.3.1 命令行工具

1. mysqladmin

# 查看状态
mysqladmin -u root -p status

# 持续监控（每秒刷新）
mysqladmin -u root -p -i 1 status

# 查看变量
mysqladmin -u root -p variables

# 查看进程列表
mysqladmin -u root -p processlist

# 扩展状态
mysqladmin -u root -p extended-status

2. mytop

# 安装
yum install mytop

# 使用
mytop -u root -p password

# 类似top的MySQL监控工具
# 显示：
# - 当前查询
# - QPS/TPS
# - 连接数
# - 慢查询

3. innotop

# 安装
yum install innotop

# 使用
innotop -u root -p password

# InnoDB监控工具
# 显示：
# - InnoDB状态
# - 事务
# - 锁
# - I/O

26.3.2 图形化监控工具

1. Percona Monitoring and Management (PMM) ⭐⭐⭐⭐⭐

安装PMM Server：

# 使用Docker安装
docker run -d -p 80:80 \
  --name pmm-server \
  -v pmm-data:/srv \
  percona/pmm-server:2

# 访问Web界面
http://your-server-ip
# 默认用户名：admin
# 默认密码：admin

安装PMM Client：

# CentOS
yum install pmm2-client

# Ubuntu
apt install pmm2-client

# 配置PMM Server地址
pmm-admin config --server-url=http://admin:admin@pmm-server:80

# 添加MySQL监控
pmm-admin add mysql --username=pmm --password=pmm_password --query-source=perfschema

# 查看监控列表
pmm-admin list

PMM功能：

✅ MySQL性能监控
✅ 查询分析（Query Analytics）
✅ 慢查询分析
✅ 图形化展示
✅ 告警功能

2. Prometheus + Grafana ⭐⭐⭐⭐⭐

安装mysqld_exporter：

# 下载
wget https://github.com/prometheus/mysqld_exporter/releases/download/v0.14.0/mysqld_exporter-0.14.0.linux-amd64.tar.gz

# 解压
tar -xzf mysqld_exporter-0.14.0.linux-amd64.tar.gz
cd mysqld_exporter-0.14.0.linux-amd64

# 创建MySQL监控用户
mysql -u root -p
CREATE USER 'exporter'@'localhost' IDENTIFIED BY 'exporter_password';
GRANT PROCESS, REPLICATION CLIENT, SELECT ON *.* TO 'exporter'@'localhost';
FLUSH PRIVILEGES;

# 创建配置文件
cat > .my.cnf <<EOF
[client]
user=exporter
password=exporter_password
EOF

# 启动exporter
./mysqld_exporter --config.my-cnf=.my.cnf &

# 访问metrics
curl http://localhost:9104/metrics

配置Prometheus：

# prometheus.yml
scrape_configs:
  - job_name: 'mysql'
    static_configs:
      - targets: ['localhost:9104']

Grafana导入MySQL仪表板：

1. 访问Grafana：http://localhost:3000
2. 导入仪表板：7362（MySQL Overview）
3. 选择数据源：Prometheus

3. Zabbix ⭐⭐⭐⭐

特点：

企业级监控方案
支持告警
支持自定义监控项

监控项：

MySQL服务状态
QPS/TPS
连接数
慢查询
主从延迟

26.4 告警配置

26.4.1 告警指标

关键告警指标：

1. MySQL服务状态
   - 告警条件：MySQL服务停止
   - 级别：紧急

2. 连接数
   - 告警条件：连接使用率>80%
   - 级别：警告

3. 主从延迟
   - 告警条件：延迟>10秒
   - 级别：警告
   - 告警条件：延迟>60秒
   - 级别：紧急

4. 磁盘空间
   - 告警条件：使用率>80%
   - 级别：警告
   - 告警条件：使用率>90%
   - 级别：紧急

5. 慢查询
   - 告警条件：慢查询数量突增
   - 级别：警告

6. 复制状态
   - 告警条件：Slave_IO_Running=No 或 Slave_SQL_Running=No
   - 级别：紧急

7. Buffer Pool命中率
   - 告警条件：命中率<95%
   - 级别：警告

8. QPS/TPS
   - 告警条件：QPS/TPS突降（可能是故障）
   - 级别：警告

26.4.2 告警方式

1. 邮件告警

# 使用mail命令
echo "MySQL连接数过高：当前连接数500，最大连接数600" | \
  mail -s "MySQL告警" admin@example.com

2. 短信告警

# 使用短信API
import requests

def send_sms(phone, message):
    url = "https://sms-api.example.com/send"
    data = {
        "phone": phone,
        "message": message
    }
    requests.post(url, json=data)

# 发送告警
send_sms("13800138000", "MySQL主从延迟60秒")

3. 企业微信/钉钉告警

# 钉钉机器人
import requests
import json

def send_dingtalk(message):
    webhook = "https://oapi.dingtalk.com/robot/send?access_token=YOUR_TOKEN"
    data = {
        "msgtype": "text",
        "text": {
            "content": message
        }
    }
    requests.post(webhook, json=data)

# 发送告警
send_dingtalk("MySQL告警：主从延迟60秒")

4. 电话告警

使用第三方服务（如阿里云、腾讯云）
关键告警使用电话通知

26.5 监控脚本

26.5.1 连接数监控脚本

#!/bin/bash
# monitor_connections.sh

# MySQL连接信息
MYSQL_USER="root"
MYSQL_PASS="password"
MYSQL_HOST="localhost"

# 告警阈值
WARNING_THRESHOLD=80  # 80%
CRITICAL_THRESHOLD=90 # 90%

# 获取当前连接数
CURRENT_CONN=$(mysql -u$MYSQL_USER -p$MYSQL_PASS -h$MYSQL_HOST -e "SHOW STATUS LIKE 'Threads_connected';" | awk 'NR==2 {print $2}')

# 获取最大连接数
MAX_CONN=$(mysql -u$MYSQL_USER -p$MYSQL_PASS -h$MYSQL_HOST -e "SHOW VARIABLES LIKE 'max_connections';" | awk 'NR==2 {print $2}')

# 计算使用率
USAGE=$(echo "scale=2; $CURRENT_CONN / $MAX_CONN * 100" | bc)

# 判断告警
if [ $(echo "$USAGE > $CRITICAL_THRESHOLD" | bc) -eq 1 ]; then
    echo "CRITICAL: MySQL连接使用率${USAGE}%，当前连接数${CURRENT_CONN}，最大连接数${MAX_CONN}"
    # 发送告警
    exit 2
elif [ $(echo "$USAGE > $WARNING_THRESHOLD" | bc) -eq 1 ]; then
    echo "WARNING: MySQL连接使用率${USAGE}%，当前连接数${CURRENT_CONN}，最大连接数${MAX_CONN}"
    exit 1
else
    echo "OK: MySQL连接使用率${USAGE}%"
    exit 0
fi

26.5.2 主从延迟监控脚本

#!/bin/bash
# monitor_replication.sh

MYSQL_USER="root"
MYSQL_PASS="password"
MYSQL_HOST="localhost"

# 告警阈值（秒）
WARNING_THRESHOLD=10
CRITICAL_THRESHOLD=60

# 获取主从延迟
DELAY=$(mysql -u$MYSQL_USER -p$MYSQL_PASS -h$MYSQL_HOST -e "SHOW SLAVE STATUS\G" | grep "Seconds_Behind_Master" | awk '{print $2}')

# 检查复制状态
IO_RUNNING=$(mysql -u$MYSQL_USER -p$MYSQL_PASS -h$MYSQL_HOST -e "SHOW SLAVE STATUS\G" | grep "Slave_IO_Running" | awk '{print $2}')
SQL_RUNNING=$(mysql -u$MYSQL_USER -p$MYSQL_PASS -h$MYSQL_HOST -e "SHOW SLAVE STATUS\G" | grep "Slave_SQL_Running" | awk '{print $2}')

# 检查复制是否运行
if [ "$IO_RUNNING" != "Yes" ] || [ "$SQL_RUNNING" != "Yes" ]; then
    echo "CRITICAL: MySQL复制未运行，IO_Running=$IO_RUNNING, SQL_Running=$SQL_RUNNING"
    exit 2
fi

# 检查延迟
if [ "$DELAY" == "NULL" ]; then
    echo "CRITICAL: MySQL复制延迟为NULL"
    exit 2
elif [ $DELAY -gt $CRITICAL_THRESHOLD ]; then
    echo "CRITICAL: MySQL主从延迟${DELAY}秒"
    exit 2
elif [ $DELAY -gt $WARNING_THRESHOLD ]; then
    echo "WARNING: MySQL主从延迟${DELAY}秒"
    exit 1
else
    echo "OK: MySQL主从延迟${DELAY}秒"
    exit 0
fi

26.5.3 磁盘空间监控脚本

#!/bin/bash
# monitor_disk.sh

# 数据目录
DATA_DIR="/var/lib/mysql"

# 告警阈值
WARNING_THRESHOLD=80
CRITICAL_THRESHOLD=90

# 获取磁盘使用率
USAGE=$(df -h $DATA_DIR | awk 'NR==2 {print $5}' | sed 's/%//')

# 判断告警
if [ $USAGE -gt $CRITICAL_THRESHOLD ]; then
    echo "CRITICAL: MySQL数据目录磁盘使用率${USAGE}%"
    exit 2
elif [ $USAGE -gt $WARNING_THRESHOLD ]; then
    echo "WARNING: MySQL数据目录磁盘使用率${USAGE}%"
    exit 1
else
    echo "OK: MySQL数据目录磁盘使用率${USAGE}%"
    exit 0
fi

26.5.4 慢查询监控脚本

#!/bin/bash
# monitor_slow_queries.sh

MYSQL_USER="root"
MYSQL_PASS="password"
MYSQL_HOST="localhost"

# 告警阈值（每分钟慢查询数）
THRESHOLD=10

# 获取当前慢查询数
CURRENT_SLOW=$(mysql -u$MYSQL_USER -p$MYSQL_PASS -h$MYSQL_HOST -e "SHOW GLOBAL STATUS LIKE 'Slow_queries';" | awk 'NR==2 {print $2}')

# 保存到临时文件
TEMP_FILE="/tmp/slow_queries.tmp"
if [ -f $TEMP_FILE ]; then
    LAST_SLOW=$(cat $TEMP_FILE)
    DIFF=$((CURRENT_SLOW - LAST_SLOW))

    if [ $DIFF -gt $THRESHOLD ]; then
        echo "WARNING: 最近1分钟产生${DIFF}个慢查询"
        exit 1
    else
        echo "OK: 最近1分钟产生${DIFF}个慢查询"
        exit 0
    fi
fi

# 保存当前值
echo $CURRENT_SLOW > $TEMP_FILE

26.6 监控最佳实践

26.6.1 监控原则

1. 全面监控
   - 系统层、MySQL层、业务层
   - 不遗漏关键指标

2. 分级告警
   - 警告：需要关注
   - 紧急：需要立即处理

3. 避免告警疲劳
   - 合理设置阈值
   - 避免频繁告警

4. 可视化
   - 使用图表展示
   - 便于发现趋势

5. 历史数据
   - 保留历史监控数据
   - 用于问题回溯

6. 自动化
   - 自动采集数据
   - 自动发送告警

26.6.2 监控检查清单

✅ 系统监控
   - CPU使用率
   - 内存使用率
   - 磁盘I/O
   - 磁盘空间
   - 网络流量

✅ MySQL服务监控
   - MySQL服务状态
   - MySQL进程状态

✅ 性能监控
   - QPS/TPS
   - 响应时间
   - 慢查询数量

✅ 连接监控
   - 当前连接数
   - 连接使用率
   - 连接错误数

✅ InnoDB监控
   - Buffer Pool命中率
   - Buffer Pool使用率
   - InnoDB行操作
   - 日志写入

✅ 复制监控
   - 主从延迟
   - 复制状态
   - 复制错误

✅ 表监控
   - 表锁等待
   - 临时表
   - 表缓存

✅ 告警配置
   - 告警规则
   - 告警方式
   - 告警接收人

26.7 本章总结

本章学习内容：

✅ 监控概述
✅ 核心监控指标（性能、连接、InnoDB、复制、表、磁盘）⭐⭐⭐⭐⭐
✅ 监控工具（命令行、PMM、Prometheus+Grafana）⭐⭐⭐⭐⭐
✅ 告警配置
✅ 监控脚本
✅ 监控最佳实践

重点掌握：

核心指标：QPS、TPS、连接数、主从延迟、Buffer Pool命中率
Buffer Pool命中率>99%
连接使用率<80%
主从延迟<5秒
磁盘使用率<80%
使用PMM或Prometheus+Grafana监控

核心监控指标：

QPS/TPS：性能指标
连接数：资源使用
Buffer Pool命中率：缓存效率
主从延迟：数据一致性
慢查询：性能问题

监控工具：

命令行：mysqladmin、mytop、innotop
PMM：企业级监控方案
Prometheus+Grafana：开源监控方案
Zabbix：企业级监控平台

告警配置：

分级告警：警告、紧急
多种告警方式：邮件、短信、电话
合理设置阈值

面试重点：

MySQL有哪些核心监控指标
如何计算QPS和TPS
Buffer Pool命中率如何计算
如何监控主从延迟
常用的监控工具有哪些
如何配置告警

下一章预告： 性能诊断工具

练习题

MySQL有哪些核心监控指标？
如何计算QPS和TPS？
Buffer Pool命中率如何计算？建议值是多少？
如何监控主从延迟？
连接使用率建议值是多少？
常用的MySQL监控工具有哪些？
PMM和Prometheus+Grafana有什么区别？
如何配置告警？
编写一个监控脚本监控连接数
设计一个MySQL监控方案

继续学习： 第27章：性能诊断工具