mysql如何保证主从一致性

来源：这里教程网时间：2026-03-01 18:30:46 作者：

MySQL主从架构故障切换，如何保证数据一致性？或者说如何最大化的保证数据不丢失？可以通过那些配置来提升一致性？dba都知道我们需要设置增强半同步(after_sync)来保证数据一致性，那只设置半同步就可以了吗？

一、先看下事务执行流程（AFTER_SYNC半同步模式）

1、客户端发起事务：客户端执行 SQL 语句并提交事务，注意只是进入两阶段的提交阶段，但是并没有提交成功，但是binlog已经写完毕了(具体binlog写到哪里取决于sync_binlog的设置)，只不过最后没有给redo打commit标签； 2、写入 redo log 和 binlog：主库写入 redo log（重做日志）并根据 innodb_flush_log_at_trx_commit 的设置刷新到磁盘，或者刷新到os cache，或者只是在redo log buffer；主库将事务写入 binlog（二进制日志），并根据 sync_binlog 的设置刷新到磁盘或者刷新到os cache； 3、主库发送 binlog 到从库并等待 ACK(提交之前)：主库将 binlog 发送到从库，并等待从库确认收到 binlog（ACK），注意只是确认收到，并非应用完毕！从库接收到 binlog 后，将其写入 relay log（中继日志），然后发送 ACK 给主库； 4、提交事务到存储引擎：主库在收到从库的 ACK 后，将事务提交到存储引擎，返回成功状态给客户端事务提交完成后，主库返回成功状态给客户端备注：何谓提交到存储引擎：

InnoDB 将 redo log 的状态从 "PREPARED" 更新为 "COMMITTED"。

InnoDB 释放事务占用的资源，包括锁和内存以及undo块。

事务提交完成，所有修改被持久生效，

二、具体配置
>>主库和从库设置双1
1.主库设置双1：set global  innodb_flush_log_at_trx_commit=1 set global sync_binlog=1

备注：
1.1、innodb_flush_log_at_trx_commit参数：
MySQL 写redo的过程：先写redo log buffer 中，然后write到文件系统的page cache，最后持久化fsync到磁盘；innodb_flush_log_at_trx_commit参数用来控制，每次提交redo的写入策略：设置为 0 的时候，表示每次事务提交时都只是把 redo log 留在 redo log buffer 中 ，然后每秒write以及fsync设置为 1 的时候，表示每次事务提交时都将 redo log 直接持久化到磁盘；设置为 2 的时候，表示每次事务提交时都只是把 redo log 写到 page cache，定期再去fsyncInnoDB 有一个后台线程，每隔 1 秒，就会把 redo log buffer 中的日志，调用 write 写到文件系统的 page cache，然后调用 fsync 持久化到磁盘。


1.2、sync_binlog参数：
write：指的就是指把日志写入到文件系统的 page cache，并没有把数据持久化到磁盘，所以速度比较快。fsync：才是将数据持久化到磁盘的操作。一般情况下，我们认为 fsync 才占磁盘的 IOPSsync_binlog=0 的时候，表示每次提交事务都只 write，不 fsync；sync_binlog=1 的时候，表示每次提交事务都会执行 fsync，
但并不是每次提交必须等fsync成功才会提交成功
；sync_binlog=N(N>1) 的时候，表示每次提交事务都 write，但累积 N 个事务后才 fsync。因此，在出现 IO 瓶颈的场景里，将 sync_binlog 设置成一个比较大的值，可以提升性能。在实际的业务场景中，考虑到丢失日志量的可控性，一般不建议将这个参数设成 0，比较常见的是将其设置为 100~1000 中的某个数值。但是，将 sync_binlog 设置为 N，对应的风险是：如果主机发生异常重启，会丢失最近 N 个事务的 binlog 日志。

2.从库设置双1以及一些安全恢复参数：set global  innodb_flush_log_at_trx_commit=1 set global sync_binlog=1 set global relay_log_recovery=on set global relay_log_purge=on set global sync_relay_log=1
2.1、relay_log_recovery参数：如果启用，此变量将在服务器启动时立即启用自动中继日志恢复。恢复过程会创建一个新的中继日志文件，将 SQL 线程的位置初始化为这个新的中继日志，并将 I/O 线程初始化为 SQL 线程的位置。然后从中继日志中继续读取数据。假设当从库意外宕机后，同时从库的relay log也一起损坏了，而主库的日志已经传到了从库，只是从库还没有来得及应用这些日志，那么从库该如何处理？在从库中将relay_log_recovery设置为on，假如果碰到上面的情形，
从库会自动放弃所有未执行的relay log，重新生成一个relay log，并将从库的io线程的position重新指向新的relay log，然后从新拉取binlog!（
其实就是从crash时应用的binlog点位开始从新再次拉取binlog，然后sql线程从这个点位继续应用），重新开始同步，这样在从库中事务不会丢失。这个参数建议开启。


