MySQL高可用之主备同步：MySQL是如何保证主备一致的_mysql 主备同步日志

1.在备库B上通过change master命令设置主库A的相关信息以及要从哪个位置开始请求binlog
2.在备库B上执行start slave命令备库会启动两个线程即io_thread和sql_thread其中io_thread负责与主库通信
3.主库A校验完信息后根据备库B转过来的位置本地读取binlog传递给B
4.备库拿到binlog后写到本地文件称为中转日志relay log
5.sql_thread读取中转日志解析出命令并执行

四. binlog的三种格式

binlog的格式实际上由两种格式一种是statement一种是row。此外还有一种mixed格式实际上是前两种的混合。

为了方便解释几种日志格式的区别我们创建一个表并写入些数据。

mysql> 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')
insert into t values(2,2,'2018-11-12')
insert into t values(3,3,'2018-11-11')
insert into t values(4,4,'2018-11-10')
insert into t values(5,5,'2018-11-09')

然后我们对于这个表执行delete语句

注意下面这个语句包含注释如果你用 MySQL 客户端来做这个实验的话要记得加 -c 参数否则客户端会自动去掉注释。

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

我们可以使用下面的命令来查看binlog中的内容

mysql> show binlog events in 'master.000001'

可以看到当binlog_format=statement时binlog里面记录的就是sql原文

现在我们来看一下图 3 的输出结果。

第一行 SET @@SESSION.GTID_NEXT='ANONYMOUS’你可以先忽略后面文章我们会在介绍主备切换的时候再提到
第二行是一个 BEGIN跟第四行的 commit 对应表示中间是一个事务
第三行就是真实执行的语句了。可以看到在真实执行的 delete 命令之前还有一个“use ‘test’”命令。这条命令不是我们主动执行的而是 MySQL 根据当前要操作的表所在的数据库自行添加的。这样做可以保证日志传到备库去执行的时候不论当前的工作线程在哪个库里都能够正确地更新到 test 库的表 t。
use 'test’命令之后的 delete 语句就是我们输入的 SQL 原文了。可以看到binlog“忠实”地记录了 SQL 命令甚至连注释也一并记录了。
最后一行是一个 COMMIT。你可以看到里面写着 xid=61。
为了说明 statement 和 row 格式的区别我们来看一下这条 delete 命令的执行效果图

可以看到运行这条 delete 命令产生了一个 warning原因是当前 binlog 设置的是 statement 格式并且语句中有 limit所以这个命令可能是 unsafe 的。

为什么这么说呢这是因为 delete 带 limit很可能会出现主备数据不一致的情况。比如上面这个例子

如果 delete 语句使用的是索引 a那么会根据索引 a 找到第一个满足条件的行也就是说删除的是 a=4 这一行
但如果使用的是索引 t_modified那么删除的就是 t_modified='2018-11-09’也就是 a=5 这一行。
由于 statement 格式下记录到 binlog 里的是语句原文因此可能会出现这样一种情况在主库执行这条 SQL 语句的时候用的是索引 a而在备库执行这条 SQL 语句的时候却使用了索引 t_modified。因此MySQL 认为这样写是有风险的。

那么如果我把 binlog 的格式改为 binlog_format=‘row’ 是不是就没有这个问题了呢我们先来看看这时候 binog 中的内容吧。

可以看到与 statement 格式的 binlog 相比前后的 BEGIN 和 COMMIT 是一样的。但是row 格式的 binlog 里没有了 SQL 语句的原文而是替换成了两个 eventTable_map 和 Delete_rows。

Table_map event用于说明接下来要操作的表是 test 库的表 t;
Delete_rows event用于定义删除的行为。
其实我们通过图 5 是看不到详细信息的还需要借助 mysqlbinlog 工具用下面这个命令解析和查看 binlog 中的内容。因为图 5 中的信息显示这个事务的 binlog 是从 8900 这个位置开始的所以可以用 start-position 参数来指定从这个位置的日志开始解析。

mysqlbinlog -vv data/master.000001 --start-position=8900;

从这个图中我们可以看到以下几个信息

1.server id 1表示这个事务是在 server_id=1 的这个库上执行的。
2.每个 event 都有 CRC32 的值这是因为我把参数 binlog_checksum 设置成了 CRC32。
3.Table_map event 跟在图 5 中看到的相同显示了接下来要打开的表map 到数字 226。现在我们这条 SQL 语句只操作了一张表如果要操作多张表呢每个表都有一个对应的 Table_map event、都会 map 到一个单独的数字用于区分对不同表的操作。
我们在 mysqlbinlog 的命令中使用了 -vv 参数是为了把内容都解析出来所以从结果里面可以看到各个字段的值比如@1=4、 @2=4 这些值。
4.binlog_row_image 的默认配置是 FULL因此 Delete_event 里面包含了删掉的行的所有字段的值。如果把 binlog_row_image 设置为 MINIMAL则只会记录必要的信息在这个例子里就是只会记录 id=4 这个信息。
5.最后的 Xid event用于表示事务被正确地提交了。

你可以看到当 binlog_format 使用 row 格式的时候binlog 里面记录了真实删除行的主键 id这样 binlog 传到备库去的时候就肯定会删除 id=4 的行不会有主备删除不同行的问题。