MySQL高可用实践

毋庸置疑，开源数据库MySQL是当今流行的关系数据库系统之一，无论是在国内还是国外，都具有极高的人气。据统计，在2019年的全球数据库市场中，MySQL以接近40%的使用率高居榜首。在此前提下，作为基础技术支撑的核心组成部分，保持MySQL数据库的高可用性变得极为重要。这是我写这本书的个原因，希望读者在构建自己的MySQL高可用环境时，本书能提供一定的参考和帮助。

网上关于MySQL的资料比比皆是，但大部分都是针对单一具体问题的解答，例如如何配置MySQL双主复制等，全面系统讲述MySQL高可用这方面的资料并不多见。而且，这些资料大多只给出简单的实现方法，缺少相应的理论背景知识和分析验证过程。这是我写这本书的第二个原因，希望能填补缺口，让读者不但知其然，也知其所以然。

从2000年至今的20年里，我一直从事各种数据库相关的应用开发和管理工作，尤其是近年来一直跟MySQL数据库打交道。从简单的单实例，到各种复制，再到集群以及与中间件的配合，在其使用上积累了一些经验和心得。这是我写这本书的第三个原因，希望将它们加以归纳和总结，汇集成书与读者分享。

本书内容

全书共16章，从复制、中间件、集群、存储、优化五个方面介绍MySQL高可用技术。

第1~6章讲述MySQL的复制，是MySQL高可用实现的基础。除了包括原生的异步复制、插件实现的半同步复制，以及MySQL 5.7版本引入的组复制等基本复制技术外，还讨论了几种常见的复制拓扑，以及各种复制的性能对比。在实际应用中，GTID、部分复制、延迟复制都是针对不同场景下常用的与复制密切相关的技术，这部分内容会分别用单独的章节加以说明。

第7~12章给出如何使用中间件构建MySQL高可用环境。具体包括MySQL自己的中间件产品Router和Fabric，以及被广泛采用的MMM、MHA、Keepalived   LVS、Heartbeat   HAProxy等第三方MySQL高可用解决方案。每种产品使用一章的篇幅进行讲解。

第13~14章介绍两种MySQL集群：InnoDB Cluster和Galera Cluster。其中InnoDB Cluster是MySQL官方推出的集群产品，而Galera Cluster是包含在MariaDB中的MySQL多主集群。

第15章讲述利用DRBD技术实现MySQL高可用。前三部分介绍的MySQL高可用解决方案都是基于MySQL自带的各种复制技术。DRBD的实现方式则完全不同，它本身与MySQL复制毫无关系，而是一个用软件实现的镜像块设备内容的存储复制解决方案。

在全书后的第16章，引入四个MySQL高可用环境下的优化案例，分别是：快速生成数字辅助表；将MySQL去重优化到极致；重叠时间段问题之算法优化；快速安全删除大表。严格来说，本章内容并不特指高可用环境下的优化。但这些案例都取自我工作中的真实需求，具有一定的代表性和实用性，既有利于读者借鉴解决实际问题，同时在某种程度上也能增加阅读的趣味。

为力求严谨，书中所涉及产品或技术的知识绝大部分来源于相应的官方文档。正如Linus的那句话：“Talk is cheap，show me the code”，对于每一个过程或结论，都配有详细的实验步骤，用实例辅以佐证，帮助读者加深理解。

源码下载

本书示例源码请扫描右边二维码下载。如果有问题和建议，请联系booksaga@163.com，邮件主题为“MySQL高可用性实践”。

读者对象

本书所定位的读者是DBA、系统管理运维、应用程序开发、架构师等MySQL相关的技术人员。所有的描绘场景与实验环境都是基于Linux操作系统的虚拟机。本书的读者应具有一定的SQL、MySQL、Linux基础。

致谢

在本书编写过程中，得到了很多人的帮助与支持。首先要感谢我所在的公司——优贝在线提供的平台和环境，感谢同事们工作中的鼎力相助。没有那里的工作氛围和团队，也就不会有这本书。感谢清华大学出版社图格事业部的老师和编辑们，他们的辛勤工作使得本书得以尽早与读者见面。感谢CSDN提供的技术分享平台，给我有一个将博客文章整理成书的机会。后，感谢家人对我一如既往的支持。

由于水平有限，错漏之处在所难免，希望读者批评指正。

编者

2020年5月

MySQL数据库是现代软件系统基础应用的核心组成部分，如何保持MySQL的高可用性在系统应用中就变得极为重要。本书从复制、中间件、集群、存储、优化五个方面介绍MySQL高可用技术。 本书分为16章，内容包括异步复制、半同步复制、GTID与复制、复制拓扑与性能、延迟复制与部分复制、组复制、MySQL Router、MySQL Fabric、MMM、MHA、Keepalived   LVS、Heartbeat   HAProxy、InnoDB Cluster、Galera Cluster、DRBD、优化案例等。 本书适合DBA、系统管理运维、应用程序开发、架构师等MySQL相关技术人员作为参考书，也适合作为高等院校和培训学校相关专业的技术培训教材。

