MySQL集群设计方案

1. 现状分析

1.1 现状分析

长久以来我们基于MySQL的数据库集群总是采用这样一种形式：

我们将这种方式称之为双主互备模式，其核心是依赖MySQL的双向复制实现两个数据库节点持有同样的数据，利用中间件模拟一个虚IP，客户端配置虚IP，正常情况只有一个数据库节点提供服务。

当当前提供服务的节点出现故障无法提供服务的时候，中间件会在检测到异常后通知另外一台数据库节点接管虚IP，在几秒钟之内可以完成failover的过程，实现高可用要求。下面是failover的示意图：

这种方式实现起来很简单，仅仅依赖MySQL自身的复制机制和Keepalived就可以实现。但是这种方式也存在很多问题：

1. MySQL的复制并不保证数据绝对的一致；
2. 虚IP的切换时间较长；
3. 单一节点性能问题明显；
4. 集群无法扩展，无法形成三个节点的环形复制拓扑。

鉴于上述限制，我们需要规划更复杂的数据库集群结构，满足不同的要求，至少应该满足下面几种要求：

1. 金融级别的数据可靠性；
2. 毫秒级别的故障转移能力；
3. 分布式系统，读写压力分散在不同的节点；
4. 良好的扩展性能。

1.2 MySQL复制技术简介

MySQL的复制技术是实现MySQL集群的基础技术。随着MySQL的发展，复制技术经历了几个阶段的发展：

• 异步复制技术
• 半同步复制技术
• 组复制技术

异步复制技术是一种很古老的复制技术，因为复制的时候Master并不需要关心Slave是否正常接收了binlog，只负责通知Slave的线程来拉去binlog，因此称为异步复制。这种方式的好处是非常快，效率非常高。但是缺点也很明显，在很多的系统上都会出现不同程度的数据不一致现象。

上图是官方编写的复制原理示意图，可以看到，Master在写好binlog之后，通知Slave来拉去，然后直接就会commit事务进行数据提交。如果出现一些不可知的问题，导致Slave没有正确的接收到binlog，那么就会出现主从数据不一致的问题，这个问题实际上困扰了MySQL很长时间。

为了解决这个问题，MySQL从5.6开始提供了半同步复制，并在5.7中进行了加强，其原理图如下：

Master在写好binlog之后会通知Slave来拉去binlog，当slave正确的将binlog转存到本地relay log以后，会返回一个ACK给Master，Master确认后，才会提交事务。这样就保证了不会出现之前异步复制时的数据不一致问题。

现在有很多集群都采用了Raft协议来实现分布一致性，MongoDB为代表的一系列noSQL数据库即是如此实现。在保证了数据安全的情况下也实现了高可用。集群内允许一定的节点失效而不影响业务，也不会丢失数据。

MySQL从5.7版本开始提供了组复制技术，使用更为复杂的Paxos算法实现了集群的分布一致性，下面是组复制的原理示意图：

三个节点都可以称为Master，原理上来说每个节点都可以对外提供服务。当一个节点准备提交数据的时候，会在组内发一条广播（这条广播实际上是更新的行），这样就保证了集群内所有的节点都持有同样的数据。只有当组内所有的节点都报告可以提交的时候，这个事务才会被提交。

组复制原理上保证了集群的高可用和数据的强一致性。基于组复制，MySQL可以保证金融级别的数据安全。

2. MySQL集群方案选择

2.1 一主多从高可用集群模式

业内常常使用一主多从的方式进行集群的构建，其主要目的是实现高可用和负载均衡。下图是一个典型的负载均衡集群的示意图。上层应用直接和中间件进行交互，中间件代理会识别请求类型，并进行请求的分发，部分的中间件没有负载均衡能力，需要额外添加一个工具来实现。

这种模式适用于大部分场景，对于读请求的性能提升较大。但是该集群本身没有高可用能力，一旦主节点宕机，则写入操作就会停止，需要借助第三方工具实现高可用，将一个Slave提升成新的Master。

然而一主多从的方式仍旧没有解决数据一致性的问题，因为这种集群的复制也是异步（或者半同步，一种特殊的异步复制），而且其涉及的工具较多，部分工具对MySQL新版本的支持并不良好。

一主多从集群还有一个比较严重的限制就是无法做数据的分片，所有的节点上持有的数据实际是同一份，集群能够承载的数据实际上是由机器的物理硬盘大小决定的。

2.2 组复制集群

MySQL5.7开始，官方提供了一种新的技术叫做MySQL Group Replication，简称MGR，即我们常说的组复制。这种复制和原先的异步复制或者增强型半同步复制完全不同，这是一种基于Paxos协议的复制，官方和部分专家都认为这种复制技术已经实现了金融级别的数据可靠性。

下图是组复制的failover过程：

当集群的leader宕机以后，集群会发起投票选举新的leader，待leader选举成功后，集群可以继续对外提供服务。集群内部不需要任何的第三方工具就能实现高可用，这是一主多从结构无法实现的。

异步复制不能完全保证数据一致性是因为Master将binlog发出后，并不会去了解binlog是否正常的被接受转录，因此在极端情况下，会出现主从不一致的情况。半同步复制则是在异步的基础上增加了一个等待slave发送ACK的机制，只有Slave正确接收到binlog，Master才会提交事务。

