基于MySQL Group Replication+Consul的高可用架构浅析和测试

本站文章除注明转载外，均为本站原创： 转载自love wife love life —Roger的Oracle/MySQL/PostgreSQL数据恢复博客

本文链接地址: 基于MySQL Group Replication+Consul的高可用架构浅析和测试

MySQL Group Replication已经发布2年了；虽然目前来讲还不够成熟和稳定，然而从技术角度来讲，我认为应该是未来的趋势；针对跨IDC容灾的情况，MGR可以很好的应对；相比而言，PXC就逊色的多了。同时PXC性能相比MGR也要差一些，本人亲测过。

MGR主要架构支持多主和单主模式；所谓多主，也就是指每个节点均可读写，不难理解，多主有点类似MySQL 双主。对于MySQL数据库而言，节点之间的数据同步复制其实是逻辑复制，因此多写必须面临的一个问题就是：数据冲突解决。同时根据我们现在经验来看，多主的问题还是比较多，因此我们都建议客户以单主为主，相对靠谱一些。

那么使用单主，就面临一个问题。解决了数据一致性，那么业务连续性呢？比如比如现在主节点是Node1；Node2、3是Read节点（slave节点）；所以写操作都在Node1进行，如果Node1 出现故障，数据库主节点切换到Node2后，怎么办？此时对于业务来讲，怎么处理？难道还要去修改IP地址吗？

当然，也有人仍然在使用MGR+Keepalive的方式，通过Keepalive来虚拟出一个VIP地址提供给业务使用；当出现节点切换后，VIP会进行飘逸。对于Keepalive来说，是一个比较古老的软件了，本身也有不少问题。因此现在大家更倾向于使用服务发现的Consul来代替了。

实际上不仅仅是MGR这种架构，目前用的最多的MHA架构，也有不少用人在试用MHA+consul.

对于Consul的一些基本知识和原理，这里我就不做过多解释了。如下是我的一个简单的测试，供参考。

1.每个节点都创建对应目录

[root@killdb tmp]# mkdir -p /etc/consul.d/
[root@killdb tmp]# mkdir -p /opt/consul
[root@killdb tmp]# mkdir -p /opt/consul/shell

[root@killdb tmp]# mkdir -p /etc/consul.d/

[root@killdb tmp]# mkdir -p /opt/consul

[root@killdb tmp]# mkdir -p /opt/consul/shell

2. consul解压即可用，无需按照，步骤略
3. consul server端json配置如下：

[root@killdb consul.d]# cat server.json 
{
"data_dir": "/opt/consul",
"datacenter": "dc1",
"log_level": "INFO",       
"server": true,         
"node_name": "killdb",      
"bootstrap_expect": 1,     
"bind_addr": "172.16.29.160",        
"client_addr": "172.16.29.160", 
"ui":true
}

[root@killdb consul.d]# cat server.json

{

"data_dir": "/opt/consul",

"datacenter": "dc1",

"log_level": "INFO",

"server": true,

"node_name": "killdb",

"bootstrap_expect": 1,

"bind_addr": "172.16.29.160",

"client_addr": "172.16.29.160",

"ui":true

}

4. mgr 每个节点都配置如下：

[root@mysqldb1 consul.d]# cat client.json 
{
"data_dir": "/opt/consul",
"enable_script_checks": true,
"bind_addr": "172.16.29.154",     --对应每个mgr节点的地址
"retry_join": ["172.16.29.160"],
"retry_interval": "30s",
"rejoin_after_leave": true,
"start_join": ["172.16.29.160"] ,
"node_name": "mgr_mysqldb1"    --对应每个mgr节点名称
}
[root@mysqldb1 consul.d]# cat w-mgr-test-enmotech.json 
{
"service": {
"name": "w-mgr-test-enmotech",           
"tags": ["enmotech-3306"],
"address": "172.16.29.154",
"meta": {
"meta": "for my service"
},
"port": 3306,
"enable_tag_override": false,
"checks": [
{
"args": ["/opt/consul/shell/check_mysql_mgr_master.sh"],
"interval": "10s"
}
]
}
}
[root@mysqldb1 consul.d]# cat r-mgr-test-enmotech.json 
{
"service": {
"name": "r-mgr-test-enmotech",
"tags": ["enmotech-3306"],
"address": "172.16.29.154",
"meta": {
"meta": "for my service"
},
"port": 3306,
"enable_tag_override": false,
"checks": [
{
"args": ["/opt/consul/shell/check_mysql_mgr_slave.sh"],
"interval": "1s"
}
]
}
}

