基于MySQL双主的高可用解决方案理论及实践

MySQL在互联网应用中已经遍地开花，但是在银行系统中，还在生根发芽的阶段。本文记录的是根据某生产系统实际需求，对数据库高可用方案从需求、各高可用技术特点对比、实施、测试等过程进行整理，完善Mysql高可用方案，同时为后续开展分布式数据库相关测试做相应准备。

存储复制技术： 传统IOE架构下，常用高可用方案，靠存储底层复制技术实现数据的一致性，优点数据安全性有保障，限制在于是依赖存储硬件，实施成本较高。

keepalived+双主复制： 两台MySQL互为主从关系，即双主模式，通过Keepalived配置虚拟IP，实现当其中的一台数据库故障时，自动切换VIP到另外一台MySQL数据库，备机快速接管业务来保证数据库的高可用。

MHA： MHA部署在每台mysql服务器上，定时探测集群中的master节点，当master出现故障时，它可以自动将最新的slave提升为新的master，然后将所有其他的slave重新指向新的master，优点在最大程度保证数据的一致性的前提下实现快速切换，最少需要3台服务器，存在数据丢失的可能性。

PXC： Percona eXtra Cluster是Percona基于galera cluster封装的集群方案。不同于普通多主复制，PXC保障强一致性和实时同步，故障切换更快。但是也需要3个节点，配置相对复杂，对性能也稍有影响。

除了上述方案外，还有MMM、Heartbeat+DRBD等高可用方案，此处不做详细介绍。

综合评估下，本次实施采用了 keepalived+mysql双主实现数据库同城双机房的高可用。MySQL版本为： 5.7.21。 *** 作系统：Red Hat Enterprise Linux Server 7.3。

配置过程如下：

Mysql-master1: IP地址1 --以下简称master1

Mysql-master2: IP地址2 --以下简称master2

Mysql-vip : VIP地址 --应用连接使用

Mysql复制相关概念描述：

1、 Mysql主从复制图示：

2、 Mysql主从复制过程描述：

（1）master记录二进制日志：在每个事务更新数据完成之前，master在二进制日志记录这些改变。MySQL将事务写入二进制日志。在事务写入二进制日志完成后，master通知存储引擎提交事务。

（2）slave将master的binarylog拷贝到自己的中继日志：首先，slave开始一个工作线程——I/O线程。I/O线程在master上打开一个普通的连接，然后开始binlog dump process。Binlog dump process从master的二进制日志中读取事务，如果已经同步了master，它会睡眠并等待master产生新的事件。I/O线程将这些事务写入中继日志。

（3）SQL slave thread处理该过程的最后一步：SQL线程从中继日志读取事务，并重放其中的事务而更新slave的数据，使其与master中的数据一致。只要该线程与I/O线程保持一致，中继日志通常会位于OS的缓存中，所以中继日志的开销很小。

主主同步就是两台机器互为主的关系，在任何一台机器上写入都会同步至备端。

为了便于后续数据库服务器的扩展，且在整个复制环境中能够自动地切换，降低运维成本，引入了当前主流的基于Mysql GTID的复制特性，工作原理及优缺点简介如下。

3、 GTID工作原理简介：

（1） master更新数据时，会在事务前产生GTID，一同记录到Binlog日志中。

（2） slave的I/O线程将变更的binlog写入到本地的relay log中。

（3） slave的sql线程从relay log中获取GTID，然后对比slave端的binlog是否有记录。

（4） 如果有记录说明该GTID的事务已经执行，slave会忽略。

（5） 如果没有记录，slave就会从relay log中执行该GTID的事务，并记录到binlog。

（6） 在解析的过程中会判断是否有主键，如果有就用索引，如果没有就用全部扫描。

4、 GTID优点：

（1） 一个事务对应一个唯一的ID，一个GTID在一个服务器上 只会执行一次。（2） GTID是用来替代传统复制的方法，GTID复制与普通复制模式的最大不同就是不需要指定二进制文件名和位置。

（3） 减少手工干预和降低服务故障时间，当主机宕机之后会通过软件从众多的备机中提升一台备机为新的master。

5、 GTID也存在一些限制：

（1） 不支持非事务引擎。

（2） 不支持create table … select 语句复制(主库直接报错)。

（3） 不允许一个sql同时更新一个事务引擎表和非事务引擎表。

（4） 在一个复制组中，必须要求统一开启GTID或者是统一关闭GTID。

（5） 开启GTID需要重启(5.7版本除外)。

