我要评论mysql是怎么保证数据不丢的？
一、问题背景
业务高峰期临时提升性能的方法，往往与数据的可靠性有关。
前置结论：只要 redo log 和 binlog 保证持久化到磁盘，就能确保 mysql 异常重启后数据可以恢复。
本文聚焦一个问题：binlog 和 redo log 是怎么写入磁盘的？如何保证真实落盘？
二、binlog 的写入机制
写入逻辑
- 事务执行过程中，先把日志写到 binlog cache；
- 事务提交时，把 binlog cache 的完整事务一次性写入 binlog 文件，并清空 cache。
一个事务的 binlog 不能被拆开，不论事务多大，都要一次性写入。每个线程独占一份 binlog cache，大小由 binlog_cache_size 控制，超出则暂存磁盘。
上图要点：每个线程有自己的 binlog cache，但共用同一份 binlog 文件。
write 与 fsync 的区别
| 操作 | 含义 | 性能 |
|---|---|---|
| write | 写入文件系统 page cache，未持久化 | 快 |
| fsync | 数据持久化到磁盘 | 慢，占 iops |
由参数 sync_binlog 控制时机：
| sync_binlog 取值 | 行为 |
|---|---|
| 0 | 每次提交只 write，不 fsync |
| 1 | 每次提交都 fsync（双1之一） |
| n（>1） | 累积 n 个事务后 fsync |
取舍：io 瓶颈场景下调大可提升性能，但主机异常重启会丢失最近 n 个事务的 binlog，常见取值 100~1000。
三、redo log 的写入机制
事务执行中生成的 redo log 先写到 redo log buffer，无需每次生成后立即落盘（事务未提交，丢了也无损失）。
redo log 的三种状态
- 红色：在 redo log buffer 中（mysql 进程内存）；
- 黄色：write 到 page cache，未 fsync；
- 绿色：持久化到磁盘。
由参数 innodb_flush_log_at_trx_commit 控制：
| 取值 | 行为 |
|---|---|
| 0 | 提交时只留在 redo log buffer |
| 1 | 提交时直接持久化到磁盘（双1之一） |
| 2 | 提交时 write 到 page cache |
未提交事务的 redo log 何时会被落盘？
除了后台线程每秒一次轮询刷盘外，还有两种场景：
- buffer 占用达到一半：后台线程主动 write 到 page cache（只 write，不 fsync）；
- 并行事务提交顺带刷盘：事务 b 提交时（
=1），会把 redo log buffer 中事务 a 的日志一起持久化。
两阶段提交时序：redo log prepare → 写 binlog → redo log commit。=1 时，prepare 阶段就要 fsync（崩溃恢复依赖 prepare redo log + binlog）。commit 阶段只需 write 到 page cache 即可。
四、组提交：tps 为何能远超磁盘 iops
疑问
"双1"配置下，一个事务提交前要两次刷盘（redo log prepare + binlog）。tps 2万岂不是要写4万次磁盘？但磁盘能力只有2万左右，怎么实现？
lsn 与组提交
lsn（log sequence number）：单调递增的日志逻辑序列号。每次写入长度 length，lsn 加 length。
三个事务的组提交过程
以 trx1(lsn=50)、trx2(lsn=120)、trx3(lsn=160) 为例：
- trx1 先到，被选为 leader；
- trx1 要写盘时，组内已有 3 个事务，lsn 已到 160；
- trx1 写盘时带的是 lsn=160，返回时所有 ≤160 的 redo log 都已落盘；
- trx2、trx3 直接返回，无需各自 fsync。
组员越多，节约 iops 效果越好。单线程压测则只能一次一持久化。
"拖时间"优化
为让一次 fsync 带的组员更多，mysql 把 redo log 的 fsync 时机推迟到 binlog 写入之后：
写 binlog 分两步：① binlog cache → binlog 文件（write）；② fsync。优化后第4步 fsync 时，多个事务的 binlog 可一起持久化。
通过参数增强 binlog 组提交效果（或关系，满足其一即 fsync）：
| 参数 | 含义 |
|---|---|
binlog_group_commit_sync_delay | 延迟多少微秒再 fsync |
binlog_group_commit_sync_no_delay_count | 累积多少次再 fsync（设为0则该参数失效） |
通常 binlog 组提交效果不如 redo log，因为 write 与 fsync 间隔短，能聚合的事务较少。
wal 机制为何能减少磁盘写？
- redo log 与 binlog 都是顺序写，远快于随机写；
- 组提交机制大幅降低 iops 消耗。
五、io 瓶颈下的三种优化方法
| 方法 | 原理 | 风险 |
|---|---|---|
调大 binlog_group_commit_sync_delay 和 _count | 故意等待，攒批 fsync | 增加响应时间，无丢数据风险 |
将 sync_binlog 设为 >1（100~1000） | 累积 fsync | 主机掉电丢失最近 n 个事务 binlog |
将 innodb_flush_log_at_trx_commit 设为 2 | write 到 page cache | 主机掉电丢数据 |
不建议设为 0：redo log 只留内存，mysql 本身异常重启也会丢数据。设为 2 性能与 0 相近，但 mysql 异常重启不丢数据，风险更小。
"双1"配置：sync_binlog=1 + innodb_flush_log_at_trx_commit=1，事务提交前两次刷盘，数据最可靠。
mysql是怎么保证主备一致的？
一、问题背景
binlog 可以用来归档，也可以做主备同步。但 binlog 的内容是什么样的？为什么备库执行了 binlog 就能跟主库保持一致？
毫不夸张地说，mysql 能成为最流行的开源数据库，binlog 功不可没。几乎所有高可用架构都直接依赖 binlog，且都是从最基本的一主一备演化而来。
理解背后的设计原理，也可以从业务开发的角度借鉴这些设计思想。本文结合数仓测试中主备同步、数据一致性验证的场景，梳理主备基本原理、binlog 三种格式对比，以及双 m 结构的循环复制问题。
二、mysql 主备的基本原理
2.1 基本主备切换流程
- 状态 1：客户端读写都访问节点 a，节点 b 是 a 的备库，只同步 a 的更新到本地执行，保持与 a 数据相同。
- 状态 2（切换后）：客户端读写访问节点 b，节点 a 成为 b 的备库。
2.2 备库为什么建议设为 readonly
即使 b 不直接被访问，仍建议把备库设为 readonly（只读），原因有三：
- 防止运营类查询误操作落到备库；
- 防止切换逻辑有 bug（如双写）造成主备不一致；
- 可用 readonly 状态判断节点角色。
疑问：备库只读，还怎么跟主库同步更新？
解答：readonly 对 super 权限用户无效，而用于同步更新的线程拥有超级权限，不受影响。
2.3 主备同步的完整流程
一个 update 语句从节点 a 同步到节点 b 的完整过程：
- 备库 b 执行
change master，设置主库 a 的 ip、端口、用户名、密码，以及开始请求 binlog 的位置（文件名 + 偏移量）； - 备库 b 执行
start slave，启动两个线程：io_thread和sql_thread。io_thread负责与主库建立连接； - 主库 a 校验用户名、密码后，按备库传来的位置读取本地 binlog 并发送给 b；
- 备库 b 拿到 binlog 后写入本地文件，称为中转日志（relay log）；
sql_thread读取 relay log，解析并执行其中的命令。
注：多线程复制方案引入后，
sql_thread演化为多个线程，本文不展开。
三、binlog 的三种格式对比
binlog 有三种格式：statement、row、mixed（前两者的混合）。先建表初始化数据用于演示：
create table `t` (
id int(11) not null,
a int(11) default null,
t_modified timestamp not null default current_timestamp,
primary key (`id`),
key `a` (`a`),
key `t_modified` (`t_modified`)
) engine=innodb;
insert into t values(1,1,'2018-11-13'),(2,2,'2018-11-12'),
(3,3,'2018-11-11'),(4,4,'2018-11-10'),(5,5,'2018-11-09');
测试语句（含注释，客户端需加 -c 参数）：
delete from t /*comment*/ where a>=4 and t_modified<='2018-11-10' limit 1;
3.1 statement 格式
binlog_format=statement 时，binlog 记录的是 sql 语句原文。用以下命令查看：
show binlog events in 'master.000001';
内容要点：
set @@session.gtid_next='anonymous'：gtid 相关，暂忽略；begin…commit：标识一个事务；- 自动添加的
use 'test'：保证备库无论在哪个库，都能正确更新到 test 库的表 t； - 真实执行的 delete 语句（含注释原文）；
commit /* xid=61 */：xid 用于标识事务正确提交。
statement 的隐患（unsafe）：
上面语句执行产生 warning：statement 格式 + limit = unsafe。原因：
- 若主库用索引
a，删除的是a=4（id=4）这一行； - 若备库用索引
t_modified，删除的是t_modified='2018-11-09'（id=5）这一行。
记录的是语句原文，主备可能选不同索引，导致主备数据不一致。
3.2 row 格式
改为 binlog_format='row' 后，binlog 不再记录 sql 原文，而是两个 event：
- table_map：说明接下来操作的是哪个库的哪张表；
- delete_rows：定义删除行为。
用 mysqlbinlog 解析查看详细信息（-vv 解析出字段值）：
mysqlbinlog -vv data/master.000001 --start-position=8900
关键信息：
server id 1：事务在 server_id=1 的实例上执行；- 每个 event 有 crc32 校验值（
binlog_checksum=crc32）； - 每张表对应一个 table_map event，映射到单独数字；
binlog_row_image=full（默认）时，delete event 包含被删行的所有字段值；设为minimal则只记录必要信息（如 id=4）；- 末尾 xid event 表示事务正确提交。
优点：row 格式记录真实删除行的主键 id，备库一定删除 id=4 那行，不存在主备删不同行的问题。
3.3 mixed 格式
为什么需要 mixed？
- statement 可能导致主备不一致 → 想用 row；
- 但 row 太占空间：删 10 万行，statement 只记一条 sql（几十字节），row 要记 10 万行，耗 io、影响速度。
mixed = 折中方案：mysql 自动判断 sql 是否可能引起主备不一致，可能不一致就用 row，否则用 statement。
结论：线上若还是 statement，基本可认为是不合理设置，至少应设为 mixed；而越来越多的场景要求设为 row，一个重要好处就是便于恢复数据。
3.4 三种格式下的数据恢复对比
| 操作 | row 格式 binlog 记录 | 恢复方法 |
|---|---|---|
| delete（误删） | 被删行的整行信息 | 把 delete 转成 insert，插回数据 |
| insert（误插） | 所有字段信息，可定位插入行 | 把 insert 转成 delete，删掉误插行 |
| update（误改） | 修改前 + 修改后的整行数据 | 对调前后两行信息再执行即可回滚 |
mariadb 的 flashback 工具即基于此原理回滚数据。
3.5 mixed 下的 now() 一致性保证
insert into t values(10,10,now());
mixed 格式下这条语句被记录为 statement。担忧：binlog 过 1 分钟才传到备库，主备数据不就不一致了？
事实是 binlog 多记了一条命令：
set timestamp=1546103491;
通过 set timestamp 约定了 now() 的返回时间，无论 1 分钟后还是 3 天后执行，插入值都固定，确保主备一致。
风险提示：直接把 mysqlbinlog 解析出的 statement 语句拷贝出来执行是有风险的，因为有些语句依赖上下文命令。正确做法是把解析结果整个发给 mysql 执行：
mysqlbinlog master.000001 --start-position=2738 --stop-position=2973 | mysql -h127.0.0.1
四、循环复制问题（双 m 结构）
生产上更常见的是双 m 结构（a、b 互为主备），切换时无需修改主备关系：
问题
节点 a 更新生成 binlog → 发给 b → b 执行后也生成 binlog（log_slave_updates=on）→ a 又把 b 新生成的 binlog 执行一次 → 无限循环。
解决方案：基于 server id
mysql 在 binlog 中记录了命令第一次执行所在实例的 server id，配合以下逻辑打破循环：
- 两个库的 server id 必须不同，否则不能设为主备；
- 备库重放 binlog 生成新 binlog 时，沿用原 binlog 的 server id；
- 每个库收到主库发来的日志后，先判断 server id，若与自己的相同（说明是自己生成的），则直接丢弃。
双 m 下的执行流：
- 节点 a 更新的事务，binlog 记的是 a 的 server id；
- 传到 b 执行后，b 生成的 binlog server id 仍是 a 的；
- 再传回 a，a 判断 server id 与自己的相同 → 丢弃，死循环在此断开。
核心总结
| 主题 | 关键点 | 结论 |
|---|---|---|
| 主备流程 | 主库 binlog → 备库 io_thread 写 relay log → sql_thread 执行 | relay log 应用后可丢弃 |
| statement | 记录 sql 原文 | 带 limit/不确定索引时主备可能不一致（unsafe） |
| row | 记录 table_map + 行 event（主键/整行） | 主备一致，空间大，便于数据恢复 |
| mixed | 自动判断选 statement 或 row | 折中方案，建议至少用 mixed，推荐 row |
| 双 m 循环复制 | 基于 server id 判断并丢弃自身日志 | 两节点 server id 必须不同 |
以上就是mysql保证数据不丢与主备一致的方法步骤的详细内容，更多关于mysql数据不丢与主备一致的资料请关注代码网其它相关文章！
发表评论