MySQL 主从同步修复完整指南

# 1. SSH 登录主库服务器
ssh root@10.244.1.10

# 2. 执行 mysqldump 备份命令
mysqldump --single-transaction \
          --routines \
          --triggers \
          --master-data=2 \
          --set-gtid-purged=ON \
          --databases mybook \
          -u root -p > /tmp/mybook_backup_$(date +%Y%m%d_%H%M%S).sql

# 输入 MySQL root 密码后等待备份完成...

# 查看备份文件中的 GTID 信息
head -50 /tmp/mybook_backup_*.sql | grep -A5 -B5 "GTID_PURGED"

# 输出示例：
# -- GTID state at the beginning of the backup 
# SET @@GLOBAL.GTID_PURGED='3e11fa47-71ca-11e1-9e33-c80aa9429562:1-12345';

-- 登录主库 MySQL
mysql -u root -p

-- 查看当前主库状态
SHOW MASTER STATUS;
-- 输出示例：
-- +------------------+----------+--------------+------------------+-------------------------------------------+
-- | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                         |
-- +------------------+----------+--------------+------------------+-------------------------------------------+
-- | mysql-bin.000123 |     1234 |              |                  | 3e11fa47-71ca-11e1-9e33-c80aa9429562:1-12345 |
-- +------------------+----------+--------------+------------------+-------------------------------------------+

-- 记录当前 GTID 执行集合
SELECT @@GLOBAL.gtid_executed;

-- 退出 MySQL
exit;

# 方法1：使用 scp 传输备份文件到从库
scp /tmp/mybook_backup_*.sql root@从库IP:/tmp/

# 方法2：如果从库可以直接访问主库文件系统，可以直接复制
# cp /tmp/mybook_backup_*.sql /共享存储路径/

# SSH 登录从库服务器
ssh root@从库IP

-- 登录从库 MySQL
mysql -u root -p

-- 查看当前同步状态
SHOW SLAVE STATUS\G

-- 停止同步进程
STOP SLAVE;

-- 重置从库配置（清除主库连接信息）
RESET SLAVE ALL;