但是半同步复制仍旧是异步复制的加强，因此Oracle公司在MGR技术中使用了Paxos协议，在集群中设置了一个一致性层的概念。当一个事务准备提交的时候，需要在一致性层中发送一个广播，利用投票机制决定这个事务是否提交。这样就保证了数据的一致性。

但是MGR仍旧有限制，集群内部所有的节点持有的数据实际上还是一份，集群的容量也是取决于集群内部最小的磁盘的大小。

需要注意一个问题，采用单主模式的集群，对外提供服务的一定是Primary节点，因此Java配置中也一定会配置Primary节点的地址。但是集群会出现选举，一旦Primary发生改变，那么程序就会出现问题。

基于此，我们需要一个中间层来隔离MGR集群和上层应用，中间层需要具有自动选主的能力，保证上层业务对集群Primary变更无感知。

ProxySQL是一款由Percona公司支持的开源中间件，对MGR支持良好，支持自动选主，支持多种规则配置的读写分离，是一款高性能易于部署配置的轻量级中间件。

不过MGR优良的特性满足了重要的数据一致性要求和故障转移功能，因此如果能以MGR为基本技术，加上一些第三方工具进行组合，是可以构建出一个完整的分布式集群的。

2.3 基于组复制的分布式集群

我们的集群应该能够满足横向扩展能力的要求，即可以灵活的向集群内部添加节点达到分散数据的要求。考虑到MGR的优势，结合类似于MongoDB之类noSQL数据库的设计，我们可以给出一套这样的集群方案：

集群内有几个关键的角色：

• router：数据路由组件，负责按照一定的预定义规则进行请求的分发；
• shard：即分片，集群内的数据按照一定的规则被分配到不同的shard上，每个shard只持有一部分数据，shard内部保持高可用。

结合之前的方案，我们可以将MGR集群作为一个shard来使用，这样就组成了一个高可用，分布式的MySQL数据库系统。

3. 技术方案比对

3.1 MyCAT/DBLE方案

MyCAT方案是开源社区基于阿里巴巴的开源方案再次开发的分布式中间件方案，DBLE是上海爱可生公司基于MyCAT方案，删除了一些不常用功能，修改了大量bug之后开源的方案。

这两种方案都是实现了一套MySQL Server，简单来说就是实现了SQL的解析器和优化器。下面的图是官方给出的结构示意图：

下面的MySQL Sharding实际上是我们正常搭建的MySQL服务器，是实际保存数据的地方，其上是一套DBLE组件，可以发现其实现非常复杂。

MyCAT与之类似。

由于DBLE实现了MySQL协议，因此开发者完全可以将其当做一个MySQL实例来使用，兼容性好于很多分布式数据库解决方案，对于开发人员来说是非常友好的。

DBLE是一种中心化的解决方案，所有的配置都会保存在一个中心节点上。

3.2 Apache ShardingSphere方案

Apache ShardingSphere（以下简称SS）方案是京东数科开发的一套分布式数据库解决方案，从4.0版本开始成为Apache项目，目前社区活跃。

SS目前有两个方案，分别是ShardingSphere-JDBC和ShardingSphere-Proxy。

SQL是一款典型的4GL，编写者本身不需要知道自己需要的查询是如何执行的，将SQL转换成指令的过程实际上是SQL解析器和优化器的职责。

SQL解析器负责将SQL解析成一颗抽象语法树（AST），SQL优化器则确定如何执行SQL。大部分的分库分表中间件（甚至一些不具备分库分表的中间件，比如青云的RadonDB）都会自行实现SQL的解析器和优化器。就像上图中DBLE的结构，就实现了这些功能。ShardingSphere同样，也实现了这些组件功能。

其核心组件的功能可以列举如下：

• SQL解析器：分为词法解析和语法解析，这一步中会检查SQL语法问题，将SQL拆解成单词，最终实现AST；
• SQL优化器：优化改写；
• SQL路由器：根据之前的解析结果，匹配分片配置，生成路由路径；
• SQL执行器：实际上执行SQL的组件；
• 结果归并：将多个分片上的结果归并在一起，通过JDBC接口返回。

这些组件基本上和之前的DBLE没有本质区别，逻辑上也在做一样的事情。

SS-Proxy类似于DBLE，实现了MySQL协议，可以当做一个MySQL实例来使用，同样没有兼容性问题。这也是一种中心化的解决方案，对DBA比较友好，可以在一个节点上完成数据库的运维操作。

下图是官方提供的SS-Proxy示意图：

观察不难发现这种结构最大的问题是单点性能问题，所有的请求都由SS-Proxy首先处理，根据其特点，很难采用横向扩展的方式扩展Proxy，只能采用更好的硬件。

SS-JDBC可以认为是一个增强版的JDBC驱动。这种模式下，每一个服务都会持有一个路由程序，路由程序只会处理该服务的请求，解决了单点的问题，下图是官方提供的结构示意图：

作为一个JDBC驱动，可以兼容主流的ORM框架，也可以支持各种主流数据库，对于开发者非常友好，不需要修改任何代码。

这种方式的问题也是存在的，有一些语法限制，但是从现在披露的兼容性问题来看，只是一个比较冷门的SQL语法不兼容。同时，因为是非中心化的方式，对于DBA的运维并不算友好，很难在一个节点上执行运维操作。另外因为每个服务都会持有一个router，在数据库上注册的连接也会比较多，实际上也造成了一些消耗。

