MySQL MGR+ Consul之数据库高可用方案最佳实践

MySQL MGR+ Consul之数据库高可用方案最佳实践
背景说明：
    基于目前存在很多MySQL数据库单点故障,传统的MHA，PXC等方案用VIP或者DNS切换的方式可以实现、基于数据库的数据强一致性考虑，采用MGR集群，采用consul服务注册发现实现应用端通过动态DNS 访问MGR集群，实现数据库高可用，自动化切换的方案
MGR简介
MySQL Group Replication（MGR）是MySQL官方在5.7.17版本引进的一个数据库高可用与高扩展的解决方案，以插件形式提供，实现了分布式下数据的最终一致性，总结MGR特点如下：
高一致性：基于分布式paxos协议实现组复制，保证数据一致性；
高容错性：自动检测机制，只要不是大多数节点都宕机就可以继续工作，内置防脑裂保护机制；
高扩展性：节点的增加与移除会自动更新组成员信息，新节点加入后，自动从其他节点同步增量数据，直到与其他节点数据一致；
高灵活性：提供单主模式和多主模式，单主模式在主库宕机后能够自动选主，所有写入都在主节点进行，多主模式支持多节点写入。
MGR原理说明：
组复制是一种可用于实现容错系统的技术。 复制组是一个通过消息传递相互交互的 server 集群。通信层提供了原子消息(atomic message)和完全有序信息交互等保障机制
实现了基于复制协议的多主更新
复制组由多个 server成员构成,并且组中的每个 server 成员可以独立地执行事务。但所有读写(RW)事务只有在冲突检测成功后才会提交。只读(RO)事务不需要在冲突检测,可以立即提交。句话说,对于任何 RW 事务,提交操作并不是由始发 server 单向决定的,而是由组来决定是否提交。准确地说,在始发 server 上,当事务准备好提交时,该 server 会广播写入值(已改变的行)和对应的写入集(已更新的行的唯一标识符)。然后会为该事务建立一个全局的顺序。最终,这意味着所有 server 成员以相同的顺序接收同一组事务。因此,所有 server 成员以相同的顺序应用相同的更改,以确保组内一致。组复制是一种 share-nothing 复制方案,其中每个 server 成员都有自己的完整数据副本。
MGR的局限性：
仅支持InnodDB存储引擎的表，并且每个表必须有主键ID, 用做wirte set的冲突检测
必须启用GTID特性，binlog日志格式必须为row模式
目前一个MGR集群最多支持9个节点
不支持外健的save point特性，无法做全局间的约束检测和部分回滚
二进制日志不支持binlog event checksum
sonsul简介
微服务架构中非常重的一个模块，提供服务注册、服务发现等，常用的服务发现模块有，zookeeper、enreka、etcd、cunsul等。

cousul是分布式、高可用、可横向发展的中间键，其特性如下：
1、service discovery：通过dns或者http接口提供服务注册和发现
2、health checking：自带健康检查，可提供服务故障时的转移
3、key/value storage：可存储动态配置的系统，提供http接口，
4、multi-datacenter：可以支持多数据中心

环境说明：
10.88.128.163 consul server 目前只部署了一个server，可部署集群模式
10.88.6.251 mysql server  mnode1、consul client
10.88.6.252 mysql server  mnode2、consul client
10.88.6.253 mysql server  mnode3、consul client
系统版本：centos 7.4
Mysql version：5.7.25
consul version:1.2.3

