MySQL-主从复制

MySQL 主从复制是一种将主数据库(Master)的数据变更同步到一个或多个从数据库(Slave)的技术，是实现数据高可用性、读写分离和负载均衡的核心方案。本文将全面解析主从复制的原理、配置步骤、优化策略以及常见问题处理。

主从复制原理与架构

基本概念

MySQL 主从复制基于二进制日志(Binary Log)实现，主库记录所有数据变更操作，从库通过读取并重放这些日志实现数据同步。这种架构下，主库负责处理写操作(INSERT/UPDATE/DELETE)，从库主要用于读操作(SELECT)，有效分担主库压力。

核心组件

主从复制涉及两日志和三线程机制：

二进制日志(Binlog)：主库记录所有数据变更的日志文件
中继日志(Relay Log)：从库暂存从主库获取的二进制日志

线程模型：

主库线程：
- Dump 线程：监听 Binlog 变化并发送给从库的 I/O 线程
从库线程：
- I/O 线程：请求主库的 Binlog 并写入 Relay Log
- SQL 线程：读取 Relay Log 并执行其中的 SQL 语句

复制模式

MySQL 支持三种复制模式：

异步复制(默认)：主库执行完事务后立即返回，不等待从库确认
半同步复制：主库至少等待一个从库接收并写入 Relay Log 后才返回
组复制(Group Replication)：基于 Paxos 协议的多主同步复制

主从复制配置步骤

环境准备

确保主从服务器网络互通，防火墙开放 3306 端口
主从服务器时间同步(建议使用 NTP 服务)
从库数据应与主库一致(可通过 mysqldump 全量备份恢复)

主机名	IP 地址	配置	系统
mysql-master	192.168.148.170	4C8G	openEuler 24.03 (LTS)
mysql-slave01	192.168.148.171	4C8G	openEuler 24.03 (LTS)
mysql-slave02	192.168.148.172	4C8G	openEuler 24.03 (LTS)

时间同步

bash

yum -y install ntp
sed -i '/^server/s/^/#/g' /etc/ntp.conf
cat <<END >>/etc/ntp.conf
server 127.127.1.0
fudge 127.127.1.0 stratum 8
END

systemctl start ntpd
systemctl enable ntpd

主库配置

修改配置文件`my.cnf`：

ini

[mysqld]
server-id=1               			# 唯一ID，主库建议设为1
log-bin=master-bin 					# 指定生成二进制文件，可加目录
log-slave-updates 					# 指定开启slave角色的更新
innodb_flush_log_at_trx_commit=2
master_info_repository=table
relay_log_info_repository=TABLE

# binlog-do-db = your_db      # 可选，指定要复制的数据库

重启 MySQL 服务

bash

systemctl restart mysqld

创建同步专用账户

sql

-- 创建同步用户
CREATE USER 'repl'@'%' IDENTIFIED WITH mysql_native_password BY '123456';

-- 设置权限
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
# 修改连接验证规则
ALTER USER 'repl'@'%' IDENTIFIED WITH mysql_native_password BY '123456';

-- 刷新权限
FLUSH PRIVILEGES;

从库配置

修改配置文件`my.cnf`

ini

[mysqld]
server-id = 2               			# 唯一ID，必须与主库不同
log_bin = slave01-bin         			# 建议开启，便于级联复制
relay_log = mysql-relay01-bin 			# 中继日志文件前缀
read_only = ON              			# 从库设为只读(超级用户仍可写)

# 复制线程配置
slave_parallel_workers = 4  			# 并行复制线程数(5.6+)
slave_parallel_type = LOGICAL_CLOCK  	# 基于事务的并行复制(5.7+)
slave_preserve_commit_order = 1  		# 保持事务提交顺序(并行复制时)
# 复制可靠性配置
relay_log_info_repository = TABLE  		# 中继日志信息存表(mysql.slave_relay_log_info)
master_info_repository = TABLE     		# 主库信息存表(mysql.slave_master_info)
sync_relay_log = 1                 		# 每次事务提交都同步中继日志
sync_relay_log_info = 10000        		# 每10000次事务同步一次复制元数据
# 复制过滤配置
# replicate-do-db = db1       			# 仅复制指定数据库
# replicate-ignore-db = db2   			# 忽略指定数据库
# replicate-wild-do-table = db3.%  		# 使用通配符指定表
# replicate-wild-ignore-table = mysql.%  	# 忽略系统表