经过和京东数科的开发者们沟通，ShardingSphere已经广泛应用于京东数科旗下的金融类产品数据库中。由于其本身是原当当团队开发的产品，这款产品在当当网也被广泛应用，从应用成熟程度上来看是可以用于生产环境的。经过我本地实际测试，ShardingSphere-JDBC在效率上并无损耗。

3.4 小结

我们可以参考SS给出的对比表格来比对不同的解决方案：

DBLE方案实际上和SS-Proxy方案是一致的，都是一种中心化的，实现了MySQL协议的方案，都可以用上面的表格来说明问题。

任何一种分布式方案都能解决一定的问题，选择方案时应该综合考虑现实条件。

基于之前章节的设计，我们可以确定使用MGR作为高可用的实现方案。而使用SS-JDBC作为分库分表方案则不需要做任何部署，只用在Java工程里引入一个依赖即可，因此使用SS-JDBC可以快速的实现分库分表。

另外，不管是SS-Proxy还是SS-JDBC，在底层的实现上都是一样的，都实现了SQL解析器，SQL优化器和路由程序，因此从性能上来说是没有太多区别的。

4. MySQL分布式系统的实现

4.1 集群设计

综合之前的论述，将所有备选的技术按需求组合起来，我们可以设计如下一个高可用的分布式集群：

这个系统具有如下特点：

1. 利用MGR组成高可用的数据分片集群，一个分片保存集群内的一部分数据；
2. 利用ProxySQL隔离上层和数据库具体实现方式，降低组件之间的耦合度，且提供部分附加功能；
3. 利用ss-jdbc实现数据路由功能，使用轻量级，零入侵的方式实现数据分片；
4. 扩容方便。

不论SS-Proxy还是SS-JDBC，都是利用配置文件进行分库分表规则的配置，本文中不会详细说明如何进行配置文件的编写，但是会随时提及配置文件。

但是SS-JDBC存在的问题就是没有静态访问入口，不利于DBA进行运维操作，因此我们需要对之前的方案进行一些改造，满足DBA的运维需求，同时也能提供一些OLAP的能力。

方案示意图如下：

将ss-proxy加入到集群中，和ss-jdbc采用同样的分片配置，只对DBA开放即可，这样就满足了运维的需要，同时可以对一些OLAP应用开放，实现统计分析功能。

4.2 集群的运维

4.2.1 巡检和备份

数据库的运维分为几大项目：

• 集群状态巡检；
• 数据备份；
• 集群扩容。

集群状态巡检需要保证集群中的每个节点都处于可用状态，并且确认没有异常的CPU升高，内存溢出，磁盘占用过多等问题。部分检查项目可以使用SS官方提供的GUI平台来实现，同时也要考虑如何实现一套自动化巡检工具，实现自动巡检，自动打印报告的功能。

数据备份是所有数据库都需要关注的事情，MySQL的备份一般分为冷备和热备两种，其中冷备并不推荐，因为需要关机进行，热备方案则首选Percona公司提供的xtraBackup工具，可以实现不停机热备，并提供全量备份和增量备份两种方式。

之前提到过，分布式集群内部添加一个ss-proxy提供给DBA进行运维使用，此时的备份就可以在ss-proxy进行，一次性备份所有的数据。

恢复采用相同的方式，利用ss-proxy即可完成数据库的恢复工作。

4.2.2 集群扩容

我们采用分布式集群的目标之一就是方便扩容，随着业务的增长能够快速的添加shard到集群中。本文中设计的分布式数据库集群也能够支持扩容，但是需要考虑的就是数据的再平衡。

我们知道，集群在使用了一段时间之后，所有的数据都会按照规则分布在不同的shard上，如果添加新的shard进入集群，就需要将原先shard上的数据按照规则重新分布，这个过程成为数据的再平衡过程。在类似于MongoDB的分片集群上，再平衡是有专门的组件去完成的，开发人员和运维人员都不需要关心，但是在我们之前设计的集群上是没有这个角色的，这部分工作需要DBA去执行。

扩容采用倍增扩容的方式，则可以尽量少的移动数据，代价较小。试描述其数学原理如下，假设原集群中有3个节点，采用按照主键模3取余的方式进行分库。

可以比对一下倍增扩容和非倍增扩容的区别，非倍增扩容时，我们假定只加入了一个新节点：

倍增扩容时，节点1内的数据只有原先4条，有一半需要被迁移，非倍增扩容时，节点1内的数据需要迁移3条。这只是其中一个方面，再次观察表格可以发现，倍增扩容时，原有节点不会接收再平衡的数据进入，只会迁出。而非倍增扩容时，原有的节点还会接收一些再平衡的数据，这样更难以维护。

4.3 分片策略

数据库采用分片方案，一般是为了实现分渠道数据隔离或者为了负载均衡。

负载均衡是一种比较常见与主流的分片策略。在一个系统运行一段时间并积累了一定量的用户以后，单节点的数据库从容量上和负载能力上都已经无法很好的支撑业务，这个时候需要按照一定的规则将用户数据分布在不同的数据库上。

从既往的经验来看，一个用户的所有数据都会分布在同一个服务器上，大部分情况下，用户只会访问自己的数据，而不需要访问别人的数据。基本上没有跨服务联查的需求。

