MySQL主从如何保证数据一致性

MySQL主从（主备）搭建请点击基于Spring的数据库读写分离。

MySQL主备基本原理

假设主备切换前，我们的主库是节点A，节点B是节点A的备库，客户端的读写都是直接访问节点A，节点B只是将A的更新同步过来然后本地执行，同步完成以后，节点AB的数据就一致了。

对于备库，建议设置成只读模式，只读模式对超级用户是无效的，用于同步更新的线程的用户就拥有超级权限，因此备库是可以正常更新的。

MySQL主备同步原理

关于redo log和binlog的详细写入过程可以看我的历史文章，这里就不再详细描述了。

Slave B和Master A直接会维持一个长连接，Master A内部有一个线程，专门用于服务Slave B的这个长连接，binlog同步的完整过程如下：

1. 在Slave B上通过change master命令，设置Master A的IP、端口、用户名、密码以及从哪个位置（包含文件名和日志偏移量）开始请求binlog
2. 在Salve B上执行start slave命令，此时Slave B会启动两个线程，就是上图中的io_thread和sql_thread，io_thread负责与Maste A建立连接
3. Master A校验完用户名和密码以后，开始按照Slave B传过来的位置，从本地读取binlog，然后发送给Slave B
4. Slave B获取到binlog后，写到中转日志(relay log)
5. sql_thread读取中转日志，解析出日志里面的命令，并执行

binlog的格式

binlog一共有三种格式：

• statement：记录的是SQL语句
• 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');
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');

statement的binlog

-- 设置binlog模式为statement
set global binlog_format = 'statement';
-- 执行删除语句
delete from t where a>=4 and t_modified<='2018-11-10' limit 1;
-- 查看当前正在写入的binlog
show master status;
-- 查看binlog中的内容
show binlog events in 'mysql-bin.000005';

1. SET @@SESSION.GTID_NEXT= 'ANONYMOUS'
2. 第二行是BEGIN，与第四行的commit相对应，表示中间是一个事务
3. 第三行是真实执行的语句，use test命令不是我们主动执行，该命令是MySQL根据当前操作的表在哪个数据自行添加，这样可以保证日志传到备库去执行的时候，不论当前工作线程在哪个库，都可以正确更新到test库的t表
4. 最后一行是一个COMMIT，并且写着xid=41，关于xid的作用也可以看我的历史文章MySQL redo log和binlog深入分析

-- 查看上述delete语句产生的waring
show warnings;

通过上图可以看出，delete语句生成了一个警告，原因是当前binlog设置的模式是statement，并且语句中含有limit，所以此命令是unsafe的。

为什么在statement的binlog下，有些命令是unsafe的？

这是因为在statement的binlog下，有些语句在传到备库执行以后会存在数据不一致的情况，比如上面的delete语句：

• 如果delete语句使用的是索引a，那么会根据索引a找到第一个满足条件的行，也就是说删除的是a=4这一行
• 如果使用的索引是t_modified，那么删除的是就是a=5这一行

此时就可能存在在Master A上使用的是索引a，但binlog传到Slave B上在执行的时候有可能使用的是索引t_modified，因此MySQL认为这些写时有风险的，给出warning提示。

row模式的binlog

-- 设置binlog模式为ROW
set global binlog_format = 'row';
-- 开启一个新的binlog文件
flush logs;
-- 执行delete语句
delete from t where a>=4 and t_modified<='2018-11-10' limit 1;
-- 查看当前正在写入的binlog
show master status;
-- 查看binlog中的内容
show binlog events in 'mysql-bin.000006';

通过上图可以看出，row模式的binlog和statement格式的binlog在BEGIN和COMMIT是一样的，但是row模式的binlog没有SQL原文，而是替换成了两个event：

• Table_map event：用于表示接下来要操作test库的表t
• Delete_rows event：用于定义删除的行为

如何查看row模式下binlog的详细信息

通过show binlog events是看不出详细信息的，如果需要查看详细信息，还需要借助mysqlbinlog这个工具：

mysqlbinlog -vv /var/log/mysql/mysql-bin.000006 --start-position=751

从上图中我们可以看出以下信息：

