MySQL的复制

Thu, May 6, 2021 阅读时间 2 分钟

复制

复制是让一台服务器的数据和其他服务器保持同步。MySQL的复制是通过binlog实现的，binlog有三种类型：

  • statement：记录的就是SQL语句，优点是人类可读，并且占用空间较小，但是基于该类型binlog的复制可能会产生不一致，比如执行某个特定函数，在复制后的结果可能和之前不一样。
  • raw：记录的是具体的每一行的变化细节，可以保证复制的正确性，保证数据一致，但是一行一行的记录占用空间较大。实际使用中采用raw的比较多。
  • mixed：是statement和raw的结合，一般的语句修改都采用statement格式保存，当碰到一些可能引起复制不一致的函数时，改为采用raw保存。这样既能缩小日志占用空间，也可以保证复制的数据一致性。

复制的过程

  1. 主库产生binlog
  2. 备库的IO线程，请求主库的binlog，并将binlog保存到自己本地的中继日志relay log中
  3. 备库的sql线程读取relay log，通过重放relay log完成复制

后续会进行详细说明，大体的过程如下图：

图片来自《MySQL技术内幕》

复制的效率问题

由于在网络正常的情况下，备库从主库拉取binlog并持久化到本地的relay log的速度是很快的，性能消耗的大头在sql线程重放relay log的过程，尤其是在MySQL5.6版本前，只有一个sql线程，这样所有在主库上并行的写入，到了备库同步时就变成了串行执行，效率自然跟不上，在5.6版本以后，MySQL增加了多sql线程的方案进行了优化，后续也会详细说明。

复制解决的问题

  1. 高可用

    数据冗余在多个地方，防止单点失效。

  2. 负载均衡

    写少读多的场景下，可以让写请求都走主库，读请求则分发到多个备库上，提高数据库集群读的效率。

复制的执行过程

图片来自于<极客时间-MySQL实战45讲>

主库：

  1. 当有一个写请求时，先写内存的undo log，undo log主要用于事务回滚和MVCC。
  2. 然后数据会写入Innodb的buffer pool缓存页中。
  3. 然后就是redo log的两阶段提交，先写redo log进入prepare阶段。
  4. 然后写入binlog（此时其实就具备了复制的条件） ，主库会开启一个dump线程，用于接收备库的dump binlog请求。
  5. 然后就是redo log的commit，当提交成功就会返回给客户端，提交失败也没关系，后续数据库会重新提交的。

备库

  1. 备库的IO线程会向主库的dump线程进行请求读取binlog的数据，然后写入relay log中
  2. 然后备库的sql线程会从relay log中读取日志，在本地重放。

备库也可以作为其他备库的主库，再把这份数据同步到备库的备库中，只需要开启一个参数log_slave_updates为ON即可在备库上也会在重放时生成binlog。

Mysql> show variables like "log_slave_updates";
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| log_slave_updates | ON    |
+-------------------+-------+

复制的拓扑结构

MySQL主备复制的限制

  1. 一个备库只能有一个主库，一个主库可以有多个备库。
  2. 每个备库都要有一个全局唯一的服务器ID。

一主多备

最常见的复制方式，备库间没有交互，都是一个主库的备库，一般备库都会设置成readonly模式，有几点好处：

  1. 有时一些运营类的查询会放到备库上查，设置为只读可以防止误操作。
  2. 防止主备切换时逻辑有bug，造成切换过程中出现双写，造成主备不一致。
  3. 可以方便的用readonly状态判断节点角色。

适用场景

  • 读写分离，主库写，所有备库读，少量写和大量读时适合。可以把不同的库，根据使用方式，设置不同的存储引擎，提高读的效率，比如如果既要支持事务又要全文搜索就可以主库用innodb，备库用MyISAM（innodb现在也支持搜索了）。
  • 容灾，往往备库中有一台是用作灾难恢复的，不处理业务逻辑，放到远程数据中心。
  • 某个备库，还可以作为培训、开发或测试使用的服务器。

主主复制

包含两台服务器，每个都是对方的主：

