MySQL 复制监控与维护

复制监控基础

复制状态监控

查看复制状态

命令：
sql
```
SHOW SLAVE STATUS\G
```

关键状态参数

Slave_IO_Running：IO线程状态
Slave_SQL_Running：SQL线程状态
Seconds_Behind_Master：复制延迟（秒）
Master_Log_File：当前读取的主库二进制日志文件
Read_Master_Log_Pos：当前读取的主库二进制日志位置
Relay_Master_Log_File：当前应用的中继日志对应的主库二进制日志文件
Exec_Master_Log_Pos：当前应用的主库二进制日志位置
Last_Error：最近的错误信息

示例输出分析

Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.100
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 12345
Relay_Log_File: slave-relay-bin.000002
Relay_Log_Pos: 6789
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 12345
Relay_Log_Space: 10240
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: 123e4567-e89b-12d3-a456-426614174000
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:

复制性能监控

复制延迟监控

方法：

sql

SELECT Seconds_Behind_Master FROM information_schema.processlist WHERE command = 'Binlog Dump';

监控工具：
- MySQL Enterprise Monitor
- Prometheus + Grafana
- Zabbix

复制吞吐量监控

方法：

sql

SHOW GLOBAL STATUS LIKE 'Slave_received_%';
SHOW GLOBAL STATUS LIKE 'Slave_sent_%';
SHOW GLOBAL STATUS LIKE 'Slave_rows_%';

关键指标：
- Slave_received_bytes：接收的字节数
- Slave_rows_replicated：复制的行数
- Slave_rows_skipped：跳过的行数

复制错误监控

方法：

sql

SHOW GLOBAL STATUS LIKE 'Slave_retried_transactions';
SHOW GLOBAL STATUS LIKE 'Slave_last_heartbeat';

监控工具与方案

内置监控工具

Performance Schema

启用复制监控：

sql

UPDATE performance_schema.setup_instruments SET ENABLED = 'YES' WHERE NAME LIKE '%replication%';

查看复制指标：

sql

SELECT * FROM performance_schema.replication_connection_status;
SELECT * FROM performance_schema.replication_applier_status;
SELECT * FROM performance_schema.replication_applier_status_by_coordinator;
SELECT * FROM performance_schema.replication_applier_status_by_worker;

Information Schema

查看复制进程：

sql

SELECT * FROM information_schema.processlist WHERE command IN ('Binlog Dump', 'Slave IO', 'Slave SQL');

MySQL Shell

复制监控：

javascript

var repl = shell.getSession('root@localhost:3306').getReplicationStatus();
shell.dump(repl);

第三方监控工具

Prometheus + Grafana

导出器：mysql_exporter
监控指标：
- mysql_slave_status_seconds_behind_master
- mysql_slave_status_slave_io_running
- mysql_slave_status_slave_sql_running

告警规则：

yaml

groups:
- name: mysql-replication
  rules:
  - alert: MySQLReplicationLag
    expr: mysql_slave_status_seconds_behind_master > 300
    for: 5m
    labels:
      severity: warning
    annotations:
      summary: "MySQL replication lag"
      description: "Replication lag on {{ $labels.instance }} is {{ $value }} seconds"
  
  - alert: MySQLReplicationDown
    expr: mysql_slave_status_slave_io_running == 0 or mysql_slave_status_slave_sql_running == 0
    for: 2m
    labels:
      severity: critical
    annotations:
      summary: "MySQL replication down"
      description: "Replication is down on {{ $labels.instance }}"

Zabbix

模板：MySQL by Zabbix agent
监控项：
- 复制状态
- 复制延迟
- 复制错误
触发器：
- 复制停止
- 复制延迟超过阈值
- 复制错误

Percona Monitoring and Management (PMM)

复制监控：专门的复制监控面板
功能：
- 复制状态可视化
- 复制延迟趋势
- 复制错误告警
- 复制拓扑图

自定义监控脚本

基本监控脚本

示例脚本：

bash

#!/bin/bash

HOST="localhost"
USER="monitor"
PASS="password"

SLAVE_STATUS=$(mysql -h $HOST -u $USER -p$PASS -e "SHOW SLAVE STATUS\G")

IO_RUNNING=$(echo "$SLAVE_STATUS" | grep "Slave_IO_Running:" | awk '{print $2}')
SQL_RUNNING=$(echo "$SLAVE_STATUS" | grep "Slave_SQL_Running:" | awk '{print $2}')
SECONDS_BEHIND=$(echo "$SLAVE_STATUS" | grep "Seconds_Behind_Master:" | awk '{print $2}')