MGR集群环境搭建
mysql的安装这里就做详细说明(有需要的话，我把自动化安装脚本放在博客上)
三台mysql server 主机都安装mysql服务并初始化密码
修改配置文件，保证三台mysql server的配置一样(serverid、IP不一致)
vim /etc/my.cnf
##gtid配置
server_id = 1 ## 保证三台主机的serverid 不一致，这里配置为1，11，111
gtid_mode=ON
enforce_gtid_consistency=ON
master_info_repository=TABLE
relay_log_info_repository=TABLE
binlog_checksum=NONE
log_slave_updates=ON
log_bin=binlog
binlog_format=ROW
innodb_buffer_pool_instances=4
innodb_buffer_pool_size=1G
innodb_flush_log_at_trx_commit=2
sync_binlog=0
#for parallel apply binlog
slave-parallel-type=LOGICAL_CLOCK
slave-parallel-workers=4
slave_preserve_commit_order=on
##mgr配置
transaction_write_set_extraction=XXHASH64
##表示将加入或者创建的复制组命名为8053c671-0622-11e8-a300-525400b9c5e8，可以自己指定
loose-group_replication_group_name=“8053c671-0622-11e8-a300-525400b9c5e8”
#设置server启动时不自动启动组复制
loose-group_replication_start_on_boot=off
#设置组复制的端口，并保证其集群可以正常访问
loose-group_replication_local_address= “10.88.6.251:33091"
#当加入组时应该连接到这些服务器上进行配置
loose-group_replication_group_seeds=“10.88.6.251:33091,10.88.6.252:33091,10.88.6.253:33091"
loose-group_replication_bootstrap_group= off
loose-group_replication_single_primary_mode=ON ##开启单主模式
#关闭多主模式
loose-group_replication_enforce_update_everywhere_checks=FALSE
#配置集群白名单
loose-group_replication_ip_whitelist="10.88.6.0/24"

mnode1：
登陆mysql
创建mysql账号，执行如下命令，创建账号关闭binglog
mysql> SET SQL_LOG_BIN=0;
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%' IDENTIFIED BY ‘pan@345';

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> SET SQL_LOG_BIN=1;
Query OK, 0 rows affected (0.00 sec)

mysql> CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='pan@345' FOR CHANNEL 'group_replication_recovery';

mysql> INSTALL PLUGIN group_replication SONAME 'group_replication.so';
Query OK, 0 rows affected (0.16 sec)
#此引导应仅由单个 sever 独立完成,该 server 启动组并且只启动一次。 这就是为什么引导配置选项的值不保存在配置文件中的原因。 如果将其保存在配置文件中,则在重新启动时,server 会自动引导具有相同名称的第二
个组。 这将导致两个不同的组具有相同的名称 mysql> SET GLOBAL group_replication_bootstrap_group=ON;  Query OK, 0 rows affected (0.00 sec)
mysql> START GROUP_REPLICATION;
Query OK, 0 rows affected (1.78 sec)

mysql> SET GLOBAL group_replication_bootstrap_group=OFF;
Query OK, 0 rows affected (0.00 sec)

mysql>SELECT * FROM performance_schema.replication_group_members\G;
CHANNEL_NAME: group_replication_applier
   MEMBER_ID: a7495a32-398b-11e9-bec1-080027857522
 MEMBER_HOST: mnode1
 MEMBER_PORT: 3309
MEMBER_STATE: ONLINE

Server mnode2、server mnode3

mysql> SET SQL_LOG_BIN=0;
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT REPLICATION SLAVE ON *.* TO rpl_user@'%' IDENTIFIED BY 'Workhard@345';

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> SET SQL_LOG_BIN=1;
Query OK, 0 rows affected (0.00 sec)

mysql> CHANGE MASTER TO MASTER_USER='rpl_user', MASTER_PASSWORD='pan@345' FOR CHANNEL 'group_replication_recovery';

mysql> INSTALL PLUGIN group_replication SONAME 'group_replication.so';
Query OK, 0 rows affected (0.16 sec)

set global group_replication_allow_local_disjoint_gtids_join=ON;
Query OK, 0 rows affected (0.00 sec) mysql> START GROUP_REPLICATION;
Query OK, 0 rows affected (44,88 sec) mysql> SELECT * FROM performance_schema.replication_group_members\G;
*************************** 1. row ***************************
CHANNEL_NAME: group_replication_applier
   MEMBER_ID: 3c68303c-391b-11e9-b5b5-08002788ba6b
 MEMBER_HOST: mnode3
 MEMBER_PORT: 3309