适用场景：当两个不同地理位置的服务器，都需要拥有写入操作时。

冲突问题：同时写一行，或者同时插入一个自增的字段时，两个操作可能会产生冲突。

这种拓扑模式问题很大，很容易造成数据不一致。一般不会采用这种方式，更常见的是下面这种，被动模式的主主复制

被动模式的主主复制

主主复制的其中一个主改成readOnly模式，可以避免普通主主复制的冲突问题，区别是其中一台服务器是只读的被动服务器。由于服务器的配置是对称的（即两个数据库表，存储引擎，配置等完全是一模一样的），所以故障转移和恢复很容易，不需要切换主备关系，只改下readOnly即可，其实就是个优化版本的主备复制。

要注意的是，主主复制只能有两个主，不能多个主构成一个环，那种结构非常脆弱，可能循环复制。

一种常见的MySQL主备拓扑的结构

A和A’为被动模式的主主复制，其余B、C、D都是A的备库，A库进行写和少量读，B、C、D处理读请求。当A出现故障时，把A’切换为新主。

分发主库

当使用一主多备的模式下，当备库过多，每个备库一个IO线程会对主库造成较高负载，尤其是当一个很大的数据插入操作时，主库的负载会显著上升，因为多个备库同时请求这块大数据。

使用一个备库作为分发主库，它也是主库的备库，然后其他备库都作为这个备库的备库，分发主库的唯一作用就是向其他所有备库提供主库的binlog。并且因为分发主库不做任何的查询，所以可以设置存储引擎为blackhole。

Tips:blackhole

blackhole存储引擎，就像它的名字，只进不出，所有插入的数据都不会插入到B+树，只会记录binlog，一般也只用于分发主库的功能

分发主库也会有问题，如果分发主库出问题了，那么所有复制都失效了，有点脆弱，所以分发主库也可以整一个备库，当主出问题时，进行主备切换，让复制不会中断。

复制的优势和不足

能显著提高读的效率，对于读多写少的业务场景非常有效。缺点是对写没什么提高，如果对写要求高的话，低价值的数据可以使用NoSQL，比如文档型的MongoDB，高价值的数据可以考虑TokuDB。

主备延迟问题

检查主备数据是否一致的方式

有两个方法可以检查主备数据是否一致

  1. percona-toolkit的pt-table-checksum工具，这个工具的原理就是给主库插入一条数据，然后看看备库中是否同步过去了
  2. 在备库执行show slave status命令，字段Seconds_Behind_Master就显示了备库延迟了多少秒
MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
......
Seconds_Behind_Master: 0
......

主备延迟的主要原因

在网络正常时，日志从主库传给备库的耗时是很短的，所以主要是备库从relay log中重放导致的Seconds_Behind_Master的延迟。

Relay log重放慢的原因

  • 机器性能差，需要升级机器
  • 备库的读压力大，可能需要水平拓展，
  • 长事务
    • 大批量的删除、写入操作，不要在一个事务里，拆分成多个事务，执行间隙再睡一会
    • 大表的DDL操作，可以使用gh-ost工具
  • 备库的sql线程的重放策略有问题，比如MySQL版本较低，sql线程只有一个

MySQL在5.6版本支持了多个sql线程尝试解决这个问题。

备库sql线程的并行复制

MySQL5.6以后支持了备库多个sql线程进行relay log的重放操作。可以通过参数slave_parallel_workers参数指定sql线程的数量，默认是0：

Mysql> show variables like "slave_parallel_workers";
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| slave_parallel_workers | 0     |
+------------------------+-------+

多个sql线程并行复制的事务执行顺序问题

MySQL5.6的并行复制策略

按库分发。即如果事务操作的是不同数据库，那么可以被分发给不同的线程执行。这种方案明显分发效率很低。如果主库有多个Database，并且各个Database的压力平衡，会有一定效果。好处是简单，这种方案对binlog，外键等没有任何要求。

MariaDB的并行复制策略

MariaDB的并行复制主要基于两点：

  • 一个组里提交的事务(redo log会按组提交以提升刷盘效率)，一定不会修改同一行
  • 主库上可以并行的事务，那么备库也一定可以

