MySQL高可用方案之MGR+ProxySQL+Keepalived

一.规划

IP、操作系统、主机名、角色、软件包版本、安装方式等
【node1】
ip地址：10.10.1.211
OS：CentOS 7.6
主机名：vl7bjsqltest01
角色：master
MySQL版本：5.7.28
安装方式：通用二进制包

【node2】
ip地址：10.10.1.212
OS：CentOS 7.6
主机名：vl7bjsqltest02
角色：slave1
MySQL版本：5.7.28
安装方式：通用二进制包

【node3】
ip地址：10.10.1.213
OS：CentOS 7.6
主机名：vl7bjsqltest03
角色：slave2 & ProxySQL2
MySQL版本：5.7.28
Proxy版本：1.4.16
安装方式：MySQL-通用二进制包，ProxySQL-rpm，Keepalived-yum

【node4】
ip地址：10.10.1.214
OS：CentOS 7.6
主机名：vl7bjsqltest04
角色：ProxySQL1
Proxy版本：1.4.16
安装方式：rpm，Keepalived-yum
VIP：10.10.1.215

二.MySQL节点基础环境准备

##1.配置主机名
MGR环境中务必将hostname配置好
编辑四个主机名和ip映射关系

vim /etc/hosts


修改主机名

vim /etc/sysconfig/network


##2.清理历史环境

rpm -qa |grep mariadb


如我这里打印出来的信息是mariadb-libs-5.5.60-1.el7_5.x86_64

rpm -e --nodeps mariadb-libs-5.5.60-1.el7_5.x86_64


##3.创建用户和目录

useradd mysql -s /sbin/nologin
id mysql

mkdir -p /app/database/
mkdir -p /data/3306/
mkdir -p /binlog/

chown -R mysql.mysql /app/ /data/ /binlog/


#三.MySQL安装
上传tar.gz文件至/app/database/目录，并解压:

tar xf mysql-5.7.28-el7-x86_64.tar.gz


我们重命名为mysql

mv mysql-5.7.28-el7-x86_64/ mysql/


环境变量配置：

vim /etc/profile


添加一行：

export PATH=/app/database/mysql/bin:$PATH


生效配置：

source /etc/profile


验证：

mysql -V


初始化：

mysqld --initialize-insecure --user=mysql --basedir=/app/database/mysql --datadir=/data/3306/


基本配置文件，以master节点为例，不同节点server_id需要设置不同数字（之后还会增加MGR参数和其他参数）

cat > /etc/my.cnf create user 'monitor'@'%' identified by '123456';
mysql> grant all on *.* to 'monitor'@'%';
mysql> create user 'proxysql'@'%' identified by '123456';
mysql> grant all on *.* to 'proxysql'@'%';


实际上权限不用给这么大

GRANT USAGE,process,replication slave,replication client ON *.* TO 'monitor'@'%';
GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO 'proxysql'@'%';


即可

十.ProxySQL Cluster配置