以之前在某手机厂商服务的项目为例，3000万用户中每100万用户一台数据库服务器，一个用户登录的时候会被路由程序定位到自己的服务器上，该用户所有的操作都被这台服务器接收处理。

每次新增一个服务器都会在路由表上维护一条路由数据，保证新增用户能够正确的连接到预定义好的数据库服务器上。

另外一种策略则是按照产品去拆分数据库。比如云计算平台，可以接入多个产品，每个产品都会发展用户，每个产品的用户应该是互相隔离的，不应该放在同一个数据库服务器上。

这种方式比较复杂一些，有时候可能需要结合负载均衡的策略，我们可以画出一个简单的示意图来表示这种结构：

示意图中有三个渠道，其中c2渠道可能因为数据量巨大，需要进行横向拆分（具体拆分策略需要根据不同业务制定）。

5. 运维手册

5.1 集群部署

采用ShardingSphere-JDBC构建分布式集群，只需要部署好每个Shard就可以。每个Shard都是一个MGR集群，并且有一个ProxySQL组件。

5.1.1 MGR集群的搭建

集群需要至少三台机器，而且集群内的节点数量应该是奇数个。我们现在假定为最低3台机器，来描述如何部署一个MGR集群。

MySQL的软件安装部分省略，版本选择应该为5.7的最新版。

MGR依赖GTID技术（Global Transaction ID），因此需要首先配置GTID，修改集群中所有节点的配置文件，添加这两条配置项：

gtid_mode=on
enforce_gtid_consistency=1
log_slave_updates=1

开启了GTID之后还需要配置一些复制相关的配置项目，同样修改配置文件，添加下面的选项：

## 复制格式选择为row
binlog_format=row
## 打开p_s视图，方便中间件进行监控
performance_schema=ON
binlog_checksum=NONE
master-info-repository=TABLE
relay-log-info-repository=TABLE

## group replication
transaction_write_set_extraction = XXHASH64
## 这里随机生成一个UUID，一定要是UUID，每个节点都要保持一样的UUID
loose-group_replication_group_name = "9ac06b4e-13aa-11e7-a62e-5254004347f9"
loose-group_replication_start_on_boot = off
## 这是本机的IP，同时找一个端口，每个节点变更此参数为本机IP
loose-group_replication_local_address = "192.168.150.131:23306"
## 这里是群组的所有机器
loose-group_replication_group_seeds = "192.168.150.131:23306,192.168.150.134:23307,192.168.150.135:23308"
loose-group_replication_bootstrap_group = off
## 这两个选项配置单主模式
loose-group_replication_single_primary_mode = true
loose-group_replication_enforce_update_everywhere_checks = false

到这里为止数据库的配置已经完全结束，另外还需要修改节点的hosts文件，写明集群内所有机器的hostname和ip地址的对应关系，否则可能加入集群的secondary节点长时间处于Recovering状态。

MySQL将MGR组件作为插件提供，默认情况下是没有安装的，需要手动进行安装，在每个节点上启动MySQL之后执行下面的命令，安装插件：

INSTALL PLUGIN group_replication SONAME 'group_replication.so';

这一步安装插件是之后所有操作的基础，一定要执行！

任意选择一台机器（这台机器会被默认选为Primary节点），在这台机器上执行下面的命令：

-- 标记下面的语句不会被记录在binlog中
SET SQL_LOG_BIN=0;
-- 新建一个用于复制的用户，用户名和密码都可以自行选择
grant replication slave on *.* to repl@'%' identified by 'Repl2019_';
-- 这里会真的指定组复制
change master to master_user='repl',master_password='Repl2019_' for channel 'group_replication_recovery';
-- 一些参数配置
set global group_replication_bootstrap_group=ON;
-- 启动组复制
start group_replication;
-- 重新开启binlog记录
SET SQL_LOG_BIN=1;

完成上述命令之后就会开启组复制，此时的集群已经存在了，但是只有一台机器。接下来就可以继续配置另外两台机器，在两个节点上都执行下面的命令：

-- 标记下面的语句都不会被记录在binlog中
SET SQL_LOG_BIN=0;
-- 新建用于复制的用户，用户名和密码都可以自行选择
grant replication slave on *.* to repl@'%' identified by 'Repl2019_';
change master to master_user='repl',master_password='Repl2019_' for channel 'group_replication_recovery';
-- 注意，这个参数在5.7.21版本中被标记为过时，将会在未来的版本中被删除，不要执行
set global group_replication_allow_local_disjoint_gtids_join=ON;
-- 如果执行这一步出现3092错误，则执行reset master命令，然后再次执行这个命令，启动组复制
start group_replication;
SET SQL_LOG_BIN=1;

成功执行这些命令之后就启动了secondary节点，此时在任意的节点上执行下面的命令，可以检查集群的状态：

SELECT * FROM performance_schema.replication_group_members;

查询结果中，如果三台节点的状态都是ONLINE则集群正常，可以开始提供服务。

5.1.2 ProxySQL的安装配置

ProxySQL是一款轻量级，高效的数据库中间件，对我们的需求来说，ProxySQL提供了我们需要的自动选主能力，开发者不需要关注MGR的细节，只需要和ProxySQL交互即可。

