mysql 怎么看主备,24 | MySQL是怎么保证主备一致的？

、 binlog归档，主备同步，内容什么样？备库执行 binlog 跟主库一致

一、MySQL主备的基本原理

图 1 MySQL 主备切换流程

读写直接访问 A， B 是备， A 更新都同步到本地执行。保持B 和 A 数据相同。

B 没有直接访问，只读(readonly)原因：

1. 防止误操作：运营类查备库

2.  防止主备不一致：切换逻辑bug，如双写

3.  判断节点角色

只读怎么跟主同步更新？ readonly 对超级 (super) 权限用户无效

内部流程A update 同步到B

图 2 主备流程图

binlog 和 redo log 写入机制，主库更新同时写 binlog。B A 维持长连接(A 有线程专门服务 B)

1.  B  change master 命令设置 A的 IP、端口、用户名、密码，哪个位置开始请求 binlog(包含文件名和日志偏移量)

2.  B 执行 start slave 命令，启动线程 io_thread(与主库连接) 和 sql_thread

3.  A 校验完用户名、密码后，按 B 传来位置，本地读取 binlog发给 B。ps：B主动拉

4. B 拿binlog 写到本地(中转日志relay log)sql_thread 执行

二、binlog三种格式对比

两种格式：statement，row。第三种格式mixed

删除一行binlog

包含注释，加 -c 参数，否则会自动去掉注释。

binlog ：mysql> delete  from t /*comment*/  where a>=4 and  t_modified<='2018-11-10' limit 1;

binlog_format=statement记录SQL原文

mysql> show  binlog events in 'master.000001'; 删除一行 binlog 中的内容：

图 3 statement 格式 binlog 示例

第一行 SET@@SESSION.GTID_NEXT='ANONYMOUS’忽略，主备切换提到

第二行BEGIN，跟的commit 对应(第四行)，表示事务；

第三行执行语句。“use ‘test’”不是我们主动执行的，自行添加。保证日志不论当前线程在哪个库，正确更新 test 库表 t。

xid=61。回顾第 15 篇文章 delete 命令的执行效果图：

图 4 delete 执行 warnings

产生warning，可能unsafe原因：statement 格式且有 limit。 limit很可能主备数据不一致：

1.  用索引 a，删除 a=4(第一个满足条件)；

2.  用索引 t_modified，删除t_modified='2018-11-09’ a=5

statement  binlog 语句原文，可能出现：主库用索引 a；备库用索引 t_modified。

binlog_format=‘row’ binog 中内容

图 5 row 格式 binlog 示例

没SQL 原文：

1.  Table_map event，说明操作test 库表t;

2.  Delete_rows event，定义删除行为

mysqlbinlog 工具：mysqlbinlog  -vv data/master.000001  --start-position=8900;从8900的日志开始解析

图 6 row 格式 binlog 示例的详细信息

server id 1：事务在 server_id=1 库上执行

binlog_checksum = CRC32：每个 event 都有 CRC32 的值

map 226(Table_map event )，区分对不同表操作

-vv 解析内容可看到各个字段值(@1=4、 @2=4 )

binlog_row_image = FULL(默认)，Delete_event包含所有字段值(删掉行)。MINIMAL，只记录必要 (id=4 )。

最后 Xid event，事务正确提交。

row 格式，binlog 删除行主键 id，备库一样

三、为什么会有mixed格式binlog？

row 缺点：占空间。删掉 10 万行：(1)statement一个SQL 语句记录binlog 中(几十个字节)；(2)row格式的 binlog，10 万条记录到 binlog 。耗费 IO 资源，影响执行速度。

binlog 为 mixed(用得不多)。引起主备不一致 row 格式，否则statement ：记录为 row 格式；去掉 limit 1 statement 格式。

row好处：恢复数据(MariaDB 的Flashback回滚数据原理)

(2)删错数据直接把 binlog 中记录的 delete 语句转成 insert；

(2)执行错了 insert语句转成 delete 语句。

(3)update记录修改前、后整行数据。event两行信息对调执行，恢复更新

mysql> insert  into t values(10,10, now()); statement格式。 binlog 过了 1 分钟传备库，不就不一致？

图 7 mixed 格式和 now()

图 8 TIMESTAMP 命令

记录 event 时多记命令：SET TIMESTAMP=1546103491。约定now() 函数返回时间。不论何时恢复，insert值固定。

重放 binlog 时：mysqlbinlog工具解析日志，statement 拷贝执行有风险。有些语句依赖于上下文，标准做法：用 mysqlbinlog 解析结果整个发给 MySQL 执行。

mysqlbinlog  master.000001  --start-position=2738  --stop-position=2973 | mysql -h127.0.0.1 -P13000 -u$user -p$pwd;

将master.000001 文件从第 2738 ~ 2973 字节放到 MySQL 去执行。

四、循环复制问题

binlog 特性确保了备库执行相同 binlog，与主库相同状态。图 1 中是 M-S 结构，实际多是双 M结构：

图 9 MySQL 主备切换流程 -- 双 M 结构

A  B互为主备，不用再修改主备。新问题：循环复制

A 更新生成的 binlog 发给 B，B 执行完生成 binlog，A 又执行B binlog (log_slave_updates = on，备库执行 relay log 后生成 binlog)。

图 6 ，记录第一次执行 server id

1.  两个库server id 必须不同，如相同不能为主备；

2.  备库连接到 binlog 重放，生成与原 binlog  server id 相同新 binlog；

3.  每个库收到主库日志，先判断 server id，相同丢弃。

双 M 结构，会变成这样：

1.  A 更新事务，binlog 记 A  server id；

2. 传到B 执行，B  binlog 的 server id 也是 A  server id；

3.  传回给节点 A，相同不处理

小结

binlog格式、基本机制、高可用方案基础。在这之上演化出：多节点、半同步、MySQL group replication 等复杂方案。

优缺点，设计者思考。

思考题

循环复制时，通过判断 server id 断掉死循环。某些场景可能死循环。什么场景吗？怎么解决？

场景1：主库更新后， set global server_id=x 修改 server_id。再传回server_id 不同执行。

场景2：三个节点(数据库迁移时)，trx1 B 执行， binlog  server_id  B传 A， A 和 A’ 双 M 结构，循环复制。

A 或者 A’上执行：stop slave；CHANGE MASTERTO  IGNORE_SERVER_IDS=(server_id_of_B); start slave;

收到日志不再执行。一段时间后改回来：stop slave；CHANGE MASTER TO IGNORE_SERVER_IDS=(); start slave;

评论1

双主，log_slave_updates=on，binlog_format=statement 都重启

(row改成statement几次没成功,binlog还是row)

测试： 表t (id ,c,d) 主键id，有一条数据(1,2,1); c不断变大

stop slave;

update t set c=c+1 ;或 update t set c=c+1 where id=1;

set global server_id=new_server_id；

start slave；

评论2

主库 A 从本地读取 binlog，发给从库 B；本地指文件系统page cache还是disk呢？

在 page cache中直接读走；否则去磁盘

评论3

双M主从复制，一方判断数据少，从对方复制，判断依据是什么？

创建主备关系由备库指定。

基于位点主备关系，备库说“从binlog文件A位置P”开始同步。而复制后，主库决定“要发数据给备库”。