mysql主从复制

一. 主从复制搭建

1.　修改主库配置：/etc/my.cnf或者/etc/mysql/mysql.conf.d/mysqld.cnf文件下添加或修改

[mysqld]
server-id = 1            # 主库的唯一标识，在复制环境中必须唯一
log-bin = mysql-bin      # 开启并设置二进制日志的文件名
binlog-do-db = your_db   # (可选) 指定要同步的数据库，不设置则同步所有

2.　重启主库mysql服务

3.　登录主库mysql 创建复制专用账户并授权：

 

-- 创建用户，请将 'slave_ip' 替换为你的从库IP，'password' 替换为强密码
CREATE USER 'repl_user'@'slave_ip' IDENTIFIED BY 'password';
-- 授予复制权限
GRANT REPLICATION SLAVE ON *.* TO 'repl_user'@'slave_ip';
-- 刷新权限
FLUSH PRIVILEGES;

 

4.　在主库上锁定数据状态并记录二进制日志文件名和位置

USE your_db;
FLUSH
TABLES WITH READ LOCK;  
-- 锁表，保证数据一致性
SHOW MASTER STATUS;
--执行完show之后不要退出mysql客户端，保持锁表状态

5.　修改从库配置，路径与主库一致

[mysqld]
server-id = 2            # 从库的唯一标识，不能与主库相同
relay-log = relay-bin    # 开启中继日志
read_only = 1            # (强烈建议) 将从库设置为只读，防止应用误写入

6.　重启从库mysql服务：sudo systemctl restart mysql

7.　如果主库是新表则跳过，如果主库有数据，在执行同步前 开一个新的终端将主库的数据导出并导入到从库中，保证初始数据一致

8.　初始数据保持一致后回到主库锁表的终端，解锁表：UNLOCK TABLES；

9.　开启主从同步，告诉从库如何连接主库并从那个位置开始同步；

-- 停止已有的复制线程（如果有）
STOP SLAVE;
-- 配置主库连接信息和同步起点
CHANGE MASTER TOMASTER_HOST = 'master_ip',        
-- 主库IP地址
MASTER_USER = 'repl_user',        
-- 之前创建的复制用户名
MASTER_PASSWORD = 'password',     
-- 复制用户的密码
MASTER_LOG_FILE = 'mysql-bin.000001', 
-- 替换为主库SHOW MASTER STATUS得到的File
MASTER_LOG_POS = 107;             -
- 替换为主库SHOW MASTER STATUS得到的Position-- 启动复制线程
START SLAVE;

10.　验证常用管理命令

　　1) 验证同步状态：SHOW SLAVE STATUS;

　　　　a.重点关注输出的

　　　　　　a) SLAVE_IQ_Running:yes //负责从主库拉取日志；

　　　　　　b) SLAVE_SQL_Running:yes//负责执行拉去回来的日志

　　　　b. 在主库上创建一个测试数据库和测试数据表并插入测试数据，检查从库是否同步

11.　常用管理命令：

  • l 停止从库复制：STOP SLAVE;
  • l 启动从库复制：START SLAVE;
  • l 查看从库状态：SHOW SLAVE STATUS
  • l 查看主库状态：SHOW MASTER STATUS
  • 总结：按“主库配置 -> 从库配置 -> 启动同步 -> 验证”的流程。
  • 主库：开启binlog，设置唯一的server-id，创建复制用户replication slave，执行SHOW MASTER STATUS记录File和Position。
  • 从库：设置唯一的server-id，开启只读（可选），用CHANGE MASTER TO指定主库信息和同步位点，最后执行START SLAVE。
  • 关键：如果主库已有数据，需先用mysqldump或备份文件初始化从库，并确保备份时的位点信息正确。
  • 验证：SHOW SLAVE STATUS\G 中 Slave_IO_Running 和 Slave_SQL_Running 均为Yes

二. 搭建过程注意点：

1) 数据一致性：对于已有数据的库，在建立同步前确保主从数据完全一致，否则会同步失败

2) 防火墙：确保两台服务器的防火墙都允许对方访问MYSQL端口

3) 同步延迟：可以通过SHOW SLAVE STATUS中的seconds_behind_master字段查看从库落后主库的秒数。如果延迟严重，考虑优化网络或者从库硬件

4) 只读模式：将从库的read_only参数设置为1，防止应用程序错误的在从库进行写操作，导致主从数据不一致

 

三. 怎么保证主从复制数据一致：

无法彻底消除延迟，只是从"复制机制"和"同步效率"两方面入手：

在源头使用半同步复制防止数据丢失，在过程中使用并行复制等方法减少延迟堆积

方案一：开启半同步复制--解决数据丢失问题（确保主库在事务提交时，必须等待至少一个从库确认已收到binlog日志，才会给客户端返回成功，这样杜绝了主库宕机导致数据丢失问题）

  • 在主库安装并启用插件：
INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
SET GLOBAL rpl_semi_sync_master_enabled = 1;
  • 在每个从库安装并启用插件：

 

INSTALL PLUGIN rpl_sync_slave SONAME 'semisync_slave.so';
SET GLOBAL rpl_semi_sync_slave_enabled = 1;

 

  • 重启从库的IO线程：
STOP SLAVE IO_THREAD; START SLAVE IO_THREAD;
  • 验证是否成功：在主库执行SHOW STATUS LIKE 'Rpl_semi_sync_master_status'。如果返回on则代表成功了

方案二：提速方案（优化同步效率）---解决同步效率

半同步复制解决数据丢失问题，但是大数据量下从库执行速度慢导致延迟依然存在

  1. 开启并行复制：这是解决大数据延迟的最有效的手段。mysql5.7及以上版本支持基于逻辑时钟（LOGICAL_CLOCK）的并行复制，让从库的SQL线程能同时播放多个来自主库的没有冲突的事务，效率提升巨明显
  2. 关键参数优化：成本最低的优化
  • 主库：innodb_flush_log_at_trx_commit=1和sync_binlog=1。这两个双"1"配置虽然由损耗，但能保证每次事务提交都可以安全罗盘，是数据一致性的基础

 

  • 从库：设置master_info_repository="TABLE";relay_log_info_repoistory="TABLE";将复制进度信息缓存到表中，比文件可靠

　　3. 业务层面优化

  • 将大事务拆小，比如大的DELETE拆分成小的分批执行，避免从库长时间的卡在一个SQL上
  • 确保主从库的硬件配置接近，避免从库性能太差造成拖后腿

方案三：业务层特殊处理（兜底）

在强一致性的场景中，可以在代码中特殊处理：

强制读主库：在执行完逻辑后，强制指定连接和查询主库

引入GTID：在配置主从的时候启用GTID(全局事务ID)，它能为每个事务提供全局的唯一编号，当主从切换或者复制中断时，GTID可以让你极为方便的定位和恢复数据，避免找日志位点的麻烦

注：在大数据量的场景下保证一致性的黄金组合：以半同步复制为基石，并”并行复制“为核心加速，以”业务层强制读主库“为特殊处理

 

四.主从复制的核心原理：

 核心基于三个线程和三个日志完成的：

主库：当数据有变更的时，会将操作记录二进制日志中，一个dump线程负责与从库通信并读取发送这个日志

从库：启用I/O线程连接主库，请求并接受日志，写入本地的中继日志。然后SQL线程读取中继日志并执行，实现数据重放

整个过程是异步的，可以通过配置变为半同步复制

 

四.异步复制/半同步复制/全同步复制的区别：

 异步复制：主库提交事务后立即返回给客户端，不关心从库是否收到，性能最好，但是主库宕机会丢失数据。

半同步复制：主库提交事务后等待至少一个从库确认收到binlog后才会返回，平衡了一致性和性能问题
全同步复制：主库提交事务后等待所有从库都执行完之后才会返回，一致性最强但是延迟极高

五.配置的server-id作用和特点：

特点：必须是唯一的数字表示

作用：

  • 标识身份：主库用它识别从库。从库用它避免无线循环。
  • 过滤复制：某些复制过滤规则比较依赖server-id，如果ID重复会导致数据混乱或者报错

 

六.如何在线更改主库到新的主库（主库切换）？

前提:确保从库已与从库数据一致

  1. 在从库执行：STOP SLAVE 
  2. 执行:CHANGE MASTER TO 新的主库IP,端口，用户，密码，以及新主库的二进制文件名(MASTER_LOG_FILE)和文件位置(MASGER_LOG_POS)
  3. 执行START SLAVE 
  4. 验证：SHOW SLAVE STATUS

七. 主从延迟的问题有哪些，怎么排查？

从库硬件差：CPU,内存，磁盘IO能力不足。解决方案：升级从库硬件或者使用SSD

大事务：主库执行了大表的DDL或者DML，从库重放慢。解决方案：拆分大事务为小事务，分批次执行，避免大批量操作

从库上跑查询：从库承担报表或者复杂的查询，锁与复制冲突。解决方案：将从库设置为read_only，将复杂查询迁移到其他从库或者分析库

网络延迟：主从间网络不稳定或宽带小。解决方案：检查网络，必要时部署在同机房

单线程复制：mysql5.6之前默认单线程SQL线程，性能平静。解决方案：升级为mysql5.7+，开启并行复制，设置slave_parallel_workers>0和slave_parallell_type=LOGICAL_CLOCK

 

八.主从复制有那些模式？如何实现读写分离？痛点是什么，怎么优化？：

