mysql主备一致性问题

前言  

   在单机环境下mysql采用innodb引擎时，可以通过redolog 和binlog的两阶段提交保证数据不丢失，不知道大家有没有想过mysql数据最终是存在磁盘上的，然而我们每次新增和查询语句是特别快的，那么mysql是如何做到这一点的。为了保证服务的高可用性，生产环境往往会采用主从结构的或者双主结构的mysql,大家都知道mysql复制主库数据的原理是拉取主库的binlog,如果没有额外配置，这个过程是一个异步的过程，因此只能保证数据的最终一致性，这里就有两个问题：1尽管可以保证最终一致性，但我想尽量缩小和主库数据之间的时延。2有什么方法可以保证强一直性呢？不用着急本文将把mysql主备一致性问题需要注意的点一一说明。

一、一条修改语句在mysql中是如何执行的

      一条修改语句在修改之前会先判断被更新的记录是否在内存中，同时对于唯一索引和普通索引又有不同的区别比如我们插入一条（3,300）的语句：

唯一索引：判断2到4之间是否存在冲突，没有则插入这个值，等待提交redolog binlog,语句执行结束。

普通索引：找出2到4之间的位置，插入这个值语句结束，等待提交redolog binlog,语句执行结束。

     如果在待更新的记录不在内存中会怎么样呢？

唯一索引：将磁盘的数据读入内存，在判断2到4之间是否存在冲突，没有冲突则插入这个值，等待提交redolog binlog,语句执行结束。

普通索引：直接将记录更新到change buffer中，等待提交redolog binlog,语句执行结束。

    聪明的同学一定会发现，这里新增的语句并没有写入磁盘啊。对大家都知道内存和磁盘的随机访问速度一个在天上，一个在地下，如果每次都写入磁盘势必造成响应速度特别慢，那么mysql是如何解决这个问题的呢。这里运用一下丁奇大神的例子，孔乙己每次买酒的小店会在生意特别忙时把交易记录记在小黑板上，而不是账本上（账本需要找到这个人，还要和旧账一起计算特别花时）。在生意不忙时或者小黑板记满了时把小黑板的账移交到账本上。mysql也是这么做的它会把‘账"记录到redolog上，在redolog没有空间时，或者mysql空闲下来时把数据刷到磁盘。（redo log 到底记录的什么消息感兴趣的同学可以看丁奇的即刻时间专栏）

二、redolog 和binlog刷盘速度对主从延迟的影响

   我们不是在讲主从一致性问题吗，为什么要讲redolog和binlog呢?我们可以把主从模式看成生产者和消费者，为了减小消费者和生产者之间的速度差异我们可以提高消费者的消费速度。而提升redolog和binlog的刷盘速度就是提升消费者速度的一种手段。为了减小硬盘和内存之间的速度差异操作系统往往会引入page cache,而page cache的刷盘才会真真的将记录写进磁盘，刷盘会占据磁盘的IOPS（耗时），而写入pagecache并不怎么耗时。我们来看看binlog和redo log的写入机制：

2.1 binlog

  binlog的写入机制是靠 sync_binlog控制的：

1sync_binlog=0 的时候，表示每次提交事务都刷盘

2sync_binlog=1 的时候，表示每次提交事务都会写入pagecache；

3sync_binlog=N(N>1) 的时候，表示每次提交事务都会写入pagecache，但累积 N 个事务后才 会刷盘。

可以看出如果mysql断电意味着2和3都有丢失binlog的风险。而sync_binlog=0时因为binlog每次都会刷盘责会导致速度非常慢。

生产环境一般选取100-1000的一个值。

2.2redolog

   和binlog不同的是redolog除了可以存在page cache 和 磁盘中外，还可以存在redo log buffer中。可以用参数innodb_flush_log_at_trx_commit参数来进行控制：

1设置为 0 的时候，表示每次事务提交时都只是把 redo log 留在 redo log buffer 中 ;

2设置为 1 的时候，表示每次事务提交时都将 redo log 直接持久化到磁盘；

3设置为 2 的时候，表示每次事务提交时都只是把 redo log 写到 page cache。

需要注意的是无论是redolog还是binlog在刷磁盘（把pagecache写入磁盘）时都采取的是顺序写，因此一次多刷一点pagecahce到磁盘里是必定能减少磁盘的IOPS的。mysql也采取了组提交，组提交等待一定秒数后再写盘（binlog_group_commit_sync_delay 参数，表示延迟多少微秒后才调用 写盘），组提交等待一定次数后再写盘（binlog_group_commit_sync_no_delay_count）的技术来提高整体的吞吐量。假如在实际生产环境中你发现从库严重落后于主库，那么通过修改从库sync_binlog和innodb_flush_log_at_trx_commit参数是一个减少主从延迟秒数的思路。