在node3和4上都修改 /etc/proxysql.cnf (修改了admin_variables段、proxysql_servers段、mysql_variables段）
截取内容参考：

datadir="/var/lib/proxysql"

admin_variables =
{

admin_credentials="admin:admin;cluster_demo:123456"
mysql_ifaces="0.0.0.0:6032"
cluster_username="cluster_demo"
cluster_password="123456"
cluster_check_interval_ms=200
cluster_check_status_frequency=100
cluster_mysql_query_rules_save_to_disk=true
cluster_mysql_servers_save_to_disk=true
cluster_mysql_users_save_to_disk=true
cluster_proxysql_servers_save_to_disk=true
cluster_mysql_query_rules_diffs_before_sync=3
cluster_mysql_servers_diffs_before_sync=3
cluster_mysql_users_diffs_before_sync=3
cluster_proxysql_servers_diffs_before_sync=3

}

proxysql_servers =
(

{
hostname="10.10.1.214"
port=6032
comment="proxysql214"
},

{
hostname="10.10.1.213"
port=6032
comment="proxysql213_mysqlslave2"
}
)

mysql_variables=

{
threads=4
max_connections=2048
default_query_delay=0
default_query_timeout=36000000
have_compress=true
poll_timeout=2000
# interfaces="0.0.0.0:6033;/tmp/proxysql.sock"
interfaces="0.0.0.0:6033"
default_schema="information_schema"
stacksize=1048576
server_version="5.7.28"
connect_timeout_server=3000
# make sure to configure monitor username and password
# https://github.com/sysown/proxysql/wiki/Global-variables#mysql-monitor_username-mysql-monitor_password
monitor_username="monitor"
monitor_password="123456"
monitor_history=600000
monitor_connect_interval=60000
monitor_ping_interval=10000
monitor_read_only_interval=1500
monitor_read_only_timeout=500
ping_interval_server_msec=120000
ping_timeout_server=500
commands_stats=true
sessions_sort=true
connect_retries_on_failure=10
}


注意：
如果想要让proxysql.cnf文件里的配置在重启proxysql服务后生效(即想要让proxysql重启时读取并解析proxysql.cnf配置文件)，则需要先删除/var/lib/proxysql/proxysql.db数据库文件，然后再重启proxysql服务。
这样就相当于初始化启动proxysql服务了，会再次生产一个纯净的proxysql.db数据库文件(如果之前配置了proxysql相关路由规则等，则就会被抹掉)。
如遇上本地proxysql管理连接不上，也需通过重建proxysql.db数据库文件解决。

mv /var/lib/proxysql/proxysql.db /var/lib/proxysql/proxysql.db.bak
或 rm -f /var/lib/proxysql/proxysql.db
systemctl restart proxysql.service


到两个节点都确认一下：

mysql -uadmin -p -h127.0.0.1 -P6032 --prompt='proxysql> '


查看ProxySQL集群节点信息：

select * from runtime_proxysql_servers ;
select * from proxysql_servers;


2个节点都正常显示信息的话，就可以做其他的配置操作了

select hostname,port,comment,Uptime_s,last_check_ms from stats_proxysql_servers_metrics;
select * from stats_proxysql_servers_metrics;


十一.ProxySQL配置

随便在一个ProxySQL节点进行配置
##1.添加服务器列表

proxysql> select * from mysql_servers; 当前应是空值
proxysql> insert into mysql_servers(hostgroup_id,hostname,port,weight,comment) values(10,'10.10.1.212',3306,1,'master'),(10,'10.10.1.211',3306,1,'slave1'),(10,'10.10.1.213',3306,3,'slave2'); 我这个地方备注写的不对，211是master，212是slave1，忽略备注即可；
proxysql> load mysql servers to runtime;
proxysql> save mysql servers to disk;
proxysql> select * from mysql_servers;


备注：
（1）表mysql_servers：后端可以连接MySQL主机的列表
（2）所有节点都是ONLINE状态
（3）slave2节点的查询权重调整为3，为了让更多的读请求路由到这台配置更好的主机上
（4）表mysql_servers常用字段说明，参考：https://blog.51cto.com/qiuyue/2413300
（5）在load mysql servers to runtime;后就可以到另一个ProxySQL节点通过select * from mysql_servers;查看信息，来验证集群是否正常同步；

##2.配置和注册监控用户monitor
因为之前已经写在了配置文件中，所以我们检查即可，如果之前配置文件没写，则需要按如下方法配置：

proxysql> set mysql-monitor_username='monitor';
proxysql> set mysql-monitor_password='123456';
proxysql> load mysql variables to runtime;
proxysql> save mysql variables to disk;
proxysql> select * from global_variables where variable_name in ('mysql-monitor_username','mysql-monitor_password');
select * from monitor.mysql_server_connect_log order by time_start_us desc limit 6;
select * from monitor.mysql_server_ping_log order by time_start_us desc limit 6;


##3.配置主从分组信息
查看表中的字段可使用命令

proxysql> show create table mysql_replication_hostgroups\G


配置分组，第一列是读写组，第二列是只读组，第三列是注释

proxysql> insert into mysql_replication_hostgroups values (10,20,'proxysql');
proxysql> load mysql servers to runtime;
proxysql> save mysql servers to disk;
proxysql> select * from mysql_replication_hostgroups;


可看到读写组的id

proxysql> select hostgroup_id,hostname,port,status,weight,comment from mysql_servers;


此时hostgroup_id会按照我们设置的显示
此时再select * from mysql_servers;发现hostgroup_id也按照我们设置的改变
备注：ProxySQL会根据MySQL中read_only的取值将主机进行分组，read_only=0的master节点被分配到编号为10的写入组，而read_only=1的两个slave节点则被分配到编号为20的读取组

##4.配置对外访问用户proxysql

proxysql> insert into mysql_users(username,password,default_hostgroup) values('proxysql','123456',10);
proxysql> load mysql users to runtime;
proxysql> save mysql users to disk;
proxysql> select * from mysql_users\G


区别于之前的mysql-monitor_username的查看方式
我们随便进入主节点或从节点

mysql -uproxysql -p -h10.10.1.214 -P6033 -e 'select @@hostname;'


最后打印的结果都是主节点主机名vl7bjsqltest02，即此时的master

再试一下另一个proxysql节点：

mysql -uproxysql -p -h10.10.1.213 -P6033 -e 'select @@hostname;'


注：后续我们在mysql中创建的账号，也需要通过这种方式添加到proxysql中。
如之前在mysql中添加过的账号wenjie.wang：

proxysql> insert into mysql_users(username,password,default_hostgroup) values('wenjie.wang','123456',10);
proxysql> load mysql users to runtime;
proxysql> save mysql users to disk;
proxysql> select * from mysql_users\G


##5.配置路由规则

proxysql> insert into mysql_query_rules(active,match_pattern,destination_hostgroup,apply) values(1,'^select.*for update$',10,1),(1,'^select',20,1);
proxysql> load mysql query rules to runtime;
proxysql> save mysql query rules to disk;
proxysql> select rule_id,active,match_pattern,destination_hostgroup,apply from mysql_query_rules;


十二.ProxySQL测试

##1.读写分离测试
首先用mysql工具连入，测试正常读写：

测试插入：

然后清空stats_mysql_query_digest表（两个proxysql节点都要清空）：

mysql -uadmin -p -h127.0.0.1 -P6032 --prompt='proxysql> '
proxysql> select * from stats_mysql_query_digest_reset;
proxysql> select * from stats_mysql_query_digest;


表stats_mysql_query_digest：SQL的执行次数、时间消耗等

mysql -u'wenjie.wang' -p123456 -h10.10.1.214 -P6033 (再哪台上执行均可，或者用连接工具执行也可以)
mysql> select * from wwj.tb;
mysql> insert into wwj.tb(age) values(612);
mysql> select * from wwj.tb for update;


再回到看stats_mysql_query_digest表中记录

proxysql> select * from stats_mysql_query_digest;


发现是按照配置正常读写分离的
（此时10.10.1.213节点是没有SQL记录的，因为SQL记录不会同步，只在当前的proxysql节点进行记录）
事实上这种简单的读写分离并不是我们想要的，正确的做法应该是在系统运行一段时间后将开销较大的查询分离出来

##2. 负载均衡测试
对于查询，我们有slave1和slave2两个节点可供使用，即vl7bjsqltest02和vl7bjsqltest03
按照之前配置的权重，最后select会以1:3的比例分配到vl7bjsqltest02和vl7bjsqltest03上，如果比例不正确，应该是需要重启一下proxysql生效；

systemctl restrt proxysql.service
for i in {1..12}; do mysql -uproxysql -p123456 -h10.10.1.214 -P6033 -e 'select @@hostname' -s -N; done


选项说明：
（1）-s：以制表符作为分隔符打印结果
（2）-N：结果中不包含列名


可以看到符合1:3的比例。

##3.故障转移测试
单主模型脚本gr_sw_mode_checker.sh，
下载地址https://github.com/ZzzCrazyPig/proxysql_groupreplication_checker
或直接查看
https://github.com/ZzzCrazyPig/proxysql_groupreplication_checker/blob/master/gr_sw_mode_checker.sh
将下载的脚本gr_sw_mode_checker.sh放置至两个proxysql节点的/var/lib/proxysql目录中，并赋予执行权限和修改属主属组：

chmod +x /var/lib/proxysql/gr_sw_mode_checker.sh
chown proxysql.proxysql /var/lib/proxysql/gr_sw_mode_checker.sh


下载addition_to_sys.sql，
下载地址https://github.com/lefred/mysql_gr_routing_check/ 打不开的话CSDN下载，或者查看自己简书
用下载的sql文件在master节点创建表sys.gr_member_routing_candidate_status：

# mysql -uroot -p select * from sys.gr_member_routing_candidate_status;


主节点和从节点显示不同，见截图
主：

从：

配置scheduler：

proxysql> insert into scheduler(active,interval_ms,filename,arg1,arg2,arg3,arg4) values(1,5000,'/var/lib/proxysql/gr_sw_mode_checker.sh',10,20,1,'/var/lib/proxysql/gr_sw_mode_checker.log');
proxysql> load scheduler to runtime;
proxysql> save scheduler to disk;
proxysql> select * from scheduler\G


问题排查可查看日志文件/var/lib/proxysql/gr_sw_mode_checker.log
执行过程中发现scheduler并没有同步到另一个proxysql节点，需手动创建；

查看MGR状态信息：

proxysql> select hostgroup_id,hostname,port,status,weight,comment from mysql_servers;


mysql> select * from performance_schema.replication_group_members;
mysql> select if((select @@server_uuid)=(select variable_value from performance_schema.global_status where variable_name='group_replication_primary_member'),1,0) as is_primary_mode,@@server_id;


主从显示信息不同
主：

从：

模拟master服务故障：

systemctl stop mysqld.service


当master节点意外宕机或下线，在满足大多数节点存活的情况下，group内部发起选举，选出下一个可用的读节点，提升其为master节点。master节点选举根据group内剩余存活节点的UUID按字典升序排列，然后选择排在最前的节点作为新的master节点。
我们在10.10.1.212上执行

select if((select @@server_uuid)=(select variable_value from performance_schema.global_status where variable_name='group_replication_primary_member'),1,0) as is_primary_mode,@@server_id;


此时MGR组中也不再有node1的信息

proxysql> select hostgroup_id,hostname,port,status,weight,comment from mysql_servers;


会发现node1已变成只读组，而node2新增一条记录，允许写入；
我们通过工具连接任意一个proxysql节点进行写入操作

发现对于用户来说无感知

#十三.MGR恢复
原master节点10.10.1.211恢复MySQL服务：

systemctl start mysqld.service


重新加入复制组：

set global group_replication_allow_local_disjoint_gtids_join=ON;
start group_replication;

select * from performance_schema.replication_group_members;

select if((select @@server_uuid)=(select variable_value from performance_schema.global_status where variable_name='group_replication_primary_member'),1,0) as is_primary_mode,@@server_id;

select * from sys.gr_member_routing_candidate_status;


查看wwj.tb表数据，发现宕机后用工具插入的那一条数据已同步
测试负载均衡：

for i in {1..12}; do mysql -uproxysql -p123456 -h10.10.1.214 -P6033 -e 'select @@hostname' -s -N; done


发现了仍为node2和node3以1:3的比例分摊；

proxysql> delete from mysql_servers where hostgroup_id=20 and hostname='10.10.1.212'
proxysql> load mysql servers to runtime;
proxysql> save mysql servers to disk;
proxysql> select hostgroup_id,hostname,port,status,weight,comment from mysql_servers;


重启proxysql后查看分摊

发现连node3节点的ProxySQL，大比例分摊到了node3，发现很少分摊到node1上，待后续研究，不过仍然保证了读写分离:

后续补充，经过大量的查询后，才逐渐稳定：

十四.Keepalived搭建

centOS6.4以后的版本，官方镜像收录了keepalived，所以直接yum安装即可

yum install -y keepalived


node4和node3均用eth0，计划VIP用10.10.1.215
把原来/etc/keepalived/keepalived.conf的内容全部删除，直接用下面的：

! Configuration File for keepalived

#指定proxysql服务检测脚本
vrrp_script chk_proxysql_port {
script "/usr/bin/killall -0 proxysql"
interval 2 #脚本检测频率
weight -5 #脚本执行成功与否，权重怎么计算
fall 2 #如果连续两次检测失败，认为节点服务不可用
rise 1 #如果连续2次检查成功则认为节点正常
}

vrrp_instance VI_1 {
state MASTER
interface eth0 #节点IP的网卡
virtual_router_id 215 #同一个instance相同
priority 100 # 优先级，数值越大，优先级越高
advert_int 1
authentication { #节点间的认证，所有的必须一致
auth_type PASS
auth_pass Hirain_ha_215
}
virtual_ipaddress { #VIP，自定的，和外网的IP要一个网段
10.10.1.215/24
}

track_script { #指定前面脚本的名字
chk_proxysql_port
}

}


两台的区别是权重不同：priority 分别为100、98
然后启动keepalived：

systemctl start keepalived.service


查看ip

用MySQL连接工具连接vip验证正常。

十五.ProxySQL宕机测试

systemctl stop proxysql.service


MySQL连接工具连接vip验证正常，用户无感知
恢复时除了重启proxysql以外还要重启keepalived

故障总结如下
应用无感知的情况：
1.MGR master故障：复制组选出新master，宕机节点踢出复制组，ProxySQL识别到新主，负载均衡比例变化；
2.MGR slave1故障：复制组master不变，宕机节点踢出复制组，ProxySQL负载均衡比例变化；
3.MGR slave2故障：复制组master不变，宕机节点踢出复制组，ProxySQL负载均衡比例变化；
4.ProxySQL专用节点故障：复制组不变，Keepalived的VIP漂移到MGR slave2节点，即ProxySQL的node2上；
5.MGR 两个从节点同时故障：复制组master不变，宕机节点踢出复制组，ProxySQL负载均衡比例变化；
6.MGR slave1和ProxySQL专用节点同时故障：复制组master不变，宕机节点踢出复制组，Keepalived的VIP漂移到MGR slave2节点，即ProxySQL的node2上，ProxySQL负载均衡比例变化；
7.MGR master和ProxySQL专用节点同时故障：复制组选出新master，宕机节点踢出复制组，ProxySQL识别到新主，Keepalived的VIP漂移到MGR slave2节点，即ProxySQL的node2上，负载均衡比例变化；
其余三种情况应用有感知：
master和任一slave同时故障
proxysql两个节点同时故障
任意三个节点同时故障