流漓 · 客

Appearance

ProxySQL安装与读写分离 ​

ProxySQL 介绍 ​

ProxySQL 如何工作 ​

ProxySQL 和 ShardingJDBC 类似竞品关系，国内使用 ShardingJDBC 更多，国外使用 ProxySQL 更多。 ProxySQL 主要能够提供：读写分离 + 故障转移 ShardingJDBC 主要能够提供：读写分离 + 分库分表 所以很多场景会考虑将 ProxySQL 和 ShardingJDBC + MySQL MGR 一起使用，通过 ProxySQL 提供读写分离 + 故障转移，通过 ShardingJDBC 提供分库分表

当我们搭建好了MGR集群，由于组复制的存在，保证每个节点的数据都是强一致的。不会出现主节点有数据，而从节点没有数据的情况。主节点可读可写，从节点只负责读操作，这是经典的MGR集群方案：

经典的MGR集群方案

不过，试想这样一种情况，当主节点发生宕机，MGR集群虽然虽然可以自动进行故障转移，重新选举新的主节点，但是Java应用不能感知到数据库发生了宕机现象，依旧会把写操作发送给宕机的主节点，此时就会发生错误。

经典的MGR集群方案故障

针对这种情况，ProxySQL就发挥出了作用，通过ProxySQL自动对MGR集群进行健康状态感知。

ProxySQL

使用ProxySQL时，如果主服务器发生了宕机，会将写入请求转移到故障转移到新晋升的主节点上，并且提供了读写分离功能。

ProxySQL 工作原理 ​

ProxySQL对于MGR集群节点，都新增了一个数据库用户，例如：monitor。ProxySQL通过monitor用户定时发送select查询请求，判断当前数据库节点是否可以，如果出现3次不能执行select查询，就认为该节点产生故障，就需要从集群中移除。

ProxySQL 工作原理

不过此时有个问题，就是ProxySQL如何判断哪个节点是主，哪个节点是从呢？ProxySQL要求每个MGR集群中的节点都在各自服务器上创建视图，作用是收集当前节点运行状态，判断当前节点是主是从。

ProxySQL 工作原理

当ProxySQL知道了主从之后，此时Java应用进行SQL操作，就会被ProxySQL进行路由分发了！

ProxySQL 工作原理

ProxySQL 安装 ​

首先，下载ProxySQL安装包，进行安装操作：

bash
# 下载 ProxySQL 安装包
$ wget --no-check-certificate https://manongbiji.oss-cn-beijing.aliyuncs.com/ittailkshow/mgr/download/proxysql-2.2.0-1-centos7.x86_64.rpm

# 安装 ProxySQL
$ yum localinstall -y proxysql-2.2.0-1-centos7.x86_64.rpm

ProxySQL源码安装 参考：ProxySQL源码安装

sysown/proxysql（GitHub）https://github.com/sysown/proxysql/releases

启动 ProxySQL 服务，可以发现启动完毕：

bash
# 启动 ProxySQL
$ service proxysql start
$ systemctl status proxysql

image-20240713160120646

安装MySQL客户端

这里直接将官方的二进制包解压并设置环境变量作为客户端使用

ProxySQL + MGR 读写分离 ​

1. 读写分离配置 ​

通过上述操作，前置准备已完成，此时链接上ProxySQL

bash
$ mysql -u admin -padmin -h 127.0.0.1 -P 6032 --prompt='Admin> '

注意：这里-padmin必须连着，不然会提示你输入密码，其他地方可以隔开

image-20240713125026360

接着，将MGR集群节点配置到ProxySQL中，使用的是MGR节点的内网ip：

sql
# 我的MGR集群的三个节点内网IP分别是：192.168.10.20 | 192.168.10.21 | 192.168.10.22
insert into mysql_servers(hostgroup_id,hostname,port) values (10,'192.168.10.20',3306);
insert into mysql_servers(hostgroup_id,hostname,port) values (10,'192.168.10.21',3306);
insert into mysql_servers(hostgroup_id,hostname,port) values (10,'192.168.10.22',3306);

# 启用上述配置 + 持久化保存
load mysql servers to runtime;
save mysql servers to disk;

image-20240713125552453

接下来这步需要在MGR主节点上进行，配置会通过主节点同步到其他从属节点：

bash
# 使用 sys 数据库
use sys;

# MySQL降低密码强度
set global validate_password.policy=0;
set global validate_password.length=4;