MEMBER_STATE: ONLINE
*************************** 2. row ***************************
CHANNEL_NAME: group_replication_applier
   MEMBER_ID: a7495a32-398b-11e9-bec1-080027857522
 MEMBER_HOST: mnode1
 MEMBER_PORT: 3309
MEMBER_STATE: ONLINE
*************************** 3. row ***************************
CHANNEL_NAME: group_replication_applier
   MEMBER_ID: b8ff481f-39d6-11e9-9208-0800278c8292
 MEMBER_HOST: mnode2
 MEMBER_PORT: 3309
MEMBER_STATE: ONLINE
3 rows in set (0.00 sec)

到此为止MGR集群已经搭建完毕、在启动集群的同时可以同时观察其成员的加入的整个过程
说几点注意事项：
1、保证集群的端口互通
2、保证performance_schema = 1 否则库里查不动成员
Check MGR集群
在primary上，创建库、表、以及增删改查信息，其集群内其它的节点都会有相应的操作，确认其集群可以正常提供服务
搭建consul 使其mysql-primary和mysql-slave 注册到服务发现上

consul-server：10.88.128.163
consul-client：10.88.6.251、10.88.6.252、10.88.6.253
consul安装so easy
在官网:https://www.consul.io/downloads.html下载对应的版本，解压后copy consul 到/usr/local/bin/下即可

分别在4台机器上安装然后运行
mkdir -pv /etc/consul.d/  && mkdir -pv /data/consul/
mkdir -pv /data/consul/shell

在consul server 10.88.128.163上 编写配置文件
vim /etc/consul.d/server.json
{
  "data_dir": "/data/consul",
  "datacenter": "dc1",
  "log_level": "INFO",
  "server": true,
  "advertise_addr":"10.88.128.163",
  "bootstrap_expect": 3,
  "bind_addr": "10.88.128.163",
  "client_addr": "10.88.128.163",
  "ui":true
}

在consul client 10.88.6.251、10.88.6.252、10.88.6.253上编写配置文件,三台服务器的上bind_addr 修改为响应IP即可
vim cat /etc/consul.d/client.json
{
  "data_dir": "/data/consul",
  "enable_script_checks": true,
  "bind_addr": "10.88.6.252",
  "retry_join": ["10.88.128.163"],
  "retry_interval": "30s",
  "rejoin_after_leave": true,
  "start_join": ["10.88.128.163"]
}
 
启动consul server 在10.88.128.163上
nohup consul agent -config-dir=/etc/consul.d > /data/consul/consul.log &

启动consul client 在10.88.6.251、10.88.6.252、10.88.6.253
nohup consul agent -config-dir=/etc/consul.d > /data/consul/consul.log &
观察consul server的log日志3个client自动注册到了consul上了

查看consul成员

访问consulserver的web页面 http://10.88.128.163:8500/ui/

到此为止consul 集群已经搭建成功了

下面我们继续为实现mysql的高可用集群 MGR+Consul高可用实现

检测MGR集群状态

查看那个是主节点

分别在三台mysql server的主机上
检测primay 脚本
vim /data/consul/shell/check_mysql_mgr_master.sh
#!/bin/bash
port=3309
user="root"
passwod="123"

comm="/usr/local/mysql/bin/mysql -u$user -h 127.0.0.1 -P $port -p$passwod"
value=`$comm -Nse "select 1"`
primary_member=`$comm -Nse "select variable_value from performance_schema.global_status WHERE VARIABLE_NAME= 'group_replication_primary_member'"`
server_uuid=`$comm -Nse "select variable_value from performance_schema.global_variables where VARIABLE_NAME='server_uuid';"` # 判断mysql是否存活
if [ -z $value ]
then
   echo "mysql $port is down....."
   exit 2
fi # 判断节点状态
node_state=`$comm -Nse "select MEMBER_STATE from performance_schema.replication_group_members where MEMBER_ID='$server_uuid'"`
if [ $node_state != "ONLINE" ]
then
   echo "MySQL $port state is not online...."
   exit 2
