mysql主从复制总结

mysql复制流程

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）
5. sql_thread 读取中转日志，解析出日志里的命令，并执行

binlog的三种格式

statement

statement 格式下，记录到 binlog 里的是语句原文

binlog 设置的是 statement 格式，并且语句中有 limit，所以这个命令可能是 unsafe 的，可能会出现这样一种情况：在主库执行这条 SQL 语句的时候，用的是索引 a；而在备库执行这条 SQL 语句的时候，却使用了索引 t_modified。因此，MySQL 认为这样写是有风险的

在statement模式下，由于他是记录的执行语句，所以，为了让这些语句在slave端也能正确执行，那么他还必须记录每条语句在执行的时候的一些相关信息，也就是上下文信息，以保证所有语句在slave端被执行的时候能够得到和在master端执行时候相同的结果。这需要使用mysqlbinlog 工具解析出来，然后把解析结果整个发给 MySQL 执行，而不是应该只使用从statement解析出的语句来执行，这样会缺少上下文

row

当 binlog_format 使用 row 格式的时候，binlog 里面记录了真实影响的行的主键 id

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

row 格式的缺点是，很占空间。比如你用一个 delete 语句删掉 10 万行数据，用 statement 的话就是一个 SQL 语句被记录到 binlog 中，占用几十个字节的空间。但如果用 row 格式的 binlog，就要把这 10 万条记录都写到 binlog 中。这样做，不仅会占用更大的空间，同时写 binlog 也要耗费 IO 资源，影响执行速度

但是现在越来越多的场景要求把 MySQL 的 binlog 格式设置成 row。因为可以进行恢复数据

在row模式下，binlog中可以不记录执行的sql语句的上下文相关的信息，仅仅只需要记录那一条记录被修改了，修改成什么样了，所以row的日志内容会非常清楚的记录下每一行数据修改的细节，非常容易理解

mixed

mixed 格式的意思是，MySQL 自己会判断这条 SQL 语句是否可能引起主备不一致，如果有可能，就用 row 格式，否则就用 statement 格式

mixed 格式可以利用 statment 格式的优点，同时又避免了数据不一致的风险

如果你的线上 MySQL 设置的 binlog 格式是 statement 的话，那基本上就可以认为这是一个不合理的设置。你至少应该把 binlog 的格式设置为 mixed

双M架构

节点 A 和 B 之间总是互为主备关系。这样在切换的时候就不用再修改主备关系

如何解决循环复制的问题

• 规定两个库的 server id 必须不同，如果相同，则它们之间不能设定为主备关系
• 一个备库接到 binlog 并在重放的过程中，生成与原 binlog 的 server id 相同的新的 binlog
• 每个库在收到从自己的主库发过来的日志后，先判断 server id，如果跟自己的相同，表示这个日志是自己生成的，就直接丢弃这个日志

主备延迟

• 在备库上执行 show slave status 命令，它的返回结果里面会显示 seconds_behind_master，用于表示当前备库延迟了多少秒

seconds_behind_master 的计算方法是这样的：

1. 每个事务的 binlog 里面都有一个时间字段，用于记录主库上写入的时间；
2. 备库取出当前正在执行的事务的时间字段的值，计算它与当前系统时间的差值，得到 seconds_behind_master，单位是秒

备库连接到主库的时候，会通过执行 SELECT UNIX_TIMESTAMP() 函数来获得当前主库的系统时间。如果这时候发现主库的系统时间与自己不一致，备库在执行 seconds_behind_master 计算的时候会自动扣掉这个差值

在网络正常的时候，日志从主库传给备库所需的时间是很短的，网络正常情况下，主备延迟的主要来源是备库接收完 binlog 和执行完这个事务之间的时间差。主备延迟最直接的表现是，备库消费中转日志（relay log）的速度，比主库生产 binlog 的速度要慢。

主备延迟的原因

• 备库所在机器的性能要比主库所在的机器性能差
• 备库的压力大。一般的想法是，主库既然提供了写能力，那么备库可以提供一些读能力。或者一些运营后台需要的分析语句，不能影响正常业务，所以只能在备库上跑。忽视了备库的压力控制。结果就是，备库上的查询耗费了大量的 CPU 资源，影响了同步速度，造成主备延迟

解决方式：

• 一主多从。除了备库外，可以多接几个从库，让这些从库来分担读的压力。
• 通过 binlog 输出到外部系统，比如 Hadoop 这类系统，让外部系统提供统计类查询的能力。

• 大事务：主库上必须等事务执行完成才会写入 binlog，再传给备库。所以，如果一个主库上的语句执行 10 分钟，那这个事务很可能就会导致从库延迟 10 分钟
• 大表DDL