官方提供了打包好的rpm包供安装用，从之前搭建的实践来看，需要的依赖包应该只有perl-DBD-mysql，安装完毕后会自动注册成系统服务，可以以服务的形式启动，服务名是proxysql。

ProxySQL实现了MySQL的部分接口，可以用mysql客户端直接连接，进行配置，默认的配置端口是6032，默认的业务端口是6033。

以如下命令进入命令行来配置：

mysql -uadmin -padmin -h127.0.0.1 -P6032

ProxySQL需要对集群状态进行实时监控，因此需要一个监控用户，下面的命令在MGR的Primary节点上执行，新建用户：

-- 注意，这里的通配符可以自由定制，为了简单，先将通配符配置为全网段
create user monitor@'%' identified by 'P@ssword1!';
grant select on sys.* to monitor@'%';

监控实际上是执行了一系列的函数来进行的，下面的脚本也要在MGR集群的Primary节点上执行：

USE sys;

DELIMITER $$

CREATE FUNCTION IFZERO(a INT, b INT)
RETURNS INT
DETERMINISTIC
RETURN IF(a = 0, b, a)$$

CREATE FUNCTION LOCATE2(needle TEXT(10000), haystack TEXT(10000), offset INT)
RETURNS INT
DETERMINISTIC
RETURN IFZERO(LOCATE(needle, haystack, offset), LENGTH(haystack) + 1)$$

CREATE FUNCTION GTID_NORMALIZE(g TEXT(10000))
RETURNS TEXT(10000)
DETERMINISTIC
RETURN GTID_SUBTRACT(g, '')$$

CREATE FUNCTION GTID_COUNT(gtid_set TEXT(10000))
RETURNS INT
DETERMINISTIC
BEGIN
  DECLARE result BIGINT DEFAULT 0;
  DECLARE colon_pos INT;
  DECLARE next_dash_pos INT;
  DECLARE next_colon_pos INT;
  DECLARE next_comma_pos INT;
  SET gtid_set = GTID_NORMALIZE(gtid_set);
  SET colon_pos = LOCATE2(':', gtid_set, 1);
  WHILE colon_pos != LENGTH(gtid_set) + 1 DO
     SET next_dash_pos = LOCATE2('-', gtid_set, colon_pos + 1);
     SET next_colon_pos = LOCATE2(':', gtid_set, colon_pos + 1);
     SET next_comma_pos = LOCATE2(',', gtid_set, colon_pos + 1);
     IF next_dash_pos < next_colon_pos AND next_dash_pos < next_comma_pos THEN
       SET result = result +
         SUBSTR(gtid_set, next_dash_pos + 1,
                LEAST(next_colon_pos, next_comma_pos) - (next_dash_pos + 1)) -
         SUBSTR(gtid_set, colon_pos + 1, next_dash_pos - (colon_pos + 1)) + 1;
     ELSE
       SET result = result + 1;
     END IF;
     SET colon_pos = next_colon_pos;
  END WHILE;
  RETURN result;
END$$

CREATE FUNCTION gr_applier_queue_length()
RETURNS INT
DETERMINISTIC
BEGIN
  RETURN (SELECT sys.gtid_count( GTID_SUBTRACT( (SELECT
Received_transaction_set FROM performance_schema.replication_connection_status
WHERE Channel_name = 'group_replication_applier' ), (SELECT
@@global.GTID_EXECUTED) )));
END$$

CREATE FUNCTION gr_member_in_primary_partition()
RETURNS VARCHAR(3)
DETERMINISTIC
BEGIN
  RETURN (SELECT IF( MEMBER_STATE='ONLINE' AND ((SELECT COUNT(*) FROM
performance_schema.replication_group_members WHERE MEMBER_STATE != 'ONLINE') >=
((SELECT COUNT(*) FROM performance_schema.replication_group_members)/2) = 0),
'YES', 'NO' ) FROM performance_schema.replication_group_members JOIN
performance_schema.replication_group_member_stats USING(member_id));
END$$

CREATE VIEW gr_member_routing_candidate_status AS SELECT
sys.gr_member_in_primary_partition() as viable_candidate,
IF( (SELECT (SELECT GROUP_CONCAT(variable_value) FROM
performance_schema.global_variables WHERE variable_name IN ('read_only',
'super_read_only')) != 'OFF,OFF'), 'YES', 'NO') as read_only,
sys.gr_applier_queue_length() as transactions_behind, Count_Transactions_in_queue as 'transactions_to_cert' from performance_schema.replication_group_member_stats;$$

DELIMITER ;

注意，这一步只在2.0版本以前需要，2.0版本以后已经不需要新建这些视图和函数了，只需要一个监控用户就可以了。

这是该脚本的原始连接，请参考：

https://github.com/lefred/mysql_gr_routing_check/blob/master/addition_to_sys.sql

接下来的配置都是要在ProxySQL的配置端进行：

-- 为了保险，我们将原先所有的配置都删除，一个新安装的系统上是不存在这些配置的。
delete from mysql_servers;
delete from mysql_group_replication_hostgroups;
delete from mysql_users;

-- 写入MySQL的服务节点信息，这些节点都是被代理的
insert into mysql_servers(hostgroup_id,hostname,port) 
values(10,'192.168.150.131',3306),
      (10,'192.168.150.134',3306),
      (10,'192.168.150.135',3306);

