原【DB宝45】MySQL高可用之MGR+Consul架构部署

发布日期 2021年6月21日 · 已更新 2022年8月3日

0 3724 7

👉 本文共约2780个字，系统预计阅读时间或需11分钟。

5.1、使用dnsmasq来配置DNS解析
5.2、MySQL通过域名连接
6.1、测试Consul Server的高可用
6.2、测试MySQL的负载均衡和读写分离
6.3、测试MGR高可用

五、配置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

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

[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

[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

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;

+---------------------------+--------------------------------------+---------------+-------------+--------------+-------------+----------------+

+---------------------------+--------------------------------------+---------------+-------------+--------------+-------------+----------------+

| 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