# 创建角色（monitor监听运行状态，proxysql是java应用连接账号）
CREATE USER 'monitor'@'%' IDENTIFIED BY "Liulike#123!";
CREATE USER 'proxysql'@'%' IDENTIFIED BY "Liulike#123!";
GRANT ALL PRIVILEGES ON *.* TO 'monitor'@'%' ;
GRANT ALL PRIVILEGES ON *.* TO 'proxysql'@'%' ;
FLUSH PRIVILEGES;

从这里使用Navicat进入sys数据库，连接到MGR主节点上，创建视图（一段一段执行，用Navicat是因为避免控制台出现ERROR）：

image-20240713130052719

sql
# 创建函数
CREATE FUNCTION my_id() RETURNS TEXT(36) DETERMINISTIC NO SQL RETURN (SELECT @@global.server_uuid as my_id);
sql
# 函数：判断是否是主分区
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 NOT IN ('ONLINE', 'RECOVERING')) >=
	((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) where member_id=my_id());
END
sql
# 创建视图
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,
Count_Transactions_Remote_In_Applier_Queue as transactions_behind, Count_Transactions_in_queue as 'transactions_to_cert'
from performance_schema.replication_group_member_stats where member_id=my_id();

此时，回到ProxySQL服务器控制台上，设置监控账号：

sql
set mysql-monitor_username='monitor';
set mysql-monitor_password='Liulike#123!';
insert into mysql_users(username,password,active,default_hostgroup,transaction_persistent) values('proxysql','Liulike#123!',1,10,1);

mysql-monitor_username、mysql-monitor_password都是在global_variables表内的，可以通过下面的语句查看：

sql
select * from global_variables;

其他账号如果也需要使用，都需要走ProxySQL监控：

sql
insert into mysql_users(username,password,active,default_hostgroup,transaction_persistent) values('root','Liulike#123!',1,10,1);

设置读写组：

sql
# 主负责写、从负责读，当MGR主库切换后，代理自动识别主从。
# ProxySQL代理每一个后端MGR集群时，都必须为这个MGR定义写组10、备写组20、读组30、离线组40，
# 注意：max_transactions_behind 是设置延迟大小，可以给大点,建议自己去开个并行复制。
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) values (10,20,30,40,1,1,0,100);

启用规则：

sql
load mysql servers to runtime;
save mysql servers to disk;
load mysql users to runtime;
save mysql users to disk;
load mysql variables to runtime;
save mysql variables to disk;

状态校验，编号为10是主节点，编号为30是从节点：

sql
select hostgroup_id, hostname, port,status from runtime_mysql_servers;

image-20240713140739629

进行读写分离配置：

sql
# select for update走主节点，其他select走从节点
# 其他 insert update delete 走主节点
insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply) VALUES (1,1,'^SELECT.*FOR UPDATE$',10,1),(2,1,'^SELECT',30,1);
sql
# 配置加载 + 持久化
load mysql query rules to runtime;
save mysql query rules to disk;

2. 读写分离测试 ​

这里注意，进行读写分离实战测试时，要连接ProxySQL 6033端口，6032是Admin，6033才是客户端（Java程序也连6033！）：

sql
# 链接 ProxySQL 6033
$ mysql -uproxysql -pLiulike#123! -h127.0.0.1 -P6033

# 创建一个test数据库，查询一条数据，写一条数据
use test;
select * from test;
insert into test values(20);
select * from test for update;

# 回到6032，查看路由日志
mysql -uadmin -padmin -h127.0.0.1 -P6032
select hostgroup,digest_text from stats_mysql_query_digest order by digest_text desc limit 15;

可以看到，读写分离成功！

image-20240713131332698

3. SpringBoot 整合 ​

这里网上抄的，仅供参考

只需要将SpringBoot的yml配置文件中，连接到ProxySQL就可以了

yaml
spring:
  datasource:
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://192.168.10.23:6033/quick_chat?useUnicode=true&characterEncoding=utf-8&serverTimezone=Asia/Shanghai&useSSL=false&rewriteBatchedStatements=true
    username: proxysql
    password: Liulike#123!

如果运行期间出现以下错误，要么mysql驱动版本号不对，要么就是ProxySQL需要调整了：

proxysql Caused by: java.sql.SQLException: Unknown system variable 'query_cache_size'

如果要调整ProxySQL，请执行如下命令，即可解决上述问题：

sql
# 连接到Proxy 6032
mysql -uadmin -padmin -h127.0.0.1 -P6032

# 解决 Unknown system variable 'query_cache_size' 问题
update global_variables set variable_value='8.0.4 (ProxySQL)' where variable_name='mysql-server_version';
load mysql variables to run;save mysql variables to disk;