# GTID 复制配置(5.6+)
# gtid_mode = ON              			# 启用GTID
# enforce_gtid_consistency = ON  			# 强制GTID一致性
# binlog_format = ROW         			# 建议使用ROW格式
# log_slave_updates = ON      			# 级联复制时需要

重启 MySQL 服务

bash

systemctl restart mysqld

配置复制链路

sql

CHANGE MASTER TO
MASTER_HOST='192.168.148.171',
MASTER_USER='repl',
MASTER_PASSWORD='123456',
MASTER_LOG_FILE='master-bin.000001',
MASTER_LOG_POS=157;

启动复制并检查状态

sql

START SLAVE;
SHOW SLAVE STATUS\G;

确认Slave_IO_Running和Slave_SQL_Running均为Yes

测试同步

在 master 主库创建测试数据

sql

-- 创建库
CREATE DATABASE userdb charset utf8mb4;

-- 用户表
CREATE TABLE user (
  id INT AUTO_INCREMENT PRIMARY KEY COMMENT '主键',
  name VARCHAR(50) NOT NULL COMMENT '姓名',
  age INT COMMENT '年龄',
  email VARCHAR(100) COMMENT '邮箱',
  bid int UNSIGNED  not null COMMENT '部门ID',
  xinzi int UNSIGNED  not null COMMENT '薪资',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间'
) CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ENGINE=InnoDB;

-- 部门表
CREATE TABLE bumen (
  id INT AUTO_INCREMENT PRIMARY KEY COMMENT '主键',
  bname VARCHAR(50) NOT NULL COMMENT '部门名',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间'
) CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ENGINE=InnoDB;

-- 先插入部门数据（假设有5个部门）
INSERT INTO bumen (bname) VALUES
('研发部'), ('市场部'), ('财务部'), ('人力资源部'), ('行政部');

