# 监控工具选择
1. 内置工具：
– MySQL命令行工具：如SHOW SLAVE STATUS、SHOW STATUS等
– MySQL Enterprise Monitor：MySQL官方提供的监控工具

2. 开源工具：
– Prometheus + Grafana：开源的监控和可视化工具
– Zabbix：开源的监控系统
– Nagios：开源的监控系统
– Icinga：Nagios的分支，提供更现代化的界面

3. 商业工具：
– Datadog：云原生监控平台
– New Relic：应用性能监控平台
– AppDynamics：应用性能监控平台

# 监控工具比较
| 工具 | 优点 | 缺点 | 适用场景 |
|——|——|——|———-|
| MySQL命令行工具 | 简单直接，无需额外安装 | 无法实时监控，需要手动执行 | 临时检查，小型系统 |
| Prometheus + Grafana | 开源，功能强大，可视化效果好 | 配置复杂，需要一定的技术知识 | 中大型系统，需要详细的监控和可视化 |
| Zabbix | 开源，功能全面，支持多种监控方式 | 配置复杂，资源消耗较大 | 中大型系统，需要全面的监控 |
| Datadog | 云原生，易于部署，功能丰富 | 付费，成本较高 | 云环境，大型系统 |

# 推荐监控工具组合
– 小型系统：MySQL命令行工具 + 简单的脚本监控
– 中型系统：Prometheus + Grafana
– 大型系统：Prometheus + Grafana + 商业监控工具

# 告警策略规划
1. 告警级别：
– 紧急：需要立即处理的问题，如复制线程停止
– 严重：需要尽快处理的问题，如复制延迟较大
– 警告：需要关注的问题，如复制延迟轻微增加
– 信息：仅供参考的信息，如复制状态正常

2. 告警方式：
– 邮件：适合非紧急的告警
– 短信：适合紧急的告警
– 电话：适合非常紧急的告警
– 即时通讯工具：如钉钉、微信等，适合团队协作

3. 告警频率：
– 紧急告警：立即发送，每5分钟重发一次，直到问题解决
– 严重告警：立即发送，每15分钟重发一次，直到问题解决
– 警告：立即发送，每30分钟重发一次，直到问题解决
– 信息：仅发送一次

4. 告警接收人：
– 紧急告警：所有运维人员
– 严重告警：主要运维人员
– 警告：值班人员
– 信息：相关人员

# 推荐告警策略
| 告警类型 | 级别 | 方式 | 频率 | 接收人 |
|———-|——|——|——|——–|
| 复制线程停止 | 紧急 | 邮件 + 短信 | 每5分钟 | 所有运维人员 |
| 复制延迟 > 300秒 | 严重 | 邮件 + 短信 | 每15分钟 | 主要运维人员 |
| 复制延迟 > 60秒 | 警告 | 邮件 | 每30分钟 | 值班人员 |
| 复制状态正常 | 信息 | 邮件 | 一次 | 相关人员 |

# 基于MySQL命令的监控
# 步骤1：创建监控脚本
# vi /mysql/scripts/monitor_replication.sh
#!/bin/bash
# monitor_replication.sh
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: http://www.fgedu.net.cn

LOG_FILE=”/mysql/logs/replication_monitor.log”
ALERT_EMAIL=”admin@example.com”

# 检查复制状态
SLAVE_STATUS=$(mysql -u root -pPassword123! -e “SHOW SLAVE STATUS\G”)