基于以上两点，MariaDB通过添加commit_id识别出同一组提交的事务，然后把每组的事务分发到多个线程执行，执行完一组，再执行下一组。

MariaDB的同一组事务提交的时候，会有一个相同的commit_id，它是全局递增的，binlog中记录的每一行都会记录下commit_id

这种方案的不足：

  1. 和主库的执行过程相比，仍有吞吐量上的差距，因为备库的重放过程是一个事务组一个事务组串行的，每一组都要等前一组事务执行完才能开始执行。主库上一组事务提交时，下一组事务是同时处于执行中状态的。
  2. 同样基于上述原因，仍然无法解决大事务的问题。

MySQL5.7的并行复制策略

MySQL5.7参考了MariaDB的实现方案，提供了slave_parallel_type参数来控制并行复制策略

  • DATABASE，那就是MySQL5.6的按库分发的策略
  • LOGICAL_CLOCK，就是基于MariaDB的分发策略，进行了一点优化，MariaDB是在事务commit时确定的commit_id，而InnoDB的事务是两阶段提交的，当redo log处于prepare状态时，事务就已经是可以提交的了，事务的所有的关于锁的检查都已经处理完了，所以MySQL5.7版本中除了同一组提交的事务外，同时处于prepare，以及prepare到commit状态的事务也是可以并行执行的。
LOGICAL_CLOCK有两个和binlog提交相关的参数可以设置：
binlog_group_commit_sync_delay：表示延迟多少微妙才调用fsync写入磁盘
binlog_group_commit_sync_no_delay_count：表示累计多少次以后才调用fsync
以上两个参数可以人为的拉长binlog写入磁盘的时间，制造出更多同时处于prepare阶段的事务，从而提升备库的并发度

MySQL5.7.22的并行复制策略

MySQL5.7.22增加一个参数binlog_transaction_dependency_tracking，有三个可选值：

  • COMMIT_ORDER：就是前面5.7的LOGICAL_CLOCK策略。
  • WRITESET：表示对于事务涉及更新的每一行，计算出这一行的hash值，组合成writeset，如果两个事务没有操作相同的行，就是说他们的writeset没有交集，就可以并行。
  • WRITESET_SESSION：在writeset基础上多了一个约束，即在主库上同一个线程先后执行的事务，在备库上也要保证先后顺序。

备库延迟的应用端解决方案

在一主多备的架构中，主要目标是将读的压力分摊到备库上，而由于复制是一定存在延迟的，及时MySQL已经通过多个版本尽可能优化了备库复制的效率，但是备库的重放过程仍然和主库的执行过程有差距，再加上网络原因，硬件差距，备库的查询压力大，大事务等等原因，主备延迟是不可避免的。这就会导致主库写完之后，从备库上读到的仍然是旧数据。要解决这个问题，就需要在应用层做一些方案。

强制走主库方案

对请求做分类，对于需要更新后立刻获取最新结果的请求，就必须要发到主库；对于可以读到旧数据的请求，就可以走主库。

对于某些场景来说，这种方案也可以使用，但缺点也很明显，如果所有的查询都不能读旧数据，那就纯纯单机数据库了。

更新后，读库之前先sleep一下

这是假设主备延迟在一定时间以内，那么我只要睡过了这段时间，就能拿到新的数据了吗。

看起来不可靠，但是应用也很多。比如前端插入数据成功以后，就可以由Ajax直接把新数据显示在前端，而不是真的走查询数据库。等到用户再次刷新页面时，或者一段时间以后，再访问数据库，就相当于做了一个sleep操作。

睡眠的这种方案明显是不准的，还是可能读到旧数据。而且如果睡过头了，还会造成性能浪费。

判断主备是否有延迟，有延迟就等没延迟了再访问