-- 插入50条用户数据，包含中文姓名和合理的数据分布
INSERT INTO user (name, age, email, bid, xinzi, created_at) VALUES
('张三', 28, 'zhangsan@example.com', 1, 15000, '2022-01-10 09:00:00'),
('李四', 35, 'lisi@example.com', 2, 18000, '2022-01-15 10:30:00'),
('王五', 42, 'wangwu@example.com', 3, 22000, '2022-02-05 14:15:00'),
('赵六', 25, 'zhaoliu@example.com', 1, 12000, '2022-02-18 11:20:00'),
('钱七', 31, 'qianqi@example.com', 4, 16000, '2022-03-02 08:45:00'),
('孙八', 29, 'sunba@example.com', 5, 14000, '2022-03-12 13:10:00'),
('周九', 38, 'zhoujiu@example.com', 2, 19000, '2022-04-07 16:30:00'),
('吴十', 27, 'wushi@example.com', 1, 13000, '2022-04-22 10:00:00'),
('郑十一', 33, 'zhengshiyi@example.com', 3, 21000, '2022-05-09 09:15:00'),
('王十二', 45, 'wangshier@example.com', 4, 24000, '2022-05-20 14:40:00'),
('李十三', 26, 'lishisan@example.com', 5, 12500, '2022-06-03 11:05:00'),
('赵十四', 32, 'zhaoshisi@example.com', 1, 15500, '2022-06-15 08:30:00'),
('钱十五', 39, 'qianshiwu@example.com', 2, 18500, '2022-07-01 13:55:00'),
('孙十六', 28, 'sunshiliu@example.com', 3, 13500, '2022-07-14 10:20:00'),
('周十七', 36, 'zhoushiqi@example.com', 4, 16500, '2022-08-05 15:45:00'),
('吴十八', 29, 'wushiba@example.com', 5, 14500, '2022-08-19 12:10:00'),
('郑十九', 41, 'zhengshijiu@example.com', 1, 22500, '2022-09-02 09:35:00'),
('王二十', 30, 'wangershi@example.com', 2, 17500, '2022-09-16 14:00:00'),
('李二十一', 27, 'lieryishi@example.com', 3, 12800, '2022-10-04 11:25:00'),
('赵二十二', 34, 'zhaoershi@example.com', 4, 16800, '2022-10-18 16:50:00'),
('钱二十三', 43, 'qianershi@example.com', 5, 23000, '2022-11-05 13:15:00'),
('孙二十四', 31, 'sunershi@example.com', 1, 15800, '2022-11-20 10:40:00'),
('周二十五', 37, 'zhouershi@example.com', 2, 18800, '2022-12-03 08:05:00'),
('吴二十六', 24, 'wurershi@example.com', 3, 11800, '2022-12-15 15:30:00'),
('郑二十七', 40, 'zhengqishi@example.com', 4, 20500, '2023-01-04 12:55:00'),
('王二十八', 28, 'wangbashi@example.com', 5, 14200, '2023-01-18 09:20:00'),
('李二十九', 35, 'lijiushi@example.com', 1, 18200, '2023-02-02 14:45:00'),
('赵三十', 44, 'zhaosanshi@example.com', 2, 23500, '2023-02-16 11:10:00'),
('钱三十一', 26, 'qiansanyi@example.com', 3, 12400, '2023-03-03 16:35:00'),
('孙三十二', 32, 'sansaner@example.com', 4, 16400, '2023-03-17 13:00:00'),
('周三十三', 38, 'zhousansan@example.com', 5, 19400, '2023-04-01 10:25:00'),
('吴三十四', 29, 'wusansi@example.com', 1, 13800, '2023-04-15 15:50:00'),
('郑三十五', 46, 'zhengwu@example.com', 2, 24500, '2023-05-02 12:15:00'),
('王三十六', 30, 'wangliu@example.com', 3, 17200, '2023-05-16 09:40:00'),
('李三十七', 27, 'liqi@example.com', 4, 12700, '2023-06-01 14:05:00'),
('赵三十八', 33, 'zhaoba@example.com', 5, 16700, '2023-06-15 11:30:00'),
('钱三十九', 42, 'qianjiu@example.com', 1, 21800, '2023-07-03 16:55:00'),
('孙四十', 31, 'sunshi@example.com', 2, 17800, '2023-07-17 13:20:00'),
('周四十一', 36, 'zhousiyi@example.com', 3, 19800, '2023-08-01 10:45:00'),
('吴四十二', 25, 'wushi@example.com', 4, 12200, '2023-08-15 16:10:00'),
('郑四十三', 39, 'zhengshi@example.com', 5, 20200, '2023-09-02 12:35:00'),
('王四十四', 28, 'wangsi@example.com', 1, 14800, '2023-09-16 09:00:00'),
('李四十五', 34, 'liwushi@example.com', 2, 18400, '2023-10-03 14:25:00'),
('赵四十六', 47, 'zhaoliu@example.com', 3, 25000, '2023-10-17 11:50:00'),
('钱四十七', 26, 'qianqi@example.com', 4, 12600, '2023-11-01 17:15:00'),
('孙四十八', 32, 'sunba@example.com', 5, 16600, '2023-11-15 13:40:00'),
('周四十九', 37, 'zhoujiu@example.com', 1, 19600, '2023-12-01 10:05:00'),
('吴五十', 30, 'wushi@example.com', 2, 17600, '2023-12-15 15:30:00');

从库查询同步

sql

show databases;
use userdb;
show tables;

MySQL-主从复制 ​

主从复制原理与架构 ​

基本概念 ​

核心组件 ​

复制模式 ​

主从复制配置步骤 ​

环境准备 ​

时间同步 ​

主库配置 ​

修改配置文件my.cnf： ​

重启 MySQL 服务 ​

创建同步专用账户 ​

从库配置 ​

修改配置文件my.cnf ​

重启 MySQL 服务 ​

配置复制链路 ​

启动复制并检查状态 ​

测试同步 ​

在 master 主库创建测试数据 ​

从库查询同步 ​

MySQL-主从复制

主从复制原理与架构

基本概念

核心组件

复制模式

主从复制配置步骤

环境准备

时间同步

主库配置

修改配置文件`my.cnf`：

重启 MySQL 服务

创建同步专用账户

从库配置

修改配置文件`my.cnf`

重启 MySQL 服务

配置复制链路

启动复制并检查状态

测试同步

在 master 主库创建测试数据

从库查询同步