基于 MRG + ProxySQL + Keepalived 的 MySQL 高可用方案
date
Aug 4, 2023
slug
mysql-high-availability
status
Published
tags
MySQL
MGR
ProxySQL
summary
基于 MRG + ProxySQL + Keepalived 的 MySQL 高可用方案
type
Post
Language
目的
- MySQL 集群各节点数据同步
- MySQL 集群拥有故障转移的机制
- MySQL 集群拥有一个固定出口
JDBC连接，拥有读写分离的功能
架构
采用
MGR 进行数据同步；ProxySQL 集群进行 SQL 分发，读写分离；Keepalived 进行真机故障转移。
MGR 数据同步 && 故障转移
MySQL Group Replication（简称MGR）是MySQL官方于2016年12月12日推出的一款高可用与高扩展的解决方案，它提供了高可用、高扩展、高可靠的MySQL集群服务，具有以下特点：
- 强一致性：基于原生复制和
paxos协议，保证数据传输的一致性和原子性
- 高容错性：只要大多数节点没有出现故障，集群就可对外提供服务
- 高扩展性：节点的加入和移除都是自动的，不需要人为过多干预
- 高灵活性：具有单主模式和多主模式，单主模式在主宕机后自动选主，多主模式多节点写入
组复制的使用需要满足如下要求：
InnoDB存储引擎
- 每个表必须有显式主键
- 网络性能要求低延时、高带宽
- server_id 要唯一
- 开启
GTID
- 事务隔离级别建议使用 RC
- 不建议使用外键级联约束
- 组成员最大数目为 9
MySQL 集群测试环境启动
坑一：default-authentication-plugin使用mysql_native_password，如果使用caching_sha2_password会导致 ProxySQL 连不上 MySQL
坑二：如果不在同一台机器上部署机器，不然会在添加子节点到 MGR 集群里的时候报连不上xxx:33061，xxx 为 docker 容器 Id；应该在 docker-compose 里的服务编排里添加hostname指定为本机 IP，command添加-report-host=<本机IP>，network_mode为host
version: '3'
services:
db-1:
image: mysql/mysql-server:8.0.25
command: --default-authentication-plugin=mysql_native_password --binlog_transaction_dependency_tracking=WRITESET --enforce_gtid_consistency=ON --gtid_mode=ON --server_id=10001 --slave_parallel_type=LOGICAL_CLOCK --slave_preserve_commit_order=ON
restart: always
hostname: db-1
ports:
- 33061:3306
environment:
MYSQL_ROOT_PASSWORD: 123
MYSQL_ROOT_HOST: '%'
db-2:
image: mysql/mysql-server:8.0.25
command: --default-authentication-plugin=mysql_native_password --binlog_transaction_dependency_tracking=WRITESET --enforce_gtid_consistency=ON --gtid_mode=ON --server_id=10002 --slave_parallel_type=LOGICAL_CLOCK --slave_preserve_commit_order=ON
restart: always
hostname: db-2
ports:
- 33062:3306
environment:
MYSQL_ROOT_PASSWORD: 123
MYSQL_ROOT_HOST: '%'
db-3:
image: mysql/mysql-server:8.0.25
command: --default-authentication-plugin=mysql_native_password --binlog_transaction_dependency_tracking=WRITESET --enforce_gtid_consistency=ON --gtid_mode=ON --server_id=10003 --slave_parallel_type=LOGICAL_CLOCK --slave_preserve_commit_order=ON
restart: always
hostname: db-3
ports:
- 33063:3306
environment:
MYSQL_ROOT_PASSWORD: 123
MYSQL_ROOT_HOST: '%'sudo docker-compose up -d本文示例 MySQL 示例
通过 MySQL Shell 环境创建 MGR 集群
MySQL Shell 中可以用Python或JavaScript语法来执行这些方法，默认是JavaScript语法
sudo docker exec -it container_id bash进入 MySQL 容器
mysqlsh --uri root:123@127.0.0.1:3306创建名为 Cluster 的集群
dba.createCluster('Cluster')添加节点，定数据同步的方式为
clone，意为全量复制，增量为 incrementaldba.getCluster().addInstance('<user>:<password>@<host>:<port>', {'recoveryMethod': 'clone'})查看集群状态
dba.getCluster().status()重启集群
如果 This function is not available through a session to a standalone instance (metadata exists, instance belongs to that metadata, but GR is not active) 这个问题，重启集群，这个问题一般是重启 MySQL 导致的
dba.rebootClusterFromCompleteOutage()清空集群元数据
如果整个集群重启，使用
dba.rebootClusterFromCompleteOutage() 重启，一直卡在 Cancelling active GR auto-initialization at xxx 不动；可以尝试清空所有节点的集群元数据，再重新创建集群后，子节点加入集群。dba.dropMetadataSchema()MGR 集群其他操作
查看当前集群的全部节点
SELECT * FROM performance_schema.replication_group_members;单主切换多主（默认为单主）
select group_replication_switch_to_multi_primary_mode();多主切换单主
select group_replication_switch_to_single_primary_mode();ProxySQL 中间代理
ProxySQL 是一个高性能的、高可用性 MySQL 中间件，优点如下：- 几乎所有的配置均可在线更改（其配置数据基于SQLite存储），无需重启
ProxySQL
- 详细的状态统计，相当于有了统一的查看 SQL 性能和 SQL 语句统计的入口
- 自动重连和重新执行机制，若一个请求在链接或执行过程中意外中断，
ProxySQL会根据其内部机制重新执行该操作
- query cache 功能：比 MySQL 自带QC更灵活，可多维度控制哪类语句可以缓存
- 支持连接池（connection pool）
- 支持分库、分表
- 支持负载均衡
- 自动下线后端DB，根据延迟超过阀值、ping 延迟超过阀值、网络不通或宕机都会自动下线节点
概念
ProxySQL 具有零停机时间变更功能，它是通过3层配置来实现的，3层配置包括：Runtime、Memory、Disk- Runtime 层表示
ProxySQL工作线程使用的内存数据结构
- Memory 层经由一个 MySQL 兼容接口露出的内存数据库，用户可以使用 MySQL 客户端连接到管理界面，查看、编辑
ProxySQL配置表
- Disk 层是一个存放在磁盘上的
SQLite3数据库，Disk 层可将内存中的配置信息保存到磁盘，以便ProxySQL重新启动后配置还可用
各层之间数据通过
load/save 命令来实现同步# 使修改立即生效
load xxx to runtime;
# 使修改永久保存到磁盘
save xxx to disk;以上 xxx 表示要加载/保存的是哪类配置，目前
ProxySQL 支持以下配置mysql users用户信息
mysql servers服务信息
mysql variables系统变量
mysql query rules服务规则
admin variables管理员变量
Docker 启动 ProxySQL
sudo docker pull proxysql/proxysql- admin 管理接口，端口为
16032，该端口用于查看、配置 ProxySQL
- 接收 SQL 语句的接口，端口为
16033，这个接口类似于 MySQL 的 3306 端口
sudo docker run -p 16032:6032 -p 16033:6033 -p 16070:6070 -d --name=proxysql -v proxysql.cnf:/etc/proxysql.cnf proxysql/proxysql配置文件
proxysql.cnf- 配置远程用户
radmin@radmin
- 配置 MySQL 版本为 8.0.25（解决连接
druid连接池Unknown system variable 'query_cache_size'异常）
- 配置
proxysql_servers集群，只有在master节点执行load mysql servers to runtime;时，其天节点才会同步配置，因为数据差异检查是根据runtime进行检查的，只对memory和disk进行更改，并不触发同步操作。
ProxySQL服务只有在第一次启动时才会去读取proxysql.cnf文件并解析；后面启动不会读取proxysql.cnf文件，如果想要让proxysql.cnf文件里的配置在重启proxysql服务后生效，则需要先删除/var/lib/proxysql/proxysql.db数据库文件，然后再重启proxysql服务（之前的相关服务器配置会被删除，需重新手动配置）
datadir="/var/lib/proxysql"
admin_variables=
{
admin_credentials="admin:admin;radmin:radmin"
mysql_ifaces="0.0.0.0:6032"
cluster_username="radmin"
cluster_password="radmin"
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="192.168.124.92"
port=16032
comment="primary"
},
{
hostname="192.168.124.101"
port=16032
comment="secondary"
}
)
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"
default_schema="information_schema"
stacksize=1048576
server_version="8.0.25"
connect_timeout_server=3000
monitor_username="monitor"
monitor_password="monitor"
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 配置 MGR
以下通过 SQL 的方式配置 ProxySQL
登录 ProxySQL 管理控制台
mysql -u radmin -pradmin -h 192.168.124.92 -P16032添加主机到 ProxySQL
# 查询已有的主机配置
select * from mysql_servers;
# 添加主机
insert into mysql_servers(hostgroup_id,max_connections,comment,hostname,port) values(1000,3000,'node1','192.168.124.92',33061);
insert into mysql_servers(hostgroup_id,max_connections,comment,hostname,port) values(1002,3000,'node2','192.168.124.92',33062);
insert into mysql_servers(hostgroup_id,max_connections,comment,hostname,port) values(1002,3000,'node3','192.168.124.92',33063);
# 加载配置 & 保存配置
load mysql servers to runtime;
save mysql servers to disk;
# 查询当前的主机配置
select * from runtime_mysql_servers;监控MGR节点状态
创建
sys.gr_member_routing_candidate_status 视图
ProxySQL 监控 MGR 状态时是通过视图 sys.gr_member_routing_candidate_status 实现的，所以首先需要在MGR集群创建视图，master 节点执行## MGR 主节点创建系统视图
USE sys;
DELIMITER $$
CREATE FUNCTION IFZERO(a INT, b INT)
RETURNS INT
DETERMINISTIC
RETURN IF(a = 0, b, a)$$
CREATE FUNCTION LOCATE2(needle TEXT(10000), haystack TEXT(10000), offset INT)
RETURNS INT
DETERMINISTIC
RETURN IFZERO(LOCATE(needle, haystack, offset), LENGTH(haystack) + 1)$$
CREATE FUNCTION GTID_NORMALIZE(g TEXT(10000))
RETURNS TEXT(10000)
DETERMINISTIC
RETURN GTID_SUBTRACT(g, '')$$
CREATE FUNCTION GTID_COUNT(gtid_set TEXT(10000))
RETURNS INT
DETERMINISTIC
BEGIN
DECLARE result BIGINT DEFAULT 0;
DECLARE colon_pos INT;
DECLARE next_dash_pos INT;
DECLARE next_colon_pos INT;
DECLARE next_comma_pos INT;
SET gtid_set = GTID_NORMALIZE(gtid_set);
SET colon_pos = LOCATE2(':', gtid_set, 1);
WHILE colon_pos != LENGTH(gtid_set) + 1 DO
SET next_dash_pos = LOCATE2('-', gtid_set, colon_pos + 1);
SET next_colon_pos = LOCATE2(':', gtid_set, colon_pos + 1);
SET next_comma_pos = LOCATE2(',', gtid_set, colon_pos + 1);
IF next_dash_pos < next_colon_pos AND next_dash_pos < next_comma_pos THEN
SET result = result +
SUBSTR(gtid_set, next_dash_pos + 1,
LEAST(next_colon_pos, next_comma_pos) - (next_dash_pos + 1)) -
SUBSTR(gtid_set, colon_pos + 1, next_dash_pos - (colon_pos + 1)) + 1;
ELSE
SET result = result + 1;
END IF;
SET colon_pos = next_colon_pos;
END WHILE;
RETURN result;
END$$
CREATE FUNCTION gr_applier_queue_length()
RETURNS INT
DETERMINISTIC
BEGIN
RETURN (SELECT sys.gtid_count( GTID_SUBTRACT( (SELECT
Received_transaction_set FROM performance_schema.replication_connection_status
WHERE Channel_name = 'group_replication_applier' ), (SELECT
@@global.GTID_EXECUTED) )));
END$$
CREATE FUNCTION gr_member_in_primary_partition()
RETURNS VARCHAR(3)
DETERMINISTIC
BEGIN
RETURN (SELECT IF( MEMBER_STATE='ONLINE' AND ((SELECT COUNT(*) FROM
performance_schema.replication_group_members WHERE MEMBER_STATE != 'ONLINE') >=
((SELECT COUNT(*) FROM performance_schema.replication_group_members)/2) = 0),
'YES', 'NO' ) FROM performance_schema.replication_group_members JOIN
performance_schema.replication_group_member_stats USING(member_id));
END$$
CREATE VIEW gr_member_routing_candidate_status AS SELECT
sys.gr_member_in_primary_partition() as viable_candidate,
IF( (SELECT (SELECT GROUP_CONCAT(variable_value) FROM
performance_schema.global_variables WHERE variable_name IN ('read_only',
'super_read_only')) != 'OFF,OFF'), 'YES', 'NO') as read_only,
sys.gr_applier_queue_length() as transactions_behind, Count_Transactions_in_queue as 'transactions_to_cert' from performance_schema.replication_group_member_stats;$$
DELIMITER;MGR 创建监控用户
一定要给
proxysql_monitor 赋予足够多的权限，不然 ProxySQL 无法监控 MGR 集群，导致使用时Max connect timeout reached while reaching hostgroup 1000 after 10000ms 异常## 在MGR主节点执行
# 创建 proxysql_monitor 用户
create user proxysql_monitor@'%' identified by '123456';
# 赋予全部权限
grant all on *.* to proxysql_monitor@'%';ProxySQL 监控配置
## 设置环境变量
set mysql-monitor_username='proxysql_monitor';
set mysql-monitor_password='123456';
# 查看监控配置
select * from global_variables where variable_name like 'mysql-monitor%';
# 检查ping
SELECT * FROM monitor.mysql_server_ping_log ORDER BY time_start_us DESC LIMIT 10;
# 连接日志
SELECT * FROM monitor.mysql_server_connect_log ORDER BY time_start_us DESC LIMIT 6;
# 修改监控时间间隔（可选）
UPDATE global_variables SET variable_value='2000' WHERE variable_name IN ('mysql-monitor_connect_interval','mysql-monitor_ping_interval','mysql-monitor_read_only_interval');
# 加载配置 && 持久化
load mysql variables to runtime;
save mysql variables to disk;录入 MGR hostgroups 信息
MRG 组信息，分配各读写配置对应的组，读写配置释义如下
以下 SQL 配置，添加编号为 1000 的组为只写组，10002 的组为只读组。
insert into mysql_group_replication_hostgroups(writer_hostgroup,backup_writer_hostgroup,reader_hostgroup,offline_hostgroup,active,max_writers,writer_is_also_reader,max_transactions_behind,comment)
values(1000,1001,1002,1003,1,1,0,10,'mgr01');
# 加载配置 && 持久化
load mysql servers to runtime;
save mysql servers to disk;
# 查看结果
select * from mysql_group_replication_hostgroups;配置 mysql_users
添加 MGR 集群用户，ProxySQL 会通过这个用户间接操作 MGR 集群
## MGR master 节点添加
# 添加 mgr_root 用户
create user mgr_root@'%' identified by '123';
# 赋予全部权限
grant all on *.* to mgr_root@'%';ProxySQL 配置
insert into mysql_users(username,password,default_hostgroup,transaction_persistent) values('mgr_root','123',1000,1);
# 加载配置 && 持久化
load mysql users to runtime;
save mysql users to disk;查看间接操作 MRG 集群 SQL 日志
select hostgroup,digest_text from stats_mysql_query_digest ORDER BY sum_time DESC limit 10; 通过 rules 配置读写分离路由
mysql_query_rules的规则较mysql_group_replication_hostgroups的优先级高
mysql_query_rules 表中有多个字段，它们能够方便的控制每个查询请求如何通过 ProxySQL 进行路由。较重要的用
username ，schemaname ，match_digest 等。其中 match_digest 是对 digest 做正则匹配，但注意 match_pattern 字段中给的规则不是 hash 值，而是 SQL 语句的文本匹配规则# 将写操作分配给编号为 1000 的组
INSERT INTO mysql_query_rules (rule_id,active,username,match_digest,destination_hostgroup,apply) VALUES (100,1,'mgr_root','^SELECT.*FOR UPDATE$',1000,1);
# 将读操作分配给编号为 1002 的组
INSERT INTO mysql_query_rules (rule_id,active,username,match_digest,destination_hostgroup,apply) VALUES (101,1,'mgr_root','^SELECT',1002,1);
# 加载配置 && 持久化
load mysql query rules to runtime;
save mysql query rules to disk;读写分离测试
# 读测试
mysql -umgr_root -p123 -P16033 -h192.168.124.92 test -e "select @@server_id;"
# 写测试
mysql -umgr_root -p123 -P16033 -h192.168.124.92 test -e "start transaction;select @@server_id;commit;"Keepalived 真机故障转移
Keepalived 是运行在 lvs 之上，是一个用于做双机热备（HA）的软件，它的主要功能是实现真实机的故障隔离及负载均衡器间的失败切换，提高系统的可用性；因为在本文中 ProxySQL 作为 MGR 的出口，如果 ProxySQL 出现问题，会导致所有高可用集群无法使用；所以本文在 ProxySQL 集群上层加一层 Keepalived 作真机故障转移。运行原理
keepalived 通过选举（看服务器设置的权重）挑选出一台热备服务器做 MASTER 机器，MASTER 机器会被分配到一个指定的虚拟 IP，外部程序可通过该 IP 访问这台服务器，如果这台服务器出现故障（断网，重启，或者本机器上的 keepalived crash 等），keepalived 会从其他的备份机器上重选（还是看服务器设置的权重）一台机器做 MASTER 并分配同样的虚拟 IP，充当前一台主服务器的角色。选举策略
选举策略是根据
VRRP 协议，完全按照权重大小，权重最大（0～255）的是 MASTER 机器，下面几种情况会触发选举keepalived启动的时候
MASTER服务器出现故障（断网，重启，或者本机器上的keepalived crash等，而本机器上其他应用程序crash不算）
- 有新的备份服务器加入且权重最大
配置文件 keepalived.conf
以下为 Keepalived 配置，需要注意以下几点
script_user root不加会报错WARNING - default user ‘keepalived_script‘ for script execution does not exist - please
interface为网卡接口，使用ip a可查看
priority为权重，一般MASTER节点权重比BACKUP高
nopreempt设置为不抢占，默认是抢占的
virtual_ipaddress为虚拟 IP 地址，必须符合当前DNS策
global_defs {
script_user root
router_id LVS_DEVEL
}
vrrp_script chk {
script "/etc/keepalived/check_proxysql.sh"
interval 2
weight -20
}
vrrp_instance VI_1 {
state MASTER
interface wlp0s20f3
virtual_router_id 1
priority 100
advert_int 1
nopreempt
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
192.168.124.200
}
track_script {
chk
}
}检测 ProxySQL 是否可用脚本 check_proxysql.sh
当检测到有程序监听
16032 和 16033 ，表示 ProxySQL 服务可用；否则不可用，关闭当前服务器 Keepalived 进程，Keepalived 会故障转移切换到其他节点，实现高可用。注意：必须在宿主机设置文件权限为 777，不然会报 WARNING - script '/etc/keepalived/check_proxysql.sh' is not executable for uid:gid 0:0 - disabling. 导致脚本无法执行sudo chmod 777 check_proxysql.sh#!/bin/bash
result=$(netstat -na | grep -e '0.0.0.0:16032' -e '0.0.0.0:16033')
if [ -z "$result" ]; then
echo "proxysql 服务不可用" >> /logs/check_proxysql.log
killall keepalived
else
echo "proxysq 服务可用" >> /logs/check_proxysql.logDocker 启动 Keepalived
version: '3'
services:
keepalived:
image: osixia/keepalived:latest
container_name: keepalived
volumes:
- ./keepalived.conf:/container/service/keepalived/assets/keepalived.conf
- ./check_proxysql.sh:/etc/keepalived/check_proxysql.sh
- ./logs:/logs
privileged: true
restart: on-failure:3
command: --copy-service
cap_drop:
- NET_ADMIN
network_mode: "host"sudo docker-compose up -d故障转移测试
ServerA（MASTER）、ServerB（BACKUP） 分别启动
ProxySQL 和 Keepalived- 不做任何操作，查看 ServerA，ServerB 网卡信息，只有 ServerA 的
wlp0s20f3网卡绑定了192.168.124.200虚拟 IP
# ServerA
ip addr | grep 192.168.124.200
# inet 192.168.124.200/32 scope global wlp0s20f3
# ServerB
ip addr | grep 192.168.124.200- 关闭 ServerA 的 ProxySQL 服务，查看 ServerA，ServerB 网卡信息，ServerA 未查询到为
192.168.124.200的 IP，ServerB 的wlp0s20f3网卡绑定了192.168.124.200虚拟 IP，实现了真机故障转移
# ServerA
ip addr | grep 192.168.124.200
# ServerB
ip addr | grep 192.168.124.200
# inet 192.168.124.200/32 scope global wlp0s20f3