1. server id 1表示事务是在server_id=1的这个库上执行的
2. 每个event都有CRC32的值，只是因为数据库参数binlog_checksum的值为CRC32
3. Table_map event显示了接下来要打开的表，map到数字109，如果操作了多张表，每个表都会有一个Table_map event，并且都会映射到一个单独的数字，用来区分对不同表的操作
4. 在postion为943开始的地方，我们看到了具体的DELT语句，-vv可以把内容解析出来，从解析的结果来看，我们可以看到各个字段的值(@1=4，@2=4, @3=1541808000)
5. 由于binlog_row_image的默认配置是FULL，因此在DELETE_event里面，包含了删掉的行的所有字段值，如果binlog_row_image设置为MINIMAL，则只会记录必要的信息，在上面的DELETE语句中，就只会记录id=4
6. 最后的Xid event用于表示事务被正确提交

为什么会有mixed格式的binlog？

• statement的binlog在特定情况下会导致主备不一致，所以需要使用ROW格式
• row格式的缺点是占用空间比较多，并且如果执行的事务比较大，影响行数比较多的话，binlog会比较大，并且写binlog也会消耗IO资源，影响执行速度

因此MySQL出现了mixed模式的binlog，MySQL会自己判断这条SQL是否可能引起主备不一致，如果是就用row格式，如果不是，就用statement格式。

-- 设置binlog模式为MIXED
set global binlog_format = 'mixed';
-- 执行插入语句
insert into t values(10, 10, now());

上述insert语句在我没有进行验证的时候我认为是会被记录为ROW模式，因为按照理解如果now函数被传到备库上执行（且binlog同步延迟比较高的话），那么主备数据肯定是不一致的，但实际上这条语句并没有如我所想记录为ROW模式，而是记录为statement模式，原因是什么呢，我们还是需要借助mysqlbinlog这个工具进行分析：

-- 首先通过下面的SQL找到binlog开始的position
show binlog events in 'mysql-bin.000006';

找到position以后我们就可以对binlog进行更进一步的分析：

mysqlbinlog -vv /var/log/mysql/mysql-bin.000006 --start-position=1022 --stop-position=1291

通过上图我们可以看出，在记录insert之前，binlog中多记录了一条SET TIMESTAMP=1645343964，用它来约定了接下来now()函数的返回时间，因此不论binlog在备库上是多久以后执行，值都是固定的。

为什么建议你将binlog设置为ROW？

首先现在随着SSD的普及，磁盘IO的性能得到大幅提升，在SSD加持下，IO成为瓶颈的可能性比较小，并且ROW模式的binlog记录了完整的变更信息，在恢复数据上面将会很容易。

即使我不消息误删了一行记录，我也可以通过binlog捞回原来的所有字段信息，然后转变成insert进行插入。

如果执行的是update语句，由于ROW模式的binlog会完整记录修改前和修改后的整行数据，所以我也可以很容易的进行恢复。

如何使用binlog恢复数据

-- 下面命令的意思，是将mysql-bin.000006文件中position在1022到1291字节之间的内容解析出来，放到MySQL中执行
mysqlbinlog /var/log/mysql/mysql-bin.000006 --start-position=1022 --stop-position=1291 | mysql -h127.0.0.1 -P3306 -u$user -p$pwd;

双主(双M)结构

在实际生产中，我们更多的是使用双Master的结构，双Master的结构和Master-Slave的结构区别只是：

节点A和节点B之间会为主备关系，这样在发生切换的时候就不需要修改主备关系了。

双M架构下的循环复制

双M架构的一个问题就是假设在节点A上进行更新，此时会发送binlog给节点B，节点B在重放这条更新以后也会生成binlog，由于节点A也是节点B的备库，因此又会把节点B的binlog拿过来进行执行，此时就会产生循环复制的问题。

如何解决循环复制问题

借助server id，在前面的实验中，我们已经知道在binlog中会记录server id。

1. 主备库server id必须不同，如果相同不允许设置为主备关系
2. 一个备库在binlog的重放过程中，生成与原binlog的server id相同的新的binlog
3. 每个库在收到主库发过来的binlog日志时，先判断server id，如果与自己的相同说明是自己生成的，就会直接丢弃这个日志