MySql教程
MySQL高可用新玩法之MGR+Consul
本文主要是介绍MySQL高可用新玩法之MGR+Consul，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！
前面的文章有提到过利用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
从节点检查脚本：
#!/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
可以看到一切正常。后续检查脚本可以判断是否延时，如果延时就不注册服务。
前面的文章有提到过利用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
从节点检查脚本：
#!/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
可以看到一切正常。后续检查脚本可以判断是否延时，如果延时就不注册服务。
这篇关于MySQL高可用新玩法之MGR+Consul的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！
您可能喜欢
-
MySQL资料：新手入门教程11-16
-
MySQL资料：新手入门教程11-16
-
MySQL教程：初学者必备的MySQL数据库入门指南11-15
-
MySQL教程：初学者必看的MySQL入门指南11-15
-
部署MySQL集群项目实战：新手入门教程11-04
-
如何部署MySQL集群资料：新手入门指南11-04
-
MySQL集群项目实战：新手入门指南11-02
-
初学者指南：部署MySQL集群资料11-02
-
部署MySQL集群教程：新手入门指南11-01
-
如何部署MySQL集群：新手入门教程11-01
-
部署MySQL集群学习：新手入门教程11-01
-
部署MySQL集群入门：新手必读指南11-01
-
BinLog入门：新手必读的MySQL二进制日志指南10-23
-
Binlog入门：MySQL数据库的日志管理指南10-23
-
MySQL数据库入门教程：从安装到基本操作10-22
栏目导航