-- 新增监控用户      
set mysql-monitor_username='monitor';
set mysql-monitor_password='P@ssword1!';

-- 这一步一定要执行，只有这样才能将配置持久化到ProxySQL的本地存储中，不至于丢失
load mysql servers to runtime;
save mysql servers to disk;

查询mysql_servers表，所有的节点的status列应该都是ONLINE状态方为正常。

针对MGR集群的代理，还有一个表需要添加配置数据：

delete from mysql_group_replication_hostgroups;

insert into mysql_group_replication_hostgroups(writer_hostgroup,
                                               backup_writer_hostgroup,
                                               reader_hostgroup,
                                               offline_hostgroup,
                                               active,
                                               max_writers,
                                               writer_is_also_reader,
                                               max_transactions_behind) 
values(10,20,30,40,1,1,0,0);

load mysql servers to runtime;
save mysql servers to disk;

backup_writer_hostgroup，reader_hostgroup和offline_hostgroup都设置了不存在的组，这不影响什么。MGR采用了单主模式，只有Primary能够处理写操作，因此这里有一个关键项目writer_is_also_reader需要设置成0，即Primary节点只用于写操作。

到这里为止ProxySQL就配置完成了，接下来在MGR集群Primary节点上新建一个业务用户即可，然后在ProxySQL的配置命令行内添加这个用户：

delete from mysql_users;

insert into mysql_users(username,password,default_hostgroup,transaction_persistent) 
values('quan','Quan2019_',10,1);

load mysql users to runtime;
save mysql users to disk;

这样就能通过MySQL客户端，GUI工具，JDBC等等连接6033端口来访问MGR集群了。

5.2 集群扩容

之前提到过集群的扩容方式应该使用倍增扩容的方式，这样迁移的数据量是最小的，而且原有节点不会有数据迁入的现象。

下面以一个具体的表t_order为例说明如何在扩容时迁移数据。假设最开始只有一个分片，现在将集群扩容到两个分片，那么t_order中以片键为依据，有一半的数据需要迁移。

下面是具体的迁移步骤：

1. 停止服务，保证不会有新的数据写入；
2. 新建t_order的子表，本例中就是t_order0和t_order1，ddl和t_order一致；
3. 按照新的分片规则将t_order中的数据拆分到t_order0和t_order1中；
4. t_order1是要被迁移走的数据，两个表的数据量应该基本相等；
5. 利用MySQL表空间传输技术可以很快的将t_order1传输到新的分片中去；
6. 删除原先的t_order表，或将该表改名，保留一段时间；
7. 在原分片和新的分片上，将t_order0表和t_order1表改名成t_order；
8. 修改分片配置，添加新的分片信息；
9. 启动服务。

原理上需要以上的步骤来实现扩容操作，这些操作也都可以集中到自动化的工具中。采用分片方案以后需要开发一系列的运维工具，很多厂商实际上也是这样做的。

但是任何操作都有可能失败，因此也必须考虑扩容失败的回退方案。

检视上面的方案，在第6步之前出现任何错误都可以直接停止操作，因为本身这些操作都没有影响原始的数据，也没有修改任何分片配置，重新启动服务即可。

若第6步之后出现问题，则应该摘除新的分片，回退配置，将原先备份的表恢复，重新启动服务。

5.3 备份和恢复

数据库的备份分为冷备和热备，之前提到过，冷备是一种不太可取的备份方式，一般来说各个厂商都会使用热备的形式，不影响线上业务。

开源热备方案中，比较成熟和可靠的是Percona公司提供的XtraBackup套件，支持全量备份和增量备份两种备份逻辑。

其备份恢复可以用下面一张图来描述：

XtraBackup保证备份期间数据库可以正常访问。

下面是一些常用的命令，可以完成备份工作：

# 完成全量备份
# 注意这里可能还需要增加几个参数能够正常的连接到数据库
# --user 一般用root用户登录备份
# --password 密码
# --socket 在本地登录，需要使用套接字时指定
xtrabackup --backup --target-dir=/data/backups/base

# 增量备份是基础备份，任何增量备份都要基于全量备份
# 下面的命令就会创建一个基于之前全量备份的增量备份
# 其余参数和全量备份相同
xtrabackup --backup --target-dir=/data/backups/inc1 --incremental-basedir=/data/backups/base

增量备份的逻辑分为两种，一种是每次增备都依赖全量备份，这样实际上在恢复的时候只需要应用全量备份和一个增量备份就可以；另外一种是每次增备都是基于之前的增备，这样的好处是每个增备的规模都会很小，但是恢复的时候比较困难，需要合并大量的增量备份。

我建议使用第一种增量备份策略。

下面的命令用于增量备份的恢复：

# 准备全量备份
xtrabackup --prepare --apply-log-only --target-dir=/data/backups/base

# 准备增量备份
xtrabackup --prepare --apply-log-only --target-dir=/data/backups/base \
--incremental-dir=/data/backups/inc1

# 将数据复制回MySQL的数据目录
# 应该首先停止MySQL服务，然后清空数据目录再执行恢复工作
xtrabackup --copy-back --target-dir=/data/backups/base

XtraBackup会自动读取my.cnf文件中的datadir，将备份文件恢复到datadir中，然后重启MySQL服务即可。

