课程B站大学记录学习极客时间团队MySQL45讲进阶数据分析和数据处理MySQL主库和备库备库为什么会延迟好几个小时一、问题背景coordinator 分发的两点基本要求二、MySQL 5.5按表分发 按行分发2.1 按表分发策略2.2 按行分发策略三、MySQL 5.6按库并行四、MariaDB基于组提交(commit_id)的并行复制五、MySQL 5.7LOGICAL_CLOCK 策略六、MySQL 5.7.22基于 WRITESET 的并行复制主库出问题了从库怎么办一、问题背景二、基于位点的主备切换取同步位点的方法为什么不精确三、GTID基本概念基本用法示例四、基于 GTID 的主备切换五、GTID 与在线 DDL六、核心总结实践是检验真理的唯一标准备库为什么会延迟好几个小时一、问题背景不论是偶发性的查询压力还是备份对备库延迟的影响一般是分钟级的而且在备库恢复正常以后都能够追上来。但是如果备库执行日志的速度持续低于主库生成日志的速度那这个延迟就有可能成了小时级。而且对于一个压力持续比较高的主库来说备库很可能永远都追不上主库的节奏。这就涉及到今天的话题备库并行复制能力。谈到主备的并行复制能力我们要关注的是图中黑色的两个箭头一个代表客户端写入主库另一个代表备库上sql_thread执行中转日志relay log。如果用箭头的粗细来代表并行度的话真实情况就如图1所示第一个箭头要明显粗于第二个箭头。主库影响并发度的原因是各种锁。由于 InnoDB 支持行锁除了所有并发事务都在更新同一行的极端场景外对业务并发度的支持很友好。并发压测线程32通常比单线程总体吞吐量高。备库日志的执行就是备库上sql_thread更新数据的逻辑。如果用单线程就会导致备库应用日志不够快造成主备延迟。在官方 5.6 版本之前MySQL 只支持单线程复制由此在主库并发高、TPS 大的场景下会出现严重的主备延迟问题。从单线程复制到最新版本的多线程复制中间经历了好几个版本。核心思想把所有多线程复制机制都是要把只有一个线程的sql_thread拆成多个线程。图2中coordinator就是原来的sql_thread不过现在它不再直接更新数据了只负责读取中转日志和分发事务真正更新数据的变成了worker线程。worker线程的个数由参数slave_parallel_workers决定。根据经验这个值设置为8~16之间最好32核物理机的情况毕竟备库还可能要提供读查询不能把 CPU 都吃光。coordinator 分发的两点基本要求不能造成更新覆盖更新同一行的两个事务必须被分发到同一个 worker。同一个事务不能拆开必须放到同一个 worker。反例思考事务能否按轮询分给各 worker不行——CPU 调度可能导致后分发的先执行若两事务更新同一行主备执行顺序相反导致主备不一致。同一事务的多个更新能否分给不同 worker也不行——会看到更新了一半的中间结果破坏隔离性。各版本多线程复制都遵循这两条原则。二、MySQL 5.5按表分发 按行分发官方 5.5 不支持并行复制。作者针对严重的主备延迟问题先后写了两个版本的并行策略按表分发和按行分发。2.1 按表分发策略基本思路如果两个事务更新不同的表就可以并行。因为数据存储在表里按表分发可以保证两个 worker 不会更新同一行。跨表事务需把涉及的表一起考虑。每个 worker 对应一个 hash 表key 是库名.表名value 表示队列中有多少事务修改这个表。分配规则若跟所有 worker 都不冲突→ 分配给最空闲的 worker若跟多于一个 worker 冲突→ coordinator等待直到冲突 worker 只剩 1 个若只跟一个 worker 冲突→ 分配给这个 worker。优缺点在多个表负载均匀的场景效果很好但热点表所有更新都涉及某一张表时所有事务都分到同一 worker退化为单线程。2.2 按行分发策略解决热点表的并行复制问题。核心思路两个事务没有更新相同的行就可以并行。显然这要求 binlog 格式必须是row。此时 key 是库名 表名 唯一键的值。仅有主键 id 还不够还需考虑唯一索引CREATETABLEt1(idint(11)NOTNULL,aint(11)DEFAULTNULL,bint(11)DEFAULTNULL,PRIMARYKEY(id),UNIQUEKEYa(a))ENGINEInnoDB;INSERTINTOt1VALUES(1,1,1),(2,2,2),(3,3,3),(4,4,4),(5,5,5);若两个事务分别更新id1和id2主键值不同但若分到不同 worker可能因执行顺序导致唯一键冲突如a1还未更新完。因此 hash 表 key 还要包含唯一索引即库名表名索引名值。约束条件恰好也是 DBA 线上规范binlog 必须能解析出表名、主键、唯一索引值 → 格式须为row表必须有主键不能有外键级联更新不记录在 binlog冲突检测不准。大事务退化操作很多行时按行策略会耗费大量内存和 CPU。超过行数阈值如 10 万行时退化为单线程coordinator hold 住事务等待所有 worker 执行完变为空队列coordinator 直接执行该事务恢复并行模式。三、MySQL 5.6按库并行官方 5.6 支持并行复制粒度是按库并行。hash 表的 key 就是数据库名。优点构造 hash 值快只需库名且实例 DB 数不多不会出现百万级项不要求 binlog 格式statement 也能拿到库名。缺点若所有表都在同一个 DB或不同 DB 热点差异大业务库 vs 配置库就没有并行效果。理论上可拆 DB 强行使用但因需移动数据用得不多。四、MariaDB基于组提交(commit_id)的并行复制利用 redo log 组提交group commit优化特性能在同一组里提交的事务一定不会修改同一行主库上可并行执行的事务备库上也一定可并行执行。实现同一组一起提交的事务有相同的commit_id下一组commit_id1commit_id写入 binlog备库上相同commit_id的事务分发到多个 worker 执行整组执行完coordinator 再取下一批。这个策略相当惊艳——目标是**“模拟主库的并行模式”**而非分析 binlog 拆分。但问题未真正模拟主库并发度。主库上一组事务提交时下一组事务是同时处于执行中的而 MariaDB 策略在备库要等整组执行完下一组才能开始吞吐量受限。此外大事务会拖后腿若 trx2 是超大事务trx1/trx3 完成后只能等 trx2期间只有一个 worker 工作浪费资源。即便如此该策略仍是优雅的创新。五、MySQL 5.7LOGICAL_CLOCK 策略MySQL 5.7 提供类似功能由slave-parallel-type控制DATABASE使用 5.6 的按库策略LOGICAL_CLOCK类似 MariaDB但做了并行度优化。核心问题同时处于执行状态的所有事务是否可并行不能——其中可能有因锁冲突而等待的事务分到不同 worker 会导致主备不一致。MariaDB 策略核心是处于 commit 状态的事务可并行已通过锁冲突检验。回顾两阶段提交其实不用等到 commit只要到达redo log prepare 阶段就表示已通过锁冲突检验。因此 5.7 策略思想同时处于prepare 状态的事务备库可并行处于prepare 状态的事务与处于commit 状态的事务之间备库也可并行。结合 binlog 组提交的两个参数故意拉长 write→fsync 时间制造更多同时 prepare的事务提升备库并行度binlog_group_commit_sync_delay延迟多少微秒才调用 fsyncbinlog_group_commit_sync_no_delay_count累积多少次才调用 fsync。这两个参数既可故意让主库提交慢些又可让备库执行快些。处理备库延迟时可调整它们提升并行度。六、MySQL 5.7.22基于 WRITESET 的并行复制2018年4月发布的 5.7.22 新增基于WRITESET的并行复制参数binlog-transaction-dependency-tracking取值含义COMMIT_ORDER根据同时进入 prepare/commit 判断是否并行WRITESET对事务更新的每一行计算 hash 组成 writeset无交集即可并行WRITESET_SESSION在 WRITESET 基础上保证主库同一线程先后执行的事务在备库顺序一致hash 值通过库名表名索引名值计算。与 5.5 按行分发类似但官方实现优势明显writeset 在主库生成后直接写入 binlog备库无需解析 event 行数据省计算量不需要扫整个事务 binlog 来决定分发更省内存分发策略不依赖 binlog 内容statement 格式也可用。对于表没主键外键约束场景WRITESET 也无法并行会退化为单线程。主库出问题了从库怎么办一、问题背景前面的文章介绍了 MySQL 主备复制的基础结构但都是一主一备的结构。大多数互联网应用场景读多写少业务发展先遇到的是读性能瓶颈。在数据库层解决读性能问题就要用到一主多从架构。本文先聊一主多从的切换正确性下一篇再聊一主多从的查询逻辑正确性读写分离。基本的一主多从结构虚线箭头表示主备关系A 与 A’ 互为主备从库 B、C、D 指向主库 A一般用于读写分离主库负责所有写入和一部分读从库分担其余读请求。主库故障后的主备切换结果相比一主一备一主多从切换完成后A’ 成为新主库从库 B、C、D 都要改接到 A’。正是多了从库重新指向这个过程切换复杂性相应增加。二、基于位点的主备切换把节点 B 设置为节点 A’ 的从库需要执行CHANGE MASTER命令CHANGE MASTERTOMASTER_HOST$host_name MASTER_PORT$port MASTER_USER$user_name MASTER_PASSWORD$password MASTER_LOG_FILE$master_log_name MASTER_LOG_POS$master_log_pos前 4 个参数新主库 A’ 的 IP、端口、用户名、密码后 2 个参数MASTER_LOG_FILE/MASTER_LOG_POS同步位点主库对应的 binlog 文件名和偏移量。问题节点 B 原本是 A 的从库本地记录的是 A 的位点而相同日志在 A 与 A’ 上的位点不同。因此 B 切换时需要先经过找同步位点逻辑而这个位点很难精确取到只能取个大概。取同步位点的方法为保证切换过程不丢数据找位点时要稍微往前再跳过从库 B 上已执行的事务等待新主库 A’ 把中转日志relay log全部同步完成在 A’ 上执行show master status得到当前最新的 File 和 Position取原主库 A 故障的时刻 T用mysqlbinlog解析 A’ 的 File得到 T 时刻的位点。mysqlbinlog File --stop-datetimeT --start-datetimeT图中end_log_pos后面的值123就是 A’ 在 T 时刻写入新 binlog 的位置可把它作为$master_log_pos用在 B 的CHANGE MASTER命令里。为什么不精确设想一种情况T 时刻主库 A 已执行完一个 insert 插入行 R并且 binlog 已传给 A’ 和 B传完瞬间 A 掉电。此时状态从库 B 上R 已存在已同步 binlog新主库 A’ 上R 也已存在日志写在 123 之后B 执行CHANGE MASTER指向 A’ 的 File 的 123 位置会再把插入 R的 binlog 同步到 B 执行。→ B 的同步线程报Duplicate entry id_of_R for key PRIMARY主键冲突停止同步。因此切换时通常要主动跳过这些错误两种常用方法方法一跳过事务setglobalsql_slave_skip_counter1;startslave;切换过程可能重复执行多个事务需要在 B 刚接到 A’ 时持续观察每次报错就执行一次跳过命令直到不再停下。方法二设置跳过指定错误-- 1062插入时唯一键冲突1032删除时找不到行setglobalslave_skip_errors1032,1062;注意这种直接跳过指定错误的方法仅用于主备切换时找不到精确同步位点的场景前提是清楚此时跳过 1032/1062 是无损的。主备同步关系建立并稳定运行后应把该参数置空避免后续真的主从不一致也被跳过。三、GTIDsql_slave_skip_counter和slave_skip_errors虽然能建立主备关系但操作复杂、易出错。MySQL 5.6 引入GTID彻底解决了找同步位点的困难。基本概念GTIDGlobal Transaction Identifier全局事务 ID事务提交时生成是该事务的唯一标识。格式GTID server_uuid : gnoserver_uuid实例首次启动时自动生成全局唯一gno整数初始 1每次提交事务时分配并加 1。官方文档写成source_id:transaction_id。这里transaction_id易误解——事务 id 在执行过程中就分配回滚也递增而 gno 在提交时才分配。GTID 通常是连续的用 gno 更易理解。开启 GTID 模式gtid_modeon enforce_gtid_consistencyon在 GTID 模式下每个事务与一个 GTID 一一对应gtid_next决定生成方式gtid_nextautomatic默认MySQL 分配server_uuid:gno记录 binlog 时先写一行SET SESSION.GTID_NEXTserver_uuid:gno;将该 GTID 加入本实例的 GTID 集合。gtid_next指定值如set gtid_nextcurrent_gtid若该 GTID 已存在于实例 GTID 集合中 → 后续事务被忽略若不存在 → 分配给后续事务不再生成新 GTIDgno 不加 1。每个实例维护一个 GTID 集合对应该实例执行过的所有事务。基本用法示例实例 X 中建表并插入数据CREATETABLEt(idint(11)NOTNULL,cint(11)DEFAULTNULL,PRIMARYKEY(id))ENGINEInnoDB;INSERTINTOtVALUES(1,1);事务BEGIN前有一条SET SESSION.GTID_NEXT命令。若实例 X 有从库同步执行时先执行这两个 SET从库的 GTID 集合就被加入这两个 GTID。场景实例 X 是 Y 的从库Y 上执行insert into t values(1,1)其 GTID 为aaaaaaaa-cccc-dddd-eeee-fffffffffff:10。X 同步该事务会出现主键冲突处理方法setgtid_nextaaaaaaaa-cccc-dddd-eeee-fffffffffff:10;begin;commit;setgtid_nextautomatic;startslave;前三条语句通过提交空事务把这个 GTID 加入实例 X 的 GTID 集合。start slave前set gtid_nextautomatic恢复默认分配行为新事务继续分配 gno3。之后同步线程再执行 Y 传来的事务时因该 GTID 已存在X 会直接跳过不再主键冲突。四、基于 GTID 的主备切换GTID 模式下备库 B 设为新主库 A’ 的从库CHANGE MASTERTOMASTER_HOST$host_name MASTER_PORT$port MASTER_USER$user_name MASTER_PASSWORD$password master_auto_position1;-- 1 表示使用 GTID 协议MASTER_LOG_FILE和MASTER_LOG_POS不再需要指定找位点的痛点消失。切换逻辑设 A’ 的 GTID 集合为set_aB 的为set_bB 指定主库 A’基于主备协议建立连接B 把set_b发给 A’A’ 计算set_a - set_b存在于 A’、不存在于 B 的 GTID 集合若 A’ 本地不包含差集所需的全部 binlog → 报错所需 binlog 已被删除若全部包含→ 从 binlog 中找出第一个不在set_b的事务发给 B从该事务开始顺序取 binlog 发给 B 执行。设计思想GTID 主备关系要求主库发给备库的日志是完整的若 B 需要的日志已不存在A’ 拒绝发送。这与基于位点的协议不同——位点协议由备库指定位点、主库照发不做完整性判断。一主多从切换时从库 B、C、D 只需分别执行CHANGE MASTER指向 A’ 即可。严格说不是不需要找位点而是找位点工作在 A’ 内部自动完成对 HA 系统开发者非常友好。切换后 GTID 集合A’ 自己生成的 binlog 为server_uuid_of_A:1-MB 原本为server_uuid_of_A:1-N切换后变为server_uuid_of_A:1-N, server_uuid_of_A:1-M。A’ 此前也是 A 的备库故 A’ 与 B 的 GTID 集合一致达到预期。五、GTID 与在线 DDL第22讲提到业务高峰期因索引缺失导致慢查询可先在备库加索引再切换。双 M 结构下为避免 DDL 传回主库曾用set sql_log_binoff关闭 binlog——但这会导致库里有索引、binlog 却没记录的数据/日志不一致问题。用 GTID 可更好解决。实例 X当前主库与 Y 互为主备均开启 GTID。切换流程在 X 上执行stop slave在 Y 上执行 DDL无需关闭 binlog记下该 DDL 的 GTID server_uuid_of_Y:gno到 X 上执行setGTID_NEXTserver_uuid_of_Y:gno;begin;commit;setgtid_nextautomatic;startslave;既让 Y 的更新有 binlog 记录又确保不会在 X 上真正执行这条 DDL。之后完成主备切换再照此流程执行一遍即可。六、核心总结维度基于位点基于 GTID找同步位点需手动mysqlbinlog估算不精确自动完成A’ 内部算差集切换复杂度需跳过 1062/1032易出错master_auto_position1简单日志完整性主库不判断按位点照发主库判断完整性缺失则报错适用版本全部5.6建议兼容旧版推荐优先使用结论若 MySQL 版本支持 GTID建议尽量用 GTID 模式做一主多从切换。实践是检验真理的唯一标准 MySQL主库出问题了，从库怎么办？备库为什么会延迟好几个小时？
课程B站大学记录学习极客时间团队MySQL45讲进阶数据分析和数据处理MySQL主库和备库备库为什么会延迟好几个小时一、问题背景coordinator 分发的两点基本要求二、MySQL 5.5按表分发 按行分发2.1 按表分发策略2.2 按行分发策略三、MySQL 5.6按库并行四、MariaDB基于组提交(commit_id)的并行复制五、MySQL 5.7LOGICAL_CLOCK 策略六、MySQL 5.7.22基于 WRITESET 的并行复制主库出问题了从库怎么办一、问题背景二、基于位点的主备切换取同步位点的方法为什么不精确三、GTID基本概念基本用法示例四、基于 GTID 的主备切换五、GTID 与在线 DDL六、核心总结实践是检验真理的唯一标准备库为什么会延迟好几个小时一、问题背景不论是偶发性的查询压力还是备份对备库延迟的影响一般是分钟级的而且在备库恢复正常以后都能够追上来。但是如果备库执行日志的速度持续低于主库生成日志的速度那这个延迟就有可能成了小时级。而且对于一个压力持续比较高的主库来说备库很可能永远都追不上主库的节奏。这就涉及到今天的话题备库并行复制能力。谈到主备的并行复制能力我们要关注的是图中黑色的两个箭头一个代表客户端写入主库另一个代表备库上sql_thread执行中转日志relay log。如果用箭头的粗细来代表并行度的话真实情况就如图1所示第一个箭头要明显粗于第二个箭头。主库影响并发度的原因是各种锁。由于 InnoDB 支持行锁除了所有并发事务都在更新同一行的极端场景外对业务并发度的支持很友好。并发压测线程32通常比单线程总体吞吐量高。备库日志的执行就是备库上sql_thread更新数据的逻辑。如果用单线程就会导致备库应用日志不够快造成主备延迟。在官方 5.6 版本之前MySQL 只支持单线程复制由此在主库并发高、TPS 大的场景下会出现严重的主备延迟问题。从单线程复制到最新版本的多线程复制中间经历了好几个版本。核心思想把所有多线程复制机制都是要把只有一个线程的sql_thread拆成多个线程。图2中coordinator就是原来的sql_thread不过现在它不再直接更新数据了只负责读取中转日志和分发事务真正更新数据的变成了worker线程。worker线程的个数由参数slave_parallel_workers决定。根据经验这个值设置为8~16之间最好32核物理机的情况毕竟备库还可能要提供读查询不能把 CPU 都吃光。coordinator 分发的两点基本要求不能造成更新覆盖更新同一行的两个事务必须被分发到同一个 worker。同一个事务不能拆开必须放到同一个 worker。反例思考事务能否按轮询分给各 worker不行——CPU 调度可能导致后分发的先执行若两事务更新同一行主备执行顺序相反导致主备不一致。同一事务的多个更新能否分给不同 worker也不行——会看到更新了一半的中间结果破坏隔离性。各版本多线程复制都遵循这两条原则。二、MySQL 5.5按表分发 按行分发官方 5.5 不支持并行复制。作者针对严重的主备延迟问题先后写了两个版本的并行策略按表分发和按行分发。2.1 按表分发策略基本思路如果两个事务更新不同的表就可以并行。因为数据存储在表里按表分发可以保证两个 worker 不会更新同一行。跨表事务需把涉及的表一起考虑。每个 worker 对应一个 hash 表key 是库名.表名value 表示队列中有多少事务修改这个表。分配规则若跟所有 worker 都不冲突→ 分配给最空闲的 worker若跟多于一个 worker 冲突→ coordinator等待直到冲突 worker 只剩 1 个若只跟一个 worker 冲突→ 分配给这个 worker。优缺点在多个表负载均匀的场景效果很好但热点表所有更新都涉及某一张表时所有事务都分到同一 worker退化为单线程。2.2 按行分发策略解决热点表的并行复制问题。核心思路两个事务没有更新相同的行就可以并行。显然这要求 binlog 格式必须是row。此时 key 是库名 表名 唯一键的值。仅有主键 id 还不够还需考虑唯一索引CREATETABLEt1(idint(11)NOTNULL,aint(11)DEFAULTNULL,bint(11)DEFAULTNULL,PRIMARYKEY(id),UNIQUEKEYa(a))ENGINEInnoDB;INSERTINTOt1VALUES(1,1,1),(2,2,2),(3,3,3),(4,4,4),(5,5,5);若两个事务分别更新id1和id2主键值不同但若分到不同 worker可能因执行顺序导致唯一键冲突如a1还未更新完。因此 hash 表 key 还要包含唯一索引即库名表名索引名值。约束条件恰好也是 DBA 线上规范binlog 必须能解析出表名、主键、唯一索引值 → 格式须为row表必须有主键不能有外键级联更新不记录在 binlog冲突检测不准。大事务退化操作很多行时按行策略会耗费大量内存和 CPU。超过行数阈值如 10 万行时退化为单线程coordinator hold 住事务等待所有 worker 执行完变为空队列coordinator 直接执行该事务恢复并行模式。三、MySQL 5.6按库并行官方 5.6 支持并行复制粒度是按库并行。hash 表的 key 就是数据库名。优点构造 hash 值快只需库名且实例 DB 数不多不会出现百万级项不要求 binlog 格式statement 也能拿到库名。缺点若所有表都在同一个 DB或不同 DB 热点差异大业务库 vs 配置库就没有并行效果。理论上可拆 DB 强行使用但因需移动数据用得不多。四、MariaDB基于组提交(commit_id)的并行复制利用 redo log 组提交group commit优化特性能在同一组里提交的事务一定不会修改同一行主库上可并行执行的事务备库上也一定可并行执行。实现同一组一起提交的事务有相同的commit_id下一组commit_id1commit_id写入 binlog备库上相同commit_id的事务分发到多个 worker 执行整组执行完coordinator 再取下一批。这个策略相当惊艳——目标是**“模拟主库的并行模式”**而非分析 binlog 拆分。但问题未真正模拟主库并发度。主库上一组事务提交时下一组事务是同时处于执行中的而 MariaDB 策略在备库要等整组执行完下一组才能开始吞吐量受限。此外大事务会拖后腿若 trx2 是超大事务trx1/trx3 完成后只能等 trx2期间只有一个 worker 工作浪费资源。即便如此该策略仍是优雅的创新。五、MySQL 5.7LOGICAL_CLOCK 策略MySQL 5.7 提供类似功能由slave-parallel-type控制DATABASE使用 5.6 的按库策略LOGICAL_CLOCK类似 MariaDB但做了并行度优化。核心问题同时处于执行状态的所有事务是否可并行不能——其中可能有因锁冲突而等待的事务分到不同 worker 会导致主备不一致。MariaDB 策略核心是处于 commit 状态的事务可并行已通过锁冲突检验。回顾两阶段提交其实不用等到 commit只要到达redo log prepare 阶段就表示已通过锁冲突检验。因此 5.7 策略思想同时处于prepare 状态的事务备库可并行处于prepare 状态的事务与处于commit 状态的事务之间备库也可并行。结合 binlog 组提交的两个参数故意拉长 write→fsync 时间制造更多同时 prepare的事务提升备库并行度binlog_group_commit_sync_delay延迟多少微秒才调用 fsyncbinlog_group_commit_sync_no_delay_count累积多少次才调用 fsync。这两个参数既可故意让主库提交慢些又可让备库执行快些。处理备库延迟时可调整它们提升并行度。六、MySQL 5.7.22基于 WRITESET 的并行复制2018年4月发布的 5.7.22 新增基于WRITESET的并行复制参数binlog-transaction-dependency-tracking取值含义COMMIT_ORDER根据同时进入 prepare/commit 判断是否并行WRITESET对事务更新的每一行计算 hash 组成 writeset无交集即可并行WRITESET_SESSION在 WRITESET 基础上保证主库同一线程先后执行的事务在备库顺序一致hash 值通过库名表名索引名值计算。与 5.5 按行分发类似但官方实现优势明显writeset 在主库生成后直接写入 binlog备库无需解析 event 行数据省计算量不需要扫整个事务 binlog 来决定分发更省内存分发策略不依赖 binlog 内容statement 格式也可用。对于表没主键外键约束场景WRITESET 也无法并行会退化为单线程。主库出问题了从库怎么办一、问题背景前面的文章介绍了 MySQL 主备复制的基础结构但都是一主一备的结构。大多数互联网应用场景读多写少业务发展先遇到的是读性能瓶颈。在数据库层解决读性能问题就要用到一主多从架构。本文先聊一主多从的切换正确性下一篇再聊一主多从的查询逻辑正确性读写分离。基本的一主多从结构虚线箭头表示主备关系A 与 A’ 互为主备从库 B、C、D 指向主库 A一般用于读写分离主库负责所有写入和一部分读从库分担其余读请求。主库故障后的主备切换结果相比一主一备一主多从切换完成后A’ 成为新主库从库 B、C、D 都要改接到 A’。正是多了从库重新指向这个过程切换复杂性相应增加。二、基于位点的主备切换把节点 B 设置为节点 A’ 的从库需要执行CHANGE MASTER命令CHANGE MASTERTOMASTER_HOST$host_name MASTER_PORT$port MASTER_USER$user_name MASTER_PASSWORD$password MASTER_LOG_FILE$master_log_name MASTER_LOG_POS$master_log_pos前 4 个参数新主库 A’ 的 IP、端口、用户名、密码后 2 个参数MASTER_LOG_FILE/MASTER_LOG_POS同步位点主库对应的 binlog 文件名和偏移量。问题节点 B 原本是 A 的从库本地记录的是 A 的位点而相同日志在 A 与 A’ 上的位点不同。因此 B 切换时需要先经过找同步位点逻辑而这个位点很难精确取到只能取个大概。取同步位点的方法为保证切换过程不丢数据找位点时要稍微往前再跳过从库 B 上已执行的事务等待新主库 A’ 把中转日志relay log全部同步完成在 A’ 上执行show master status得到当前最新的 File 和 Position取原主库 A 故障的时刻 T用mysqlbinlog解析 A’ 的 File得到 T 时刻的位点。mysqlbinlog File --stop-datetimeT --start-datetimeT图中end_log_pos后面的值123就是 A’ 在 T 时刻写入新 binlog 的位置可把它作为$master_log_pos用在 B 的CHANGE MASTER命令里。为什么不精确设想一种情况T 时刻主库 A 已执行完一个 insert 插入行 R并且 binlog 已传给 A’ 和 B传完瞬间 A 掉电。此时状态从库 B 上R 已存在已同步 binlog新主库 A’ 上R 也已存在日志写在 123 之后B 执行CHANGE MASTER指向 A’ 的 File 的 123 位置会再把插入 R的 binlog 同步到 B 执行。→ B 的同步线程报Duplicate entry id_of_R for key PRIMARY主键冲突停止同步。因此切换时通常要主动跳过这些错误两种常用方法方法一跳过事务setglobalsql_slave_skip_counter1;startslave;切换过程可能重复执行多个事务需要在 B 刚接到 A’ 时持续观察每次报错就执行一次跳过命令直到不再停下。方法二设置跳过指定错误-- 1062插入时唯一键冲突1032删除时找不到行setglobalslave_skip_errors1032,1062;注意这种直接跳过指定错误的方法仅用于主备切换时找不到精确同步位点的场景前提是清楚此时跳过 1032/1062 是无损的。主备同步关系建立并稳定运行后应把该参数置空避免后续真的主从不一致也被跳过。三、GTIDsql_slave_skip_counter和slave_skip_errors虽然能建立主备关系但操作复杂、易出错。MySQL 5.6 引入GTID彻底解决了找同步位点的困难。基本概念GTIDGlobal Transaction Identifier全局事务 ID事务提交时生成是该事务的唯一标识。格式GTID server_uuid : gnoserver_uuid实例首次启动时自动生成全局唯一gno整数初始 1每次提交事务时分配并加 1。官方文档写成source_id:transaction_id。这里transaction_id易误解——事务 id 在执行过程中就分配回滚也递增而 gno 在提交时才分配。GTID 通常是连续的用 gno 更易理解。开启 GTID 模式gtid_modeon enforce_gtid_consistencyon在 GTID 模式下每个事务与一个 GTID 一一对应gtid_next决定生成方式gtid_nextautomatic默认MySQL 分配server_uuid:gno记录 binlog 时先写一行SET SESSION.GTID_NEXTserver_uuid:gno;将该 GTID 加入本实例的 GTID 集合。gtid_next指定值如set gtid_nextcurrent_gtid若该 GTID 已存在于实例 GTID 集合中 → 后续事务被忽略若不存在 → 分配给后续事务不再生成新 GTIDgno 不加 1。每个实例维护一个 GTID 集合对应该实例执行过的所有事务。基本用法示例实例 X 中建表并插入数据CREATETABLEt(idint(11)NOTNULL,cint(11)DEFAULTNULL,PRIMARYKEY(id))ENGINEInnoDB;INSERTINTOtVALUES(1,1);事务BEGIN前有一条SET SESSION.GTID_NEXT命令。若实例 X 有从库同步执行时先执行这两个 SET从库的 GTID 集合就被加入这两个 GTID。场景实例 X 是 Y 的从库Y 上执行insert into t values(1,1)其 GTID 为aaaaaaaa-cccc-dddd-eeee-fffffffffff:10。X 同步该事务会出现主键冲突处理方法setgtid_nextaaaaaaaa-cccc-dddd-eeee-fffffffffff:10;begin;commit;setgtid_nextautomatic;startslave;前三条语句通过提交空事务把这个 GTID 加入实例 X 的 GTID 集合。start slave前set gtid_nextautomatic恢复默认分配行为新事务继续分配 gno3。之后同步线程再执行 Y 传来的事务时因该 GTID 已存在X 会直接跳过不再主键冲突。四、基于 GTID 的主备切换GTID 模式下备库 B 设为新主库 A’ 的从库CHANGE MASTERTOMASTER_HOST$host_name MASTER_PORT$port MASTER_USER$user_name MASTER_PASSWORD$password master_auto_position1;-- 1 表示使用 GTID 协议MASTER_LOG_FILE和MASTER_LOG_POS不再需要指定找位点的痛点消失。切换逻辑设 A’ 的 GTID 集合为set_aB 的为set_bB 指定主库 A’基于主备协议建立连接B 把set_b发给 A’A’ 计算set_a - set_b存在于 A’、不存在于 B 的 GTID 集合若 A’ 本地不包含差集所需的全部 binlog → 报错所需 binlog 已被删除若全部包含→ 从 binlog 中找出第一个不在set_b的事务发给 B从该事务开始顺序取 binlog 发给 B 执行。设计思想GTID 主备关系要求主库发给备库的日志是完整的若 B 需要的日志已不存在A’ 拒绝发送。这与基于位点的协议不同——位点协议由备库指定位点、主库照发不做完整性判断。一主多从切换时从库 B、C、D 只需分别执行CHANGE MASTER指向 A’ 即可。严格说不是不需要找位点而是找位点工作在 A’ 内部自动完成对 HA 系统开发者非常友好。切换后 GTID 集合A’ 自己生成的 binlog 为server_uuid_of_A:1-MB 原本为server_uuid_of_A:1-N切换后变为server_uuid_of_A:1-N, server_uuid_of_A:1-M。A’ 此前也是 A 的备库故 A’ 与 B 的 GTID 集合一致达到预期。五、GTID 与在线 DDL第22讲提到业务高峰期因索引缺失导致慢查询可先在备库加索引再切换。双 M 结构下为避免 DDL 传回主库曾用set sql_log_binoff关闭 binlog——但这会导致库里有索引、binlog 却没记录的数据/日志不一致问题。用 GTID 可更好解决。实例 X当前主库与 Y 互为主备均开启 GTID。切换流程在 X 上执行stop slave在 Y 上执行 DDL无需关闭 binlog记下该 DDL 的 GTID server_uuid_of_Y:gno到 X 上执行setGTID_NEXTserver_uuid_of_Y:gno;begin;commit;setgtid_nextautomatic;startslave;既让 Y 的更新有 binlog 记录又确保不会在 X 上真正执行这条 DDL。之后完成主备切换再照此流程执行一遍即可。六、核心总结维度基于位点基于 GTID找同步位点需手动mysqlbinlog估算不精确自动完成A’ 内部算差集切换复杂度需跳过 1062/1032易出错master_auto_position1简单日志完整性主库不判断按位点照发主库判断完整性缺失则报错适用版本全部5.6建议兼容旧版推荐优先使用结论若 MySQL 版本支持 GTID建议尽量用 GTID 模式做一主多从切换。实践是检验真理的唯一标准