王雪迎 ，毕业于中国地质大学计算机专业，高级工程师，20年数据库、数据仓库相关技术工作经验。先后供职于北京现代商业信息技术有限公司、北京在线九州信息技术服务有限公司、华北计算技术研究所、北京优贝在线网络科技有限公司，担任DBA、数据架构师等职位。著有图书《Hadoop构建数据仓库实践》《HAWQ数据仓库与数据挖掘实战》《SQL机器学习库MADlib技术解析》。

第1章  异步复制 / 1

1.1  MySQL异步复制简介 / 1

1.1.1  复制的用途 / 1

1.1.2  复制如何工作 / 2

1.1.3  两阶段提交 / 6

1.2  搭建实验环境 / 8

1.2.1  实验环境说明 / 8

1.2.2  安装MySQL / 8

1.3  配置异步复制 / 12

1.3.1  空库 / 12

1.3.2  脱机 / 16

1.3.3  联机 / 17

1.4  小结 / 20

第2章  半同步复制 / 21

2.1  MySQL半同步复制简介 / 21

2.2  性能提升 / 22

2.2.1  ACK异步化 / 22

2.2.2  控制从库反馈的数量 / 24

2.2.3  二进制日志互斥锁的改进 / 25

2.3  数据一致性 / 26

2.3.1  rpl_semi_sync_master_wait_point配置 / 26

2.3.2  sync_binlog配置 / 28

2.3.3  sync_relay_log配置 / 30

2.4  管理接口 / 32

2.5  配置半同步复制 / 32

2.5.1  安装插件 / 32

2.5.2  启用半同步复制 / 34

2.5.3  监控半同步复制 / 35

2.6  测试 / 37

2.6.1  正常提交事务 / 37

2.6.2  回滚事务 / 38

2.6.3  rpl_semi_sync_master_wait_no_slave与从库数量 / 40

2.7  小结 / 44

第3章  GTID与复制 / 45

3.1  GTID简介 / 45

3.1.1  什么是GTID / 45

3.1.2  GTID的格式与存储 / 46

3.2  GTID生命周期 / 54

3.2.1  典型事务的GTID生命周期 / 54

3.2.2  GTID分配 / 57

3.2.3  gtid_next系统变量 / 58

3.2.4  gtid_purged系统变量 / 60

3.3  GTID自动定位 / 68

3.4  配置GTID复制 / 70

3.4.1  联机配置GTID复制 / 70

3.4.2  联机更改复制模式 / 74

3.4.3  GTID相关系统变量 / 75

3.5  GTID运维 / 76

3.5.1  跳过一个事务 / 76

3.5.2  mysqldump导出 / 77

3.5.3  主从切换 / 78

3.6  GTID限制 / 81

3.7  GTID集合运算函数 / 84

3.7.1  GTID内置函数 / 84

3.7.2  用户自定义函数 / 84

3.7.3  使用示例 / 85

3.8  小结 / 88

第4章  复制拓扑结构与性能 / 89

4.1  复制拓扑结构 / 89

4.1.1  一主一（多）从 / 89

4.1.2  双（多）主复制 / 91

4.1.3  多源复制 / 99

4.1.4  Blackhole引擎与日志服务器 / 104

4.2  复制性能 / 108

4.2.1  测试规划 / 108

4.2.2  sync_binlog与innodb_flush_log_at_trx_commit / 111

4.2.3  组提交与多线程复制 / 111

4.2.4  基于WriteSet的多线程复制 / 117

4.3  小结 / 120

第5章  延迟复制与部分复制 / 121

5.1  延迟复制 / 121

5.1.1  延迟复制简介 / 121

5.1.2  延迟复制时间戳 / 124

5.1.3  监控延迟复制 / 125

5.2  部分复制 / 125

5.2.1  部分复制简介 / 126

5.2.2  评估库级复制选项 / 126

5.2.3  评估表级复制选项 / 129

5.2.4  复制规则应用 / 134

5.2.5  部分复制示例 / 136

5.3  主从切换 / 139

5.3.1  计划内主从切换 / 139

5.3.2  计划外切换 / 140

5.4  小结 / 148

第6章  组复制 / 149

6.1  组复制基本原理 / 149

6.1.1  MySQL复制技术 / 149

6.1.2  组复制使用场景 / 151

6.1.3  组复制相关服务 / 151

6.1.4  组复制插件体系结构 / 152