（6） 开启GTID后，就不再使用原理的传统复制方式。

（7） 不支持create temporary table 和 drop temporary table语句。

（8） 不支持sql_slave_skip_counter。

前置条件：

主备两个节点使用行内统一的安装部署脚本安装mysql5.7.21介质(略)

Master1端创建应用的数据库(略)

1、 修改MySQL配置文件

参考相关配置规范，分别设置master1、master2的my.cnf文件，

其中server-id参数设置为不同值

由于后续keepalived会挂起VIP，应用通过VIP连接数据库，为了避免应用程序无法通过VIP访问，需将两个节点的bind-address参数注释掉；

2、 设置master1端自动半同步模式

Mysql的同步模式主要有如下3种：

a. 主从同步复制：数据完整性好，但是性能消耗略高；

b. 主从异步复制：性能消耗低，但容易出现不一致；

c. 主从半自动复制：介于上述两种之间，既保持了数据的完整性，又提高了性能；

基于上述特性，建议采用半自动同步模式，由于后续要配置为双主模式，因此任一节点其角色既为master又为slave，因此相关的master/slave插件要同时配置，过程如下。

（1） 首先查看库是否支持动态加载(默认都支持)

（2） 主从库上分别安装插件

作为主库，安装插件semisync_master.so

作为从库，安装插件semisync_slave.so

（3） 安装完成后，从plugin表中能够看到刚刚安装的插件

（4） 分别打开主从库半同步复制

同时添加到各自的my.cnf中，在后续数据库实例重启时自动加载该配置。

此时查看状态还没有启动

（5） 两个节点分别启动IO进程

（6） 查看半同步状态

3、 将master1设为master2的主服务器

（1）在master1主机上创建授权账户，允许在master2主机上连接

（2）将主库master1数据导出

（3）将master.sql传输到master2上并导入

（4）在master2端将master1设置为自己的主库，并开启slave功能

在master2上查看slave状态

至此master1到master2的主从复制关系已经建立完成。

4、 将master2设为master1的主服务器

在master1上执行

在master1上查看slave状态

1、keepalived相关概念说明：

keepalived是集群管理中保证集群高可用的一个软件解决方案，其功能类似于heartbeat，用来防止单点故障

keepalived是以VRRP协议为实现基础的，VRRP全称VirtualRouter Redundancy Protocol，即虚拟路由冗余协议。

虚拟路由冗余协议，可以认为是实现路由器高可用的协议，即将N台提供相同功能的路由器组成一个路由器组，这个组里面有一个master和多个backup，master上面有一个对外提供服务的vip，master会发组播（组播地址为224.0.0.18），当backup收不到vrrp包时就认为master宕掉了，这时就需要根据VRRP的优先级来选举一个backup当master，这样的话就可以保证路由器的高可用了。

keepalived主要有三个模块，分别是core 、check和vrrp。core模块为keepalived的核心，负责主进程的启动、维护以及全局配置文件的加载和解析。check负责 健康 检查，包括常见的各种检查方式。vrrp模块是来实现VRRP协议的。同时为了避免出现脑裂，应关闭防火墙或者开启防火墙但允许接收VRRP协议。

2、keepalived的安装配置

（1）配置本地yum源，在master1和master2两台服务器上安装keepalived的相关依赖包Kernel-devel/openssl-devel/popt-devl等

配置指向rhel-7.5.iso的yum本地源，步骤略

注意：如不知道keepalived需要哪些依赖包，可到下载后的源码解压目录下查看INSTALL 文件内容，安装需要的依赖包，源码安装任何一个软件都要养成查看源码包文档的习惯，比如INSTALL，README,doc等文档，可以获得很多有用的信息。

（2）在两台mysql上解压缩并编译安装keepalived

（3）master1、master2上分别配置keepalived.conf

注意上图红色字体中两个节点配置相同处及差异。

说明：keepalived只有一个配置文件keepalived.conf，里面主要包括以下几个配置区域：

· global_defs：主要是配置故障发生时的通知对象以及机器标识。

· vrrp_instance：用来定义对外提供服务的VIP区域及其相关属性。

· virtual_server：虚拟服务器定义

（4）同时两个节点上都需要添加检测脚本

作用：是当mysql停止工作时自动关闭本机的keeplived服务，从而实现将故障主机踢出热备组，因每台机器上keepalived只添加了本机为realserver，所以当mysqld正常启动后，我们还需要手动启动keepalived服务。