fi # 判断是不是主节点
if [[ $server_uuid == $primary_member ]]
then
   echo "MySQL $port  Instance is master ........"
   exit 0
else
   echo "MySQL $port  Instance is slave ........"
   exit 2
fi

检测slave脚本
vim /data/consul/shell/check_mysql_mgr_slave.sh

#!/bin/bash
port=3309
user="root"
passwod="123"

comm="/usr/local/mysql/bin/mysql -u$user -h 127.0.0.1 -P $port -p$passwod"
value=`$comm -Nse "select 1"`
primary_member=`$comm -Nse "select variable_value from performance_schema.global_status WHERE VARIABLE_NAME= 'group_replication_primary_member'"`
server_uuid=`$comm -Nse "select variable_value from performance_schema.global_variables where VARIABLE_NAME='server_uuid';"` # 判断mysql是否存活
if [ -z $value ]
then
   echo "mysql $port is down....."
   exit 2
fi

# 判断节点状态
node_state=`$comm -Nse "select MEMBER_STATE from performance_schema.replication_group_members where MEMBER_ID='$server_uuid'"`
if [ $node_state != "ONLINE" ]
then
   echo "MySQL $port state is not online...."
   exit 2
fi # 判断是不是主节点
if [[ $server_uuid != $primary_member ]]
then
   echo "MySQL $port  Instance is slave ........"
   exit 0
else
   node_num=`$comm -Nse "select count(*) from  performance_schema.replication_group_members"`
   # 判断如果没有任何从节点，主节点也注册从角色服务。
   if [ $node_num -eq 1 ]
   then
       echo "MySQL $port  Instance is slave ........"
       exit 0
   else
       echo "MySQL $port  Instance is master ........"
       exit 2
   fi
fi

consul client端接服务发现的json脚本

检测master
[root@mnode1 consul.d]# cat /etc/consul.d/master.json
{
  "services": [
    {
      "name": "write-mysql-primary",
      "tags": [
        "master-write"
      ],
      "address": "10.88.6.251",
      "port": 3309,
      "checks": [
        {
           "Args":["/data/consul/shell/check_mysql_mgr_master.sh"],
          "Shell": "/bin/bash",
          "interval": "15s"
        }
      ]
    }
  ]
}
检测slave
 cat /etc/consul.d/slave.json
{
  "services": [
    {
      "name": "read-mysql-slave",
      "tags": [
        "slave-read"
      ],
      "address": "10.88.6.251",
      "port": 3309,
      "checks": [
        {
           "Args":["/data/consul/shell/check_mysql_mgr_slave.sh"],
           "Shell": "/bin/bash",
           "interval": "15s"
        }
      ]
    }
  ]

基于不同consul版本配置可能不太一样，三台机器都需要相应的json脚本检测mysql是否为主或从，配置文件修改相应的IP即可使用

查看consul server ui界面即可看到三台mysql的mgr集群已经注册到consul服务上了

注意：由于每台mysql server 上都有master、slave 检测脚本、而mysql server 只能是master 或者slave、所以存在失败的检测，master检测只有一个成功，slave检测只有一个失败

consul dns配置
查看dns信息

App端配置域名服务器IP来解析consul后缀的域名，DNS解析及跳转， 有三个方案：
1. 原内网dns服务器,做域名转发，consul后缀的，都转到consul server上，目前采用的这种方式
2. dns全部跳到consul DNS服务器上，非consul后缀的,使用 recursors 属性跳转到原DNS服务器上
3. dnsmaq 转： server=/consul/10.16.X.X#8600 解析consul后缀的
我们内网dns是用的bind，对于bind的如何做域名转发consul官网也有栗子：https://www.consul.io/docs/guides/forwarding.html check下MGR 切换master 解析的IP是否转换

切换mgr primary

参考：https://www.consul.io
           https://www.jianshu.com/p/ca1af156f656
           https://blog.csdn.net/sinat_36888624/article/details/79215233
           https://www.cnblogs.com/gomysql/p/8985774.html
           http://www.cnblogs.com/gomysql/p/8010552.html