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 到此为止：mysql%20mgr集群+consul实现高可用集群方案已尽全部完成 另：有很多公司服务的注册会到指定的consul%20client上，用consul%20client%20方式启动容易其他服务注册上去，可以改用API的方式注册到client上 如下： 参考：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