三、mysql多线程复制

  上面提到在从数据库这端提升消费binlog的速度是减小主从不一致时延的一种思路。其实mysql本身就有在这么做了我们来看看，从书库拉取binlog的流程：

1在备库 B 上通过 change master 命令，设置主库 A 的 IP、端口、用户名、密码，以及要从哪个位置开始请求 binlog，这个位置包含文件名和日志偏移量。

2在备库 B 上执行 start slave 命令，这时候备库会启动两个线程，就是图中的 io_thread 和 sql_thread。其中 io_thread 负责与主库建立连接。

3主库 A 校验完用户名、密码后，开始按照备库 B 传过来的位置，从本地读取 binlog，发给 B。

4备库 B 拿到 binlog 后，写到本地文件，称为中转日志（relay log）。

5sql_thread 读取中转日志，解析出日志里的命令，并执行。

其中在mysql5.6版本之前sql_thread是单线程复制，经过几个版本之后架构演化为下图：

并行复制时一定要保证并行之后生成的数据是和主库一致的比如有两条语句

1update t set name ='a' where id = 1,

2update t set name ='aaa' where id = 1.

在主库中执行的顺序是1,2。那么在备库并发复制时也一定要先运行1，再运行2.

 其中在mysql5.6版本中mysql只支持按库并行，按库并行的意思就是如果所有的表都在同一个库中其实就是串型，而在mysql5.7中可以由参数slave-parallel-type来控制 并行工作的策略。

1配置为DATABASE时，还是使用5.6的按库并行策略。

2配置为LOGICAL_CLOCK时运用了一下两条策略：

    2.1能够在同一组里提交的事务，一定不会修改同一行；

    2.2主库上可以并行执行的事务，备库上也一定是可以并行执行的。

四、主备延迟的来源

在现实开发中我们可以通过使用命令show slave status查看slave库的seconds_behind_master参数来判断从库的延迟情况。如果这个参数比较大那我们应该从什么方面来分析问题呢？

1有可能从库的性能本来就是不如主库的。但现实中这种情况是比较少的。当主库挂了时，需要把从库切为主库，因此应该保证主从库的性能是一样的。

2在主从模式中，备库一般会作为待查询库，因此如果此时正有大量的查询语句打到备库，导致备库压力太大。没有能力消化主库同步过来的binlog也是有可能的。

3大事务，binlog是与事务挂勾的，如果一个事务本来就执行了10分钟，执行完后才会提交binlog.此时从库必定是会有延迟的。因此在生产开发中一定要注意大事务。

五、面试官问采取mysql主从结构的业务如何保证强一致性

你可以从以下几点来回答：

1强行走主库方案

2sleep方案

3判断主备无延迟方案，判断主库位点方案，等GTID方案

4配合semi-sync方案

5.1强行走主库方案

对于必须要拿到新结果的数据强行发到主库上，对于可以容忍短暂延迟的数据发到从库上执行。

5.2sleep方案

引用一下丁奇大神的例子。以卖家发布商品为例，商品发布后，用 Ajax（Asynchronous JavaScript + XML，异步 JavaScript 和 XML）直接把客户端输入的内容作为“新的商品”显示在页面上，而不是真正地去数据库做查询。这样，卖家就可以通过这确认是否已经发布成功。等下一次刷新页面时再查数据库。

5.3 判断主备无延迟方案，判断主库位点方案，等GTID方案

首先我们要知道如何去判断主备是否不一致，看下图

上文讲到的seconds_behind_master就是判断主备是否延迟的一种手段。

图中Master_Log_File，Read_Master_Log_Pos以及Relay_Master_File,Exec_Master_Log_Pos就是判断主库位点方案。

在主库位点方案中我们可以执行以下语句来判断是否有延迟，如果结果>=0就表示可以在从库查询：

select master_pos_wait(file, pos[, timeout]);

Retrieved_Gtid_Set 和 Executed_Gtid_Set 就是等GTID方案

同理我们可以执行以下语句来判断是否查从库：

 select wait_for_executed_gtid_set(gtid_set, 1);

如果结果等于0表示可以从从库执行。

5.4配合semi-sync方案

大家都知道mysql主库同步binlog到从库是异步的，及主库写完binlog后就不用管接下来的从库复制binlog逻辑了。而semi-sync则做了下面的设计：

1事务提交的时候，主库把 binlog 发给从库；、

2从库收到 binlog 以后，发回给主库一个 ack，表示收到了；

3主库收到这个 ack 以后，才能给客户端返回“事务完成”的确认。

及改异步为同步的方式。

感兴趣的同学可以看看这篇文章：https://blog.csdn.net/qq_36431213/article/details/85790448