（5）分别启动两个节点的keepalived服务

检查两个节点keepalived启动进程

检查两个节点的vip挂载情况

（6）主备机故障切换测试

停止master2的mysql服务，看keepalived 健康 检查程序是否会触发脚本，自动进行故障切换，步骤略

查看master1节点的VIP挂载情况，验证是否实现了自动切换，步骤略

说明在master2服务器的mysql服务发生故障时，触发了脚本，自动完成了切换。

（7）现在我们把master2的mysql服务开起来，并且keepalived的服务也需要启动。

即便master2的mysql服务和keepalived服务都重新开启了，master1仍然是主master了，master2未对主master的权利进行抢夺，说明设置的nopreempt参数生效了，为了保证群集的稳定性，生产环境不允许抢占配置，只有当master1的mysql服务坏掉的时候，master2才会再次成为主master，否则它永远只能当master1的备份。（注：nopreempt一般是在优先级高的mysql上设置）

Sysbench是一个模块化的、跨平台、多线程基准测试工具，可用于评估数据库负载情况，通过sysbench命令配置IP地址、端口号、用户名、密码连接到指定的数据库db1中，创建多个表，并快速插入指定条数的记录，观察主备库同步效率

(1) 下载开源工具sysbench-0.4.12.14.tar.gz，放置在相应目录下并解压

(2) 使用iso配置本地yum源并安装Sysbench如下的依赖包(步骤略)：autoconf/automake/cdbs/debhelper(>=9)/docbook-xml/docbook-xsl/libmysqlclient15-dev/libtool/xsltproc

(3) 编译sysbench

编辑配置文件/etc/ld.so.conf中添加mysql lib目录/mysql/app/5.7.21/lib，并执行命令ldconfig生效

(4) 执行sysbench压测

使用sysbench工具向主节点的db1数据库中创建5张表，并且每张表分别插入10万条记录

同时观察备机同步效率

几个重要的参数说明：

B、半自动同步模式、异步模式切换测试

(1) 检查主备同步状态，及同步参数设置

rpl_semi_sync_master_enabled参数表示启用半同步模式；

rpl_semi_sync_master_timeout参数单位为毫秒，表示主库事务等待从库返回commit成功信息超过10秒就降为异步模式，不再等待从库，等探测到从库io线程恢复后，再返回为半自动同步；

rpl_semi_sync_master_wait_no_slave参数表示事务提交后需要等待从库返回确认信息；

(2) 将slave的io线程停止

(3) 使用sysbench向master写入少量的数据，本例创建一张表，并插入10条记录，命令包装在1.sh测试脚本中

通过记录的时间戳发现，master在等待了slave10秒无响应，自动切换为异步模式，将数据写入本地。

(4) Slave启动io线程，数据自动追平

至此MySQL主主复制配置完成，运行在半自动同步模式，通过keepalived实现Mysql的HA高可用。

上线后应符合统一的标准监控策略，添加备份协议对数据进行周期备份并保存到带库中，以及定期的数据恢复测试。

由于是靠keepalived实现的高可用，还应将如下资源添加到监控管理平台：

1、 对每台数据库主机的3个keepalived进程进行监控；

2、 对主备节点的io线程、sql线程工作状态进行监控；

PXC简介

Percona XtraDB Cluster（简称PXC集群）提供了MySQL高可用的一种实现方法。

1.集群是有节点组成的，推荐配置至少3个节点，但是也可以运行在2个节点上。

2.每个节点都是普通的mysql/percona服务器，可以将现有的数据库服务器组成集群，反之，也可以将集群拆分成单独的服务器。

3.每个节点都包含完整的数据副本。

PXC集群主要由两部分组成：Percona Server with XtraDB和Write Set Replication patches（使用了Galera library，一个通用的用于事务型应用的同步、多主复制插件）。

PXC特性：

1，同步复制，事务要么在所有节点提交或不提交。

2，多主复制，可以在任意节点进行写 *** 作。

3，在从服务器上并行应用事件，真正意义上的并行复制。

4，节点自动配置，数据一致性，不再是异步复制。

PXC劣势：

1、 当前版本（5.6.20）的复制只支持InnoDB引擎，其他存储引擎的更改不复制。然而，DDL（Data Definition Language） 语句在statement级别被复制，并且，对mysql.*表的更改会基于此被复制。例如CREATE USER...语句会被复制，但是 INSERT INTO mysql.user...语句则不会。（也可以通过wsrep_replicate_myisam参数开启myisam引擎的复制，但这是一个实验性的参数）。