注意，复制回去的文件权限有问题，需要重新执行chown命令，将datadir的owner和组设置为mysql.mysql。

6. 双机房方案和相关技术

6.1 综述

双机房双活方案要求两个机房互为备份，且能够同时提供服务。根据前文设计的分布式集群方案，每一个分片都是一个MGR集群，在A机房中的分片1，在B机房中会有一个全完一样的副本1’。

将机房简化为只有数据库服务器，则简单的双机房示意图如下图所示：

两个机房间的数据同步依赖MySQL的复制技术实现，相当于传统的Master-Slave模式。如果单纯的使用异步复制技术的话，那么还是会出现和前面论述异步复制时一样的数据不一致问题。

阿里巴巴集团因为要作中美之间的数据同步，先后开发了两款工具canal和otter，官方介绍otter能够支持下面描述的规模的数据同步：

1. 同步数据量6亿
2. 文件同步1.5TB(2000w张图片)
3. 涉及200+个数据库实例之间的同步
4. 80+台机器的集群规模

应对我们的需求是完全够用的。我们构建双机房，主要需要面对的问题就是数据同步问题，因此选择一个高效的，不丢数据的组件，是其中最为关键所在。

下图是双机房双向同步的架构图，下面的章节中，将从技术和操作上逐一讲解这张图的内容：

6.2 otter技术概述

首先要介绍一个canal技术。

canal也是阿里巴巴开源的一款数据同步工具，其基本原理是将自己伪装成一个MySQL的slave，接收Master推过来的binlog。

官方给出了一张很简单的示意图来说明canal的作用和原理：

这里显示的canal就像是一个管道，这种方式有点类似于logbeat或者flume之类的工具。提供了多种语言的客户端，可以在此基础上进行开发，实现一个消费者，完成数据的同步功能。

最简单的方式只需要利用官方提供的client实现一个消费者即可，向我们的目标端写入数据。

otter则整合了canal技术，实现了一整套的解决方案，可以简单地配置一个同步数据任务。下面是官方提供的原理示意图：

这里阐明几个基本的概念：

• manager：提供web服务，配置同步规则，数据源等等，工作时会推送配置到node；
• node：工作节点，会将工作状态反馈给manager，负责根据任务配置对数据源进行解析并同步到目标数据库；
• zookeeper：协调node工作；
• canal：获取数据库binlog。

otter使用纯Java开发，只要求机器上有java即可部署使用，不依赖任何其他的包。

6.3 otter在分布式MySQL数据库系统中的应用

之前设计的方案中，MySQL的分片采用了MGR方式，由一个统一的代理层对外提供JDBC接口。

这种方式给otter的同步带来一个问题，因为canal是采集binlog的，代理实际上并不提供binlog，因此不能直接将canal配置到代理上。而且MGR集群中只有Primary节点能够处理写操作，因此同步的目的端一定要是Primary节点，但是这个节点并不是绝对确定的，在otter原生没有支持自动选主的情况下，只能使用Proxy作为目的端。

基于这个限制，将机房中的结构简化为一个MGR集群，我们只能设计这样一种同步的结构：

• A机房的一台数据库节点作为otter的源节点；
• B机房的Proxy作为otter的目标节点。

B->A的复制则反之。采用这种真实数据库节点到Proxy的方式，就可以绕过限制，进行数据同步。

加上了otter之后，双机房之间的数据库同步逻辑应该如下图所示：

逻辑上机房分为存储层和同步工具层，从双机房同步角度来看，proxy也是同步工具的一部分，因为需要接受otter从另一个机房传输来的数据。

otter负责构建一个binlog通道，从MGR集群的任意一个节点接收binlog并转发数据到另一个机房的Proxy上，实现数据的双向同步。

otter原生不支持MGR，因此无法识别MGR内部的自动选主操作，我们选择的源端只是MGR集群内部的任意一个节点。当这个节点出现故障的时候，otter就无法抽取数据了。

otter支持主从配置，即配置源端的时候可以指定一个主从结构，两个节点，在manager的页面上提供一个开关，这个开关可以切换两个节点的角色。平时源端会自动挂载在主上，当主节点故障以后，手动触发开关，切换角色。

6.4 otter部署手册

我们将机房内部的集群简化成单点数据库，每个数据库上有一个ProxySQL，用于演示，MGR上的otter部署与之相同。

ProxySQL的部署，MySQL的部署和zookeeper的部署过程略。

otter分为两个组件，manager和node，分别对应两个包，我们将其解压到同一个节点上。

我们用三台机器来模拟双机房的同步：

6.4.1 manager的部署

解压manager的包到一个目录下，这里假设为/usr/local/manager，我们所有要配置的文件都在conf目录下，一般来说只需要配置otter.properties即可。

下面是配置项：

图中红框内是需要配置的地方，按照实际情况配置，说明如下：

• otter.domainName：这里配置本机的IP地址，不要配置成127.0.0.1，web端跳转的时候有问题；
• otter.port：选择一个没有冲突的端口，默认会是8080；
• otter.database.driver.url：otter有一些元数据，这里配置元数据保存地址的jdbc串；
• otter.database.driver.username：数据库用户名
• otter.database.driver.password：数据库密码
• otter.zookeeper.cluster.default：zk的地址和端口

