MySql教程
【DB宝45】MySQL高可用之MGR+Consul架构部署
本文主要是介绍【DB宝45】MySQL高可用之MGR+Consul架构部署，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！
目录
- 一、MGR+Consul架构简介
- 二、搭建MGR
- 2.1、申请3台MGR机器
- 2.2、3台主机安装MySQL环境
- 2.3、修改MySQL参数
- 2.4、重启MySQL环境
- 2.5、安装MGR插件（所有节点执行）
- 2.6、设置复制账号
- 2.7、启动MGR，在主库(192.168.68.60)上执行
- 2.8、其他节点加入MGR，在从库（192.168.68.61，192.168.68.62）上执行
- 2.9、测试同步
- 三、搭建Consul Server集群
- 3.1、申请3台Consul Server主机
- 3.2、安装Consul Server
- 3.3、web访问
- 四、在MySQL节点上安装Consul Client
- 4.1、安装Consul Client
- 4.2、在Consul client的3个节点上编写检测primay脚本和检测slave脚本
- 4.3、service配置
- 4.4、service测试
- 五、配置DNS解析域名
- 5.1、使用dnsmasq来配置DNS解析
- 5.2、MySQL通过域名连接
- 六、测试高可用
- 6.1、测试Consul Server的高可用
- 6.2、测试MySQL的负载均衡和读写分离
- 6.3、测试MGR高可用
一、MGR+Consul架构简介
基于目前存在很多MySQL数据库单点故障，传统的MHA，PXC等方案用VIP或者DNS切换的方式可以实现、基于数据库的数据强一致性考虑，采用MGR集群，采用consul服务注册发现实现应用端通过动态DNS 访问MGR集群，实现数据库高可用，自动化切换的方案。
有关MGR之前发布过几篇文章，连接如下：
【DB宝18】在Docker中安装使用MySQL高可用之MGR
【DB宝35】使用MySQL 8.0 克隆(clone)插件快速添加MGR节点
本次环境一共包括7台机器，其中3台做MySQL的MGR数据节点（其上也需要安装Consul，作为Consul Client），单主模式，还有3台做Consul Server集群，实现Consul的高可用，剩下一台做DNS服务器，用来解析MGR节点域名，规划如下表所示：
二、搭建MGR
2.1、申请3台MGR机器
-- 拉取镜像 docker pull lhrbest/lhrcentos76:8.0 -- 创建专用网络 docker network create --subnet=192.168.68.0/16 mhalhr docker network inspect mhalhr -- 生成3台CentOS的环境 docker rm -f mysql8022mgr33060 mysql8022mgr33061 mysql8022mgr33062 docker run -d --name mysql8022mgr33060 -h lhrmgr60 \ --network mhalhr --ip 192.168.68.60 --privileged=true \ -p 33060:3306 \ -v /sys/fs/cgroup:/sys/fs/cgroup \ lhrbest/lhrcentos76:8.0 init docker run -d --name mysql8022mgr33061 -h lhrmgr61 \ --network mhalhr --ip 192.168.68.61 --privileged=true \ -p 33061:3306 \ -v /sys/fs/cgroup:/sys/fs/cgroup \ lhrbest/lhrcentos76:8.0 init docker run -d --name mysql8022mgr33062 -h lhrmgr62 \ --network mhalhr --ip 192.168.68.62 --privileged=true \ -p 33062:3306 \ -v /sys/fs/cgroup:/sys/fs/cgroup \ lhrbest/lhrcentos76:8.0 init -- 拷贝MySQL安装文件到MySQL容器内 docker cp mysql-8.0.22-1.el7.x86_64.rpm-bundle.tar mysql8022mgr33060:/ docker cp mysql-8.0.22-1.el7.x86_64.rpm-bundle.tar mysql8022mgr33061:/ docker cp mysql-8.0.22-1.el7.x86_64.rpm-bundle.tar mysql8022mgr33062:/ docker network connect bridge mysql8022mgr33060 docker network connect bridge mysql8022mgr33061 docker network connect bridge mysql8022mgr33062 docker restart mysql8022mgr33060 mysql8022mgr33061 mysql8022mgr33062
2.2、3台主机安装MySQL环境
docker exec -it mysql8022mgr33060 bash docker exec -it mysql8022mgr33061 bash docker exec -it mysql8022mgr33062 bash tar -xvf mysql-8.0.22-1.el7.x86_64.rpm-bundle.tar yum localinstall -y mysql-community-*.rpm --去掉密码验证策略 mv /usr/lib64/mysql/plugin/validate_password.so /usr/lib64/mysql/plugin/validate_password.so_bk mv /usr/lib64/mysql/plugin/component_validate_password.so /usr/lib64/mysql/plugin/component_validate_password.so_bk -- 启动mysql systemctl start mysqld systemctl status mysqld -- 修改密码 grep 'temporary password' /var/log/mysqld.log mysql -uroot -p alter 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; select user,host,grant_priv,super_priv,authentication_string,password_last_changed from mysql.user;
2.3、修改MySQL参数
-- 节点1 cat > /etc/my.cnf <<"EOF" [mysqld] user=mysql port=3306 character_set_server=utf8mb4 secure_file_priv= server-id = 802233060 default-time-zone = '+8:00' log_timestamps = SYSTEM log-bin = binlog_format=row binlog_checksum=NONE skip-name-resolve log_slave_updates = 1 gtid-mode=ON enforce-gtid-consistency=on default_authentication_plugin=mysql_native_password max_allowed_packet = 500M master_info_repository=TABLE relay_log_info_repository=TABLE relay_log=lhrmgr60-relay-bin transaction_write_set_extraction=XXHASH64 loose-group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa" loose-group_replication_start_on_boot=OFF loose-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=OFF loose-group_replication_ip_whitelist="192.168.68.60,192.168.68.61,192.168.68.62" report_host=192.168.68.60 report_port=3306 EOF -- 节点2 cat > /etc/my.cnf <<"EOF" [mysqld] user=mysql port=3306 character_set_server=utf8mb4 secure_file_priv= server-id = 802233061 default-time-zone = '+8:00' log_timestamps = SYSTEM log-bin = binlog_format=row binlog_checksum=NONE log_slave_updates = 1 gtid-mode=ON enforce-gtid-consistency=ON skip_name_resolve default_authentication_plugin=mysql_native_password max_allowed_packet = 500M master_info_repository=TABLE relay_log_info_repository=TABLE relay_log=lhrmgr61-relay-bin transaction_write_set_extraction=XXHASH64 loose-group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa" loose-group_replication_start_on_boot=OFF loose-group_replication_local_address= "192.168.68.61: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=OFF loose-group_replication_ip_whitelist="192.168.68.60,192.168.68.61,192.168.68.62" report_host=192.168.68.61 report_port=3306 EOF -- 节点3 cat > /etc/my.cnf <<"EOF" [mysqld] user=mysql port=3306 character_set_server=utf8mb4 secure_file_priv= server-id = 802233062 default-time-zone = '+8:00' log_timestamps = SYSTEM log-bin = binlog_format=row binlog_checksum=NONE log_slave_updates = 1 gtid-mode=ON enforce-gtid-consistency=ON skip_name_resolve default_authentication_plugin=mysql_native_password max_allowed_packet = 500M master_info_repository=TABLE relay_log_info_repository=TABLE relay_log=lhrmgr62-relay-bin transaction_write_set_extraction=XXHASH64 loose-group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa" loose-group_replication_start_on_boot=OFF loose-group_replication_local_address= "192.168.68.62: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=OFF loose-group_replication_ip_whitelist="192.168.68.60,192.168.68.61,192.168.68.62" report_host=192.168.68.62 report_port=3306 EOF
2.4、重启MySQL环境
-- 重启MySQL systemctl restart mysqld -- 进入MySQL docker exec -it mysql8022mgr33060 bash docker exec -it mysql8022mgr33060 mysql -uroot -plhr -- 远程连接MySQL mysql -uroot -plhr -h192.168.66.35 -P33060 mysql -uroot -plhr -h192.168.66.35 -P33061 mysql -uroot -plhr -h192.168.66.35 -P33062 -- 查看MySQL日志 docker logs -f --tail 10 mysql8022mgr33060 docker logs -f --tail 10 mysql8022mgr33061 docker logs -f --tail 10 mysql8022mgr33062 -- 查看MySQL的主机名、server_id和server_uuid mysql -uroot -plhr -h192.168.66.35 -P33060 -e "select @@hostname,@@server_id,@@server_uuid" mysql -uroot -plhr -h192.168.66.35 -P33061 -e "select @@hostname,@@server_id,@@server_uuid" mysql -uroot -plhr -h192.168.66.35 -P33062 -e "select @@hostname,@@server_id,@@server_uuid"
结果：
C:\Users\lhrxxt>mysql -uroot -plhr -h192.168.66.35 -P33060 -e "select @@hostname,@@server_id,@@server_uuid" mysql: [Warning] Using a password on the command line interface can be insecure. +------------+-------------+--------------------------------------+ | @@hostname | @@server_id | @@server_uuid | +------------+-------------+--------------------------------------+ | lhrmgr60 | 802233060 | 44c693d8-80bb-11eb-b4bb-0242c0a8443c | +------------+-------------+--------------------------------------+ C:\Users\lhrxxt>mysql -uroot -plhr -h192.168.66.35 -P33061 -e "select @@hostname,@@server_id,@@server_uuid" mysql: [Warning] Using a password on the command line interface can be insecure. +------------+-------------+--------------------------------------+ | @@hostname | @@server_id | @@server_uuid | +------------+-------------+--------------------------------------+ | lhrmgr61 | 802233061 | 408acdb5-80bc-11eb-97a7-0242c0a8443d | +------------+-------------+--------------------------------------+ C:\Users\lhrxxt>mysql -uroot -plhr -h192.168.66.35 -P33062 -e "select @@hostname,@@server_id,@@server_uuid" mysql: [Warning] Using a password on the command line interface can be insecure. +------------+-------------+--------------------------------------+ | @@hostname | @@server_id | @@server_uuid | +------------+-------------+--------------------------------------+ | lhrmgr62 | 802233062 | 9d5c3e3e-80bc-11eb-a0b2-0242c0a8443e | +------------+-------------+--------------------------------------+
2.5、安装MGR插件（所有节点执行）
mysql -uroot -plhr -h192.168.66.35 -P33060 INSTALL PLUGIN group_replication SONAME 'group_replication.so'; -- 如果MySQL版本大于8.0.17，那么建议再安装clone插件 INSTALL PLUGIN clone SONAME 'mysql_clone.so'; show plugins;
2.6、设置复制账号
-- 在主库(192.168.68.60)上执行 CREATE USER repl@'%' IDENTIFIED BY 'lhr'; GRANT REPLICATION SLAVE,BACKUP_ADMIN ON *.* TO repl@'%'; FLUSH PRIVILEGES; -- 所有节点执行 CHANGE MASTER TO MASTER_USER='repl', MASTER_PASSWORD='lhr' FOR CHANNEL 'group_replication_recovery';
2.7、启动MGR，在主库(192.168.68.60)上执行
SET GLOBAL group_replication_bootstrap_group=ON; START GROUP_REPLICATION; SET GLOBAL group_replication_bootstrap_group=OFF; -- 查看MGR组信息 SELECT * FROM performance_schema.replication_group_members;
2.8、其他节点加入MGR，在从库（192.168.68.61，192.168.68.62）上执行
reset master; START GROUP_REPLICATION; -- 查看MGR组信息 SELECT * FROM performance_schema.replication_group_members;
执行结果：
MySQL [(none)]> SELECT * FROM performance_schema.replication_group_members; +---------------------------+--------------------------------------+---------------+-------------+--------------+-------------+----------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | +---------------------------+--------------------------------------+---------------+-------------+--------------+-------------+----------------+ | group_replication_applier | 408acdb5-80bc-11eb-97a7-0242c0a8443d | 192.168.68.61 | 3306 | ONLINE | SECONDARY | 8.0.22 | | group_replication_applier | 44c693d8-80bb-11eb-b4bb-0242c0a8443c | 192.168.68.60 | 3306 | ONLINE | PRIMARY | 8.0.22 | | group_replication_applier | 9d5c3e3e-80bc-11eb-a0b2-0242c0a8443e | 192.168.68.62 | 3306 | ONLINE | SECONDARY | 8.0.22 | +---------------------------+--------------------------------------+---------------+-------------+--------------+-------------+----------------+ 3 rows in set (0.05 sec)
可以看到，3个节点状态为online，并且主节点为192.168.68.60，只有主节点可以写入，其他节点只读，MGR单主模式搭建成功。
2.9、测试同步
在主节点上执行以下命令，然后在其它节点查询：
create database lhrdb; CREATE TABLE lhrdb.`tb1` ( `id` int(11) NOT NULL AUTO_INCREMENT, `hostname` varchar(100) DEFAULT NULL, `server_id` varchar(100) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; insert into lhrdb.tb1(hostname,server_id) select @@hostname,@@server_id; select * from lhrdb.tb1; -- 3个节点查询出来的值一样 MySQL [(none)]> select * from lhrdb.tb1; +----+----------+-----------+ | id | hostname | server_id | +----+----------+-----------+ | 1 | lhrmgr60 | 802233060 | +----+----------+-----------+ 1 row in set (0.05 sec)
三、搭建Consul Server集群
3.1、申请3台Consul Server主机
docker rm -f lhrconsulmgr66 lhrconsulmgr67 lhrconsulmgr68 docker run -d --name lhrconsulmgr66 -h lhrconsulmgr66 \ --net=mhalhr --ip 192.168.68.66 \ -p 8566:8500 \ -v /sys/fs/cgroup:/sys/fs/cgroup \ --privileged=true lhrbest/lhrcentos76:8.0 \ /usr/sbin/init docker run -d --name lhrconsulmgr67 -h lhrconsulmgr67 \ --net=mhalhr --ip 192.168.68.67 \ -p 8567:8500 \ --privileged=true lhrbest/lhrcentos76:8.0 \ /usr/sbin/init docker run -d --name lhrconsulmgr68 -h lhrconsulmgr68 \ --net=mhalhr --ip 192.168.68.68 \ -p 8568:8500 \ --privileged=true lhrbest/lhrcentos76:8.0 \ /usr/sbin/init docker cp consul_1.9.4_linux_amd64.zip lhrconsulmgr66:/ docker cp consul_1.9.4_linux_amd64.zip lhrconsulmgr67:/ docker cp consul_1.9.4_linux_amd64.zip lhrconsulmgr68:/ docker network connect bridge lhrconsulmgr66 docker network connect bridge lhrconsulmgr67 docker network connect bridge lhrconsulmgr68 docker restart lhrconsulmgr66 lhrconsulmgr67 lhrconsulmgr68
3.2、安装Consul Server
docker exec -it lhrconsulmgr66 bash docker exec -it lhrconsulmgr67 bash docker exec -it lhrconsulmgr68 bash unzip consul_1.9.4_linux_amd64.zip mv consul /usr/local/bin/ mkdir -p /consul/logs/ -- 66节点 启动 nohup 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 & -- 67节点 启动 nohup consul agent -server -bootstrap-expect 3 -bind=192.168.68.67 -client=0.0.0.0 -data-dir=/consul/data \ -node=n67 -ui >> /consul/logs/consul.log 2>&1 & -- 68节点 启动 nohup consul agent -server -bootstrap-expect 3 -bind=192.168.68.68 -client=0.0.0.0 -data-dir=/consul/data \ -node=n68 -ui >> /consul/logs/consul.log 2>&1 & -- 67和68节点加入集群 consul join 192.168.68.66 -- 查询集群状态 [root@lhrconsulmgr66 /]# consul members Node Address Status Type Build Protocol DC Segment n66 192.168.68.66:8301 alive server 1.9.4 2 dc1 <all> n67 192.168.68.67:8301 alive server 1.9.4 2 dc1 <all> n68 192.168.68.68:8301 alive server 1.9.4 2 dc1 <all> [root@lhrconsulmgr66 /]# consul operator raft list-peers Node ID Address State Voter RaftProtocol n66 719e2a32-5c4b-bd0f-35ef-9ac11a8e79e0 192.168.68.66:8300 leader true 3 n67 f80e2542-3d30-ca5a-af79-08879723c882 192.168.68.67:8300 follower true 3 n68 43520b16-1b65-7d22-4172-6c2822070a44 192.168.68.68:8300 follower true 3
为了后续启动方便，我们使用参数文件：
-- 66节点，其它节点修改node_name、advertise_addr和bind_addr即可：
mkdir -p /etc/consul.d/
pkill -9 consul
cat > /etc/consul.d/server.json <<"EOF"
{
"data_dir": "/consul/data",
"datacenter": "dc1",
"node_name": "n66",
"enable_syslog": true,
"log_level": "INFO",
"server": true,
"advertise_addr":"192.168.68.66",
"bootstrap_expect": 3,
"bind_addr": "192.168.68.66",
"client_addr": "0.0.0.0",
"retry_join": ["192.168.68.66","192.168.68.67","192.168.68.68"],
"retry_interval": "10s",
"rejoin_after_leave": true,
"start_join": ["192.168.68.66","192.168.68.67","192.168.68.68"] ,
"ui": true
}
EOF
nohup consul agent -config-dir=/etc/consul.d > /consul/consul.log &3.3、web访问
http://192.168.66.35:8566
http://192.168.66.35:8567
http://192.168.66.35:8568
四、在MySQL节点上安装Consul Client
4.1、安装Consul Client
docker cp consul_1.9.4_linux_amd64.zip mysql8022mgr33060:/
docker cp consul_1.9.4_linux_amd64.zip mysql8022mgr33061:/
docker cp consul_1.9.4_linux_amd64.zip mysql8022mgr33062:/
docker exec -it mysql8022mgr33060 bash
unzip consul_1.9.4_linux_amd64.zip
mv consul /usr/local/bin/
mkdir -p /consul/logs/
-- mgr60配置文件，其它节点修改bind_addr和node_name即可
mkdir -p /etc/consul.d/
cat > /etc/consul.d/client.json <<"EOF"
{
"data_dir": "/data/consul",
"enable_script_checks": true,
"bind_addr": "192.168.68.60",
"retry_join": ["192.168.68.66","192.168.68.67","192.168.68.68"],
"retry_interval": "10s",
"rejoin_after_leave": true,
"start_join": ["192.168.68.66","192.168.68.67","192.168.68.68"] ,
"node_name": "MGR60"
}
EOF
nohup consul agent -config-dir=/etc/consul.d > /consul/consul.log &查询状态：
[root@lhrconsulmgr66 /]# consul members Node Address Status Type Build Protocol DC Segment n66 192.168.68.66:8301 alive server 1.9.4 2 dc1 <all> n67 192.168.68.67:8301 alive server 1.9.4 2 dc1 <all> n68 192.168.68.68:8301 alive server 1.9.4 2 dc1 <all> MGR60 192.168.68.60:8301 alive client 1.9.4 2 dc1 <default> MGR61 192.168.68.61:8301 alive client 1.9.4 2 dc1 <default> MGR62 192.168.68.62:8301 alive client 1.9.4 2 dc1 <default> [root@lhrconsulmgr66 /]# consul operator raft list-peers Node ID Address State Voter RaftProtocol n66 719e2a32-5c4b-bd0f-35ef-9ac11a8e79e0 192.168.68.66:8300 follower true 3 n67 f80e2542-3d30-ca5a-af79-08879723c882 192.168.68.67:8300 leader true 3 n68 43520b16-1b65-7d22-4172-6c2822070a44 192.168.68.68:8300 follower true 3
4.2、在Consul client的3个节点上编写检测primay脚本和检测slave脚本
-- 检测脚本【master】 mkdir -p /data/consul/shell/ cat > /data/consul/shell/check_mysql_mgr_master.sh <<"EOF" #!/bin/bash port=3306 user="root" passwod="lhr" comm="/usr/bin/mysql -u$user -hlocalhost -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 EOF -- 检测脚本【slave】 cat > /data/consul/shell/check_mysql_mgr_slave.sh <<"EOF" #!/bin/bash port=3306 user="root" passwod="lhr" comm="/usr/bin/mysql -u$user -hlocalhost -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 EOF
赋权：
chmod +x /data/consul/shell/check_mysql_mgr_master.sh chmod +x /data/consul/shell/check_mysql_mgr_slave.sh
4.3、service配置
官网配置参考：https://www.consul.io/docs/discovery/services
在3台MySQL节点上都需要操作，注意修改address地址为本机地址：
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
cat > /etc/consul.d/r-mysql-mgr-server-lhr.json <<"EOF"
{
"service": [
{
"name": "r-mysql-mgr-server-lhr",
"tags": ["MGR-Slave"],
"address": "192.168.68.60",
"port": 3306,
"check":
{
"args": ["/data/consul/shell/check_mysql_mgr_slave.sh"],
"interval": "5s"
}
}
]
}
EOF
-- 检查配置文件是否正常
consul validate /etc/consul.d/
-- 重新加载配置文件
consul reload
-- 检查配置结果
[root@lhrmgr60 ~]# consul catalog services
consul
r-mysql-mgr-server-lhr
rw-mysql-mgr-server-lhr注意：由于每台mysql server 上都有master、slave 检测脚本、而mysql server 只能是master 或者slave、所以存在失败的检测，master检测只有一个成功，slave检测只有一个失败 。
所有服务：
主库连接：
从库连接：
4.4、service测试
yum install -y bind-utils bind bind-chroot dnsmasq dig @192.168.68.66 -p 8600 r-mysql-mgr-server-lhr.service.consul a dig @192.168.68.66 -p 8600 rw-mysql-mgr-server-lhr.service.consul a dig @127.0.0.1 -p 8600 MGR60.node.consul dig @127.0.0.1 -p 8600 MGR61.node.consul dig @127.0.0.1 -p 8600 MGR62.node.consul
测试结果：
[root@lhrmgr60 ~]# dig @192.168.68.66 -p 8600 r-mysql-mgr-server-lhr.service.consul a ; <<>> DiG 9.11.4-P2-RedHat-9.11.4-26.P2.el7_9.4 <<>> @192.168.68.66 -p 8600 r-mysql-mgr-server-lhr.service.consul a ; (1 server found) ;; global options: +cmd ;; Got answer: ;; ->>HEADER<<- opcode: QUERY, status: NOERROR, id: 7862 ;; flags: qr aa rd; QUERY: 1, ANSWER: 2, AUTHORITY: 0, ADDITIONAL: 1 ;; WARNING: recursion requested but not available ;; OPT PSEUDOSECTION: ; EDNS: version: 0, flags:; udp: 4096 ;; QUESTION SECTION: ;r-mysql-mgr-server-lhr.service.consul. IN A ;; ANSWER SECTION: r-mysql-mgr-server-lhr.service.consul. 0 IN A 192.168.68.62 r-mysql-mgr-server-lhr.service.consul. 0 IN A 192.168.68.61 ;; Query time: 0 msec ;; SERVER: 192.168.68.66#8600(192.168.68.66) ;; WHEN: Wed Mar 10 10:52:12 CST 2021 ;; MSG SIZE rcvd: 98 [root@lhrmgr60 ~]# dig @192.168.68.66 -p 8600 rw-mysql-mgr-server-lhr.service.consul A ; <<>> DiG 9.11.4-P2-RedHat-9.11.4-26.P2.el7_9.4 <<>> @192.168.68.66 -p 8600 rw-mysql-mgr-server-lhr.service.consul a ; (1 server found) ;; global options: +cmd ;; Got answer: ;; ->>HEADER<<- opcode: QUERY, status: NOERROR, id: 13129 ;; flags: qr aa rd; QUERY: 1, ANSWER: 1, AUTHORITY: 0, ADDITIONAL: 1 ;; WARNING: recursion requested but not available ;; OPT PSEUDOSECTION: ; EDNS: version: 0, flags:; udp: 4096 ;; QUESTION SECTION: ;rw-mysql-mgr-server-lhr.service.consul. IN A ;; ANSWER SECTION: rw-mysql-mgr-server-lhr.service.consul. 0 IN A 192.168.68.60 ;; Query time: 0 msec ;; SERVER: 192.168.68.66#8600(192.168.68.66) ;; WHEN: Wed Mar 10 10:52:17 CST 2021 ;; MSG SIZE rcvd: 83
可以看到，r-mysql-mgr-server-lhr.service.consul解析出来的地址是192.168.68.61和192.168.68.62，即2个从库；而rw-mysql-mgr-server-lhr.service.consul解析出来的地址为192.168.68.60，即主库。
五、配置DNS解析域名
App端配置域名服务器来解析consul后缀的域名，DNS解析及跳转， 有多个方案：
- 原内网dns服务器,做域名转发，consul后缀的，都转到consul server上
- dns全部跳到consul DNS服务器上，非consul后缀的,使用 recursors 属性跳转到原DNS服务器上
- dnsmaq 转： server=/consul/192.168.X.X#8600 解析consul后缀的
- 使用BIND配置DNS服务器
这里只列举dnsmasq这种方案，更多方法请参考官方文档：https://learn.hashicorp.com/tutorials/consul/dns-forwarding
5.1、使用dnsmasq来配置DNS解析
在192.168.66.69上配置dnsmasq。
docker rm -f lhrconsuldns69 docker run -d --name lhrconsuldns69 -h lhrconsuldns69 \ --net=mhalhr --ip 192.168.68.69 \ -p 53:53 \ -v /sys/fs/cgroup:/sys/fs/cgroup \ --privileged=true lhrbest/lhrcentos76:8.0 \ /usr/sbin/init docker network connect bridge lhrconsuldns69 docker exec -it lhrconsuldns69 bash yum install -y bind-utils bind bind-chroot dnsmasq echo " server=/consul/192.168.68.66#8600 server=/consul/192.168.68.67#8600 server=/consul/192.168.68.68#8600 " > /etc/dnsmasq.d/10-consul echo " server=114.114.114.114 server=8.8.8.8 server=223.5.5.5 " >> /etc/dnsmasq.conf -- 添加到所有机器，包括本机、3个client、3个server端等 echo "nameserver 192.168.68.69" > /etc/resolv.conf systemctl enable dnsmasq systemctl restart dnsmasq systemctl status dnsmasq dig @192.168.68.69 -p 53 r-mysql-mgr-server-lhr.service.consul a dig @192.168.68.69 -p 53 rw-mysql-mgr-server-lhr.service.consul a nslookup r-mysql-mgr-server-lhr.service.consul nslookup rw-mysql-mgr-server-lhr.service.consul ping r-mysql-mgr-server-lhr.service.consul -c 4 ping rw-mysql-mgr-server-lhr.service.consul -c 4
结果：
[root@lhrmgr60 /]# nslookup r-mysql-mgr-server-lhr.service.consul nslookup rw-mysql-mgr-server-lhr.service.consulServer: 192.168.68.69 Address: 192.168.68.69#53 Name: r-mysql-mgr-server-lhr.service.consul Address: 192.168.68.62 Name: r-mysql-mgr-server-lhr.service.consul Address: 192.168.68.61 [root@lhrmgr60 /]# nslookup rw-mysql-mgr-server-lhr.service.consul Server: 192.168.68.69 Address: 192.168.68.69#53 Name: rw-mysql-mgr-server-lhr.service.consul Address: 192.168.68.60 [root@lhrmgr60 /]# [root@lhrmgr60 /]# ping r-mysql-mgr-server-lhr.service.consul -c 4 PING r-mysql-mgr-server-lhr.service.consul (192.168.68.61) 56(84) bytes of data. 64 bytes from 192.168.68.61 (192.168.68.61): icmp_seq=1 ttl=64 time=0.083 ms 64 bytes from 192.168.68.61 (192.168.68.61): icmp_seq=2 ttl=64 time=0.065 ms 64 bytes from 192.168.68.61 (192.168.68.61): icmp_seq=3 ttl=64 time=0.108 ms 64 bytes from 192.168.68.61 (192.168.68.61): icmp_seq=4 ttl=64 time=0.084 ms --- r-mysql-mgr-server-lhr.service.consul ping statistics --- 4 packets transmitted, 4 received, 0% packet loss, time 7022ms rtt min/avg/max/mdev = 0.065/0.085/0.108/0.015 ms [root@lhrmgr60 /]# [root@lhrmgr60 /]# ping rw-mysql-mgr-server-lhr.service.consul -c 4 PING rw-mysql-mgr-server-lhr.service.consul (192.168.68.60) 56(84) bytes of data. 64 bytes from lhrmgr60 (192.168.68.60): icmp_seq=1 ttl=64 time=0.058 ms 64 bytes from lhrmgr60 (192.168.68.60): icmp_seq=2 ttl=64 time=0.070 ms 64 bytes from lhrmgr60 (192.168.68.60): icmp_seq=3 ttl=64 time=0.091 ms 64 bytes from lhrmgr60 (192.168.68.60): icmp_seq=4 ttl=64 time=0.071 ms --- rw-mysql-mgr-server-lhr.service.consul ping statistics --- 4 packets transmitted, 4 received, 0% packet loss, time 2999ms rtt min/avg/max/mdev = 0.058/0.072/0.091/0.014 ms [root@lhrmgr60 /]# [root@lhrmgr60 /]# dig @192.168.68.69 -p 53 r-mysql-mgr-server-lhr.service.consul a ; <<>> DiG 9.11.4-P2-RedHat-9.11.4-26.P2.el7_9.4 <<>> @192.168.68.69 -p 53 r-mysql-mgr-server-lhr.service.consul a ; (1 server found) ;; global options: +cmd ;; Got answer: ;; ->>HEADER<<- opcode: QUERY, status: NOERROR, id: 48770 ;; flags: qr aa rd ra; QUERY: 1, ANSWER: 2, AUTHORITY: 0, ADDITIONAL: 1 ;; OPT PSEUDOSECTION: ; EDNS: version: 0, flags:; udp: 4096 ;; QUESTION SECTION: ;r-mysql-mgr-server-lhr.service.consul. IN A ;; ANSWER SECTION: r-mysql-mgr-server-lhr.service.consul. 0 IN A 192.168.68.62 r-mysql-mgr-server-lhr.service.consul. 0 IN A 192.168.68.61 ;; Query time: 1 msec ;; SERVER: 192.168.68.69#53(192.168.68.69) ;; WHEN: Thu Mar 11 10:30:03 CST 2021 ;; MSG SIZE rcvd: 98 [root@lhrmgr60 /]# [root@lhrmgr60 /]# dig @192.168.68.69 -p 53 rw-mysql-mgr-server-lhr.service.consul a ; <<>> DiG 9.11.4-P2-RedHat-9.11.4-26.P2.el7_9.4 <<>> @192.168.68.69 -p 53 rw-mysql-mgr-server-lhr.service.consul a ; (1 server found) ;; global options: +cmd ;; Got answer: ;; ->>HEADER<<- opcode: QUERY, status: NOERROR, id: 47378 ;; flags: qr aa rd ra; QUERY: 1, ANSWER: 1, AUTHORITY: 0, ADDITIONAL: 1 ;; OPT PSEUDOSECTION: ; EDNS: version: 0, flags:; udp: 4096 ;; QUESTION SECTION: ;rw-mysql-mgr-server-lhr.service.consul. IN A ;; ANSWER SECTION: rw-mysql-mgr-server-lhr.service.consul. 0 IN A 192.168.68.60 ;; Query time: 1 msec ;; SERVER: 192.168.68.69#53(192.168.68.69) ;; WHEN: Thu Mar 11 10:30:13 CST 2021 ;; MSG SIZE rcvd: 83
5.2、MySQL通过域名连接
[root@lhrmgr60 /]# mysql -uroot -plhr -hrw-mysql-mgr-server-lhr.service.consul mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 53753 Server version: 8.0.22 MySQL Community Server - GPL Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> SELECT * FROM performance_schema.replication_group_members; +---------------------------+--------------------------------------+---------------+-------------+--------------+-------------+----------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | +---------------------------+--------------------------------------+---------------+-------------+--------------+-------------+----------------+ | group_replication_applier | 408acdb5-80bc-11eb-97a7-0242c0a8443d | 192.168.68.61 | 3306 | ONLINE | SECONDARY | 8.0.22 | | group_replication_applier | 44c693d8-80bb-11eb-b4bb-0242c0a8443c | 192.168.68.60 | 3306 | ONLINE | PRIMARY | 8.0.22 | | group_replication_applier | 9d5c3e3e-80bc-11eb-a0b2-0242c0a8443e | 192.168.68.62 | 3306 | ONLINE | SECONDARY | 8.0.22 | +---------------------------+--------------------------------------+---------------+-------------+--------------+-------------+----------------+ 3 rows in set (0.00 sec) mysql> select @@hostname,@@report_host; +------------+---------------+ | @@hostname | @@report_host | +------------+---------------+ | lhrmgr60 | 192.168.68.60 | +------------+---------------+ 1 row in set (0.00 sec) mysql> exit Bye [root@lhrmgr60 /]# mysql -uroot -plhr -hr-mysql-mgr-server-lhr.service.consul mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 47616 Server version: 8.0.22 MySQL Community Server - GPL Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> SELECT * FROM performance_schema.replication_group_members; +---------------------------+--------------------------------------+---------------+-------------+--------------+-------------+----------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | +---------------------------+--------------------------------------+---------------+-------------+--------------+-------------+----------------+ | group_replication_applier | 408acdb5-80bc-11eb-97a7-0242c0a8443d | 192.168.68.61 | 3306 | ONLINE | SECONDARY | 8.0.22 | | group_replication_applier | 44c693d8-80bb-11eb-b4bb-0242c0a8443c | 192.168.68.60 | 3306 | ONLINE | PRIMARY | 8.0.22 | | group_replication_applier | 9d5c3e3e-80bc-11eb-a0b2-0242c0a8443e | 192.168.68.62 | 3306 | ONLINE | SECONDARY | 8.0.22 | +---------------------------+--------------------------------------+---------------+-------------+--------------+-------------+----------------+ 3 rows in set (0.00 sec) mysql> create database lhrdb2; ERROR 1290 (HY000): The MySQL server is running with the --super-read-only option so it cannot execute this statement
六、测试高可用
6.1、测试Consul Server的高可用
如下图所示，目前Consul Server有3台机器，组成一个集群，若其中任意一台机器宕机，都不能影响service的解析：
目前的状态：
[root@lhrmgr60 /]# consul members Node Address Status Type Build Protocol DC Segment n66 192.168.68.66:8301 alive server 1.9.4 2 dc1 <all> n67 192.168.68.67:8301 alive server 1.9.4 2 dc1 <all> n68 192.168.68.68:8301 alive server 1.9.4 2 dc1 <all> MGR60 192.168.68.60:8301 alive client 1.9.4 2 dc1 <default> MGR61 192.168.68.61:8301 alive client 1.9.4 2 dc1 <default> MGR62 192.168.68.62:8301 alive client 1.9.4 2 dc1 <default> [root@lhrmgr60 /]# consul operator raft list-peers Node ID Address State Voter RaftProtocol n68 43520b16-1b65-7d22-4172-6c2822070a44 192.168.68.68:8300 follower true 3 n66 ca78760f-2f7e-f350-4e47-2db17e8719fe 192.168.68.66:8300 leader true 3 n67 f80e2542-3d30-ca5a-af79-08879723c882 192.168.68.67:8300 follower true 3
接下来，把n66、n67进行关机操作：
docker stop lhrconsulmgr66 lhrconsulmgr67
查询集群状态：
[root@lhrmgr60 /]# consul members Node Address Status Type Build Protocol DC Segment n66 192.168.68.66:8301 failed server 1.9.4 2 dc1 <all> n67 192.168.68.67:8301 failed server 1.9.4 2 dc1 <all> n68 192.168.68.68:8301 alive server 1.9.4 2 dc1 <all> MGR60 192.168.68.60:8301 alive client 1.9.4 2 dc1 <default> MGR61 192.168.68.61:8301 alive client 1.9.4 2 dc1 <default> MGR62 192.168.68.62:8301 alive client 1.9.4 2 dc1 <default> [root@lhrmgr60 /]# consul operator raft list-peers Error getting peers: Failed to retrieve raft configuration: Unexpected response code: 500 (rpc error making call: No cluster leader) [root@lhrmgr60 /]# consul catalog services Error listing services: Unexpected response code: 500 (rpc error making call: No cluster leader)
查询域名是否正常使用：
dig @192.168.68.69 -p 53 r-mysql-mgr-server-lhr.service.consul a dig @192.168.68.69 -p 53 rw-mysql-mgr-server-lhr.service.consul a nslookup r-mysql-mgr-server-lhr.service.consul nslookup rw-mysql-mgr-server-lhr.service.consul ping r-mysql-mgr-server-lhr.service.consul -c 4 ping rw-mysql-mgr-server-lhr.service.consul -c 4
校验结果：
[root@lhrmgr60 /]# nslookup r-mysql-mgr-server-lhr.service.consul Server: 192.168.68.69 Address: 192.168.68.69#53 Name: r-mysql-mgr-server-lhr.service.consul Address: 192.168.68.61 Name: r-mysql-mgr-server-lhr.service.consul Address: 192.168.68.62 [root@lhrmgr60 /]# [root@lhrmgr60 /]# nslookup rw-mysql-mgr-server-lhr.service.consul Server: 192.168.68.69 Address: 192.168.68.69#53 Name: rw-mysql-mgr-server-lhr.service.consul Address: 192.168.68.60
可以看到，Consul Server集群的高可用运行正常。
6.2、测试MySQL的负载均衡和读写分离
首先测试读负载均衡：
[root@lhrmgr60 /]# for i in $(seq 1 10); do mysql -uroot -plhr -hr-mysql-mgr-server-lhr.service.consul -P3306 \ > -e 'select @@server_id;'; done | egrep '[0-9]' mysql: [Warning] Using a password on the command line interface can be insecure. 802233061 mysql: [Warning] Using a password on the command line interface can be insecure. 802233061 mysql: [Warning] Using a password on the command line interface can be insecure. 802233062 mysql: [Warning] Using a password on the command line interface can be insecure. 802233062 mysql: [Warning] Using a password on the command line interface can be insecure. 802233062 mysql: [Warning] Using a password on the command line interface can be insecure. 802233061 mysql: [Warning] Using a password on the command line interface can be insecure. 802233061 mysql: [Warning] Using a password on the command line interface can be insecure. 802233061 mysql: [Warning] Using a password on the command line interface can be insecure. 802233062 mysql: [Warning] Using a password on the command line interface can be insecure. 802233061
可以看到，读操作被分配到61和62主机上。
读写分离由于是通过不同的主机域名进行连接的，之前已经测试过，所以，这里就不再测试。
6.3、测试MGR高可用
目前192.168.68.60为主库，192.168.68.61和192.168.68.62为从库，所以，我们把192.168.68.60关机，然后通过域名来连接，检查能否正常连接。
[root@lhrmgr60 /]# mysql -uroot -plhr -hrw-mysql-mgr-server-lhr.service.consul -P3306 mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 54447 Server version: 8.0.22 MySQL Community Server - GPL Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> select @@hostname,@@report_host; +------------+---------------+ | @@hostname | @@report_host | +------------+---------------+ | lhrmgr60 | 192.168.68.60 | +------------+---------------+ 1 row in set (0.00 sec) mysql> SELECT * FROM performance_schema.replication_group_members; +---------------------------+--------------------------------------+---------------+-------------+--------------+-------------+----------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | +---------------------------+--------------------------------------+---------------+-------------+--------------+-------------+----------------+ | group_replication_applier | 408acdb5-80bc-11eb-97a7-0242c0a8443d | 192.168.68.61 | 3306 | ONLINE | SECONDARY | 8.0.22 | | group_replication_applier | 44c693d8-80bb-11eb-b4bb-0242c0a8443c | 192.168.68.60 | 3306 | ONLINE | PRIMARY | 8.0.22 | | group_replication_applier | 9d5c3e3e-80bc-11eb-a0b2-0242c0a8443e | 192.168.68.62 | 3306 | ONLINE | SECONDARY | 8.0.22 | +---------------------------+--------------------------------------+---------------+-------------+--------------+-------------+----------------+ 3 rows in set (0.01 sec) mysql> shutdown ; Query OK, 0 rows affected (0.00 sec) mysql> select @@hostname,@@report_host; ERROR 2013 (HY000): Lost connection to MySQL server during query mysql> select @@hostname,@@report_host; +------------+---------------+ | @@hostname | @@report_host | +------------+---------------+ | lhrmgr61 | 192.168.68.61 | +------------+---------------+ 1 row in set (0.00 sec) mysql> SELECT * FROM performance_schema.replication_group_members; +---------------------------+--------------------------------------+---------------+-------------+--------------+-------------+----------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | +---------------------------+--------------------------------------+---------------+-------------+--------------+-------------+----------------+ | group_replication_applier | 408acdb5-80bc-11eb-97a7-0242c0a8443d | 192.168.68.61 | 3306 | ONLINE | PRIMARY | 8.0.22 | | group_replication_applier | 9d5c3e3e-80bc-11eb-a0b2-0242c0a8443e | 192.168.68.62 | 3306 | ONLINE | SECONDARY | 8.0.22 | +---------------------------+--------------------------------------+---------------+-------------+--------------+-------------+----------------+ 2 rows in set (0.00 sec) [root@lhrmgr60 /]# nslookup rw-mysql-mgr-server-lhr.service.consul Server: 192.168.68.69 Address: 192.168.68.69#53 Name: rw-mysql-mgr-server-lhr.service.consul Address: 192.168.68.61 [root@lhrmgr60 /]# nslookup r-mysql-mgr-server-lhr.service.consul Server: 192.168.68.69 Address: 192.168.68.69#53 Name: r-mysql-mgr-server-lhr.service.consul Address: 192.168.68.62
可以看到，MGR的高可用也运行正常。
这篇关于【DB宝45】MySQL高可用之MGR+Consul架构部署的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！
您可能喜欢
-
部署MySQL集群教程：新手入门指南11-01
-
如何部署MySQL集群：新手入门教程11-01
-
部署MySQL集群学习：新手入门教程11-01
-
部署MySQL集群入门：新手必读指南11-01
-
BinLog入门：新手必读的MySQL二进制日志指南10-23
-
Binlog入门：MySQL数据库的日志管理指南10-23
-
MySQL数据库入门教程：从安装到基本操作10-22
-
MySQL读写分离入门教程：轻松实现数据库性能提升10-22
-
MySQL分库分表入门教程10-22
-
MySQL慢查询的诊断与优化指南10-22
-
MySQL索引入门教程：快速理解与应用指南10-22
-
MySQL基础入门教程：从安装到基本操作10-22
-
MySQL数据库中的Binlog详解与操作教程10-22
-
部署MySQL集群项目实战：新手入门教程10-12
-
部署MySQL集群教程：新手入门指南10-12
栏目导航