复制模式：

  • 一主一从：基础高可用，手动或者工具切换
  • 一主多从：分担读压力，适合读多写少场景
  • 级联复制：A->B->C，减轻主库Dump线程压力
  • 双主复制：互为主从，注意避免写冲突，通常配合keepalived实现

读写分离：

  • 代码层：在应用内配置多个数据源，通过AOP或注解动态选择
  • 中间件：使用proxysql，maxscale,等对应用透明，自动分发读写请求

痛点：主存同步存在毫秒/秒级延迟，写入后立马查从库会出现数据不一致的情况。

规避方案：①实时性高的场景，强制走主库查询。

②从库开启并行复制加速回放。

③配合MHA做主动主从切换。

④非实时性的查询，比如统计，走从库查询；

⑤热点数据、营销数据缓存至redis，减少数据库查询。

⑥优化数据库配置，减少大事务，降低同步延迟。

⑦核心业务采用半同步复制，保证数据同步可靠性。

 

 九.Binlog记录模式：

  • row 模式：基于行的复制，最安全（记录每行变更）；
  • statement ：基于语句的复制，模式日志量小但可能有主从数据不一致；
  • mixed：混合模式复制， 是自动切换；
因为电商场景对数据一致性要求极高，row 模式虽然日志量稍大，但能保证主从数据完全一致，不会出现 statement 模式因函数导致的不一致问题

 

STATEMENT 模式：

优点：日志文件非常小，节省磁盘和网络带宽，在批量操作时尤其明显。

致命缺点：不安全。如果 SQL 中包含 NOW()、RAND()、CURRENT_USER() 等非确定性函数，或者使用了存储过程、触发器，主库执行时的计算结果和从库重放时计算的结果可能完全不同，导致主从数据最终不一致。

现状：MySQL 8.0 版本中，STATEMENT 模式已被官方废弃，不建议在生产环境使用

MIXED 模式（混合模式）

这是为了解决 STATEMENT 安全问题而诞生的“折中方案”，由 MySQL 自己来当裁判。

工作机制：MySQL 会先判断这条 SQL 是否“安全”。

如果是普通的 INSERT、UPDATE 等确定性的 DML，就用 STATEMENT（省空间）。

如果 SQL 里带了 NOW()、UUID()，或者涉及 BLOB、TEXT 大字段更新，就自动切换成 ROW（保安全）。

优点：兼顾了 STATEMENT 的日志小和 ROW 的安全性，不需要你手动纠结。

缺点：不可控。你无法预判它什么时候切 ROW。而且，在 MIXED 模式下，如果某条 SQL 被记录为 STATEMENT，你就无法做“数据闪回”；被记录为 ROW 的部分才能闪回，操作日志解析起来很混乱

Row模式：

优点：

l 安全性层面（决定性优势）：ROW 模式通过记录“行级数据快照”，从根本上杜绝了 NOW()、UUID() 等非确定性函数导致的主从数据不一致问题。这是 STATEMENT 模式无法逾越的致命缺陷。

l 运维层面：因为记录了完整的数据“前像”和“后像”，ROW 模式天然支持 “闪回”（通过 binlog2sql 等工具）。在误删数据、误更新数据的紧急故障中，这是救命稻草。MIXED 模式因为日志格式不纯，无法做到全量闪回。

l 架构层面：现代 MySQL 高级功能，如 MGR（组复制）、GTID（全局事务 ID） 等，都强制要求使用 ROW 模式。如果不开启 ROW，你就无法搭建这些高可用架构。

缺点：日志量可能膨胀

对比维度

STATEMENT 模式 (SBR)

ROW 模式 (RBR)

MIXED 模式 (MBR)

记录内容

记录执行的 SQL 语句本身。

记录每一行数据的具体变化（前像+后像）。

MySQL 自动判断，默认用 STATEMENT，不安全时自动切 ROW。

日志大小

极小（一条 DELETE 删 10万行，只记一句话）。

极大（10万行变化就记10万条变更）。

动态变化，通常介于两者之间。

主从一致性

不安全（NOW()、UUID() 等会导致主从数据不一致）。

最安全（直接复制最终结果值，彻底杜绝函数歧义）。

安全（遇到不安全函数会自动切换成 ROW）。

数据闪回

不支持（无法根据 SQL 反向生成回滚语句）。

支持（可用 binlog2sql 等工具做误删恢复）。

不一定（切换成 ROW 的部分支持，STATEMENT 部分不支持）。

执行效率

主库快（只记录逻辑），从库慢（需重放复杂 SQL 并重新计算）。

主库略慢（日志写入多），从库极快（直接应用行变更，无需计算）。

动态平衡。
posted @ 2026-04-02 15:46  阿陌i  阅读(23)  评论(0)    收藏  举报