otter有一些元数据表和初始化数据要创建，需要在之前配置的库中进行配置，SQL文件见附件。

启动脚本在bin目录下，直接用startup.sh启动即可。观察日志的manager.log文件发现这样的提示，表示正常启动：

此时在web浏览器中打开http://IP:port/login.htm，即可登录，默认用户名密码都是admin。

现在启动成功，可以继续下面的操作。下面是我们的配置清单：

• zookeeper：用于协同node工作，配置一个；
• node：因为是演示，只需要配置一个node；
• 数据源：因为是双向复制，要配置两个源端以及各自对应的目的端，一共四个数据源；
• 数据表：作为演示环境，需要一张测试表，这张测试表在每个数据源上配置一次；
• canal：每一个源端对应一个canal，一共配置两个；
• channel：channel可以理解为一个管道，我们需要配置一个双向管道；
• pipeline：pipeline可以理解为管道中的一个导线，是单向的，两个不同方向的导线组成了一个双向管道；
• 映射关系：pipeline的属性，规定了源和目的表，是实际上的表的对应关系。

我们的目的是配置一个双向通道的otter，并且实现node的高可用，下面是配置的步骤和最终结果：

1. “机器管理”-“Zookeeper管理”：

1. “机器管理”-“Node管理”，我们要配置两个以上的node：

将具体的node所在机器的IP和端口填写在这里即可。

1. “配置管理”-“主备配置”，添加主备信息，将机房A的主备信息添加进来：

这样就能保证在节点1以外退出之后，能够切换到节点2去继续完成同步工作。

1. “配置管理”-“数据源管理”，这里要配置两对一共四个数据源：

这里要注意的是“源端A”的配置，因为我们之前配置了主备，因此这里的jdbc url配置的时候要采用这种方式，将刚才的主备配置进去。

1. “配置管理”-“canal配置”，canal可以认为是一个binlog的消费者，因此双向复制的话需要两个canal，一个源对应一个：

上图是配置结束后的效果图，这里有几个细节需要注意一下：

需要将HA机制改成media才能保证使用到主备，这一点需要格外注意。

1. “配置管理”-“数据表配置”，配置要同步的数据表：

我的例子中只配置了一个，可以按照规则配置多个，也支持正则表达式。因为我们有四个数据源，因此要配置四个：

1. “同步管理”-“Channel管理”：

   
   官方推荐的双A机房部署，应该要打开“是否开启数据一致性”开关，打开即可，配置不需要改变。

因为是双向复制，需要配置两个pipeline，在channel管理页点击刚才配置好的channel就能进入pipeline管理页，按照下面的方式配置pipeline：

在配置的时候需要指定一个主站点，一个非主站点，其中主站点的高级设置中有“支持DDL”开关，需要勾选是，非主站点勾选否。

另外要注意到高可用性，即配置多个node，在配置pipeline的时候，选择node时将刚才配置好的node都勾选上：

每个pipeline都要配置自己的canal，定义映射关系。

这里是推荐的配置资料，可以参考：配置演示。

6.4.2 node的部署

和manager类似，node解压即可使用，所有要配置的配置文件都在conf目录下，我们只需要配置otter.properties文件即可。下面是配置项：

一般只需要配置最后一项即可，选择正确的manager地址和端口。需要注意，这里要在conf下添加一个nid文件，之前配置的时候我们的manager中，node的序号是1，因此这里配置成1：

echo 1 > nid

启动的脚本在bin目录下，执行startup.sh脚本即可，在之前步骤中，我们在manager中配置了node，此时就可以直接启动了。

6.4.3 高可用性部署

otter的高可用分为node的高可用和manager的高可用。

根据官方资料，node只有在第一次启动任务的时候需要manager，其他时间是不需要manager的，因此manager被定义为node的optional环境。

作为使用者来说，我们是希望manager能够自动实现failover。为了解决manager的高可用的问题，我们可以做到下面几点：

• 在两台机器上启动manager；
• 利用keepalived实现一个VIP，保证node连接在VIP机器上。

这样的情况下，一台manager宕机情况下，可以在最短时间内切换到新的manager。

node是工作节点，需要满足高可用需求。node本身在设计的时候是有高可用考量的，其高可用实现，根据官方描述，实现原理如下:

• 每个node在启动完成后，都会在zookeeper中创建一个Ephemerals节点(此节点特点，当node节点发生crash之后，与zookeeper建立的session因为没有心跳，超过一定时间后就会出现SesstionExpired，然后zookeeper会删除该节点)
• manager监听整个node节点列表的变化，任何一个node节点的消失，都会收到zookeeper watcher通知，与内存中上一个版本进行比较，判断出当前消失的node节点
• 针对该消失的node节点，会有一段保护期(因为可能正常的发布，会关闭node，同样会触发该watcher)，如果该node在保护期内重新启动了，则不做任何处理。默认保护期为90秒
• 如果保护期内node节点未正常启动，说明node是异常crash，通过查询配置，找到使用了该node的所有同步任务，对每个同步任务发起一个RESTART指令，让所有同步任务重新做一次负载均衡选择，避免挂死在老的node上，一直死等其结果返回。

根据官方的表述，我们只需要尽多的启动node节点就可以，节点越多，容错能力越强。

针对双机房的方案，应该保证下图中类似的部署方案：