前面的文章有提到過利用consul+mha實作mysql的高可用,以及利用consul+sentinel實作redis的高可用,具體的請檢視:http://www.cnblogs.com/gomysql/p/8010552.html。本次給大家帶來mysql高可用的新玩法,利用mysql 5.7的mgr+consul實作,至于mgr是什麼,有什麼優勢,如何搭建這裡就不說了,大家自己google,我這裡就是介紹利用mgr+consul實作高可用及故障自動切換。至于consul是什麼可以參考前面的文章。
環境:mgr至少需要3個節點。資料庫版本:mysql 5.7.19
我這裡使用單主模式。
192.168.100.78
192.168.100.75
192.168.100.74
mgr搭建完成以後檢視狀态:
[root@localhost][performance_schema]> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-----------------+-------------+--------------+
| CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-----------------+-------------+--------------+
| group_replication_applier | 8e4d93b8-4dd1-11e8-8306-6c92bf7e18e2 | ym_DB_16_100075 | 3306 | ONLINE |
| 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 |
+---------------------------+--------------------------------------+-----------------+-------------+--------------+
檢視主節點是哪個(我的環境是單主,官方也是推薦使用單主)
[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)
可以看見我目前的主節點是100.75。到此mgr環境ok。下面到部署consul,前面的文章提到過,需要服務發現的機器都需要安裝consul用戶端,也就是3台伺服器都需要安裝。其中涉及到2個檢查腳本(腳本不夠完善,比如複制延時是否進行注冊)。
主節點檢查腳本:
#!/bin/bash
port=$1
user="root"
passwod="123"
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....."
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
View Code
從節點檢查腳本:
#!/bin/bash
port=$1
user="root"
passwod="123"
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....."
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
其中一台伺服器的consul配置檔案,有master和slave,如下:
[root@ym_DB_16_100075 conf]# cat payment-3306-mydb-ser.json
{
"services": [
{
"name": "payment-3306-mydb-ser",
"tags": [
"充值-3306"
],
"address": "192.168.100.75",
"port": 3306,
"checks": [
{
"script": "/usr/local/consul/shell/check_mysql_mgr_master.sh 3306",
"interval": "15s"
}
]
}
]
}
[root@ym_DB_16_100075 conf]# cat r-payment-3306-mydb-ser.json
{
"services": [
{
"name": "r-payment-3306-mydb-ser",
"tags": [
"充值-3306"
],
"address": "192.168.100.75",
"port": 3306,
"checks": [
{
"script": "/usr/local/consul/shell/check_mysql_mgr_slave.sh 3306",
"interval": "15s"
}
]
}
]
}
其他兩台伺服器配置檔案一樣,隻是"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會解析到哪裡。
[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)
停掉以後發現之前的100.75主節點已經被移除,已經自動選出新的節點:
[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 |
+---------------------------+--------------------------------------+-----------------+-------------+--------------+
可以看到現在的主節點已經是100.74,那麼我們看看域名payment-3306-mydb-ser.service.consul解析到哪裡:
可以看到沒有問題,正常。再看看讀域名:r-payment-3306-mydb-ser.service.consul
可以看到一切正常。後續檢查腳本可以判斷是否延時,如果延時就不注冊服務。
作者:Atlas
出處:Atlas的部落格 http://www.cnblogs.com/gomysql
您的支援是對部落客最大的鼓勵,感謝您的認真閱讀。本文版權歸作者所有,歡迎轉載,但請保留該聲明。如果您需要技術支援,本人亦提供有償服務。