[toc]

# 一、MGR+Consul架構簡介

基於目前存在很多MySQL資料庫單點故障，傳統的MHA，PXC等方案用VIP或者DNS切換的方式可以實現、基於資料庫的資料強一致性考慮，採用MGR叢集，採用consul服務註冊發現實現應用端透過動態DNS 訪問MGR叢集，實現資料庫高可用，自動化切換的方案。

有關MGR之前釋出過幾篇文章，連線如下：

[【DB寶18】在Docker中安裝使用MySQL高可用之MGR](https://mp.weixin.qq.com/s/CCC33OHrGfd6vop9g03t_A)

[【DB寶35】使用MySQL 8.0 克隆(clone)外掛快速新增MGR節點](https://mp.weixin.qq.com/s/HE8GskR_Thyi8nTFMt5PZg)

本次環境一共包括7臺機器，其中3臺做MySQL的MGR資料節點（其上也需要安裝Consul，作為Consul Client），單主模式，還有3臺做Consul Server叢集，實現Consul的高可用，剩下一臺做DNS伺服器，用來解析MGR節點域名，規劃如下表所示：

![](https://gitee.com/lhrbest/pic/raw/master/img/20210311114430.png)

![](https://gitee.com/lhrbest/pic/raw/master/img/20210311113832.png)

# 二、搭建MGR

![](https://gitee.com/lhrbest/pic/raw/master/img/20210311123319.png)

## 2.1、申請3臺MGR機器

```sql
-- 拉取映象
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環境

```sql
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引數

```sql
-- 節點1
cat > /etc/my.cnf <  /etc/my.cnf < /etc/my.cnf <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外掛（所有節點執行）

```sql
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、設定複製賬號

```sql
-- 在主庫(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)上執行

```sql
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）上執行

```sql
reset master;
START GROUP_REPLICATION;
-- 檢視MGR組資訊
SELECT * FROM performance_schema.replication_group_members;
```

執行結果：

可以看到，3個節點狀態為online，並且主節點為192.168.68.60，只有主節點可以寫入，其他節點只讀，MGR單主模式搭建成功。

## 2.9、測試同步

在主節點上執行以下命令，然後在其它節點查詢：

```sql
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叢集

![](https://gitee.com/lhrbest/pic/raw/master/img/20210311194619.png)

## 3.1、申請3臺Consul Server主機

```sql
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

```sql
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

```

為了後續啟動方便，我們使用引數檔案：

```sql
-- 66節點，其它節點修改node_name、advertise_addr和bind_addr即可：
mkdir -p /etc/consul.d/
pkill -9 consul
cat > /etc/consul.d/server.json  < /consul/consul.log &

```

## 3.3、web訪問

http://192.168.66.35:8566

http://192.168.66.35:8567

http://192.168.66.35:8568

![](https://gitee.com/lhrbest/pic/raw/master/img/20210309183102.png)

# 四、在MySQL節點上安裝Consul Client

## 4.1、安裝Consul Client

```sql
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 < /consul/consul.log &
```

查詢狀態：

```sql

[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指令碼

```sql
-- 檢測指令碼【master】
mkdir -p /data/consul/shell/
cat > /data/consul/shell/check_mysql_mgr_master.sh  < /data/consul/shell/check_mysql_mgr_slave.sh < /etc/consul.d/rw-mysql-mgr-server-lhr.json < /etc/consul.d/r-mysql-mgr-server-lhr.json <> 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<> 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< App端配置域名伺服器來解析consul字尾的域名，DNS解析及跳轉， 有多個方案：
>
> 1. 原內網dns伺服器,做域名轉發，consul字尾的，都轉到consul server上
> 2. dns全部跳到consul DNS伺服器上，非consul字尾的,使用 recursors 屬性跳轉到原DNS伺服器上
> 3. dnsmaq 轉： server=/consul/192.168.X.X#8600 解析consul字尾的 
> 4. 使用BIND配置DNS伺服器

這裡只列舉dnsmasq這種方案，更多方法請參考官方文件：https://learn.hashicorp.com/tutorials/consul/dns-forwarding

## 5.1、使用dnsmasq來配置DNS解析

在192.168.66.69上配置dnsmasq。

```sql
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
```

結果：

```sql
[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

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

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> 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的解析：

![](https://gitee.com/lhrbest/pic/raw/master/img/20210311105326.png)

目前的狀態：

```sql
[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進行關機操作：

```sql
docker stop  lhrconsulmgr66 lhrconsulmgr67
```

查詢叢集狀態：

```sql
[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)
```

查詢域名是否正常使用：

```sql
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
```

校驗結果：

```sql
[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的負載均衡和讀寫分離

首先測試讀負載均衡：

```sql
[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關機，然後透過域名來連線，檢查能否正常連線。

```sql
[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

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> shutdown ;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@hostname,@@report_host;
ERROR 2013 (HY000): Lost connection to MySQL server during query

[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的高可用也執行正常。

**About Me**
***
> ● 本文作者：小麥苗，部分內容整理自網路，若有侵權請聯絡小麥苗刪除  
> ● 本文在個人微 信公眾號（[DB寶](https://mp.weixin.qq.com/s/36ahcv7o8u67NcxGSGO5WQ)）上有同步更新  
> ● QQ群號： 230161599 、618766405，微信群私聊  
> ● 個人QQ號（646634621），微 訊號（db_bao），註明新增緣由  
> ● 於 2021年3月 在西安完成  
> ● 最新修改時間：2021年3月  
> ● 版權所有，歡迎分享本文，轉載請保留出處  
> ***
> ●小麥苗的微店： [https://weidian.com/?userid=793741433](https://weidian.com/?userid=793741433)  
> ●小麥苗出版的資料庫類叢書： [http://blog.itpub.net/26736162/viewspace-2142121/](http://blog.itpub.net/26736162/viewspace-2142121/)  
> ●小麥苗OCP、OCM、高可用、DBA學習班（Oracle、MySQL、NoSQL）： [http://blog.itpub.net/26736162/viewspace-2148098/](http://blog.itpub.net/26736162/viewspace-2148098/)  
> ●資料庫筆試面試題庫及解答： [https://mp.weixin.qq.com/s/Vm5PqNcDcITkOr9cQg6T7w](https://mp.weixin.qq.com/s/Vm5PqNcDcITkOr9cQg6T7w)  
> ***
> 使用微信客戶端掃描下面的二維碼來關注小麥苗的微信公眾號（[DB寶](https://mp.weixin.qq.com/s/Vm5PqNcDcITkOr9cQg6T7w)）及QQ群（DBA寶典）、新增小麥苗微信， 學習最實用的資料庫技術。
> ![小麥苗資訊](https://img-blog.csdnimg.cn/20191021104734877.png)
> ***</default></default></default></all></all></all></default></default></default></all></all></all></default></default></default></all></all></all></all></all></all>

【DB寶45】MySQL高可用之MGR+Consul架構部署

相關文章