# 提取关键信息
SLAVE_IO_RUNNING=$(echo “$SLAVE_STATUS” | grep “Slave_IO_Running” | awk ‘{print $2}’)
SLAVE_SQL_RUNNING=$(echo “$SLAVE_STATUS” | grep “Slave_SQL_Running” | awk ‘{print $2}’)
SECONDS_BEHIND_MASTER=$(echo “$SLAVE_STATUS” | grep “Seconds_Behind_Master” | awk ‘{print $2}’)
LAST_IO_ERROR=$(echo “$SLAVE_STATUS” | grep “Last_IO_Error” | sed ‘s/Last_IO_Error: //’)
LAST_SQL_ERROR=$(echo “$SLAVE_STATUS” | grep “Last_SQL_Error” | sed ‘s/Last_SQL_Error: //’)

# 记录监控信息
echo “[$(date +%Y-%m-%d%H:%M:%S)] Replication status check” >> $LOG_FILE
echo “Slave_IO_Running: $SLAVE_IO_RUNNING” >> $LOG_FILE
echo “Slave_SQL_Running: $SLAVE_SQL_RUNNING” >> $LOG_FILE
echo “Seconds_Behind_Master: $SECONDS_BEHIND_MASTER” >> $LOG_FILE
echo “Last_IO_Error: $LAST_IO_ERROR” >> $LOG_FILE
echo “Last_SQL_Error: $LAST_SQL_ERROR” >> $LOG_FILE

# 检查IO线程状态
if [ “$SLAVE_IO_RUNNING” != “Yes” ]; then
echo “[$(date +%Y-%m-%d%H:%M:%S)] ERROR: Slave IO thread is not running!” >> $LOG_FILE
# 发送告警邮件
echo “Slave IO thread is not running!\n\n$SLAVE_STATUS” | mail -s “Replication Alert: Slave IO thread stopped” $ALERT_EMAIL
fi

# 检查SQL线程状态
if [ “$SLAVE_SQL_RUNNING” != “Yes” ]; then
echo “[$(date +%Y-%m-%d%H:%M:%S)] ERROR: Slave SQL thread is not running!” >> $LOG_FILE
# 发送告警邮件
echo “Slave SQL thread is not running!\n\n$SLAVE_STATUS” | mail -s “Replication Alert: Slave SQL thread stopped” $ALERT_EMAIL
fi

# 检查复制延迟
if [ “$SECONDS_BEHIND_MASTER” -gt 300 ]; then
echo “[$(date +%Y-%m-%d%H:%M:%S)] WARNING: Replication delay is $SECONDS_BEHIND_MASTER seconds!” >> $LOG_FILE
# 发送告警邮件
echo “Replication delay is $SECONDS_BEHIND_MASTER seconds!\n\n$SLAVE_STATUS” | mail -s “Replication Alert: High replication delay” $ALERT_EMAIL
fi

# 检查IO错误
if [ -n “$LAST_IO_ERROR” ]; then
echo “[$(date +%Y-%m-%d%H:%M:%S)] ERROR: IO error: $LAST_IO_ERROR” >> $LOG_FILE
# 发送告警邮件
echo “IO error: $LAST_IO_ERROR\n\n$SLAVE_STATUS” | mail -s “Replication Alert: IO error” $ALERT_EMAIL
fi

# 检查SQL错误
if [ -n “$LAST_SQL_ERROR” ]; then
echo “[$(date +%Y-%m-%d%H:%M:%S)] ERROR: SQL error: $LAST_SQL_ERROR” >> $LOG_FILE
# 发送告警邮件
echo “SQL error: $LAST_SQL_ERROR\n\n$SLAVE_STATUS” | mail -s “Replication Alert: SQL error” $ALERT_EMAIL
fi

# 步骤2：设置执行权限
chmod +x /mysql/scripts/monitor_replication.sh

# 步骤3：添加cron任务
# crontab -e
*/5 * * * * /mysql/scripts/monitor_replication.sh

# 步骤4：验证监控效果
# 查看监控日志
tail -f /mysql/logs/replication_monitor.log

# 模拟复制错误
# 在从库上执行
mysql> STOP SLAVE IO_THREAD;

# 检查是否收到告警邮件

# 基于Prometheus的监控
# 步骤1：安装Prometheus
# 下载并安装Prometheus
wget https://github.com/prometheus/prometheus/releases/download/v2.40.0/prometheus-2.40.0.linux-amd64.tar.gz
tar -xzf prometheus-2.40.0.linux-amd64.tar.gz
mv prometheus-2.40.0.linux-amd64 /usr/local/prometheus

# 配置Prometheus
# vi /usr/local/prometheus/prometheus.yml
global:
scrape_interval: 15s
evaluation_interval: 15s

scrape_configs:
– job_name: ‘mysql’
static_configs:
– targets: [‘192.168.1.100:9104’, ‘192.168.1.101:9104’]

# 启动Prometheus
nohup /usr/local/prometheus/prometheus –config.file=/usr/local/prometheus/prometheus.yml &

# 步骤2：安装MySQL Exporter
# 下载并安装MySQL 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
mv mysqld_exporter-0.14.0.linux-amd64 /usr/local/mysqld_exporter

# 创建监控用户
mysql> CREATE USER ‘exporter’@’localhost’ IDENTIFIED BY ‘ExporterPassword123!’;
mysql> GRANT PROCESS, REPLICATION CLIENT, SELECT ON *.* TO ‘exporter’@’localhost’;
mysql> FLUSH PRIVILEGES;

# 创建配置文件
# vi /etc/.mysqld_exporter.cnf
[client]
user=exporter
password=ExporterPassword123!

# 启动MySQL Exporter
nohup /usr/local/mysqld_exporter/mysqld_exporter –config.my-cnf=/etc/.mysqld_exporter.cnf &

# 步骤3：安装Grafana
# 下载并安装Grafana
yum install -y https://dl.grafana.com/oss/release/grafana-9.0.0-1.x86_64.rpm
systemctl start grafana-server
systemctl enable grafana-server

# 步骤4：配置Grafana
# 登录Grafana，添加Prometheus数据源
# 访问 http://localhost:3000
# 用户名：admin，密码：admin

# 导入MySQL监控面板
# 导入ID为7362的MySQL监控面板

# 步骤5：配置告警
# 在Grafana中配置告警规则
# 配置邮件告警

# 步骤6：验证监控效果
# 访问Grafana面板
# http://localhost:3000

# 查看MySQL复制状态
# 查看复制延迟
# 查看复制错误

# 基于Zabbix的监控
# 步骤1：安装Zabbix Server
# 安装Zabbix Server
yum install -y zabbix-server-mysql zabbix-web-mysql zabbix-apache-conf zabbix-agent

# 创建Zabbix数据库
mysql> CREATE DATABASE zabbix CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;
mysql> CREATE USER ‘zabbix’@’localhost’ IDENTIFIED BY ‘ZabbixPassword123!’;
mysql> GRANT ALL PRIVILEGES ON zabbix.* TO ‘zabbix’@’localhost’;
mysql> FLUSH PRIVILEGES;

# 导入Zabbix数据库 schema
zcat /usr/share/doc/zabbix-server-mysql*/create.sql.gz | mysql -u zabbix -p zabbix

# 配置Zabbix Server
# vi /etc/zabbix/zabbix_server.conf
DBPassword=ZabbixPassword123!

# 启动Zabbix Server
systemctl start zabbix-server zabbix-agent httpd

systemctl enable zabbix-server zabbix-agent httpd

# 步骤2：安装Zabbix Agent
# 在主库和从库上安装Zabbix Agent
yum install -y zabbix-agent

# 配置Zabbix Agent
# vi /etc/zabbix/zabbix_agentd.conf
Server=192.168.1.100

# 启动Zabbix Agent
systemctl start zabbix-agent
systemctl enable zabbix-agent

# 步骤3：配置Zabbix监控项
# 登录Zabbix Web界面
# 访问 http://localhost/zabbix
# 用户名：Admin，密码：zabbix

# 添加主机
# 配置MySQL监控模板

# 添加自定义监控项
# 监控复制状态
# 监控复制延迟
# 监控复制错误

# 步骤4：配置告警
# 在Zabbix中配置告警规则
# 配置邮件告警

# 步骤5：验证监控效果
# 访问Zabbix Web界面
# 查看MySQL复制状态
# 查看复制延迟
# 查看复制错误

# 主从复制状态监控
# 步骤1：创建监控脚本
# vi /mysql/scripts/monitor_replication_status.sh
#!/bin/bash
# monitor_replication_status.sh

LOG_FILE=”/mysql/logs/replication_status.log”
ALERT_EMAIL=”admin@example.com”

# 检查复制状态
SLAVE_STATUS=$(mysql -u root -pPassword123! -e “SHOW SLAVE STATUS\G”)

# 提取关键信息
SLAVE_IO_RUNNING=$(echo “$SLAVE_STATUS” | grep “Slave_IO_Running” | awk ‘{print $2}’)
SLAVE_SQL_RUNNING=$(echo “$SLAVE_STATUS” | grep “Slave_SQL_Running” | awk ‘{print $2}’)
LAST_IO_ERROR=$(echo “$SLAVE_STATUS” | grep “Last_IO_Error” | sed ‘s/Last_IO_Error: //’)
LAST_SQL_ERROR=$(echo “$SLAVE_STATUS” | grep “Last_SQL_Error” | sed ‘s/Last_SQL_Error: //’)

# 记录监控信息
echo “[$(date +%Y-%m-%d%H:%M:%S)] Replication status check” >> $LOG_FILE
echo “Slave_IO_Running: $SLAVE_IO_RUNNING” >> $LOG_FILE
echo “Slave_SQL_Running: $SLAVE_SQL_RUNNING” >> $LOG_FILE
echo “Last_IO_Error: $LAST_IO_ERROR” >> $LOG_FILE
echo “Last_SQL_Error: $LAST_SQL_ERROR” >> $LOG_FILE

# 检查IO线程状态
if [ “$SLAVE_IO_RUNNING” != “Yes” ]; then
echo “[$(date +%Y-%m-%d%H:%M:%S)] ERROR: Slave IO thread is not running!” >> $LOG_FILE
# 发送告警邮件
echo “Slave IO thread is not running!\n\n$SLAVE_STATUS” | mail -s “Replication Alert: Slave IO thread stopped” $ALERT_EMAIL
fi

# 检查SQL线程状态
if [ “$SLAVE_SQL_RUNNING” != “Yes” ]; then
echo “[$(date +%Y-%m-%d%H:%M:%S)] ERROR: Slave SQL thread is not running!” >> $LOG_FILE
# 发送告警邮件
echo “Slave SQL thread is not running!\n\n$SLAVE_STATUS” | mail -s “Replication Alert: Slave SQL thread stopped” $ALERT_EMAIL
fi

# 检查IO错误
if [ -n “$LAST_IO_ERROR” ]; then
echo “[$(date +%Y-%m-%d%H:%M:%S)] ERROR: IO error: $LAST_IO_ERROR” >> $LOG_FILE
# 发送告警邮件
echo “IO error: $LAST_IO_ERROR\n\n$SLAVE_STATUS” | mail -s “Replication Alert: IO error” $ALERT_EMAIL
fi

# 检查SQL错误
if [ -n “$LAST_SQL_ERROR” ]; then
echo “[$(date +%Y-%m-%d%H:%M:%S)] ERROR: SQL error: $LAST_SQL_ERROR” >> $LOG_FILE
# 发送告警邮件
echo “SQL error: $LAST_SQL_ERROR\n\n$SLAVE_STATUS” | mail -s “Replication Alert: SQL error” $ALERT_EMAIL
fi

# 步骤2：设置执行权限
chmod +x /mysql/scripts/monitor_replication_status.sh

# 步骤3：添加cron任务
# crontab -e
*/5 * * * * /mysql/scripts/monitor_replication_status.sh

# 步骤4：验证监控效果
# 查看监控日志
tail -f /mysql/logs/replication_status.log

# 模拟复制错误
# 在从库上执行
mysql> STOP SLAVE IO_THREAD;

# 检查是否收到告警邮件

# 复制延迟监控
# 步骤1：创建监控脚本
# vi /mysql/scripts/monitor_replication_delay.sh
#!/bin/bash
# monitor_replication_delay.sh

LOG_FILE=”/mysql/logs/replication_delay.log”
ALERT_EMAIL=”admin@example.com”

# 检查复制延迟
SLAVE_STATUS=$(mysql -u root -pPassword123! -e “SHOW SLAVE STATUS\G”)
SECONDS_BEHIND_MASTER=$(echo “$SLAVE_STATUS” | grep “Seconds_Behind_Master” | awk ‘{print $2}’)

# 记录监控信息
echo “[$(date +%Y-%m-%d%H:%M:%S)] Replication delay check” >> $LOG_FILE
echo “Seconds_Behind_Master: $SECONDS_BEHIND_MASTER” >> $LOG_FILE

# 检查复制延迟
if [ “$SECONDS_BEHIND_MASTER” -gt 300 ]; then
echo “[$(date +%Y-%m-%d%H:%M:%S)] WARNING: Replication delay is $SECONDS_BEHIND_MASTER seconds!” >> $LOG_FILE
# 发送告警邮件
echo “Replication delay is $SECONDS_BEHIND_MASTER seconds!\n\n$SLAVE_STATUS” | mail -s “Replication Alert: High replication delay” $ALERT_EMAIL
elif [ “$SECONDS_BEHIND_MASTER” -gt 60 ]; then
echo “[$(date +%Y-%m-%d%H:%M:%S)] NOTICE: Replication delay is $SECONDS_BEHIND_MASTER seconds!” >> $LOG_FILE
# 发送通知邮件
echo “Replication delay is $SECONDS_BEHIND_MASTER seconds!\n\n$SLAVE_STATUS” | mail -s “Replication Notice: Replication delay” $ALERT_EMAIL
else
echo “[$(date +%Y-%m-%d%H:%M:%S)] INFO: Replication delay is $SECONDS_BEHIND_MASTER seconds” >> $LOG_FILE
fi

# 步骤2：设置执行权限
chmod +x /mysql/scripts/monitor_replication_delay.sh

# 步骤3：添加cron任务
# crontab -e
*/5 * * * * /mysql/scripts/monitor_replication_delay.sh

# 步骤4：验证监控效果
# 查看监控日志
tail -f /mysql/logs/replication_delay.log

# 模拟复制延迟
# 在主库上执行大量写操作
mysql> CREATE DATABASE test_delay;
mysql> USE test_delay;
mysql> CREATE TABLE test_table (id INT PRIMARY KEY AUTO_INCREMENT, data VARCHAR(1000));
# 插入10000条数据
mysql> DELIMITER //
mysql> CREATE PROCEDURE insert_data()
BEGIN
DECLARE i INT DEFAULT 0;
WHILE i < 10000 DO INSERT INTO test_table (data) VALUES (REPEAT('a', 1000)); SET i = i + 1; END WHILE; END // mysql> DELIMITER ;
mysql> CALL insert_data();

# 检查复制延迟
mysql> SHOW SLAVE STATUS\G | grep Seconds_Behind_Master

# 检查是否收到告警邮件

# 复制错误监控
# 步骤1：创建监控脚本
# vi /mysql/scripts/monitor_replication_errors.sh
#!/bin/bash
# monitor_replication_errors.sh

LOG_FILE=”/mysql/logs/replication_errors.log”
ALERT_EMAIL=”admin@example.com”

# 检查复制错误
SLAVE_STATUS=$(mysql -u root -pPassword123! -e “SHOW SLAVE STATUS\G”)
LAST_IO_ERROR=$(echo “$SLAVE_STATUS” | grep “Last_IO_Error” | sed ‘s/Last_IO_Error: //’)
LAST_SQL_ERROR=$(echo “$SLAVE_STATUS” | grep “Last_SQL_Error” | sed ‘s/Last_SQL_Error: //’)

# 记录监控信息
echo “[$(date +%Y-%m-%d%H:%M:%S)] Replication errors check” >> $LOG_FILE
echo “Last_IO_Error: $LAST_IO_ERROR” >> $LOG_FILE
echo “Last_SQL_Error: $LAST_SQL_ERROR” >> $LOG_FILE

# 检查IO错误
if [ -n “$LAST_IO_ERROR” ]; then
echo “[$(date +%Y-%m-%d%H:%M:%S)] ERROR: IO error: $LAST_IO_ERROR” >> $LOG_FILE
# 发送告警邮件
echo “IO error: $LAST_IO_ERROR\n\n$SLAVE_STATUS” | mail -s “Replication Alert: IO error” $ALERT_EMAIL
fi

# 检查SQL错误
if [ -n “$LAST_SQL_ERROR” ]; then
echo “[$(date +%Y-%m-%d%H:%M:%S)] ERROR: SQL error: $LAST_SQL_ERROR” >> $LOG_FILE
# 发送告警邮件
echo “SQL error: $LAST_SQL_ERROR\n\n$SLAVE_STATUS” | mail -s “Replication Alert: SQL error” $ALERT_EMAIL
fi

# 步骤2：设置执行权限
chmod +x /mysql/scripts/monitor_replication_errors.sh

# 步骤3：添加cron任务
# crontab -e
*/5 * * * * /mysql/scripts/monitor_replication_errors.sh

# 步骤4：验证监控效果
# 查看监控日志
tail -f /mysql/logs/replication_errors.log

# 模拟复制错误
# 在主库上创建表
mysql> CREATE DATABASE test_error;
mysql> USE test_error;
mysql> CREATE TABLE test_table (id INT PRIMARY KEY AUTO_INCREMENT, data VARCHAR(100));

# 在从库上手动创建相同的表，但结构不同
mysql> CREATE DATABASE test_error;
mysql> USE test_error;
mysql> CREATE TABLE test_table (id INT PRIMARY KEY AUTO_INCREMENT, data VARCHAR(50));

# 在主库上插入数据
mysql> INSERT INTO test_table (data) VALUES (REPEAT(‘a’, 100));

# 检查复制错误
mysql> SHOW SLAVE STATUS\G | grep Last_SQL_Error

# 检查是否收到告警邮件

# 监控告警自动化
# 步骤1：创建自动化监控脚本
# vi /mysql/scripts/monitor_replication_auto.sh
#!/bin/bash
# monitor_replication_auto.sh
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: http://www.fgedu.net.cn

LOG_FILE=”/mysql/logs/replication_auto.log”
ALERT_EMAIL=”admin@example.com”

# 检查复制状态
SLAVE_STATUS=$(mysql -u root -pPassword123! -e “SHOW SLAVE STATUS\G”)

# 提取关键信息
SLAVE_IO_RUNNING=$(echo “$SLAVE_STATUS” | grep “Slave_IO_Running” | awk ‘{print $2}’)
SLAVE_SQL_RUNNING=$(echo “$SLAVE_STATUS” | grep “Slave_SQL_Running” | awk ‘{print $2}’)
SECONDS_BEHIND_MASTER=$(echo “$SLAVE_STATUS” | grep “Seconds_Behind_Master” | awk ‘{print $2}’)
LAST_IO_ERROR=$(echo “$SLAVE_STATUS” | grep “Last_IO_Error” | sed ‘s/Last_IO_Error: //’)
LAST_SQL_ERROR=$(echo “$SLAVE_STATUS” | grep “Last_SQL_Error” | sed ‘s/Last_SQL_Error: //’)

# 记录监控信息
echo “[$(date +%Y-%m-%d%H:%M:%S)] Replication status check” >> $LOG_FILE
echo “Slave_IO_Running: $SLAVE_IO_RUNNING” >> $LOG_FILE
echo “Slave_SQL_Running: $SLAVE_SQL_RUNNING” >> $LOG_FILE
echo “Seconds_Behind_Master: $SECONDS_BEHIND_MASTER” >> $LOG_FILE
echo “Last_IO_Error: $LAST_IO_ERROR” >> $LOG_FILE
echo “Last_SQL_Error: $LAST_SQL_ERROR” >> $LOG_FILE

# 自动处理复制问题
# 处理IO线程停止
if [ “$SLAVE_IO_RUNNING” != “Yes” ]; then
echo “[$(date +%Y-%m-%d%H:%M:%S)] ERROR: Slave IO thread is not running! Attempting to restart…” >> $LOG_FILE
# 尝试重启IO线程
mysql -u root -pPassword123! -e “STOP SLAVE IO_THREAD; START SLAVE IO_THREAD;”
# 检查重启结果
SLAVE_STATUS=$(mysql -u root -pPassword123! -e “SHOW SLAVE STATUS\G”)
SLAVE_IO_RUNNING=$(echo “$SLAVE_STATUS” | grep “Slave_IO_Running” | awk ‘{print $2}’)
if [ “$SLAVE_IO_RUNNING” = “Yes” ]; then
echo “[$(date +%Y-%m-%d%H:%M:%S)] SUCCESS: Slave IO thread restarted successfully!” >> $LOG_FILE
# 发送恢复通知
echo “Slave IO thread restarted successfully!\n\n$SLAVE_STATUS” | mail -s “Replication Notice: Slave IO thread recovered” $ALERT_EMAIL
else
echo “[$(date +%Y-%m-%d%H:%M:%S)] FAILED: Failed to restart Slave IO thread!” >> $LOG_FILE
# 发送告警邮件
echo “Failed to restart Slave IO thread!\n\n$SLAVE_STATUS” | mail -s “Replication Alert: Slave IO thread failed to recover” $ALERT_EMAIL
fi
fi

# 处理SQL线程停止
if [ “$SLAVE_SQL_RUNNING” != “Yes” ]; then
echo “[$(date +%Y-%m-%d%H:%M:%S)] ERROR: Slave SQL thread is not running! Attempting to restart…” >> $LOG_FILE
# 尝试重启SQL线程
mysql -u root -pPassword123! -e “STOP SLAVE SQL_THREAD; START SLAVE SQL_THREAD;”
# 检查重启结果
SLAVE_STATUS=$(mysql -u root -pPassword123! -e “SHOW SLAVE STATUS\G”)
SLAVE_SQL_RUNNING=$(echo “$SLAVE_STATUS” | grep “Slave_SQL_Running” | awk ‘{print $2}’)
if [ “$SLAVE_SQL_RUNNING” = “Yes” ]; then
echo “[$(date +%Y-%m-%d%H:%M:%S)] SUCCESS: Slave SQL thread restarted successfully!” >> $LOG_FILE
# 发送恢复通知
echo “Slave SQL thread restarted successfully!\n\n$SLAVE_STATUS” | mail -s “Replication Notice: Slave SQL thread recovered” $ALERT_EMAIL
else
echo “[$(date +%Y-%m-%d%H:%M:%S)] FAILED: Failed to restart Slave SQL thread!” >> $LOG_FILE
# 发送告警邮件
echo “Failed to restart Slave SQL thread!\n\n$SLAVE_STATUS” | mail -s “Replication Alert: Slave SQL thread failed to recover” $ALERT_EMAIL
fi
fi

# 处理复制延迟
if [ “$SECONDS_BEHIND_MASTER” -gt 300 ]; then
echo “[$(date +%Y-%m-%d%H:%M:%S)] WARNING: Replication delay is $SECONDS_BEHIND_MASTER seconds!” >> $LOG_FILE
# 发送告警邮件
echo “Replication delay is $SECONDS_BEHIND_MASTER seconds!\n\n$SLAVE_STATUS” | mail -s “Replication Alert: High replication delay” $ALERT_EMAIL
fi

# 步骤2：设置执行权限
chmod +x /mysql/scripts/monitor_replication_auto.sh

# 步骤3：添加cron任务
# crontab -e
*/5 * * * * /mysql/scripts/monitor_replication_auto.sh

# 步骤4：验证自动化效果
# 查看监控日志
tail -f /mysql/logs/replication_auto.log

# 模拟复制问题
# 在从库上执行
mysql> STOP SLAVE IO_THREAD;

# 检查是否自动重启IO线程
mysql> SHOW SLAVE STATUS\G | grep Slave_IO_Running

# 检查是否收到通知邮件