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讲

你可能感兴趣的