MySQL主从复制及监控脚本

欢迎来到8455线路检测中心技术小课堂，每天分享一个技术小知识。

MySQL 主从复制（Replication）是构建高可用、高性能数据库架构的基石。

一、主从复制是什么？

主从复制 是指数据从一个 MySQL 数据库（主库）复制到一个或多个 MySQL 数据库（从库）的过程。

核心价值：

数据备份：从库作为主库的实时备份

读写分离：主库处理写操作，从库处理读操作

负载均衡：将读请求分散到多个从库

高可用：主库故障时，从库可升级为主库

数据分析：在从库执行报表查询，不影响主库性能

二、主从复制原理架构

复制流程概览：

主库 (Master)

    ↓ 二进制日志 (Binlog)

    ↓

从库 I/O 线程 (Slave I/O Thread)

    ↓ 中继日志 (Relay Log)

    ↓

从库 SQL 线程 (Slave SQL Thread)

    ↓

从库数据文件

核心组件详解：

二进制日志 (Binary Log)

记录所有对数据库的数据修改操作

三种格式：STATEMENT、ROW、MIXED

主从复制的数据来源

复制线程

主库 Binlog Dump 线程：读取 Binlog 发送给从库

从库 I/O 线程：连接主库，接收 Binlog 数据，写入中继日志

从库 SQL 线程：读取中继日志，重放 SQL 语句

中继日志 (Relay Log)

从库的临时存储，格式与 Binlog 相同

SQL 线程从中读取并执行

三、主从复制配置实战

环境准备：

主库 IP: 192.168.1.10

从库 IP: 192.168.1.11

复制用户: repl

版本: MySQL 8.0+

步骤 1：主库配置

1.1 修改主库配置文件

/etc/mysql/my.cnf

[mysqld]

 服务器唯一ID

server-id = 1

 开启二进制日志

log_bin = /var/log/mysql/mysql-bin.log

 二进制日志格式（推荐ROW）

binlog_format = ROW

 需要复制的数据库（可选）

 binlog_do_db = mydb

 忽略复制的数据库（可选）

 binlog_ignore_db = mysql

 自动清理过期binlog

expire_logs_days = 7

max_binlog_size = 100M

 从库也需要记录binlog（用于级联复制）

log_slave_updates = 1

1.2 重启主库服务

sudo systemctl restart mysql

1.3 创建复制用户

-- 创建复制专用用户

CREATE USER 'repl'@'192.168.1.%' IDENTIFIED BY 'ReplPassword123!';

-- 授予复制权限

GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.1.%';

-- 刷新权限

FLUSH PRIVILEGES;

1.4 查看主库状态

SHOW MASTER STATUS;

记录输出结果：

++-+--++-+

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

++-+--++-+

| mysql-bin.000001 |      154 |              |                  |                   |

++-+--++-+

1.5 备份主库数据（可选）

使用 mysqldump 备份

mysqldump -u root -p --all-databases --master-data > master_backup.sql

步骤 2：从库配置

2.1 修改从库配置文件

/etc/mysql/my.cnf

[mysqld]

 服务器唯一ID（必须与主库不同）

server-id = 2

 开启中继日志

relay_log = /var/log/mysql/mysql-relay-bin

 从库只读（防止误写）

read_only = 1

 超级用户仍可写（可选）

 super_read_only = 1

 记录binlog（用于级联复制）

log_bin = /var/log/mysql/mysql-bin.log

2.2 重启从库服务

sudo systemctl restart mysql

2.3 导入主库数据（如果做了备份）

mysql -u root -p < master_backup.sql

2.4 配置复制链路

-- 停止从库复制

STOP SLAVE;

-- 配置主库连接信息

CHANGE MASTER TO

MASTER_HOST = '192.168.1.10',

MASTER_USER = 'repl',

MASTER_PASSWORD = 'ReplPassword123!',

MASTER_LOG_FILE = 'mysql-bin.000001',  -- 主库的File

MASTER_LOG_POS = 154;                  -- 主库的Position

-- 启动从库复制

START SLAVE;

步骤 3：验证复制状态

3.1 检查从库复制状态

SHOW SLAVE STATUS\\G

关键指标检查：

-- 查看简洁状态

SELECT

    Slave_IO_State,

    Master_Host,

    Master_Log_File,

    Read_Master_Log_Pos,

    Relay_Log_File,

    Relay_Log_Pos,

    Slave_IO_Running,

    Slave_SQL_Running,

    Seconds_Behind_Master