2.2、relay_log_purge参数：
relay_log_recovery和relay_log_purge 变量相互作用，
relay_log_purge控制在relay日志不再需要时的清理操作。启用 relay_log_recovery 时禁用 relay_log_purge，可能会导致从中继日志文件中读取未被清理的日志，从而导致数据不一致。建议开启。


2.3、sync_relay_log参数：
在 MySQL 中，如果这个变量的值大于 0，MySQL 服务器会在每写入 sync_relay_log 个事件到中继日志后(写入到OS cache)，将中继日志同步到磁盘（使用 fdatasync()）。设置这个变量会立即对所有复制通道生效，包括正在运行的通道。将 sync_relay_log 设置为 0 会导致不对磁盘进行同步操作；在这种情况下，服务器依赖于操作系统来像处理其他文件一样，不时地将中继日志的内容从缓存刷新到磁盘。设置为 1 是最安全的选择，因为如果发生意外停机，
你最多只会丢失中继日志中的一个事件。然而，这也是最慢的选择（除非磁盘有电池备份的缓存，这会使同步操作非常快）。备注：电池支持的缓存（Battery-Backed Cache，简称BBC）是一种在存储设备中使用的高速缓存技术，它可以在电源故障时保护缓存中的数据，从而避免数据丢失。电池支持的缓存可以显著提高数据同步的速度，因为它减少了需要写入磁盘的次数。这种缓存通常位于存储控制器中，能够快速地处理数据写入和读取操作。

>>增强半同步：
1、何为增强半同步:客户端执行一个事务，master执行这个事务，在提交之前把事务binlog发送给slave，slave收到事务binlog之后，给master返回ACK，master再提交事务(只需要redo写commit标签即可)。在slave返回信息的时间里（以rpl_semi_sync_master_timeout参数为准，默认为10秒，超时后master就退化成异步复制），此时一旦master发生宕机，由于事务没有发送给slave，并且master也没有提交数据，主从数据都没有更改，所以不会出现数据不一致。
2、增强半同步的注意事项：5.7增强半同步中对于主库而言，虽然没有提交但是binlog中却已经有了该事务的binlog! 所以这个时候如果你使用gh-ost修改表结构，就可能出现数据不一致的问题！如果master发生故障，则master上提交的所有事务都已复制到slave。由于slave是最新的，因此master发生了failover后不会丢数据。但是，请注意，在这种情况下，启动原来主库数据库就发生实例恢复，由于二进制日志可能包含未提交的完整的事务binlog，这样原来的主库就会提交这个原来没提交成功的事务，此时原来的主库就可能多一些事务！


3、具体参数配置：


3.1、主库设置如下参数
SET GLOBAL rpl_semi_sync_master_enabled = 1;   #开启master角色的增强半同步 SET GLOBAL rpl_semi_sync_master_wait_point = 'AFTER_SYNC';   #设置半同步为after_sync的方式 set global rpl_semi_sync_master_wait_for_slave_count=2;   #主库事务提交后需要从库的确认数量,越多则越安全 set global rpl_semi_sync_master_timeout=3600;  #设置足够大(足够dba去发现和处理问题)，避免从库长时间未反馈ACK，主库超时退化成异步复制，存在数据丢失的风险 set global rpl_semi_sync_master_wait_no_slave=on;   #从库数量下降到少于rpl_semi_sync_master_wait_for_slave_count的个数，主库也要等待超时过期


3.2、从库设置如下参数：
set global  rpl_semi_sync_slave_enabled=1  #开启slave角色的增强半同步

三、总结：虽然你设置了增强半同步，并且主库也收到ack了，并且你设置从库sync_relay_log=1了，那也可能会丢一个事务，假设主库crash了，切换到从库，从库立马就crash了，从库是整个服务器crash了，这个时候就有可能由于relay log还没有来得及刷盘，导致relay log丢失，进而丢失事务。因为主库收到从库的ack的时候，从库接到的relay  log可能才只写到了 os cache中，尽管sync_relay_log=1，但是从库IO线程接收到主库binlog，并写入到relay log(OS  cache), 然后就可以反馈给主库ACK。此时binlog还在os cache中，一旦服务器死掉，那么从库就会丢失一个事务，进而导致集群丢失一个事务(前提是主库无法启动了)。所以主从同步的架构中无法保证绝对的一致性。


                        编辑推荐：
                          mysql如何保证主从一致性03-01
  YashanDB：统计信息未触发导致 SQL 性能下降03-01
  第39期 MySQL给邮箱，身份证类似的字段添加索引的方法03-01
  数据库管理-第329期 MySQL 30周年生日快乐（20250525）03-01
  第25期 MySQL部分复制03-01
  第42期 MySQL存储过程报错noAccessToProcedureBodies03-01
  百亿大表的实时分析：华安基金 HTAP 数据库的选型历程与 TiDB 使用体验03-01
  主从从库MTS HANG死一列03-01

                    
下一篇：
返回列表

             
                        