主备切换方式

可靠性优先策略

1. 判断备库 B 现在的 seconds_behind_master，如果小于某个值（比如 5 秒）继续下一步，否则持续重试这一步
2. 把主库 A 改成只读状态，即把 readonly 设置为 true
3. 判断备库 B 的 seconds_behind_master 的值，直到这个值变成 0 为止
4. 把备库 B 改成可读写状态，也就是把 readonly 设置为 false
5. 把业务请求切到备库 B

这个切换流程中是有不可用时间的。因为在步骤 2 之后，主库 A 和备库 B 都处于 readonly 状态，也就是说这时系统处于不可写状态，直到步骤 5 完成后才能恢复

在这个不可用状态中，比较耗费时间的是步骤 3，可能需要耗费好几秒的时间。这也是为什么需要在步骤 1 先做判断，确保 seconds_behind_master 的值足够小

可用性优先策略

不等主备数据同步，直接把连接切到备库 B，并且让备库 B 可以读写，那么系统几乎就没有不可用时间了

可用性优先策略，且 binlog_format=mixed

1. 主库 A 执行完 insert 语句，插入了一行数据（4,4），之后开始进行主备切换。
2. 由于主备之间有 5 秒的延迟，所以备库 B 还没来得及应用“插入 c=4”这个中转日志，就开始接收客户端“插入 c=5”的命令。
3. 备库 B 插入了一行数据（4,5），并且把这个 binlog 发给主库 A
4. 备库 B 执行“插入 c=4”这个中转日志，插入了一行数据（5,4）。
5. 而直接在备库 B 执行的“插入 c=5”这个语句，传到主库 A，就插入了一行新数据（5,5）。
6. 最后的结果就是，主库 A 和备库 B 上出现了两行不一致的数据。可以看到，这个数据不一致，是由可用性优先流程导致的。

可用性优先策略，但设置 binlog_format=row

因为 row 格式在记录 binlog 的时候，会记录新插入的行的所有字段值，所以最后只会有一行不一致。而且，两边的主备同步的应用线程会报错 duplicate key error 并停止。也就是说，这种情况下，备库 B 的 (5,4) 和主库 A 的 (5,5) 这两行数据，都不会被对方执行

总结：

1. 使用 row 格式的 binlog 时，数据不一致的问题更容易被发现。
2. 而使用 mixed 或者 statement 格式的 binlog 时，数据很可能悄悄地就不一致了。如果你过了很久才发现数据不一致的问题，很可能这时的数据不一致已经不可查，或者连带造成了更多的数据逻辑不一致。
3. 主备切换的可用性优先策略会导致数据不一致。因此，大多数情况下，建议使用可靠性优先策略。毕竟对数据服务来说的话，数据的可靠性一般还是要优于可用性的。

并行复制

复制时，excecution阶段可以并行执行，binlog flush的时候，按顺序进行；所以主节点和从节点的binlog还是一致的

mysql5.6

按库并行；同一个库的binlog由同一个worker处理，不同库的binlog由不同worker处理

mysql5.7

• DATABASE：默认值，基于库的并行复制方式

• LOGICAL_CLOCK：基于组提交的并行复制方式。

  • 一个组提交的事务都是可以并行回放，因为这些事务都已进入到事务的 Prepare 阶段，则说明事务之间没有任何冲突（否则就不可能提交）

  • 一个组提交的事务都是可以并行回放（配合binary log group commit）；slave机器的relay log中 last_committed相同的事务（sequence_num不同）可以并发执行

    • sequence_number 是自增事务 ID，last_commited 代表上一个提交的事务 ID。
    • 如果两个事务的 last_commited 相同，说明这两个事务是在同一个 Group 内提交的

  mysql5.7.22

  即使主库在串行提交的事务，只有互相不冲突，在备库就可以并行回放。

新增了一个参数 binlog-transaction-dependency-tracking，用来控制是否启用这个新策略。这个参数的可选值有以下三种

• COMMIT_ORDER，表示的就是前面介绍的，根据5.7的组提交策略

• WRITESET，表示的是对于事务涉及更新的每一行，计算出这一行的 hash 值，组成集合 writeset。如果两个事务没有操作相同的行，也就是说它们的 writeset 没有交集，就可以并行

  • hash 值是通过“库名 + 表名 + 索引名 + 值 + （唯一索引、值）”计算出来的
  • writeset 是在主库生成后直接写入到 binlog 里面的，从库执行时需要解析binlog，判断writeset之间是否可以并行
  • WRITESET 是一个 hash 数组，大小由参数 binlog_transaction_dependency_history_size 决定
• WRITESET_SESSION，在 WRITESET 方式的基础上，保证同一个 session 内的事务不可并行