有多种方法都可以完成这个判断

  1. 通过命令show slave status查询备库的Seconds_Behind_Master参数，如果不等于0，就等这个值等于0了再执行查询。缺陷是这个单位是秒，并不是十分准确。

  2. 对比位点，主库的Master_Log_File和Read_Master_Log_Pos表示主库的最新位点；Relay_Master_Log_File和Exec_Read_Master_Log_Pos表示备库执行的最新位点，对比看看两组数一不一样，不一样就等一样了再查询，这种比方法1更准确一点。

  3. 对比GTID，主库的Auto_Position值为1，说明开启了GTID。然后检查备库的Retrieved_Gtid_Set和Executed_Gtid_Set是否一致，不一致就等一致了再去读，两个参数分别表示备库收到的所有日志的GTID集合，和备库已经执行完的GTID集合，这也比方法1更准确。

关于GTID：

GTID代表执行过的事务的编号，在MySQL5.6版本引入，它是一个全局事务的ID，是一个事务在提交时生成的，全局递增。这里要注意和另一个事务ID transaction_id进行区分，transaction_id是在事务开始时分配的，GTID是在事务提交时分配的，所以虽然都是递增的，但GTID一定是连续的，而transaction_id可能会因为事务回滚造成不连续。

每个数据库实例都会维护一个GTID集合记录自己执行过的事务，备库中的`Retrieved_Gtid_Set`维护的是从主库接收到的GTID集合，`Executed_Gtid_Set`则表示备库已经执行的GTID集合。

GTID主要是解决主备切换时需要寻找位点的问题，有了GTID后切换的新主会通过和旧主的GTID集合进行对比完成同步，并可以快速的从binlog里找到需要继续向后发送的binlog位点。备库只需要change master就可以更换新的主库了。

开启GTID：只需要启动MySQL时，加上参数`gtid_mode=on`和`enforce_gtid_consistency=on`即可。

方法2和方法3也并不是完全精确的，不管是位点还是GTID，都是判断备库读到的binlog位置，和备库执行的binlog位置，只要这两个一致，就说明主库发给备库的binlog，备库都执行完了。但其实还是有可能，主库执行完的事务，但是还没发给备库的情况，这种情况也不可能从备库中读出数据了，只能从主库读。

如果要解决上述问题，就要确保主库上执行完了的事务，备库也一定收到了binlog，这样才能从备库读，semi-sync尝试解决该问题。关于semi-sync后续会详细说明。总之，semi-sync方案，可以更大可能让主库在binlog已经发给了备库之后，再提交事务。

同时对比位点和GTID的方案，如果是在业务的高峰期，主库的位点或者GTID更新的很快，我们客户端查询时，迟迟检查不到两个位点相等，即使我们新写入的数据已经同步到了备库上，我们的判断逻辑依旧不能让我们从备库中读取数据。要解决这个问题，可以使用下述的等主库位点和等GTID的方案。

等主库位点：

命令select master_pos_wait(file, pos[, timeout]);介绍：

从库执行这条命令，file和pos参数是主库上的binlog文件名和位置，timeout是可选参数，整数，单位是秒，表示等待多少时间。如果主库上指定的pos，备库已经执行过了，则该命令返回一个正整数，表示从命令开始，到应用完file和pos表示的binlog位置，执行了多少个事务，只要是>=0就说明pos位置的binlog已经执行过了；如果执行期间，备库同步线程发生异常，则返回NULL，如果等待超过了timeout时间，则返回-1。

则利用这个命令就可以知道主库的某个位置的binlog，备库是否已完成同步了，如果已完成，则不需要等待主备库位点一样就可以从备库读了。过程大概如下：

  1. 主库事务完成后，马上执行主库的show master status得到当前主库执行到的binlog位置和position
  2. 然后要查询时选定一个备库先执行select master_pos_wait(file, pos, 1)，假设延迟时间最大是1s
  3. 如果返回值>=0，则说明备库已经同步过那个主库的binlog位置了
  4. 否则，就说明这个备库还没同步到，此时可以有多种方案，可以选择到主库执行该查询，或者换个备库重复上述过程，或者在这个备库再执行一遍这套操作。到主库查询比较快，如果继续访问备库，就要设个最大重试次数或者超时时间，避免无限等待。