FROM performance_schema.replication_applier_status_by_worker;

正常状态应该是：

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Seconds_Behind_Master: 0（或很小的数字）

3.2 测试数据同步

-- 在主库创建测试数据

CREATE DATABASE IF NOT EXISTS test_repl;

USE test_repl;

CREATE TABLE test_table (id INT, name VARCHAR(100));

INSERT INTO test_table VALUES (1, 'test data');

-- 在从库检查数据是否同步

SELECT * FROM test_repl.test_table;

四、复制模式详解

基于语句的复制 (SBR)

binlog_format = STATEMENT

优点： 日志量小，节省带宽 缺点： 不确定性函数可能导致主从不一致

基于行的复制 (RBR) - 推荐

binlog_format = ROW

优点： 数据一致性最好，最安全 缺点： 日志量较大

混合模式复制 (MBR)

binlog_format = MIXED

优点： 兼顾性能和一致性 缺点： 仍有小概率不一致

五、高级复制架构

一主多从架构

     主库

    /  |  \\

从库1 从库2 从库3

应用场景： 读写分离、负载均衡

级联复制架构

主库 → 从库1(中继) → 从库2

               ↘ 从库3

优点： 减轻主库压力 配置： 在中继从库设置 log_slave_updates = 1

双主复制架构

主库A ↔ 主库B

注意： 需要处理自增主键冲突，配置自动故障切换

六、监控与维护

关键监控指标

-- 查看复制延迟

SHOW SLAVE STATUS\\G

-- 关注: Seconds_Behind_Master

-- 查看复制线程状态

SHOW PROCESSLIST;

-- 查看二进制日志状态

SHOW BINARY LOGS;

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

自动化监控脚本

!/bin/bash

 check_replication.sh

SLAVE_STATUS=$(mysql -u root -p -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 broken!" | mail -s "MySQL Replication Alert" admin@company.com

fi

if [ "$SECONDS_BEHIND" -gt 60 ]; then

    echo "WARNING: Replication delay $SECONDS_BEHIND seconds" | mail -s "MySQL Replication Delay" admin@company.com

fi

常见问题处理

复制中断修复

-- 查看错误信息

SHOW SLAVE STATUS\\G

-- 跳过指定错误（谨慎使用）

STOP SLAVE;

SET GLOBAL sql_slave_skip_counter = 1;

START SLAVE;

-- 重新配置复制

STOP SLAVE;

RESET SLAVE;

CHANGE MASTER TO ...;

START SLAVE;

主从数据不一致修复

使用 pt-table-checksum 检查一致性

pt-table-checksum --replicate=test.checksums h=192.168.1.10

 使用 pt-table-sync 修复不一致

pt-table-sync --execute h=192.168.1.10 h=192.168.1.11

七、GTID 复制（推荐）

全局事务标识 (GTID) 优势：

自动位置跟踪

故障切换更简单

避免位置错误

GTID 配置：

主从库都配置

gtid_mode = ON

enforce_gtid_consistency = ON

GTID 复制配置：

-- 从库配置（无需指定文件和位置）

CHANGE MASTER TO

MASTER_HOST = '192.168.1.10',

MASTER_USER = 'repl',

MASTER_PASSWORD = 'ReplPassword123!',

MASTER_AUTO_POSITION = 1;

八、最佳实践总结

配置检查清单

✅ 主从 server-id 不同

✅ 网络连通性正常

✅ 复制用户权限正确

✅ 数据初始状态一致

✅ 时区设置相同

安全建议

专用复制用户，最小权限原则

SSL 加密复制连接

定期监控复制状态

从库设置 read_only = 1

性能优化

使用 ROW 格式的二进制日志

适当增大 binlog_cache_size

监控并及时清理二进制日志

考虑使用多线程复制

多线程复制配置

slave_parallel_workers = 4

slave_parallel_type = LOGICAL_CLOCK

通过以上完整的配置和优化，您可以构建稳定、高效的 MySQL 主从复制环境，为业务系统提供可靠的数据保障。

8455线路检测中心官网上拥有完善的技术支持库可供参考，大家可自行查阅，更多技术问题，可以直接咨询。同时，8455线路检测中心整理了运维必备的工具包免费分享给大家使用，需要的朋友可以直接咨询。

更多技术知识，8455线路检测中心期待与你一起探索。