主从切换

在传统的复制里面，当发生故障，需要主从切换，需要找到 Binlog 和 位点信息，恢复完成数据之后将主节点指向新的主节点。在 MySQL 5.6 里面，提供了新的数据恢复思路，只需要知道主节点的 IP、端口以及账号密码就行，因为复制是自动的，MySQL 会通过内部机制 GTID 自动找点同步

GTID (global transaction identifier)

全局事务 ID，一个事务对应一个 GTID，保证了在每个在主库上提交的事务在集群中有一个唯一的 ID

• GTID = source_id:transaction_id

source_id 正常即是 server_uuid，在第一次启动时生成(函数 generate_server_uuid)，并持久化到 DATADIR/auto.cnf 文件里。
transaction_id 是顺序化的序列号(sequence number)，在每台 MySQL 服务器上都是从 1 开始自增长的序列，是事务的唯一标识。

• GTID 的生成受参数gtid_next的值控制。
  在 Master 上，gtid_next默认是 AUTOMATIC,即 GTID 在每次事务提交时自动生成。它从当前已执行的 GTID 集合(即 gtid_executed)中，找一个大于 0 的未使用的最小值作为下个事务 GTID。在实际的更新事务记录之前将 GTID 写入到 Binlog。也可以设置gtid_next为一个指定的值。通过 set gtid_next='current_gtid’指定为 current_gtid，那么就有两种可能：

  • a. 如果 current_gtid 已经存在于实例的 GTID 集合中，接下来执行的这个事务会直接被系统忽略；
  • b. 如果 current_gtid 没有存在于实例的 GTID 集合中，就将这个 current_gtid 分配给接下来要执行的事务。
• 注意，一个 current_gtid 只能给一个事务使用。这个事务提交后，如果要执行下一个事务，就要执行 set 命令，把 gtid_next 设置成另外一个 gtid 或者 automatic
  每个 MySQL 实例都维护了一个 GTID 集合，用来对应“这个实例执行过的所有事务”
• GTID 方便实现主从之间的 failover（主从切换），不用一步一步的去定位 Binlog日志文件和查找 Binlog 的位点信息
• GTID 模式的启动也很简单，我们只需要在启动一个 MySQL 实例的时候，加上参数 gtid_mode=on 和 enforce_gtid_consistency=on 就可以了

在 Slave 上，从 Binlog 先读取到主库的 GTID(即 set gtid_next 记录)，而后执行的事务采用该 GTID。

• 在原来基于日志的复制中，从库需要告知主库要从哪个偏移量进行增量同步， 如果指定错误会造成数据的遗漏，从而造成数据的不一致。
• 而基于 GTID 的复制中，从库会告知主库已经执行的事务的 GTID 的值，然后主库会将所有未执行的事务的 GTID 的列表返回给从库，并且可以保证同一个事务只在指定的从库执行一次，通过全局的事务 ID 确定从库要执行的事务的方式代替了以前需要用 Binlog 和 位点确定从库要执行的事务的方式

步骤

• master 更新数据时，会在事务前产生 GTID，一同记录到 Binlog 日志中
• slave 端的 I/O 线程将变更的 Binlog，写入到本地的 relay log 中,读取值是根据gitd_next变量，告诉我们 slave 下一个执行哪个 GTID
• SQL 线程从 relay log 中获取 GTID，然后对比 slave 端的 Binlog 是否有记录。如果有记录，说明该 GTID 的事务已经执行，slave 会忽略
• 如果没有记录，slave 就会从 relay log 中执行该 GTID 的事务，并记录到 Binlog

从库切换主备步骤

A是曾经的主库，A‘是A的备库，它们互为主备；B是从库，最开始它从A同步数据，现在需要从A‘同步数据

1. 把现在这个时刻，实例 A’的 GTID 集合记为 set_a
2. 实例 B 的 GTID 集合记为 set_b
3. 在实例 B 上执行 start slave 命令，实例 B 指定主库 A’，基于主备协议建立连接
4. 实例 B 把 set_b 发给主库 A’。

5. 实例 A’算出 set_a 与 set_b 的差集，也就是所有存在于 set_a，但是不存在于 set_b 的 GTID 的集合，判断 A’本地是否包含了这个差集需要的所有 binlog 事务

   1. 如果不包含，表示 A’已经把实例 B 需要的 binlog 给删掉了，直接返回错误；
   2. 如果确认全部包含，A’从自己的 binlog 文件里面，找出第一个不在 set_b 的事务，发给 B；之后就从这个事务开始，往后读文件，按顺序取 binlog 发给 B 去执行

参考资料

极客时间，mysql实战45讲