keepalived + VIP + ProxySQL 双机热备 ​

keepalived在之前单体架构开发时，是一个用的较为频繁的高可用技术，比如MySQL、Redis、MQ、Tomcat等都会通过keepalived提供的虚拟IP（VIP）机制，实现单节点应用的高可用。Keepalived是一个基于VRRP协议来实现的服务高可用方案，VRRP协议允许一台机器可以拥有一个或者多个VIP。

在高可用ProxySQL中，有一个为主ProxySQL，多个备ProxySQL，整个集群对外提供一个VIP，通过VIP访问主ProxySQL，备用ProxySQL负责监控主节点运行状态，如果发现主节点宕机，则选举其中一个备用ProxySQL接管主节点的工作，VIP绑定到新选举的主ProxySQL节点上，这个流程俗称IP漂移，从而实现了高可用。

按照上文能够搭建出，以下结构，但是这种方式存在单点故障

单读写分离

为避免这种单点故障的情况，使用keepalived + VIP + ProxySQL实现ProxySQL双机热备，结构如下：

双机热备读写分离

安装 Keepalived ​

首先，通过 yum【也可以选择其他方式】 安装 keepalived 程序，两台服务器都需要进行下载：

bash
$ yum install -y keepalived

1. 编写 Shell ​

接着需要编写 ProxySQL 故障检测 Shell 脚本，Keepalived会定时执行ps -C proxysql --no-heading|wc -l命令，如果返回0，代表Nginx挂了，然后尝试重启，如果重启失败，停止keepalived触发故障转移如果返回大于0，代表 Nginx 正常运行：

bash
# 两台服务器都需要创建该脚本（注意切换容器ID）
$ cat > /etc/keepalived/check_proxysql.sh <<-'EOF'
#!/bin/bash
counter=$(ps -C proxysql --no-heading|wc -l)
if [ "${counter}" = "0" ]; then
	systemctl stop keepalived
fi
EOF

然后，两台服务器都需要调整一下脚本权限：

bash
# 两台服务器都需要执行
chmod 755 /etc/keepalived/check_proxysql.sh

2. 调整 KeepAlived 配置 ​

两台服务器需要调整的配置内容有些区别，所以分别在两台服务器上执行下述命令：

服务器1上的配置内容

bash
$ cat > /etc/keepalived/keepalived.conf <<-'EOF'
! Configuration File for keepalived
# 全局配置，路由ID，固定不变
global_defs {
    router_id LVS_DEVEL
}
# 定义ProxySQL状态脚本
vrrp_script chk_proxysql {
    script "/etc/keepalived/check_proxysql.sh" 
    # 间隔时间，单位为秒，默认1秒
    interval 1
    # 权重，当脚本成功或失败对当前节点的优先级是增加还是减少
    weight -5 
}

# VRRP实例
vrrp_instance VI_1 {
    # 主节点
    state MASTER
    # 绑定的网卡,使用ifconfig命令查看获取
    interface ens32
    # 虚拟路由id,保证相同
    virtual_router_id 51
    # 优先级，抢占模式下优先级高的称为主
    priority 101
    # 指定发送VRRP通告的间隔。单位是秒。
    advert_int 2
    # 安全认证用的密码，自定义即可
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    # 对外暴露的VIP地址
    virtual_ipaddress {
        对外暴露的VIP
    }
    # 指定ProxySQL执行状态脚本
    track_script {
       chk_proxysql 
    }
}
EOF

服务器2上的配置内容

bash
$ cat > /etc/keepalived/keepalived.conf <<-'EOF'
! Configuration File for keepalived
global_defs {
    router_id LVS_DEVEL
}

vrrp_script chk_nginx {
    script "/etc/keepalived/check_nginx.sh" 
    interval 1
    weight -5 
}

vrrp_instance VI_1 {
    # 初始角色Backup
    state BACKUP
    interface eth0
    virtual_router_id 51
    # 优先级比master低
    priority 100
    advert_int 2
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        对外暴露的VIP
    }
    track_script {
       chk_nginx 
    }
}
EOF

两台服务器，都启动 Keepalived

# 启动 Keepalived
$ systemctl start keepalived

# 查看日志
$ tail -f /var/log/messages

通过控制台日志可以看到高可用集群部署完毕，此时通过访问对外暴露的VIP就能够连接到ProxySQL，停止掉某台机器上的keepalived就会出现故障转移，出现VIP漂移现象！