-- 查看 mybook 数据库大小（可选）
SELECT 
    SCHEMA_NAME as '数据库',
    ROUND(SUM(DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024, 2) as 'DB大小(MB)'
FROM information_schema.SCHEMATA s
LEFT JOIN information_schema.TABLES t ON s.SCHEMA_NAME = t.TABLE_SCHEMA
WHERE s.SCHEMA_NAME = 'mybook'
GROUP BY SCHEMA_NAME;

-- 查看当前 GTID 状态
SELECT @@GLOBAL.gtid_executed;
SELECT @@GLOBAL.gtid_purged;

-- 退出 MySQL 准备恢复数据
exit;

# 恢复备份数据到 mybook 数据库
mysql -u root -p mybook < /tmp/mybook_backup_*.sql

# 输入 MySQL root 密码后等待恢复完成...

-- 重新登录 MySQL
mysql -u root -p

-- 检查恢复的数据
USE mybook;
SHOW TABLES;

-- 检查关键表的记录数（替换为实际表名）
SELECT COUNT(*) FROM your_table_name;

-- 查看恢复后的 GTID 状态
SELECT @@GLOBAL.gtid_executed;
SELECT @@GLOBAL.gtid_purged;

-- 查看数据库大小确认恢复完整性
SELECT 
    SCHEMA_NAME as '数据库',
    ROUND(SUM(DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024, 2) as 'DB大小(MB)'
FROM information_schema.SCHEMATA s
LEFT JOIN information_schema.TABLES t ON s.SCHEMA_NAME = t.TABLE_SCHEMA
WHERE s.SCHEMA_NAME = 'mybook'
GROUP BY SCHEMA_NAME;

-- 重置主库信息（清除之前的二进制日志位置信息）
RESET MASTER;

-- 设置 GTID_PURGED 值（使用步骤 1.2 中记录的值）
-- 注意：这里的值必须从备份文件中获取
SET GLOBAL gtid_purged='3e11fa47-71ca-11e1-9e33-c80aa9429562:1-12345';

-- 配置主从连接参数
CHANGE MASTER TO 
    MASTER_HOST='10.244.1.10',
    MASTER_PORT=3306,
    MASTER_USER='repl',
    MASTER_PASSWORD='xxx',
    MASTER_AUTO_POSITION=1;

-- 启动主从同步
START SLAVE;

-- 检查同步状态详细信息
SHOW SLAVE STATUS\G

-- 重点关注以下字段：
-- Slave_IO_Running: Yes        (IO 线程运行状态)
-- Slave_SQL_Running: Yes       (SQL 线程运行状态)
-- Last_IO_Errno: 0             (IO 错误号，0 表示无错误)
-- Last_SQL_Errno: 0            (SQL 错误号，0 表示无错误)
-- Seconds_Behind_Master: 数字   (延迟秒数，应该逐渐减小)
-- Retrieved_Gtid_Set: GTID集合  (已接收的 GTID)
-- Executed_Gtid_Set: GTID集合   (已执行的 GTID)

-- 登录主库 MySQL
mysql -u root -p

-- 使用 mybook 数据库
USE mybook;

-- 创建测试表
CREATE TABLE sync_test (
    id INT AUTO_INCREMENT PRIMARY KEY,
    test_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    test_data VARCHAR(100)
);

-- 插入测试数据
INSERT INTO sync_test (test_data) VALUES ('同步测试数据');

-- 查看插入的数据
SELECT * FROM sync_test;

-- 登录从库 MySQL
mysql -u root -p

-- 使用 mybook 数据库
USE mybook;

-- 等待几秒后查看数据是否同步
SELECT * FROM sync_test;

-- 如果能看到刚才插入的数据，说明同步正常
-- 可以继续插入更多测试数据验证实时同步

-- 登录主库 MySQL
mysql -u root -p

-- 增加二进制日志保留时间（设置为30天）
SET GLOBAL expire_logs_days = 30;

-- 查看当前设置
SHOW VARIABLES LIKE 'expire_logs_days';

-- 退出 MySQL
exit;

# 编辑 MySQL 配置文件
vim /etc/my.cnf

# 在 [mysqld] 段添加：
# expire_logs_days = 30

# 重启 MySQL 服务使配置生效
systemctl restart mysql

# 创建监控脚本目录
mkdir -p /usr/local/bin

# 创建监控脚本
cat > /usr/local/bin/check_slave.sh << 'EOF'
#!/bin/bash

# MySQL 连接参数
MYSQL_USER="root"
MYSQL_PASS="your_password"

# 获取同步状态
RESULT=$(mysql -u${MYSQL_USER} -p${MYSQL_PASS} -e "SHOW SLAVE STATUS\G" 2>/dev/null | grep -E "(Slave_IO_Running|Slave_SQL_Running|Seconds_Behind_Master)")

# 记录日志
echo "$(date '+%Y-%m-%d %H:%M:%S'): $RESULT"

# 检查延迟
LAG=$(mysql -u${MYSQL_USER} -p${MYSQL_PASS} -e "SHOW SLAVE STATUS\G" 2>/dev/null | grep "Seconds_Behind_Master" | awk '{print $2}')

# 如果延迟超过300秒发送告警
if [ "$LAG" != "NULL" ] && [ "$LAG" -gt 300 ] 2>/dev/null; then
    echo "$(date '+%Y-%m-%d %H:%M:%S'): WARNING - MySQL slave lag is $LAG seconds" | logger -t mysql_slave
    # 可以在这里添加邮件或短信告警
fi

# 检查同步线程状态
IO_RUNNING=$(mysql -u${MYSQL_USER} -p${MYSQL_PASS} -e "SHOW SLAVE STATUS\G" 2>/dev/null | grep "Slave_IO_Running" | awk '{print $2}')
SQL_RUNNING=$(mysql -u${MYSQL_USER} -p${MYSQL_PASS} -e "SHOW SLAVE STATUS\G" 2>/dev/null | grep "Slave_SQL_Running" | awk '{print $2}')

if [ "$IO_RUNNING" != "Yes" ] || [ "$SQL_RUNNING" != "Yes" ]; then
    echo "$(date '+%Y-%m-%d %H:%M:%S'): ERROR - Slave threads not running: IO=$IO_RUNNING, SQL=$SQL_RUNNING" | logger -t mysql_slave
fi
EOF

# 设置脚本执行权限
chmod +x /usr/local/bin/check_slave.sh

# 创建日志目录
mkdir -p /var/log/mysql

# 设置定时任务（每5分钟检查一次）
crontab -e
# 添加以下行：
# */5 * * * * /usr/local/bin/check_slave.sh >> /var/log/mysql/slave_check.log 2>&1

# 创建邮件告警脚本（可选）
cat > /usr/local/bin/mysql_alert.sh << 'EOF'
#!/bin/bash

ALERT_EMAIL="admin@example.com"
SUBJECT="MySQL Slave Alert"
MESSAGE="$1"

# 发送邮件告警
echo "$MESSAGE" | mail -s "$SUBJECT" "$ALERT_EMAIL"

# 或者使用企业微信、钉钉等 API 发送告警
# curl -X POST "your_webhook_url" -d "{'text':'$MESSAGE'}"
EOF

chmod +x /usr/local/bin/mysql_alert.sh

-- 执行位置：从库服务器
RESET MASTER;
SET GLOBAL gtid_purged='your_gtid_set';

-- 执行位置：主库服务器
CREATE USER 'repl'@'%' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
FLUSH PRIVILEGES;

# 执行位置：从库服务器
# 1. 测试网络连通性
ping 10.244.1.10

# 2. 测试端口连通性
telnet 10.244.1.10 3306

# 3. 检查防火墙设置
iptables -L | grep 3306