MySQL高可用新玩法之MGR+Consul

mgr搭建完成以後查看狀態：

<code>[root@localhost][performance_schema]> select * from performance_schema.replication_group_members;+---------------------------+--------------------------------------+-----------------+-------------+--------------+
| CHANNEL_NAME              |
 MEMBER_ID                            
| MEMBER_HOST     |
 MEMBER_PORT 
| MEMBER_STATE |
+---------------------------+--------------------------------------+-----------------+-------------+--------------+
| group_replication_applier |
 
8
e4d93b8-
4
dd1-
11
e8-
8306
-
6
c92bf7e18e2 
| ym_DB_16_100075 |
        
3306
 
| ONLINE       |
| group_replication_applier |
 
9062
a0ef-
4
dd1-
11
e8-
8822
-
6
c92bf744dd6 
| ym_DB_15_100074 |
        
3306
 
| ONLINE       |
| group_replication_applier |
 
9
a7e7cd5-
4
dd1-
11 
e8-b28c-
6
c92bf7e0d2e 
| ym_DB_19_100078 |
        
3306
 
| ONLINE       |
+---------------------------+--------------------------------------+-----------------+-------------+--------------+/<code>

查看主節點是哪個（我的環境是單主，官方也是推薦使用單主）

<code>[
root@localhost
][
performance_schema
]> select * from  performance
_schema.replication_
group
_members where member_
id =(select variable
_value from performance_
schema.global
_status WHERE VARIABLE_
NAME= 'group
_replication_
primary
_member');+---------------------------+--------------------------------------+-----------------+-------------+--------------+| CHANNEL_
NAME              | MEMBER
_ID                            | MEMBER_
HOST     | MEMBER
_PORT | MEMBER_
STATE |+---------------------------+--------------------------------------+-----------------+-------------+--------------+| group
_replication_
applier | 8e4d93b8-4dd1-11e8-8306-6c92bf7e18e2 | ym
_DB_
16_100075 |        3306 | ONLINE       |+---------------------------+--------------------------------------+-----------------+-------------+--------------+1 row in set (0.00 sec)/<code>

可以看見我目前的主節點是100.75。到此mgr環境ok。下面到部署consul，前面的文章提到過，需要服務發現的機器都需要安裝consul客戶端，也就是3臺服務器都需要安裝。其中涉及到2個檢查腳本（腳本不夠完善，比如複製延時是否進行註冊）。

主節點檢查腳本：

<code>#!/bin/bashport=$user=
"root"
passwod=
""
 comm=
"/usr/local/mysql/bin/mysql -u
$user
 -h 127.0.0.1 -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....."
   exitfi # 判斷節點狀態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...."
   exitfi # 判斷是不是主節點
if
 [[ $server_uuid == $primary_member ]]then   echo 
"MySQL 
$port
  Instance is master ........"
   exitelse   echo 
"MySQL 
$port
  Instance is slave ........" 
   exitfi/<code>

從節點檢查腳本：

<code>#!/bin/bashport=$user=
"root"
passwod=
""
 comm=
"/usr/local/mysql/bin/mysql -u
$user
 -h 127.0.0.1 -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....."
   exitfi # 判斷節點狀態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...."
   exitfi # 判斷是不是主節點
if
 [[ $server_uuid != $primary_member ]]then   echo 
"MySQL 
$port
  Instance is slave ........"
   exitelse   node_num=`$comm -Nse 
"select count(*) from  performance_schema.replication_group_members" 
`   # 判斷如果沒有任何從節點，主節點也註冊從角色服務。   
if
 [ $node_num -eq  ]   then       echo 
"MySQL 
$port
  Instance is slave ........"
       exit   
else
       echo 
"MySQL 
$port
  Instance is master ........"
       exit   fifi/<code>

其中一臺服務器的consul配置文件，有master和slave，如下：

<code>[root@ym_DB_16_100075 conf]/<code>

<code>[root@ym_DB_16_100075 conf]/<code>

其他兩臺服務器配置文件一樣，只是"address"改成對應服務器的地址就完事。啟動consul。ping其中一個域名，比如：payment-3306-mydb-ser.service.consul，那麼返回的是主節點的ip，因為這個域名是寫的。如果ping r-payment-3306-mydb-ser.service.consul，那麼返回的是另外兩個從節點的ip。

從上面可以看到寫的域名解析到的主節點，讀的域名解析到了2個從節點，從節點可以實現負載均衡的效果。

故障測試：

1. 把主節點停掉，查看寫的域名payment-3306-mydb-ser.service.consul會解析到哪裡。

<code>[
root@localhost
][
(none)
]> select * from  performance
_schema.replication_
group
_members;+---------------------------+--------------------------------------+-----------------+-------------+--------------+| CHANNEL_
NAME              | MEMBER
_ID                            | MEMBER_
HOST     | MEMBER
_PORT | MEMBER_
STATE |+---------------------------+--------------------------------------+-----------------+-------------+--------------+| group
_replication_
applier | 9062a0ef-4dd1-11e8-8822-6c92bf744dd6 | ym
_DB_
15
_100074 |        3306 | ONLINE       || group_
replication
_applier | 9a7e7cd5-4dd1-11e8-b28c-6c92bf7e0d2e | ym_
DB
_19_
100078 |        3306 | ONLINE       |+---------------------------+--------------------------------------+-----------------+-------------+--------------+2 rows in set (0.00 sec)/<code>

停掉以後發現之前的100.75主節點已經被移除，已經自動選出新的節點：

<code>[
root@localhost
][
(none)
]> select * from  performance
_schema.replication_ 
group
_members where member_
id =(select variable
_value from performance_
schema.global
_status WHERE VARIABLE_
NAME= 'group
_replication_
primary
_member');+---------------------------+--------------------------------------+-----------------+-------------+--------------+| CHANNEL_
NAME              | MEMBER
_ID                            | MEMBER_
HOST     | MEMBER
_PORT | MEMBER_
STATE |+---------------------------+--------------------------------------+-----------------+-------------+--------------+| group
_replication_
applier | 9062a0ef-4dd1-11e8-8822-6c92bf744dd6 | ym
_DB_
15_100074 |        3306 | ONLINE       |+---------------------------+--------------------------------------+-----------------+-------------+--------------+/<code>

可以看到現在的主節點已經是100.74，那麼我們看看域名payment-3306-mydb-ser.service.consul解析到哪裡：

可以看到沒有問題，正常。再看看讀域名：r-payment-3306-mydb-ser.service.consul

可以看到一切正常。後續檢查腳本可以判斷是否延時，如果延時就不註冊服務。