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

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 n67 192.168.68.67:8301 alive server 1.9.4 2 dc1 n68 192.168.68.68:8301 alive server 1.9.4 2 dc1 MGR60 192.168.68.60:8301 alive client 1.9.4 2 dc1 MGR61 192.168.68.61:8301 alive client 1.9.4 2 dc1 MGR62 192.168.68.62:8301 alive client 1.9.4 2 dc1 [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 n67 192.168.68.67:8301 failed server 1.9.4 2 dc1 n68 192.168.68.68:8301 alive server 1.9.4 2 dc1 MGR60 192.168.68.60:8301 alive client 1.9.4 2 dc1 MGR61 192.168.68.61:8301 alive client 1.9.4 2 dc1 MGR62 192.168.68.62:8301 alive client 1.9.4 2 dc1 [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的高可用也运行正常。

MySQL 数据库

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。