if [ "$IO_RUNNING" != "Yes" ] || [ "$SQL_RUNNING" != "Yes" ]; then
  echo "CRITICAL: Replication is not running! IO: $IO_RUNNING, SQL: $SQL_RUNNING"
  exit 2
fi

if [ "$SECONDS_BEHIND" -gt 300 ]; then
  echo "WARNING: Replication lag is high: $SECONDS_BEHIND seconds"
  exit 1
fi

echo "OK: Replication is running normally. Lag: $SECONDS_BEHIND seconds"
exit 0

高级监控脚本

功能：
- 监控多个从库
- 发送邮件告警
- 记录历史数据
- 自动尝试恢复

复制维护操作

日常维护

复制状态检查

频率：每日至少一次
内容：
- 检查复制是否运行正常
- 检查复制延迟
- 检查复制错误

二进制日志管理

清理过期日志：

sql

PURGE BINARY LOGS BEFORE '2023-01-01 00:00:00';

设置自动清理：

sql

SET GLOBAL expire_logs_days = 7;
-- MySQL 8.0+
SET GLOBAL binlog_expire_logs_seconds = 604800;

中继日志管理

清理中继日志：
sql
```
RESET SLAVE ALL;
```

设置中继日志大小：

sql

SET GLOBAL max_relay_log_size = 1073741824;

故障处理

复制中断处理

IO线程中断

常见原因：
- 网络连接问题
- 主库宕机
- 复制用户权限问题
- 主库二进制日志损坏

解决方法：

sql

STOP SLAVE IO_THREAD;
START SLAVE IO_THREAD;

SQL线程中断

常见原因：
- 主键冲突
- 表结构不一致
- 权限问题
- 数据类型不匹配

解决方法：

sql

-- 跳过错误
STOP SLAVE;
SET GLOBAL sql_slave_skip_counter = 1;
START SLAVE;

-- 或使用 GTID 模式
STOP SLAVE;
CHANGE MASTER TO MASTER_AUTO_POSITION = 1;
START SLAVE;

复制延迟处理

原因分析

网络延迟：网络带宽不足或网络不稳定
主库负载高：主库写入速度快于从库应用速度
从库配置低：从库硬件配置低于主库
大事务：单个大事务需要长时间应用
锁竞争：从库上的查询与复制线程竞争资源

解决方法

优化从库配置：

sql

SET GLOBAL innodb_buffer_pool_size = 8G;
SET GLOBAL innodb_log_file_size = 1G;
SET GLOBAL innodb_flush_log_at_trx_commit = 2;
SET GLOBAL sync_binlog = 1000;

使用多线程复制：

sql

SET GLOBAL slave_parallel_workers = 4;
SET GLOBAL slave_parallel_type = 'LOGICAL_CLOCK';

优化网络：
- 增加网络带宽
- 使用专用网络
- 优化网络路由
拆分大事务：
- 将大事务拆分为小事务
- 使用批量操作

复制数据一致性检查

方法

使用 pt-table-checksum：

bash

pt-table-checksum --host=master_host --user=checksum_user --password=password --databases=test

使用 pt-table-sync：

bash

pt-table-sync --execute --host=master_host --user=sync_user --password=password --databases=test h=slave_host

手动检查：

sql

-- 在主库
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;
-- 记录二进制日志位置

-- 在从库
SELECT MASTER_POS_WAIT('mysql-bin.000001', 12345);
-- 执行数据一致性检查

-- 在主库
UNLOCK TABLES;

复制拓扑管理

主从切换

手动切换

步骤：

确保所有从库已应用完所有二进制日志

sql

STOP SLAVE IO_THREAD;
SHOW SLAVE STATUS\G
-- 等待 Seconds_Behind_Master 为 0

提升从库为主库
sql
```
STOP SLAVE;
RESET MASTER;
```

其他从库指向新主库

sql

STOP SLAVE;
CHANGE MASTER TO MASTER_HOST='new_master_host', MASTER_PORT=3306, MASTER_USER='repl', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=1;
START SLAVE;

使用工具切换

使用 MHA (Master High Availability)：

bash

masterha_master_switch --master_state=alive --conf=/etc/masterha/app1.cnf --new_master_host=slave1 --new_master_port=3306

使用 Orchestrator：
- Web界面操作
- 命令行操作

复制拓扑变更

添加从库

步骤：

备份主库数据

bash

mysqldump --single-transaction --master-data=2 --all-databases > backup.sql

恢复到新从库
bash
```
mysql < backup.sql
```