6.1.5  组复制分布式恢复 / 153

6.2  组复制安装部署 / 156

6.2.1  部署单主模式组复制 / 157

6.2.2  组复制监控 / 165

6.2.3  容错示例 / 165

6.3  组复制性能 / 172

6.3.1  概述 / 172

6.3.2  测试规划 / 174

6.3.3  消息压缩 / 177

6.3.4  组通信线程循环 / 178

6.3.5  写入集 / 178

6.3.6  流控 / 179

6.3.7  其他配置 / 181

6.3.8  主从、半同步、组复制性能对比测试 / 183

6.4  组复制要求与限制 / 185

6.4.1  组复制要求 / 185

6.4.2  组复制限制 / 187

6.5  小结 / 188

第7章  MySQL Router / 189

7.1  MySQL Router简介 / 189

7.1.1  透明路由 / 189

7.1.2  元数据缓存 / 190

7.1.3  简单重定向 / 190

7.1.4  部署与使用 / 191

7.1.5  MySQL Router 8.0的新特性 / 192

7.2  Router实现MySQL高可用示例 / 192

7.2.1  安装和配置 / 192

7.2.2  自动故障转移 / 199

7.2.3  负载均衡 / 202

7.2.4  读写分离 / 202

7.2.5  多实例 / 202

7.3  Router 8安装和配置 / 205

7.3.1  安装 / 205

7.3.2  启动 / 206

7.3.3  配置 / 210

7.4  小结 / 214

第8章  MySQL Fabric / 215

8.1  MySQL Fabric简介 / 215

8.1.1  MySQL Fabric的用途 / 215

8.1.2  MySQL Fabric架构 / 216

8.2  高可用性 / 218

8.2.1  概述 / 218

8.2.2  安装与配置 / 218

8.2.3  HA功能测试 / 226

8.3  数据分片 / 232

8.4  MySQL Fabric限制 / 233

8.5  小结 / 234

第9章  MMM / 235

9.1  MMM简介 / 235

9.1.1  基本功能 / 235

9.1.2  工作原理 / 236

9.1.3  典型用例 / 237

9.1.4  切换流程 / 238

9.1.5  系统需求 / 238

9.2  实现MySQL主主复制的高可用 / 240

9.2.1  基本环境 / 240

9.2.2  安装和配置 / 241

9.2.3  功能测试 / 244

9.3  小结 / 248

第10章  MHA / 249

10.1  MHA简介 / 249

10.1.1  基本功能 / 249

10.1.2  切换流程 / 250

10.1.3  部署架构 / 250

10.1.4  主要特点 / 251

10.1.5  工具介绍 / 252

10.2  安装和配置 / 252

10.2.1  配置主从复制 / 253

10.2.2  安装Perl依赖模块 / 253

10.2.3  配置SSH免密登录 / 254

10.2.4  安装MHA Node / 254

10.2.5  安装MHA Manager / 255

10.2.6  配置MHA / 255

10.2.7  创建相关脚本 / 257

10.2.8  检查MHA配置 / 265

10.3  功能测试 / 267

10.3.1  初始绑定VIP / 267

10.3.2  测试自动切换 / 267

10.3.3  测试手工切换 / 271

10.3.4  测试在线主从切换 / 273

10.3.5  修复宕机的主库 / 276

10.4  小结 / 276

第11章  Keepalived   LVS / 277

11.1  Keepalived简介 / 277

11.1.1  VRRP协议 / 277

11.1.2  Keepalived架构设计 / 278

11.2  LVS简介 / 280

11.3  实现MySQL主从复制中的读写分离及高可用 / 281

11.3.1  架构设计 / 282

11.3.2  安装和配置 / 283

11.3.3  功能测试 / 292

11.4  实现MySQL双主复制的读写负载均衡及高可用 / 295

11.4.1  架构设计 / 295

11.4.2  安装和配置 / 296

11.4.3  功能测试 / 301

11.5  小结 / 304

第12章  Heartbeat   HAProxy / 305

12.1  Heartbeat简介 / 305

12.1.1  Heartbeat组成模块 / 305

12.1.2  Heartbeat工作原理 / 307

12.2  HAProxy简介 / 308

12.3  实现MySQL主从复制的读写分离及高可用 / 309

12.3.1  架构设计 / 310

12.3.2  安装和配置 / 310

12.3.3  功能测试 / 320

12.4  实现MySQL双主复制的读写负载均衡及高可用 / 323

12.4.1  架构设计 / 323

12.4.2  安装和配置 / 324

12.4.3  功能测试 / 326

12.5  小结 / 329

第13章  InnoDB Cluster / 330

13.1  InnoDB Cluster简介 / 330

13.1.1  整体架构 / 330

13.1.2  MySQL Shell / 331