Mysql> show master status;
+------------------+-----------+--------------+------------------+
| File             | Position  | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+-----------+--------------+------------------+
| mysql-bin.000003 | 173798701 |              |                  |
+------------------+-----------+--------------+------------------+
1 row in set (0.000 sec)

Mysql> select master_pos_wait("mysql-bin.000003", 173798701, 1);
+---------------------------------------------------+
| master_pos_wait("mysql-bin.000003", 173798701, 1) |
+---------------------------------------------------+
|                                                 0 |
+---------------------------------------------------+
1 row in set (0.000 sec)

等GTID：

和等主库位点类似的，同样也有一个等GTID的命令可以使用：select wait_for_executed_gtid_set(gtid_set, timeout);

这条命令的含义是等待，直到这个从库执行过的事务中包含传入的gtid_set，返回0，如果超时就返回1。

等GTID相比于等主库位点的好处是：等主库位点的方案中，执行完一个事务，还要使用show master status命令得到binlog的文件名和位置，而在MySQL5.7.6版本开始，可以在执行完一个更新类的事务后，返回这个事务的GTID给客户端，这样就比等位点少了一次主库的查询操作。

于是流程就是这样的：

  1. 主库上的更新事务执行完成后，从返回中得到事务的GTID
  2. 然后选择一个从库，执行wait_for_executed_gtid_set(gtid_set, 1)，超时时间同样设为1秒
  3. 如果返回值是0，则可以在这个备库上执行查询
  4. 否则，退化到主库查询，或是等待等操作

设置事务执行完成后返回GTID的方法：

需要将参数session_track_gtids设置为OWN_GTID，然后通过API接口mysql_session_track_get_firrst从返回的包中解析出GTID即可，不能通过SQL语句查到，只能通过程序的API拿到。

binlog的复制方案

异步方案

我们前面讨论的都是基于异步复制的方案，即复制的执行过程那张图。在主库写完binlog后异步生成一个dump_thread，然后主库的redo log就可以提交了。即主库的提交过程和备库的复制binlog过程完全不相关，因此就会产生主库的事务已经提交了，但是binlog却还没有发给备库，导致备库认为自己已经完全和主库同步了，但是从备库中依然读不到新写的数据。

同时，异步方案，如果主库突然宕机，而binlog还没有发送给备库时，这部分数据就有可能会在备库丢失。

半同步方案

即semi-sync，在MySQL5.7中通过一下命令可以开启：

set global rpl_semi_sync_master_wait_point = AFTER_SYNC;

semi-sync的设计

  1. 主库中一个事务提交的时候，会把binlog发给备库
  2. 备库收到了binlog之后，会返回主库一个ACK，以确认自己收到了
  3. 主库收到了一条ACK确认信息，就会给客户端返回事务成功的响应

由于semi-sync叫做半同步方案，即主库只需要等待一个备库给自己返回ACK就可以确认事务提交，所以对于一主一备来说，semi-sync配合上述的位点、GTID的方案，可以完全解决主库事务执行完，但binlog还没发给备库导致的主备延迟问题。

但是对于一主多备的架构来说，即使使用了semi-sync，如果读请求刚好落在了一个没收到主库binlog的备库上，还是得不到最新的数据。

全同步方案

MySQL5.7.17以后的MGR集群（MySQL Group Replication）通过raft算法，保证所有MySQL的强一致，并自带故障转移，自动选主，就是把MySQL变成了一个强一致性的分布式数据库。明显这种强一致的数据库性能肯定不会太好，并且MGR集群的限制很多：

  • 仅支持InnoDB引擎
  • 只能在GTID模式下
  • binlog只能为raw格式
  • 需要设置–binlog-checksum=none
  • 不支持gap lock间隙锁
  • 隔离级别要设为read_commited
  • 不支持对表进行锁操作
  • 不支持串行化的隔离级别
  • DDL不支持原子性，不能检查冲突，需要自己校验是否一致
  • 多主模式下，不支持外键，单主可以
  • 最多支持9个节点，超过9个无法加入MGR组

这个感觉用的人也不多，先仅作了解吧。