2、PXC集群一致性控制机制，事有可能被终止，原因如下：集群允许在两个节点上同时执行 *** 作同一行的两个事务，但是只有一个能执行成功，另一个会被终止，集群会给被终止的客户端返回死锁错误(Error: 1213 SQLSTATE: 40001 (ER_LOCK_DEADLOCK)).

3、写入效率取决于节点中最弱的一台，因为PXC集群采用的是强一致性原则，一个更改 *** 作在所有节点都成功才算执行成功。

原理描述

分布式系统的CAP理论：

C 一致性，所有的节点数据一致

A 可用性，一个或者多个节点失效，不影响服务请求P 分区容忍性，节点间的连接失效，仍然可以处理请求任何一个分布式系统，需要满足这三个中的两个安装部署

环境描述

三个node节点

node #1

hostname: percona1

IP: 192.168.100.7

node #2

hostname: percona2

IP: 192.168.100.8

node #3

hostname: percona3

IP: 192.168.100.9

基础环境包

可以选择源码或者yum，在此使用yum安装。

三个node节点都要执行以下 *** 作。

基础环境

yum -y groupinstall Base Compatibility libraries Debugging Tools Dial-up Networking suppport Hardware monitoring utilities Performance Tools Development tools组件安装

yum install http://www.percona.com/downloads/percona-release/redhat/0.1-3/percona-release-0.1-3.noarch.rpm -yyum install Percona-XtraDB-Cluster-55 -y

数据库配置

选择一个node作为名义上的master，咱们以node1为master，以下 *** 作只在node1上执行。

只需要修改mysql的配置文件－－/etc/my.cnf

说明：这里的IP地址是内网地址。

[root@i-kysyolko ~]# cat /etc/my.cnf

# Template my.cnf for PXC

# Edit to your requirements.

[mysqld]

datadir=/var/lib/mysql

user=mysql

# Path to Galera library

wsrep_provider=/usr/lib64/libgalera_smm.so# Cluster connection URL contains the IPs of node#1, node#2 and node#3wsrep_cluster_address=gcomm://192.168.100.7,192.168.100.8,192.168.100.9# In order for Galera to work correctly binlog format should be ROWbinlog_format=ROW

# MyISAM storage engine has only experimental supportdefault_storage_engine=InnoDB

# This changes how InnoDB autoincrement locks are managed and is a requirement for Galerainnodb_autoinc_lock_mode=2

# Node #1 address

wsrep_node_address=192.168.100.7

# SST method

wsrep_sst_method=xtrabackup-v2

# Cluster name

wsrep_cluster_name=my_centos_cluster

# Authentication for SST method

wsrep_sst_auth="sstuser:s3cret"

[mysqld_safe]

pid-file = /run/mysqld/mysql.pid

syslog

!includedir /etc/my.cnf.d

启动数据库

CentOS6：/etc/init.d/mysql bootstrap-pxc

CentOS7：systemctl start mysql@bootstrap.service配置数据库

mysql>show status like 'wsrep%'

+----------------------------+--------------------------------------+| Variable_name | Value |

+----------------------------+--------------------------------------+| wsrep_local_state_uuid | c2883338-834d-11e2-0800-03c9c68e41ec |...

| wsrep_local_state | 4 |

| wsrep_local_state_comment | Synced |

...

| wsrep_cluster_size | 1 ＃主要看这里 |

| wsrep_cluster_status | Primary |

| wsrep_connected | ON |

...

| wsrep_ready | ON |

+----------------------------+--------------------------------------+40 rows in set (0.01 sec)

＃ 数据库用户名密码的设置

mysql@percona1>UPDATE mysql.user SET password=PASSWORD("Passw0rd") where user='root'# 创建、授权、同步账号

mysql@percona1>CREATE USER 'sstuser'@'localhost' IDENTIFIED BY 's3cret'mysql@percona1>GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'sstuser'@'localhost'mysql@percona1>FLUSH PRIVILEGES

node2、node3节点配置

接下来进行其它节点的配置，上面的组件都已经安装完成。现在直接进行数据库的配置。

只需要修改第26行，当前node的IP地址。两个node都是只是修改这个地址即可。

wsrep_node_address=192.168.100.8

[root@i-kysyolko ~]# cat /etc/my.cnf

# Template my.cnf for PXC

# Edit to your requirements.

[mysqld]

datadir=/var/lib/mysql

