MGR+Consul 高可用架构搭建指南

一、MGR+Consul 架构简介

基于当前MySQL数据库单点故障的问题，传统的MHA、PXC等方案通过VIP或DNS切换实现数据库的高可用，然而这些方案难以实现数据库的数据强一致性。在此基础上，采用MGR集群结合Consul服务注册发现的方案，使得应用端能够通过动态DNS方式访问MGR集群，从而实现数据库的高可用性和自动化切换。

本次环境共包含7台机器，其中3台作为MySQL的MGR数据节点（同时安装Consul作为Consul Client），采用单主模式；3台作为Consul Server集群，确保Consul的高可用；剩下1台作为DNS服务器，负责解析MGR节点的域名。

二、MGR 搭建

2.1 申请3台MGR机器

docker pull lhrbest/lhrcentos76:8.0docker network create --subnet=192.168.68.0/16 mhalhrdocker rm -f mysql8022mgr33060 mysql8022mgr33061 mysql8022mgr33062docker run -d --name mysql8022mgr33060 \--network mhalhr --ip 192.168.68.60 --privileged=true \-p 33060:3306 --v /sys/fs/cgroup:/sys/fs/cgroup lhrbest/lhrcentos76:8.0 init...

2.2 安装MySQL环境

docker exec -it mysql8022mgr33060 bashtar -xvf mysql-8.0.22-1.el7.x86_64.rpm-bundle.taryum localinstall -y mysql-community-*.rpmmv /usr/lib64/mysql/plugin/validate_password.so /usr/lib64/mysql/plugin/validate_password.so.bkmv /usr/lib64/mysql/plugin/component_validate_password.so /usr/lib64/mysql/plugin/component_validate_password.so.bksystemctl start mysqldmysql -uroot -palter user root@localhost identified with mysql_native_password by 'lhr';grant all on *.* to root@localhost with grant option;create user root@'%' identified with mysql_native_password by 'lhr';grant all on *.* to root@'%' with grant option;flush privileges;

2.3 修改MySQL参数

cat > /etc/my.cnf << EOF[mysqld]user=mysqlport=3306character_set_server=utf8mb4secure_file_priv=serverserver-id = 802233060default-time-zone = '+8:00'log_timestamps = SYSTEMlog-bin = binlog_format=rowbin_checksum=NONEskip-name-resolvedlog_slave_updates = 1gtid-mode=ONenforce-gtid-consistency=onmax_allowed_packet = 500Mmaster_info_repository=TABLErelay_log_info_repository=TABLErelay_log=lhrmgr60-relay-bintransaction_write_set_extraction=XXHASH64loose-group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"loose-group_replication_start_on_boot=OFFloose-group_replication_local_address= "192.168.68.60:33061"loose-group_replication_group_seeds= "192.168.68.60:33061,192.168.68.61:33061,192.168.68.62:33061"loose-group_replication_bootstrap_group=OFFloose-group_replication_ip_whitelist="192.168.68.60,192.168.68.61,192.168.68.62"report_host=192.168.68.60report_port=3306EOF

2.4 重启MySQL环境

systemctl restart mysqlddocker restart mysql8022mgr33060 mysql8022mgr33061 mysql8022mgr33062

2.5 安装MGR插件

mysql -uroot -plhr -h 192.168.66.35 -P33060 INSTALL PLUGIN group_replication SONAME 'group_replication.so';

2.6 设置复制账号

mysql -uroot -plhr -h 192.168.68.60 -P33060 CREATE USER repl@'%' IDENTIFIED BY 'lhr';GRANT REPLICATION SLAVE, BACKUP_ADMIN ON *.* TO repl@'%';FLUSH PRIVILEGES;

2.7 启动MGR

SET GLOBAL group_replication_bootstrap_group=ON;START GROUP_REPLICATION;SET GLOBAL group_replication_bootstrap_group=OFF;

2.8 其他节点加入MGR

reset master;START GROUP_REPLICATION;

三、Consul Server 集群搭建

3.1 申请3台Consul Server主机

docker rm -f lhrconsulmgr66 lhrconsulmgr67 lhrconsulmgr68docker run -d --name lhrconsulmgr66 -h lhrconsulmgr66 \--network mhalhr --ip 192.168.68.66 -p 8566:8500 --privileged=true lhrbest/lhrcentos76:8.0 /usr/sbin/init...

3.2 安装Consul Server

docker exec -it lhrconsulmgr66 bashunzip consul_1.9.4_linux_amd64.zipmkdir -p /consul/datanohup consul agent -server -bootstrap-expect 3 -bind=192.168.68.66 -client=0.0.0.0 -data-dir=/consul/data -node=n66 -ui > /consul/logs/consul.log 2>&1 &

3.3 Consul Server 集群验证

consul membersconsul operator raft list-peers

四、MySQL节点上安装Consul Client

4.1 安装Consul Client

docker cp consul_1.9.4_linux_amd64.zip mysql8022mgr33060:/dockerunzip consul_1.9.4_linux_amd64.zipmkdir -p /consul/logs

4.2 检测主次节点脚本

#!/bin/bashport=3306user="root"pass="lhr"comm="/usr/bin/mysql -u $user -h localhost -P $port -p $pass"value=`$comm -Nse "select 1"`server_uuid=`$comm -Nse "select variable_value from performance_schema.global_variables where VARIABLE_NAME='server_uuid';"`primary_member=`$comm -Nse "select variable_value from performance_schema.global_status where VARIABLE_NAME='group_replication_primary_member';"`

4.3 服务配置

cat > /etc/consul.d/rw-mysql-mgr-server-lhr.json << EOF{  "service": {    "name": "rw-mysql-mgr-server-lhr",    "tags": ["MGR-Master"],    "address": "192.168.68.60",    "port": 3306,    "check": {      "args": ["/data/consul/shell/check_mysql_mgr_master.sh"],      "interval": "5s"    }  }}EOF

4.4 服务测试

yum install -y bind-utils bind bind-chroot dnsmasqdig @192.168.68.66 -p 8600 r-mysql-mgr-server-lhr.service.consuldig @192.168.68.66 -p 8600 rw-mysql-mgr-server-lhr.service.consul

五、DNS 解析配置

5.1 使用dnsmasq配置DNS解析

docker rm -f lhrconsuldns69docker run -d --name lhrconsuldns69 -h lhrconsuldns69 \--network mhalhr --ip 192.168.68.69 -p 53:53 --privileged=true lhrbest/lhrcentos76:8.0 /usr/sbin/init...

5.2 DNS解析测试

nslookup r-mysql-mgr-server-lhr.service.consulnslookup rw-mysql-mgr-server-lhr.service.consul

六、测试高可用

6.1 Consul Server 高可用测试

docker stop lhrconsulmgr66 lhrconsulmgr67consul membersconsul operator raft list-peers

6.2 MGR 高可用测试

mysql -uroot -plhr -h 192.168.68.60 -P3306 shutdownmysql -uroot -plhr -h 192.168.68.61 -P3306 SELECT * FROM performance_schema.replication_group_members;

通过以上步骤，可以成功搭建MGR+Consul高可用架构，实现数据库的高可用性和自动化切换。

转载地址：http://ijnyz.baihongyu.com/

你可能感兴趣的文章