【DB宝45】MySQL高可用之MGR+Consul架构部署
[toc]
一、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 mhalhrdocker network inspect mhalhr-- 生成3台CentOS的环境docker rm -f mysql8022mgr33060 mysql8022mgr33061 mysql8022mgr33062docker 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 initdocker 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 initdocker 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 mysql8022mgr33060docker network connect bridge mysql8022mgr33061docker network connect bridge mysql8022mgr33062docker restart mysql8022mgr33060 mysql8022mgr33061 mysql8022mgr33062
2.2、3台主机安装MySQL环境
docker exec -it mysql8022mgr33060 bashdocker exec -it mysql8022mgr33061 bashdocker exec -it mysql8022mgr33062 bashtar -xvf mysql-8.0.22-1.el7.x86_64.rpm-bundle.taryum localinstall -y mysql-community-*.rpm--去掉密码验证策略mv /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_bk-- 启动mysqlsystemctl start mysqldsystemctl status mysqld-- 修改密码grep 'temporary password' /var/log/mysqld.logmysql -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;select user,host,grant_priv,super_priv,authentication_string,password_last_changed from mysql.user;
2.3、修改MySQL参数
-- 节点1cat > /etc/my.cnf <<"EOF"[mysqld]user=mysqlport=3306character_set_server=utf8mb4secure_file_priv=server-id = 802233060default-time-zone = '+8:00'log_timestamps = SYSTEMlog-bin = binlog_format=rowbinlog_checksum=NONEskip-name-resolvelog_slave_updates = 1gtid-mode=ONenforce-gtid-consistency=ondefault_authentication_plugin=mysql_native_passwordmax_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-- 节点2cat > /etc/my.cnf <<"EOF"[mysqld]user=mysqlport=3306character_set_server=utf8mb4secure_file_priv=server-id = 802233061default-time-zone = '+8:00'log_timestamps = SYSTEMlog-bin = binlog_format=rowbinlog_checksum=NONElog_slave_updates = 1gtid-mode=ONenforce-gtid-consistency=ONskip_name_resolvedefault_authentication_plugin=mysql_native_passwordmax_allowed_packet = 500Mmaster_info_repository=TABLErelay_log_info_repository=TABLErelay_log=lhrmgr61-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.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=OFFloose-group_replication_ip_whitelist="192.168.68.60,192.168.68.61,192.168.68.62"report_host=192.168.68.61report_port=3306EOF-- 节点3cat > /etc/my.cnf <<"EOF"[mysqld]user=mysqlport=3306character_set_server=utf8mb4secure_file_priv=server-id = 802233062default-time-zone = '+8:00'log_timestamps = SYSTEMlog-bin = binlog_format=rowbinlog_checksum=NONElog_slave_updates = 1gtid-mode=ONenforce-gtid-consistency=ONskip_name_resolvedefault_authentication_plugin=mysql_native_passwordmax_allowed_packet = 500Mmaster_info_repository=TABLErelay_log_info_repository=TABLErelay_log=lhrmgr62-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.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=OFFloose-group_replication_ip_whitelist="192.168.68.60,192.168.68.61,192.168.68.62"report_host=192.168.68.62report_port=3306EOF
2.4、重启MySQL环境
-- 重启MySQLsystemctl restart mysqld-- 进入MySQLdocker exec -it mysql8022mgr33060 bashdocker exec -it mysql8022mgr33060 mysql -uroot -plhr-- 远程连接MySQLmysql -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 mysql8022mgr33060docker logs -f --tail 10 mysql8022mgr33061docker logs -f --tail 10 mysql8022mgr33062-- 查看MySQL的主机名、server_id和server_uuidmysql -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 -P33060INSTALL 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 lhrconsulmgr68docker 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/initdocker run -d --name lhrconsulmgr67 -h lhrconsulmgr67 \ --net=mhalhr --ip 192.168.68.67 \ -p 8567:8500 \ --privileged=true lhrbest/lhrcentos76:8.0 \ /usr/sbin/initdocker run -d --name lhrconsulmgr68 -h lhrconsulmgr68 \ --net=mhalhr --ip 192.168.68.68 \ -p 8568:8500 \ --privileged=true lhrbest/lhrcentos76:8.0 \ /usr/sbin/initdocker 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 lhrconsulmgr66docker network connect bridge lhrconsulmgr67docker network connect bridge lhrconsulmgr68docker restart lhrconsulmgr66 lhrconsulmgr67 lhrconsulmgr68
3.2、安装Consul Server
docker exec -it lhrconsulmgr66 bashdocker exec -it lhrconsulmgr67 bashdocker exec -it lhrconsulmgr68 bashunzip consul_1.9.4_linux_amd64.zipmv 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 membersNode Address Status Type Build Protocol DC Segmentn66 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-peersNode ID Address State Voter RaftProtocoln66 719e2a32-5c4b-bd0f-35ef-9ac11a8e79e0 192.168.68.66:8300 leader true 3n67 f80e2542-3d30-ca5a-af79-08879723c882 192.168.68.67:8300 follower true 3n68 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 consulcat > /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}EOFnohup 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 bashunzip consul_1.9.4_linux_amd64.zipmv 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"}EOFnohup consul agent -config-dir=/etc/consul.d > /consul/consul.log &
查询状态:
[root@lhrconsulmgr66 /]# consul membersNode Address Status Type Build Protocol DC Segmentn66 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-peersNode ID Address State Voter RaftProtocoln66 719e2a32-5c4b-bd0f-35ef-9ac11a8e79e0 192.168.68.66:8300 follower true 3n67 f80e2542-3d30-ca5a-af79-08879723c882 192.168.68.67:8300 leader true 3n68 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/bashport=3306user="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 2fi# 判断节点状态,是否存活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 2fi# 判断是不是主节点if [[ $server_uuid == $primary_member ]]then echo "MySQL $port Instance is master ........" exit 0else echo "MySQL $port Instance is slave ........" exit 2fiEOF-- 检测脚本【slave】cat > /data/consul/shell/check_mysql_mgr_slave.sh <<"EOF"#!/bin/bashport=3306user="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 2fi# 判断节点状态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 2fi# 判断是不是主节点if [[ $server_uuid != $primary_member ]]then echo "MySQL $port Instance is slave ........" exit 0else 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 fifiEOF
赋权:
chmod +x /data/consul/shell/check_mysql_mgr_master.shchmod +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" } }}EOFcat > /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 servicesconsulr-mysql-mgr-server-lhrrw-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 dnsmasqdig @192.168.68.66 -p 8600 r-mysql-mgr-server-lhr.service.consul adig @192.168.68.66 -p 8600 rw-mysql-mgr-server-lhr.service.consul adig @127.0.0.1 -p 8600 MGR60.node.consuldig @127.0.0.1 -p 8600 MGR61.node.consuldig @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.62r-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 lhrconsuldns69docker 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/initdocker network connect bridge lhrconsuldns69docker exec -it lhrconsuldns69 bashyum install -y bind-utils bind bind-chroot dnsmasqecho "server=/consul/192.168.68.66#8600server=/consul/192.168.68.67#8600server=/consul/192.168.68.68#8600" > /etc/dnsmasq.d/10-consulecho "server=114.114.114.114server=8.8.8.8server=223.5.5.5" >> /etc/dnsmasq.conf-- 添加到所有机器,包括本机、3个client、3个server端等echo "nameserver 192.168.68.69" > /etc/resolv.confsystemctl enable dnsmasqsystemctl restart dnsmasqsystemctl status dnsmasqdig @192.168.68.69 -p 53 r-mysql-mgr-server-lhr.service.consul adig @192.168.68.69 -p 53 rw-mysql-mgr-server-lhr.service.consul anslookup r-mysql-mgr-server-lhr.service.consulnslookup rw-mysql-mgr-server-lhr.service.consulping r-mysql-mgr-server-lhr.service.consul -c 4ping rw-mysql-mgr-server-lhr.service.consul -c 4
结果:
[root@lhrmgr60 /]# nslookup r-mysql-mgr-server-lhr.service.consulnslookup rw-mysql-mgr-server-lhr.service.consulServer: 192.168.68.69Address: 192.168.68.69#53Name: r-mysql-mgr-server-lhr.service.consulAddress: 192.168.68.62Name: r-mysql-mgr-server-lhr.service.consulAddress: 192.168.68.61[root@lhrmgr60 /]# nslookup rw-mysql-mgr-server-lhr.service.consulServer: 192.168.68.69Address: 192.168.68.69#53Name: rw-mysql-mgr-server-lhr.service.consulAddress: 192.168.68.60[root@lhrmgr60 /]# [root@lhrmgr60 /]# ping r-mysql-mgr-server-lhr.service.consul -c 4PING 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 ms64 bytes from 192.168.68.61 (192.168.68.61): icmp_seq=2 ttl=64 time=0.065 ms64 bytes from 192.168.68.61 (192.168.68.61): icmp_seq=3 ttl=64 time=0.108 ms64 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 7022msrtt 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 4PING 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 ms64 bytes from lhrmgr60 (192.168.68.60): icmp_seq=2 ttl=64 time=0.070 ms64 bytes from lhrmgr60 (192.168.68.60): icmp_seq=3 ttl=64 time=0.091 ms64 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 2999msrtt 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.62r-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.consulmysql: [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 53753Server version: 8.0.22 MySQL Community Server - GPLCopyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.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> exitBye[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 47616Server version: 8.0.22 MySQL Community Server - GPLCopyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.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 membersNode Address Status Type Build Protocol DC Segmentn66 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-peersNode ID Address State Voter RaftProtocoln68 43520b16-1b65-7d22-4172-6c2822070a44 192.168.68.68:8300 follower true 3n66 ca78760f-2f7e-f350-4e47-2db17e8719fe 192.168.68.66:8300 leader true 3n67 f80e2542-3d30-ca5a-af79-08879723c882 192.168.68.67:8300 follower true 3
接下来,把n66、n67进行关机操作:
docker stop lhrconsulmgr66 lhrconsulmgr67
查询集群状态:
[root@lhrmgr60 /]# consul membersNode Address Status Type Build Protocol DC Segmentn66 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-peersError getting peers: Failed to retrieve raft configuration: Unexpected response code: 500 (rpc error making call: No cluster leader)[root@lhrmgr60 /]# consul catalog servicesError 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 adig @192.168.68.69 -p 53 rw-mysql-mgr-server-lhr.service.consul anslookup r-mysql-mgr-server-lhr.service.consulnslookup rw-mysql-mgr-server-lhr.service.consulping r-mysql-mgr-server-lhr.service.consul -c 4ping rw-mysql-mgr-server-lhr.service.consul -c 4
校验结果:
[root@lhrmgr60 /]# nslookup r-mysql-mgr-server-lhr.service.consulServer: 192.168.68.69Address: 192.168.68.69#53Name: r-mysql-mgr-server-lhr.service.consulAddress: 192.168.68.61Name: r-mysql-mgr-server-lhr.service.consulAddress: 192.168.68.62[root@lhrmgr60 /]# [root@lhrmgr60 /]# nslookup rw-mysql-mgr-server-lhr.service.consulServer: 192.168.68.69Address: 192.168.68.69#53Name: rw-mysql-mgr-server-lhr.service.consulAddress: 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.802233061mysql: [Warning] Using a password on the command line interface can be insecure.802233061mysql: [Warning] Using a password on the command line interface can be insecure.802233062mysql: [Warning] Using a password on the command line interface can be insecure.802233062mysql: [Warning] Using a password on the command line interface can be insecure.802233062mysql: [Warning] Using a password on the command line interface can be insecure.802233061mysql: [Warning] Using a password on the command line interface can be insecure.802233061mysql: [Warning] Using a password on the command line interface can be insecure.802233061mysql: [Warning] Using a password on the command line interface can be insecure.802233062mysql: [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 -P3306mysql: [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 54447Server version: 8.0.22 MySQL Community Server - GPLCopyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.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 querymysql> 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.consulServer: 192.168.68.69Address: 192.168.68.69#53Name: rw-mysql-mgr-server-lhr.service.consulAddress: 192.168.68.61[root@lhrmgr60 /]# nslookup r-mysql-mgr-server-lhr.service.consul Server: 192.168.68.69Address: 192.168.68.69#53Name: r-mysql-mgr-server-lhr.service.consulAddress: 192.168.68.62
可以看到,MGR的高可用也运行正常。
About Me
©著作权归作者所有:来自51CTO博客作者小麦苗best的原创作品,如需转载,请注明出处,否则将追究法律责任● 本文作者:小麦苗,部分内容整理自网络,若有侵权请联系小麦苗删除
● 本文在个人微 信公众号(DB宝)上有同步更新
● QQ群号: 230161599 、618766405,微信群私聊
● 个人QQ号(646634621),微 信号(db_bao),注明添加缘由
● 于 2021年3月 在西安完成
● 最新修改时间:2021年3月
● 版权所有,欢迎分享本文,转载请保留出处●小麦苗的微店: https://weidian.com/?userid=793741433
●小麦苗出版的数据库类丛书: http://blog.itpub.net/26736162/viewspace-2142121/
●小麦苗OCP、OCM、高可用、DBA学习班(Oracle、MySQL、NoSQL): http://blog.itpub.net/26736162/viewspace-2148098/
●数据库笔试面试题库及解答: https://mp.weixin.qq.com/s/Vm5PqNcDcITkOr9cQg6T7w使用微信客户端扫描下面的二维码来关注小麦苗的微信公众号(DB宝)及QQ群(DBA宝典)、添加小麦苗微信, 学习最实用的数据库技术。
你的鼓励让我更有动力
赞赏
0人进行了赞赏支持
更多相关文章
- 图神经网络GNN的可解释性问题与解释方法最新进展
- 2021-03-27:给你一个链表的头节点 head ,旋转链表,将链表每个节点向
- EGG NETWORK永动金融EFTalk火爆来袭
- 携程数据库高可用架构实践
- Java的十大算法,你知道几个?
- 对复杂网络节点重要性的排序方法
- 线索二叉树(C语言)
- OpenKruise 如何实现 K8s 社区首个规模化镜像预热能力
- CDH 6.2.1 集成 freeipa