配置复制

sql

CHANGE MASTER TO MASTER_HOST='master_host', MASTER_PORT=3306, MASTER_USER='repl', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=12345;
START SLAVE;

移除从库

步骤：
1. 停止复制
  sql
```
STOP SLAVE;
RESET SLAVE ALL;
```
2. 从监控系统中移除
3. 清理相关配置

高级维护技巧

复制过滤优化

表级过滤

配置：

sql

CHANGE MASTER TO
  REPLICATE_DO_TABLE = ('db1.table1', 'db2.table2'),
  REPLICATE_IGNORE_TABLE = ('db1.table3', 'db2.table4');

库级过滤

配置：

sql

CHANGE MASTER TO
  REPLICATE_DO_DB = ('db1', 'db2'),
  REPLICATE_IGNORE_DB = ('db3', 'db4');

通配符过滤

配置：

sql

CHANGE MASTER TO
  REPLICATE_WILD_DO_TABLE = ('db1.%, 'db2.tbl%'),
  REPLICATE_WILD_IGNORE_TABLE = ('db1.temp%', 'db2.%_log');

复制参数调优

主库参数

关键参数：

sql

SET GLOBAL binlog_format = 'ROW';
SET GLOBAL binlog_row_image = 'MINIMAL';
SET GLOBAL sync_binlog = 1;
SET GLOBAL binlog_group_commit_sync_delay = 100;
SET GLOBAL binlog_group_commit_sync_no_delay_count = 100;

从库参数

关键参数：

sql

SET GLOBAL slave_parallel_workers = 8;
SET GLOBAL slave_parallel_type = 'LOGICAL_CLOCK';
SET GLOBAL slave_preserve_commit_order = 1;
SET GLOBAL relay_log_recovery = 1;
SET GLOBAL slave_net_timeout = 60;
SET GLOBAL master_connect_retry = 10;

复制模式优化

GTID复制

启用方法：

sql

-- 主库和从库
SET GLOBAL gtid_mode = ON;
SET GLOBAL enforce_gtid_consistency = ON;

-- 从库配置
CHANGE MASTER TO
  MASTER_HOST='master_host',
  MASTER_PORT=3306,
  MASTER_USER='repl',
  MASTER_PASSWORD='password',
  MASTER_AUTO_POSITION = 1;
START SLAVE;

半同步复制

启用方法：

sql

-- 主库
INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
SET GLOBAL rpl_semi_sync_master_enabled = 1;
SET GLOBAL rpl_semi_sync_master_timeout = 10000;

-- 从库
INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
SET GLOBAL rpl_semi_sync_slave_enabled = 1;

增强半同步复制

启用方法：

sql

-- 主库
SET GLOBAL rpl_semi_sync_master_wait_point = 'AFTER_SYNC';

最佳实践

监控最佳实践

监控指标

核心指标：
- 复制状态（IO线程和SQL线程）
- 复制延迟
- 复制错误
- 复制吞吐量
- 二进制日志和中继日志大小

告警阈值

建议阈值：
- 复制延迟：超过300秒告警
- 复制中断：立即告警
- 复制错误：立即告警
- 复制重试：超过10次告警

监控频率

建议频率：
- 复制状态：每30秒一次
- 复制延迟：每1分钟一次
- 复制错误：每1分钟一次
- 详细检查：每小时一次

维护最佳实践

定期维护任务

每日：
- 检查复制状态
- 监控复制延迟
- 检查复制错误
每周：
- 清理过期二进制日志
- 检查数据一致性
- 备份复制配置
每月：
- 进行复制切换测试
- 优化复制配置
- 更新监控阈值

升级与迁移

版本升级

步骤：
1. 升级从库
2. 进行主从切换
3. 升级原主库
4. 重新配置复制

架构迁移

方法：
- 使用 GTID 简化迁移
- 利用复制进行无缝迁移
- 实施滚动升级

文档与记录

建议文档：
- 复制拓扑图
- 复制配置详情
- 维护操作手册
- 故障处理流程
- 历史问题记录

常见问题（FAQ）

Q1: 如何快速检查所有从库的复制状态？

A1: 可以使用以下方法：

使用 Shell 脚本：编写脚本连接所有从库并检查状态
使用 MySQL Router：通过 Router 管理复制拓扑
使用 Orchestrator：Web界面查看所有从库状态
使用 PMM：在监控面板中查看所有从库状态

Q2: 复制延迟突然增加怎么办？

A2: 处理步骤：

分析原因：查看从库负载、网络状态、主库事务大小
检查从库状态：SHOW SLAVE STATUS\G
查看从库进程：SHOW PROCESSLIST;
采取措施：
- 增加从库资源
- 优化从库配置
- 启用多线程复制
- 检查并解决锁竞争

Q3: 如何处理复制中的主键冲突？

A3: 处理方法：

临时跳过：使用 sql_slave_skip_counter 跳过错误
修复数据：在从库手动修复数据一致性
使用工具：使用 pt-table-sync 同步数据
预防措施：确保应用程序避免在从库写入数据

Q4: GTID 复制和传统复制有什么区别？

A4: 主要区别：

定位方式：GTID 使用全局事务ID，传统复制使用二进制日志文件名和位置
故障恢复：GTID 自动定位，传统复制需要手动指定位置
一致性：GTID 确保事务只执行一次，避免重复执行
配置复杂度：GTID 配置更简单，传统复制需要手动管理位置

Q5: 如何监控多源复制？

A5: 监控方法：

使用 SHOW SLAVE STATUS：指定通道名称
sql
```
SHOW SLAVE STATUS FOR CHANNEL 'channel1'\G
```

使用 Performance Schema：

sql

SELECT * FROM performance_schema.replication_applier_status_by_channel;

监控工具：确保监控工具支持多源复制

Q6: 复制维护时需要停止业务吗？

A6: 大多数维护操作不需要停止业务：

日常检查：不需要停止业务
配置调整：大部分参数可以在线调整
二进制日志清理：不需要停止业务
主从切换：使用工具可以实现几乎无感知切换
数据一致性检查：pt-table-checksum 可以在线执行

Q7: 如何确保复制的安全性？

A7: 安全措施：

使用 SSL 加密：

sql

CHANGE MASTER TO
  MASTER_SSL = 1,
  MASTER_SSL_CA = '/path/to/ca.pem',
  MASTER_SSL_CERT = '/path/to/client-cert.pem',
  MASTER_SSL_KEY = '/path/to/client-key.pem';

限制复制用户权限：

sql

CREATE USER 'repl'@'%' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';

使用专用网络：使用内网或 VPN 进行复制
定期更改复制密码：定期更新复制用户密码
监控复制用户活动：监控复制用户的登录和操作

MySQL 复制监控与维护 ​

复制监控基础 ​

复制状态监控 ​

查看复制状态 ​

关键状态参数 ​

示例输出分析 ​

复制性能监控 ​

复制延迟监控 ​

复制吞吐量监控 ​

复制错误监控 ​

监控工具与方案 ​

内置监控工具 ​

Performance Schema ​

Information Schema ​

MySQL Shell ​

第三方监控工具 ​

Prometheus + Grafana ​

Zabbix ​

Percona Monitoring and Management (PMM) ​

自定义监控脚本 ​

基本监控脚本 ​

高级监控脚本 ​

复制维护操作 ​

日常维护 ​

复制状态检查 ​

二进制日志管理 ​

中继日志管理 ​

故障处理 ​

复制中断处理 ​

IO线程中断 ​

SQL线程中断 ​

复制延迟处理 ​

原因分析 ​

解决方法 ​

复制数据一致性检查 ​

方法 ​

复制拓扑管理 ​

主从切换 ​

手动切换 ​

使用工具切换 ​

复制拓扑变更 ​

添加从库 ​

移除从库 ​

高级维护技巧 ​

复制过滤优化 ​

表级过滤 ​

库级过滤 ​

通配符过滤 ​

复制参数调优 ​

主库参数 ​

从库参数 ​

复制模式优化 ​

GTID复制 ​

MySQL 复制监控与维护

复制监控基础

复制状态监控

查看复制状态

关键状态参数

示例输出分析

复制性能监控

复制延迟监控

复制吞吐量监控

复制错误监控

监控工具与方案

内置监控工具

Performance Schema

Information Schema

MySQL Shell

第三方监控工具

Prometheus + Grafana

Zabbix

Percona Monitoring and Management (PMM)

自定义监控脚本

基本监控脚本

高级监控脚本

复制维护操作

日常维护

复制状态检查

二进制日志管理

中继日志管理

故障处理

复制中断处理

IO线程中断

SQL线程中断

复制延迟处理

原因分析

解决方法

复制数据一致性检查

方法

复制拓扑管理

主从切换

手动切换

使用工具切换

复制拓扑变更

添加从库

移除从库

高级维护技巧

复制过滤优化

表级过滤

库级过滤

通配符过滤

复制参数调优

主库参数

从库参数

复制模式优化

GTID复制