规避单点故障，MySQL8。0MGR软负载怎么选？

　　本文转载自dbaplus社群
　　作者介绍
　　毛思平，12年IT工作经验，7年数据库管理维护经验，现就职于中国农业银行软开中心，从事数据库应用研究。
　　一、背景简介
　　近期笔者所在部门计划批量上线一批基于MySQL 8.0社区版的系统，数据库部署架构为MySQL MGR一主两从并由MySQL Router组件实现读写分离，为了规避MySQL Router单点故障，提高系统稳定性架构设计过程中增加了软负载和以及Keepalived，并将软负载及Keepalived部署在管理节点，管理节点主备部署达到高可用目的，整体架构如下图。
　　另外目前市场上负载软件比较多但大部分面向web应用，笔者梳理发现Haproxy和Nginx在数据库方面有一定应用案例，尤其Haproxy在MySQL主从复制方面应用较广。那么这两款软负载是否可以应用在MySQL8.0 MGR上，谁的性能更好呢，该如何选择呢？带着疑问笔者做了一系列性能比较。
　　二、环境配置
　　硬件配置
　　颖
　　测试硬件环境均采用虚拟机，具体配置如下表：
　　序号
　　节点
　　CPU
　　内存
　　磁盘
　　IP
　　管理节点1
　　4G
　　32G
　　10.*.*.216
　　管理节点2
　　4G
　　32G
　　10.*.*.217
　　数据库节点1
　　8G
　　128G
　　10.*.*.210
　　数据库节点2
　　8G
　　128G
　　10.*.*.211
　　5
　　数据库节点3
　　8G
　　128G
　　10.*.*.212
　　6
　　虚拟IP
　　-
　　-
　　-
　　10.*.*.218
　　软件配置
　　颖
　　参与测试软件版本配置如下表：
　　序号
　　节点
　　版本
　　操作系统
　　CentOS 8.0
　　数据库
　　MYSQL 8.0.18
　　keepalived
　　2.0.10
　　haproxy
　　1.8.15
　　5
　　ngnix
　　1.18.0
　　三、软件配置
　　关于MySQL MGR配置网上有很多相关教程此处不赘述，以下是关于软负载及高可用插件关键配置。
　　Keepalived配置
　　颖
　　完成keepalived软件安装后，只需要在/etc/keepalived目录下修改keepalived.conf文件，修改完成后执行systemctl start keepalived即可，需要修改地方如下：
　　vrrp_instance VI_1 {
　　state MASTER #不用修改，本方案采用争抢模式管理节点主备一致
　　interface ens192 #在用网卡名称
　　virtual_router_id 51 #虚拟路由ID取值0-255
　　priority 100 #优先级 本方案采用争抢模式管理节点主备一致
　　advert_int 1 #探活频率 单位秒
　　authentication { #认证机制及密码
　　auth_type PASS
　　auth_pass 11111111 }
　　virtual_ipaddress { #虚拟地址配置
　　10.*.*.218 }
　　track_script { #自定义探活方法
　　chk_alived
　　}
　　vrrp_script chk_haproxy {
　　script ＂/etc/keepalived/checkalived.sh＂ #探活脚本位置
　　interval 3 #重新探活时间间隔
　　weight -5 #失败后权重设置
　　}
　　探活脚本Checkalived.sh配置如下以Nginx为例：
　　#!/bin/bash
　　if [ `ps -C nginx --no-header |wc -l` -eq 0 ];then
　　systemctl stop keepalived #当发现Nginx不存在时停止该节点Keepalived由备节点接管
　　fi
　　以上配置主备管理节点一致即可。
　　Haproxy配置
　　颖
　　完成Haproxy安装后，只需在/etc/haproxy修改haproxy.cfg文件后执行systemctl start haproxy即可。配置文件主要修改以下内容：
　　listen mysqlrw
　　bind :3366 #虚拟IP读写访问端口
　　mode tcp #访问协议
　　balance roundrobin #负载均衡算法
　　#数据库ip 地址及访问断开，300为软负载最大连接数
　　server mysql1 10.*.*.210:6446 check port 6446 maxconn 300
　　server mysql2 10.*.*.211:6446 check port 6446 maxconn 300
　　server mysql3 10.*.*.212:6446 check port 6446 maxconn 300
　　listen mysqlro
　　bind :3367 #虚拟IP读访问端口
　　mode tcp #访问协议
　　balance roundrobin #负载均衡算法
　　server mysql1 10.*.*.210:6447 check port 6447 maxconn 300
　　server mysql2 10.*.*.211:6447 check port 6447 maxconn 300
　　server mysql3 10.*.*.212:6447 check port 6447 maxconn 300
　　listen stats #图形展示界面地址及端口
　　bind 10.230.137.218:1080
　　mode http
　　option httplog
　　maxconn 10
　　stats refresh 30s
　　stats uri /dbs
　　stats realm XingCloud Haproxy
　　stats auth admin:admin
　　stats hide-version
　　stats admin if TRUE
　　Nginx配置
　　颖
　　完成Haproxy安装后,只需在/etc/nginx/conf下修改nginx.conf,在stream方法下添加以下内容即可，添加完成后执行systemctl start nginx即可启动服务，添加如下：
　　stream {-
　　upstream db{
　　server 10.*.*.210:6446;
　　server 10.*.*.211:6446;
　　server 10.*.*.212:6446;
　　}
　　server{
　　listen 3366;
　　proxy_pass db;
　　proxy_connect_timeout 2s;
　　access_log /usr/local/nginx/logs/access3366_log mysql; --读写日志输出路径
　　}
　　upstream dbro{
　　server 10.*.*.210:6447;
　　server 10.*.*.211:6447;
　　server 10.*.*.212:6447;
　　}
　　server{
　　listen 3367;
　　proxy_pass dbro;
　　proxy_connect_timeout 2s;
　　access_log /usr/local/nginx/logs/access3367_log mysql; --读日志输出路径
　　}
　　}
　　四、测试方法
　　测试功能点
　　测试方法
　　判断依据
　　负载软件负载分发是否均衡
　　批量执行数据库查询操作，检测负载软件负载分发情况。
　　各个数据库节点均匀获得分发会话。
　　负载软件是否能识别MySQL Router单点故障
　　通过Kill掉任意数据库节点单个MySQL Router进程，检测负载软件负载分发情况。
　　无MySQL Router故障节点，各数据库匀获得相同分发会话。
　　负载软件负载分发性能比较
　　批量执行数据库查询操作，统计同等会话量，负载软件耗时。
　　同会话数量会话分发耗时短的负载能力更强。
　　负载软件高可用验证
　　通过Kill掉管理节点负载进程模拟负载软件故障，通过虚拟IP登陆数据库验证备管理节点是否正常。
　　虚拟IP登陆数据库正常，表明备节点软负载正常分发会话。
　　五、测试结果
　　负载软件负载分发是否均衡
　　颖
　　通过编写批量执行SQL的形式，访问数据库，并统计两款负载软件各节点分发情况。Haproxy通过软件自带图行界面统计，Nginx由于采用的是免费版通过后台日志统计负载分发情况。
　　批量SQL核心算法如下：
　　echo -e
　　while true
　　do
　　mysql -u$USER -p$PASSWORD -h$ROUTE_HOST -P$RW_PORT --protocol=TCP -e＂${SQL_TEXT1}＂
　　let COUNT+=1
　　if [ $COUNT -ge $SESS_NUM ]
　　then
　　break
　　fi
　　done
　　Haproxy发起64次查询后各个节点会话分发情况，如下图。
　　Nginx发起64次查询后各个节点会话分发情况，如下图。
　　测试结果如下：
　　负载软件是否能识别MySQL Router单点故障
　　颖
　　选择任意数据库节点，Kill -9 杀死MySQL Router进程，同样的方法测试验证负载软件会话分发情况，测试结果如下。
　　负载软件负载分发性能比较
　　颖
　　通过批量执行空查询SQL：select @@hostname,统计相同并发下不同负载软件分发耗时测算负载软件负载分发效率，测试结果如下：
　　负载软件高可用验证
　　颖
　　当在主节点杀死负载程序后，虚拟IP切换到了备节点，通过虚拟IP能正常登陆数据库，以Nginx为例。
　　1）在主管理节点杀死Nginx进程：
　　2）在主节点查看虚拟IP是否存在：
　　3）在备节点查看虚拟IP是否切换过来，判断Keepalived是否主备切换。
　　4）在以虚拟IP登陆数据库验证备节点负载分发是否正常。
　　由上测试可以看出该架构满足软负载高可用需求，即管理节点软负载故障，备节点通过keepalived探测异常后接管升级成主。同样方法测试Haproxy也满足，在此不赘述。
　　六、结论
　　功能方面，Haproxy和Nginx两款软负载软件均能实现多并发读写均衡分发，在MySQL Router出现故障时Haproxy和Nginx均能识别故障并转移连接到正常运行节点。Haproxy和Nginx均能在keepalived下实现高可用。
　　性能方面，在并发能力发面，同等软硬件条件Haproxy与Nginx相差不大，相同并发量两个负载软件分发耗时相近。
　　易用性方面，Haproxy提供图形界面能直观查看各个服务端口负载分配情况，连接时长，失败连接次数，并能直观显示故障节点等。Nginx无图形管理界面，需要单独加载日志监控模块，需要手动配置日志输出，从后台日志查找个节点连接情况比较麻烦。
　　高可用性方面，两款软负载+Keepalived均能规避MySQL Router单点故障，同时主备模式的设计使负载软件自身也具备高可用，避免单点故障对整个集群架构造成影响。