MySQL 组复制配置

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, 用做write set的冲突检测
  • 必须启用GTID特性,binlog日志格式必须为row模式
    目前一个MGR集群最多支持9个节点
  • 不支持外健的save point特性,无法做全局间的约束检测和部分回滚
    二进制日志不支持binlog event checksum

consul简介

微服务架构中非常重的一个模块,提供服务注册、服务发现等,常用的服务发现模块有,zookeeper、enreka、etcd、consul等。

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

环境说明:

192.168.4.3 consul server 目前只部署了一个server,可部署集群模式
192.168.4.4 mysql server mnode1、consul client
192.168.4.5 mysql server mnode2、consul client
192.168.4.6 mysql server mnode3、consul client
系统版本:centos 7.8
Mysql version:5.7.30
consul version:1.2.3

MGR集群环境搭建

mysql的安装这里就做详细说明(有需要的话,我把自动化安装脚本放在博客上)
三台mysql server 主机都安装mysql服务并初始化密码
修改配置文件,保证三台mysql server的配置一样(serverid、IP不一致)
vim /etc/my.cnf

[mysqld]
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
 
basedir = /app/mysql
datadir = /app/mysql/data
port = 3306
socket = /tmp/mysql.sock
character-set-server=utf8
 
log-error = /app/mysql/logs/mysqld.log
pid-file = /app/mysql/mysqld.pid
explicit_defaults_for_timestamp=true
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO"
 
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
server_id = 1
slave-parallel-type=LOGICAL_CLOCK
slave-parallel-workers=4
slave_preserve_commit_order=on
transaction_write_set_extraction=XXHASH64
loose-group_replication_group_name="8053c671-0622-11e8-a300-525400b9c5e8"
loose-group_replication_start_on_boot=off
loose-group_replication_local_address="192.168.4.4:33091"
loose-group_replication_group_seeds="192.168.4.4:33091,192.168.4.5:33091,192.168.4.6: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="192.168.4.0/24"
#eskip-grant-tables
#设置server启动时不自动启动组复制
replicate_ignore_db=mysql

在mnode1上执行的操作:

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

mysql> GRANT REPLICATION SLAVE ON *.* TO [email protected]'%' IDENTIFIED BY ‘[email protected]';

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='[email protected]' 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;
*************************** 1. row ***************************
CHANNEL_NAME: group_replication_applier
   MEMBER_ID: dd1d3e5e-af90-11ea-b7a6-000c29e6834a
 MEMBER_HOST: k8snode1
 MEMBER_PORT: 3306
MEMBER_STATE: ONLINE
1 row in set (0.00 sec)

Server mnode2、server mnode3

mysql> SET SQL_LOG_BIN=0;
Query OK, 0 rows affected (0.00 sec)
 
mysql> GRANT REPLICATION SLAVE ON *.* TO [email protected]'%' IDENTIFIED BY '[email protected]';
 
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='[email protected]' 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: c7cee656-af95-11ea-b025-000c294e6aa5
 MEMBER_HOST: k8snode3
 MEMBER_PORT: 3306
MEMBER_STATE: ONLINE
*************************** 2. row ***************************
CHANNEL_NAME: group_replication_applier
   MEMBER_ID: cbd1a474-af94-11ea-80ac-000c29874440
 MEMBER_HOST: k8snode2
 MEMBER_PORT: 3306
MEMBER_STATE: ONLINE
*************************** 3. row ***************************
CHANNEL_NAME: group_replication_applier
   MEMBER_ID: dd1d3e5e-af90-11ea-b7a6-000c29e6834a
 MEMBER_HOST: k8snode1
 MEMBER_PORT: 3306
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:192.168.4.3
consul-client:192.168.4.4、192.168.4.5、192.168.4.5
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 192.168.4.3上 编写配置文件

“`vim /etc/consul.d/server.json
{
“data_dir”: “/data/consul”,
“datacenter”: “dc1”,
“log_level”: “INFO”,
“server”: true,
“advertise_addr”:”192.168.4.3″,

“bootstrap_expect”: 3,
“bind_addr”: “192.168.4.3”,
“client_addr”: “192.168.4.3”,
“ui”:true
}

在`consul client 192.168.4.4、192.168.4.5、192.168.4.6`上编写配置文件,三台服务器的上bind_addr 修改为响应IP即可
​```vim cat /etc/consul.d/client.json
{
 "data_dir": "/data/consul",
 "enable_script_checks": true,
 "bind_addr": "192.168.4.4",
 "retry_join": ["192.168.4.3"],
 "retry_interval": "30s",
 "rejoin_after_leave": true,
 "start_join": ["192.168.4.3"]
}

启动consul server 在192.168.4.3上
nohup consul agent -config-dir=/etc/consul.d > /data/consul/consul.log &
启动consul client 在192.168.4.4、192.168.4.5、192.168.4.6nohup consul agent -config-dir=/etc/consul.d > /data/consul/consul.log &
观察consul server的log日志3个client自动注册到了consul上了
查看consul成员

consul members

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

MGR+Consul高可用实现

检测MGR集群状态

select * from performance_schema.replication_group_members;
分别在三台mysql server的主机上
检测primay 脚本

vim /data/consul/shell/check_mysql_mgr_master.sh
#!/bin/bash
port=3306
user="root"
passwod="123"
comm="/usr/local/mysql/bin/mysql -uuser -h 127.0.0.1 -Pport -ppasswod"
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 "mysqlport 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 "MySQLport state is not online...."
  exit 2
fi
{1}
{1}

判断是不是主节点

if [[ server_uuid ==primary_member ]]
then
  echo "MySQL port Instance is master ........"
  exit 0
else
  echo "MySQLport Instance is slave ........"
  exit 2
fi

检测slave脚本

vim /data/consul/shell/check_mysql_mgr_slave.sh
#!/bin/bash
port=3306
user="root"
passwod="123"
comm="/usr/local/mysql/bin/mysql -uuser -h 127.0.0.1 -Pport -ppasswod"
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 "mysqlport 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 "MySQLport state is not online...."
  exit 2
fi
{1}

判断是不是主节点

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 "MySQLport Instance is slave ........"
    exit 0
  else
    echo "MySQL $port Instance is master ........"
    exit 2
  fi
fi

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

检测master

[[email protected] 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

[[email protected] consul.d]# 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

到此为止:mysql mgr集群+consul实现高可用集群方案已尽全部完成
另:有很多公司服务的注册会到指定的consul client上,用consul client 方式启动容易其他服务注册上去,可以改用API的方式注册到client上.

Q.E.D.