user=mysql

# Path to Galera library

wsrep_provider=/usr/lib64/libgalera_smm.so# Cluster connection URL contains the IPs of node#1, node#2 and node#3wsrep_cluster_address=gcomm://192.168.100.7,192.168.100.8,192.168.100.9# In order for Galera to work correctly binlog format should be ROWbinlog_format=ROW

# MyISAM storage engine has only experimental supportdefault_storage_engine=InnoDB

# This changes how InnoDB autoincrement locks are managed and is a requirement for Galerainnodb_autoinc_lock_mode=2

# Node #1 address

wsrep_node_address=192.168.100.8

# SST method

wsrep_sst_method=xtrabackup-v2

# Cluster name

wsrep_cluster_name=my_centos_cluster

# Authentication for SST method

wsrep_sst_auth="sstuser:s3cret"

[mysqld_safe]

pid-file = /run/mysqld/mysql.pid

syslog

!includedir /etc/my.cnf.d

启动数据库

CentOS6：/etc/init.d/mysql start

CentOS7：systemctl start mysql.service

说明：

1、除了名义上的master之外，其它的node节点只需要启动mysql即可。

2、节点的数据库的登陆和master节点的用户名密码一致，自动同步。所以其它的节点数据库用户名密码无须重新设置。

测试

在任意一个node上，进行 *** 作，然后去其它的节点上看是否取得相同的结果

为了保障数据的安全与稳定性，我们常用数据库的主从复制与主主复制来实现。主从复制为从机实时拷贝一份主机的数据，当主机有数据变化时，从机的数据会跟着变，当从机数据有变化时，主机数据不变；同样地，主主复制就是，多个主机之间，只要有一个主机的数据变化了，其它主机数据也会跟着变化。

添加以下内容

如果你是使用我之前那种方式启动的MySQL，那么你只需要去你相关联的宿主机的配置文件夹里面去建立一个 my.cnf 然后写入上面的类容就好了。

比如：我的启动命令如下（不应该换行的，这里为了方便查看，我给它分行了）

那么我只需要在 /docker/mysql_master/conf 这个目录下创建 my.cnf 文件就好了。

这个命令是需要在容器里面执行的

docker重启mysql会关闭容器，我们需要重启容器。

确保在主服务器上 skip_networking 选项处于 OFF 关闭状态, 这是默认值。 如果是启用的，则从站无法与主站通信，并且复制失败。

我的命令如下

在从服务器配置连接到主服务器的相关信息 （在容器里面的mysql执行）

上面代码的xxxxx你需要换成你的IP，docker 查看容器 IP 的命令如下：

启动的那个从服务器的线程

测试的话，你可以在主服务器里面，创建一个数据库，发现从服务器里面也有了，就成功了。

如果你还想要一个从服务器，那么你只需要按照上面配置从服务器再配置一个就行了，新建的从服务器，会自动保存主服务器之前的数据。（测试结果） 如果你上面的主从复制搞定了，那么这个主主复制就很简单了。我们把上面的从服务器也改成主服务器

1）、修改上面的从服务器的my.cnf文件，和主服务器的一样（注意这个server-id不能一样）然后重启服务器 2）、在从服务器里面创建一个复制用户创建命令一样（这里修改一下用户名可以改为 repl2） 3）、在之前的主服务器里面运行下面这个代码

上面主要是教你怎么搭建一个MySQL集群，但是这里面还有很多其它的问题。也是我在学习过程中思考的问题，可能有的小伙伴上来看到文章长篇大论的看不下去，只想去实现这样一直集群功能，所以我就把问题写在下面了。

1）、MySQL的replication和pxc MySQL的集群方案有replication和pxc两种，上面是基于replication实现的。

replication: 异步复制，速度快，无法保证数据的一致性。 pxc: 同步复制，速度慢，多个集群之间是事务提交的数据一致性强。

2）、MySQL的replication数据同步的原理 我们在配置的时候开启了它的二进制日志，每次 *** 作数据库的时候都会更新到这个日志里面去。主从通过同步这个日志来保证数据的一致性。

3）、可否不同步全部的数据 可以配置，同步哪些数据库，甚至是哪些表。

4）、怎么关闭和开始同步

5）、我就我的理解画出了，主从、主从从、主主、复制的图。

往期推荐：

利用Docker仅花1分钟时间安装好MySQL服务

Linux下MySQL 5.7的离线与在线安装（图文）

Linux下安装MySQL8.0（收藏！）

---