[root@mysqldb1 consul.d]# cat client.json

{

"data_dir": "/opt/consul",

"enable_script_checks": true,

"bind_addr": "172.16.29.154", --对应每个mgr节点的地址

"retry_join": ["172.16.29.160"],

"retry_interval": "30s",

"rejoin_after_leave": true,

"start_join": ["172.16.29.160"] ,

"node_name": "mgr_mysqldb1" --对应每个mgr节点名称

}

[root@mysqldb1 consul.d]# cat w-mgr-test-enmotech.json

{

"service": {

"name": "w-mgr-test-enmotech",

"tags": ["enmotech-3306"],

"address": "172.16.29.154",

"meta": {

"meta": "for my service"

"port": 3306,

"enable_tag_override": false,

"checks": [

{

"args": ["/opt/consul/shell/check_mysql_mgr_master.sh"],

"interval": "10s"

}

]

}

[root@mysqldb1 consul.d]# cat r-mgr-test-enmotech.json

{

"service": {

"name": "r-mgr-test-enmotech",

"tags": ["enmotech-3306"],

"address": "172.16.29.154",

"meta": {

"meta": "for my service"

"port": 3306,

"enable_tag_override": false,

"checks": [

{

"args": ["/opt/consul/shell/check_mysql_mgr_slave.sh"],

"interval": "1s"

}

]

}

5. mgr每个节点部署上述2个check mysql状态的脚本

这里我参考了互联网上的一些脚本，大家可以进行随意更改；

[root@mysqldb1 shell]# cat check_mysql_mgr_master.sh 
#!/bin/bash
port=3306
user="root"
passwod="enmotech"
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
[root@mysqldb1 shell]# cat check_mysql_mgr_slave.sh 
#!/bin/bash
port=3306
user="root"
passwod="enmotech"
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

[root@mysqldb1 shell]# cat check_mysql_mgr_master.sh

#!/bin/bash

port=3306

user="root"

passwod="enmotech"

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

# 判断节点状态,是否存活

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

# 判断是不是主节点

if [[ $server_uuid == $primary_member ]]

then

echo "MySQL $port Instance is master ........"

exit 0

else

echo "MySQL $port Instance is slave ........"

exit 2

[root@mysqldb1 shell]# cat check_mysql_mgr_slave.sh

#!/bin/bash

port=3306

user="root"

passwod="enmotech"

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

# 判断节点状态

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

# 判断是不是主节点

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

6. 启动consul server

如果是生产环境，建议至少3个节点的consul集群，这里模拟暂且用一个节点把。
[root@killdb ~]# /etc/consul.d/consul agent -config-dir=/etc/consul.d -enable-script-checks > /opt/consul/consul.log &
[1] 19187
[root@killdb ~]#

7. 启动mgr每个节点的consul angnt

./consul agent -config-dir=/etc/consul.d -enable-script-checks > /opt/consul/consul.log &

1	./consul agent -config-dir=/etc/consul.d -enable-script-checks > /opt/consul/consul.log &

正常启动后会看到如下的类似的信息：

==> Starting Consul agent...
==> Joining cluster...
Join completed. Synced with 1 initial agents
==> Consul agent running!
Version: 'v1.4.0'
Node ID: '1b1f724e-a57a-01f7-2bed-661faea0f3d1'
Node name: 'mgr_mysqldb1'
Datacenter: 'dc1' (Segment: '')
Server: false (Bootstrap: false)
Client Addr: [127.0.0.1] (HTTP: 8500, HTTPS: -1, gRPC: -1, DNS: 8600)
Cluster Addr: 172.16.29.154 (LAN: 8301, WAN: 8302)
Encrypt: Gossip: false, TLS-Outgoing: false, TLS-Incoming: false
==> Log data will now stream in as it occurs:
2018/12/27 17:05:07 [WARN] agent: Node name "mgr_mysqldb1" will not be discoverable via DNS due to invalid characters. Valid characters include all alpha-numerics and dashes.
2018/12/27 17:05:07 [INFO] serf: Ignoring previous leave in snapshot
2018/12/27 17:05:07 [INFO] serf: EventMemberJoin: mgr_mysqldb1 172.16.29.154
2018/12/27 17:05:07 [INFO] serf: Attempting re-join to previously known node: killdb: 172.16.29.160:8301
2018/12/27 17:05:07 [INFO] agent: Started DNS server 127.0.0.1:8600 (udp)
2018/12/27 17:05:07 [WARN] agent/proxy: running as root, will not start managed proxies
2018/12/27 17:05:07 [INFO] agent: Started DNS server 127.0.0.1:8600 (tcp)
2018/12/27 17:05:07 [INFO] agent: Started HTTP server on 127.0.0.1:8500 (tcp)
2018/12/27 17:05:07 [INFO] agent: (LAN) joining: [172.16.29.160]
2018/12/27 17:05:07 [INFO] agent: Retry join LAN is supported for: aliyun aws azure digitalocean gce k8s os packet scaleway softlayer triton vsphere
2018/12/27 17:05:07 [INFO] agent: Joining LAN cluster...
2018/12/27 17:05:07 [INFO] agent: (LAN) joining: [172.16.29.160]
2018/12/27 17:05:07 [INFO] serf: EventMemberJoin: killdb 172.16.29.160
2018/12/27 17:05:07 [INFO] agent: (LAN) joined: 1 Err: <nil>
2018/12/27 17:05:07 [INFO] agent: started state syncer
2018/12/27 17:05:07 [INFO] consul: adding server killdb (Addr: tcp/172.16.29.160:8300) (DC: dc1)
2018/12/27 17:05:07 [INFO] serf: Re-joined to previously known node: killdb: 172.16.29.160:8301
2018/12/27 17:05:07 [INFO] agent: (LAN) joined: 1 Err: <nil>
2018/12/27 17:05:07 [INFO] agent: Join LAN completed. Synced with 1 initial agents
2018/12/27 17:05:07 [INFO] agent: Synced service "r-mgr-test-enmotech"
2018/12/27 17:05:07 [INFO] agent: Synced service "w-mgr-test-enmotech"
2018/12/27 17:05:07 [INFO] agent: Deregistered service "r-mgr-test-enmotech.com"
2018/12/27 17:05:07 [INFO] agent: Deregistered service "w-mgr-test-enmotech.com"
2018/12/27 17:05:07 [INFO] agent: Deregistered check "service:r-mgr-test-enmotech.com"
2018/12/27 17:05:07 [INFO] agent: Deregistered check "service:w-mgr-test-enmotech.com"
2018/12/27 17:05:09 [INFO] agent: Synced check "service:r-mgr-test-enmotech"
2018/12/27 17:05:16 [WARN] agent: Check "service:w-mgr-test-enmotech" is now critical
2018/12/27 17:05:26 [WARN] agent: Check "service:w-mgr-test-enmotech" is now critical
......

==> Starting Consul agent...

==> Joining cluster...

Join completed. Synced with 1 initial agents

==> Consul agent running!

Version: 'v1.4.0'

Node ID: '1b1f724e-a57a-01f7-2bed-661faea0f3d1'

Node name: 'mgr_mysqldb1'

Datacenter: 'dc1' (Segment: '')

Server: false (Bootstrap: false)

Client Addr: [127.0.0.1] (HTTP: 8500, HTTPS: -1, gRPC: -1, DNS: 8600)

Cluster Addr: 172.16.29.154 (LAN: 8301, WAN: 8302)

Encrypt: Gossip: false, TLS-Outgoing: false, TLS-Incoming: false

==> Log data will now stream in as it occurs:

2018/12/27 17:05:07 [WARN] agent: Node name "mgr_mysqldb1" will not be discoverable via DNS due to invalid characters. Valid characters include all alpha-numerics and dashes.

2018/12/27 17:05:07 [INFO] serf: Ignoring previous leave in snapshot

2018/12/27 17:05:07 [INFO] serf: EventMemberJoin: mgr_mysqldb1 172.16.29.154

2018/12/27 17:05:07 [INFO] serf: Attempting re-join to previously known node: killdb: 172.16.29.160:8301

2018/12/27 17:05:07 [INFO] agent: Started DNS server 127.0.0.1:8600 (udp)

2018/12/27 17:05:07 [WARN] agent/proxy: running as root, will not start managed proxies

2018/12/27 17:05:07 [INFO] agent: Started DNS server 127.0.0.1:8600 (tcp)

2018/12/27 17:05:07 [INFO] agent: Started HTTP server on 127.0.0.1:8500 (tcp)

2018/12/27 17:05:07 [INFO] agent: (LAN) joining: [172.16.29.160]

2018/12/27 17:05:07 [INFO] agent: Retry join LAN is supported for: aliyun aws azure digitalocean gce k8s os packet scaleway softlayer triton vsphere

2018/12/27 17:05:07 [INFO] agent: Joining LAN cluster...

2018/12/27 17:05:07 [INFO] agent: (LAN) joining: [172.16.29.160]

2018/12/27 17:05:07 [INFO] serf: EventMemberJoin: killdb 172.16.29.160

2018/12/27 17:05:07 [INFO] agent: (LAN) joined: 1 Err: <nil>

2018/12/27 17:05:07 [INFO] agent: started state syncer

2018/12/27 17:05:07 [INFO] consul: adding server killdb (Addr: tcp/172.16.29.160:8300) (DC: dc1)

2018/12/27 17:05:07 [INFO] serf: Re-joined to previously known node: killdb: 172.16.29.160:8301

2018/12/27 17:05:07 [INFO] agent: (LAN) joined: 1 Err: <nil>

2018/12/27 17:05:07 [INFO] agent: Join LAN completed. Synced with 1 initial agents

2018/12/27 17:05:07 [INFO] agent: Synced service "r-mgr-test-enmotech"

2018/12/27 17:05:07 [INFO] agent: Synced service "w-mgr-test-enmotech"

2018/12/27 17:05:07 [INFO] agent: Deregistered service "r-mgr-test-enmotech.com"

2018/12/27 17:05:07 [INFO] agent: Deregistered service "w-mgr-test-enmotech.com"

2018/12/27 17:05:07 [INFO] agent: Deregistered check "service:r-mgr-test-enmotech.com"

2018/12/27 17:05:07 [INFO] agent: Deregistered check "service:w-mgr-test-enmotech.com"

2018/12/27 17:05:09 [INFO] agent: Synced check "service:r-mgr-test-enmotech"

2018/12/27 17:05:16 [WARN] agent: Check "service:w-mgr-test-enmotech" is now critical

2018/12/27 17:05:26 [WARN] agent: Check "service:w-mgr-test-enmotech" is now critical

......

8. 当所有节点都启动后后，可以检查整个consul的注册信息，如下：

[root@killdb ~]# /etc/consul.d/consul members -http-addr='172.16.29.160:8500'
Node Address Status Type Build Protocol DC Segment
killdb 172.16.29.160:8301 alive server 1.4.0 2 dc1 <all>
mgr_mysqldb1 172.16.29.154:8301 alive client 1.4.0 2 dc1 <default>
mgr_mysqldb2 172.16.29.132:8301 alive client 1.4.0 2 dc1 <default>
mgr_mysqldb3 172.16.29.133:8301 alive client 1.4.0 2 dc1 <default>
[root@killdb ~]#

[root@killdb ~]# /etc/consul.d/consul members -http-addr='172.16.29.160:8500'

Node Address Status Type Build Protocol DC Segment

killdb 172.16.29.160:8301 alive server 1.4.0 2 dc1 <all>

mgr_mysqldb1 172.16.29.154:8301 alive client 1.4.0 2 dc1 <default>

mgr_mysqldb2 172.16.29.132:8301 alive client 1.4.0 2 dc1 <default>

mgr_mysqldb3 172.16.29.133:8301 alive client 1.4.0 2 dc1 <default>

[root@killdb ~]#

9.进行验证

–检查域名状态

[root@killdb ~]# dig @172.16.29.160 -p 8600 w-mgr-test-enmotech.service.consul
; <<>> DiG 9.8.2rc1-RedHat-9.8.2-0.17.rc1.0.2.el6_4.6 <<>> @172.16.29.160 -p 8600 w-mgr-test-enmotech.service.consul
; (1 server found)
;; global options: +cmd
;; Got answer:
;; ->>HEADER<<- opcode: QUERY, status: NOERROR, id: 10320
;; flags: qr aa rd; QUERY: 1, ANSWER: 1, AUTHORITY: 0, ADDITIONAL: 1
;; WARNING: recursion requested but not available
;; QUESTION SECTION:
;w-mgr-test-enmotech.service.consul. IN A
;; ANSWER SECTION:
w-mgr-test-enmotech.service.consul. 0 IN A      172.16.29.154
;; ADDITIONAL SECTION:
w-mgr-test-enmotech.service.consul. 0 IN TXT    "consul-network-segment="
;; Query time: 4 msec
;; SERVER: 172.16.29.160#8600(172.16.29.160)
;; WHEN: Sat Oct  7 04:07:45 2017
;; MSG SIZE  rcvd: 104
[root@killdb ~]# dig @172.16.29.160 -p 8600 r-mgr-test-enmotech.service.consul
; <<>> DiG 9.8.2rc1-RedHat-9.8.2-0.17.rc1.0.2.el6_4.6 <<>> @172.16.29.160 -p 8600 r-mgr-test-enmotech.service.consul
; (1 server found)
;; global options: +cmd
;; Got answer:
;; ->>HEADER<<- opcode: QUERY, status: NOERROR, id: 13148
;; flags: qr aa rd; QUERY: 1, ANSWER: 2, AUTHORITY: 0, ADDITIONAL: 2
;; WARNING: recursion requested but not available
;; QUESTION SECTION:
;r-mgr-test-enmotech.service.consul. IN A
;; ANSWER SECTION:
r-mgr-test-enmotech.service.consul. 0 IN A      172.16.29.133
r-mgr-test-enmotech.service.consul. 0 IN A      172.16.29.132
;; ADDITIONAL SECTION:
r-mgr-test-enmotech.service.consul. 0 IN TXT    "consul-network-segment="
r-mgr-test-enmotech.service.consul. 0 IN TXT    "consul-network-segment="
;; Query time: 4 msec
;; SERVER: 172.16.29.160#8600(172.16.29.160)
;; WHEN: Sat Oct  7 04:06:53 2017
;; MSG SIZE  rcvd: 156

[root@killdb ~]# dig @172.16.29.160 -p 8600 w-mgr-test-enmotech.service.consul

; <<>> DiG 9.8.2rc1-RedHat-9.8.2-0.17.rc1.0.2.el6_4.6 <<>> @172.16.29.160 -p 8600 w-mgr-test-enmotech.service.consul

; (1 server found)

;; global options: +cmd

;; Got answer:

;; ->>HEADER<<- opcode: QUERY, status: NOERROR, id: 10320

;; flags: qr aa rd; QUERY: 1, ANSWER: 1, AUTHORITY: 0, ADDITIONAL: 1

;; WARNING: recursion requested but not available

;; QUESTION SECTION:

;w-mgr-test-enmotech.service.consul. IN A

;; ANSWER SECTION:

w-mgr-test-enmotech.service.consul. 0 IN A 172.16.29.154

;; ADDITIONAL SECTION:

w-mgr-test-enmotech.service.consul. 0 IN TXT "consul-network-segment="

;; Query time: 4 msec

;; SERVER: 172.16.29.160#8600(172.16.29.160)

;; WHEN: Sat Oct 7 04:07:45 2017

;; MSG SIZE rcvd: 104

[root@killdb ~]# dig @172.16.29.160 -p 8600 r-mgr-test-enmotech.service.consul

; <<>> DiG 9.8.2rc1-RedHat-9.8.2-0.17.rc1.0.2.el6_4.6 <<>> @172.16.29.160 -p 8600 r-mgr-test-enmotech.service.consul

; (1 server found)

;; global options: +cmd

;; Got answer:

;; ->>HEADER<<- opcode: QUERY, status: NOERROR, id: 13148

;; flags: qr aa rd; QUERY: 1, ANSWER: 2, AUTHORITY: 0, ADDITIONAL: 2

;; WARNING: recursion requested but not available

;; QUESTION SECTION:

;r-mgr-test-enmotech.service.consul. IN A

;; ANSWER SECTION:

r-mgr-test-enmotech.service.consul. 0 IN A 172.16.29.133

r-mgr-test-enmotech.service.consul. 0 IN A 172.16.29.132

;; ADDITIONAL SECTION:

r-mgr-test-enmotech.service.consul. 0 IN TXT "consul-network-segment="

;; Query time: 4 msec

;; SERVER: 172.16.29.160#8600(172.16.29.160)

;; WHEN: Sat Oct 7 04:06:53 2017

;; MSG SIZE rcvd: 156

可以看到，目前写节点在154；读节点在132，133上。接下来我们停掉154，看看写服务是否会转移到其他节点：

—stop mgr node1

mysql> show variables like '%hostname%';
+---------------+----------+
| Variable_name | Value    |
+---------------+----------+
| hostname      | mysqldb1 |
+---------------+----------+
1 row in set (0.00 sec)
mysql> stop group_replication;
Query OK, 0 rows affected (9.36 sec)
mysql> shutdown;
Query OK, 0 rows affected (0.00 sec)
mysql> exit
Bye

mysql> show variables like '%hostname%';

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

| Variable_name | Value |

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

| hostname | mysqldb1 |

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

1 row in set (0.00 sec)

mysql> stop group_replication;

Query OK, 0 rows affected (9.36 sec)

mysql> shutdown;

Query OK, 0 rows affected (0.00 sec)

mysql> exit

Bye

—再次检查服务

[root@killdb ~]# dig @172.16.29.160 -p 8600 w-mgr-test-enmotech.service.consul
; <<>> DiG 9.8.2rc1-RedHat-9.8.2-0.17.rc1.0.2.el6_4.6 <<>> @172.16.29.160 -p 8600 w-mgr-test-enmotech.service.consul
; (1 server found)
;; global options: +cmd
;; Got answer:
;; ->>HEADER<<- opcode: QUERY, status: NOERROR, id: 63202
;; flags: qr aa rd; QUERY: 1, ANSWER: 1, AUTHORITY: 0, ADDITIONAL: 1
;; WARNING: recursion requested but not available
;; QUESTION SECTION:
;w-mgr-test-enmotech.service.consul. IN A
;; ANSWER SECTION:
w-mgr-test-enmotech.service.consul. 0 IN A 172.16.29.133
;; ADDITIONAL SECTION:
w-mgr-test-enmotech.service.consul. 0 IN TXT "consul-network-segment="
;; Query time: 4 msec
;; SERVER: 172.16.29.160#8600(172.16.29.160)
;; WHEN: Sat Oct 7 04:09:57 2017
;; MSG SIZE rcvd: 104
[root@killdb ~]# dig @172.16.29.160 -p 8600 r-mgr-test-enmotech.service.consul
; <<>> DiG 9.8.2rc1-RedHat-9.8.2-0.17.rc1.0.2.el6_4.6 <<>> @172.16.29.160 -p 8600 r-mgr-test-enmotech.service.consul
; (1 server found)
;; global options: +cmd
;; Got answer:
;; ->>HEADER<<- opcode: QUERY, status: NOERROR, id: 49407
;; flags: qr aa rd; QUERY: 1, ANSWER: 1, AUTHORITY: 0, ADDITIONAL: 1
;; WARNING: recursion requested but not available
;; QUESTION SECTION:
;r-mgr-test-enmotech.service.consul. IN A
;; ANSWER SECTION:
r-mgr-test-enmotech.service.consul. 0 IN A 172.16.29.132
;; ADDITIONAL SECTION:
r-mgr-test-enmotech.service.consul. 0 IN TXT "consul-network-segment="
;; Query time: 4 msec
;; SERVER: 172.16.29.160#8600(172.16.29.160)
;; WHEN: Sat Oct 7 04:10:02 2017
;; MSG SIZE rcvd: 104

[root@killdb ~]# dig @172.16.29.160 -p 8600 w-mgr-test-enmotech.service.consul

; <<>> DiG 9.8.2rc1-RedHat-9.8.2-0.17.rc1.0.2.el6_4.6 <<>> @172.16.29.160 -p 8600 w-mgr-test-enmotech.service.consul

; (1 server found)

;; global options: +cmd

;; Got answer:

;; ->>HEADER<<- opcode: QUERY, status: NOERROR, id: 63202

;; flags: qr aa rd; QUERY: 1, ANSWER: 1, AUTHORITY: 0, ADDITIONAL: 1

;; WARNING: recursion requested but not available

;; QUESTION SECTION:

;w-mgr-test-enmotech.service.consul. IN A

;; ANSWER SECTION:

w-mgr-test-enmotech.service.consul. 0 IN A 172.16.29.133

;; ADDITIONAL SECTION:

w-mgr-test-enmotech.service.consul. 0 IN TXT "consul-network-segment="

;; Query time: 4 msec

;; SERVER: 172.16.29.160#8600(172.16.29.160)

;; WHEN: Sat Oct 7 04:09:57 2017

;; MSG SIZE rcvd: 104

[root@killdb ~]# dig @172.16.29.160 -p 8600 r-mgr-test-enmotech.service.consul

; <<>> DiG 9.8.2rc1-RedHat-9.8.2-0.17.rc1.0.2.el6_4.6 <<>> @172.16.29.160 -p 8600 r-mgr-test-enmotech.service.consul

; (1 server found)

;; global options: +cmd

;; Got answer:

;; ->>HEADER<<- opcode: QUERY, status: NOERROR, id: 49407

;; flags: qr aa rd; QUERY: 1, ANSWER: 1, AUTHORITY: 0, ADDITIONAL: 1

;; WARNING: recursion requested but not available

;; QUESTION SECTION:

;r-mgr-test-enmotech.service.consul. IN A

;; ANSWER SECTION:

r-mgr-test-enmotech.service.consul. 0 IN A 172.16.29.132

;; ADDITIONAL SECTION:

r-mgr-test-enmotech.service.consul. 0 IN TXT "consul-network-segment="

;; Query time: 4 msec

;; SERVER: 172.16.29.160#8600(172.16.29.160)

;; WHEN: Sat Oct 7 04:10:02 2017

;; MSG SIZE rcvd: 104

我们可以看到，写节点已经发生了改变，成功解析到其他节点了。我们不难看出，如果能结合dns，业务通过域名的方式来访问mgr集群，几乎可以完美解决业务连续性问题。

Tags: consul, mysql group replication
Posted in MySQL, MySQL 架构设计 on 12月 29, 2018

You must be logged in to post a comment.

love wife love life —Roger的Oracle/MySQL/PostgreSQL数据恢复博客

Categories

Archives

最新评论

国内圈子

oracle security

基于MySQL Group Replication+Consul的高可用架构浅析和测试

Leave a Reply