运维首页 > 系统运维 > 在AWS上使用ProxySQL轻松实现SQL流量镜像

在AWS上使用ProxySQL轻松实现SQL流量镜像

2023年 8月 24日 46.3k 0

本文基于ProxySQL Mirroring功能介绍一种捕获当前数据库集群上的SQL流量，然后将捕获的流量1:1镜像复制转发到另一个数据库集群的解决方案。我们期望这个方案能做到MySQL全部的SQL(DQL、DDL、DML)流量的1:1复制,满足一些特殊场景下的需求。关于ProxySQL大家想必都非常熟悉，我们大多用来做数据库的读写分离、数据库的分库分表、连接池代理(连接复用,查询缓存)等。

但是我们假设例外的几种场景:

  • 数据库多版本测试,比如RDS MySQL 5.7升级到RDS MySQL 8,升级前需要用线上的流量进行真实的全压力的充分对比评估.
  • Aurora或者RDS数据库参数组变更的前后性能测试
  • 线上环境数据库系统无性能损失的SQL审计
  • DML类数据增量操作的抽取,比如把数据变更透明的转发到数据分析系统
  • 迁移前后的数据库资源规划

    • 在这类场景下尽量安全的将线上环境的流量复现到待验证环境是非常有必要的,这时我们可以使用ProxySQL Mirroring来实现,它的原理就是在代理层为我们捕获感兴趣SQL流量并进行镜像转发,一份流量按照以前的逻辑直接发到现有的线上环境数据库目标上(不影响当前的环境),新镜像出的一份转发到待验证环境(但不返回结果给客户端).实际上这个功能在其他领域也有类似的,比如对业务层的HTTP流量我们可以在 Amazon EKS 上利用 Nginx ingress mirror 功能实现流量复制.

    架构设计

    我们以一个典型的Web场景为例,假设我们线上环境的Aurora MySQL 5.7集群面临比较大的读写压力并且MySQL不久将结束支持,我们想尝试换用Aurora MySQL 8来优化现有的架构;线上环境不能轻易变更,在替换前我们需要准确的评估出Aurora的性能优势并规划资源,不过测试环境的各种压测手法很难模拟出真实的场景中的复杂情况!

    我们就可以进行ProxySQL的高可用设计:

  • 首先application的数据库连接先通过AWS NLB进行接入,通过NLB把流量分摊到多个ProxySQL服务上避免单点故障,
  • 接着我们在ProxySQL上进行规则的创建,在现有架构完成了Aurora数据库读写分离的前提下,对全部的SQL进行捕获后1:1转发到例外一个新的待验证Aurora数据库集群上.
  • 通过一段时间的对比观察并不断的调整被测试环境的Aurora的规格/参数,直到最后得到非常有信心的对照结果


    • 注: 绿色实线表示现有的线上环境高可用的读写分离流量流向,黄色虚线表示被1:1镜像的全量测试流量

    架构验证

    在本文中,我使用的了如下的环境:

    软件名称 版本 备注
    Aurora MySQL 2.11.3 (MySQL 5.7) 一个写节点一个读节点
    Aurora MySQL 3.04.0 (MySQL 8.0.28) 一个写节点一个读节点
    ProxySQL 2.5.5 EC2(安装Amazon Linux 2(X86))
    MySQL Client 8.0.34 mysql clent仅用于连接mysql

    我们计划用EC2运行ProxySQL服务将Aurora MySQL（5.7）集群做好读写分离，然后再将Aurora MySQL（5.7）集群的流量钱不转发到Aurora MySQL（8.0）上做1:1模拟。

    示意图如下：

    ProxySQL 安装

    为amazon linux 2安装mysql client

    wget https://repo.mysql.com//mysql80-community-release-el7-9.noarch.rpm
sudo yum install ./mysql80-community-release-el7-9.noarch.rpm -y
sudo yum install mysql -y

    下载ProxySQL安装包并进行安装

    # 下载适配了Amazon Linux 2（X86）的安装包
wget https://github.com/sysown/proxysql/releases/download/v2.5.5/proxysql-2.5.5-1-centos7.x86_64.rpm
# 安装
sudo yum install ./proxysql-2.5.5-1-centos7.x86_64.rpm -y

    如下图

    为amazon linux 2系统设置ProxySQL服务

    # 设置服务开机自启动
sudo systemctl enable proxysql
# 启动服务
sudo systemctl start proxysql
# 检查服务状态
sudo systemctl status proxysql

    如下图

    Mirror 设置

    首先直接登陆Aurora MySQL（5.7）和Aurora MySQL（8.0）集群，并创建如下的用户，后续这个用户会提供给ProxySQL用于来监控Aurora集群的状态。

    # 在Aurora MySQL上执行
# 创建用户（建议后续更改MySQL上的admin密码保证安全）
CREATE USER 'monitor'@'%' IDENTIFIED BY 'monitor';
# 赋予用户权限
GRANT USAGE, REPLICATION CLIENT ON *.* TO 'monitor'@'%';

    然后登陆ProxySQL进行Mirror设置，首先ProxySQL为设置监控和变量信息

    # 登陆ProxySQL（建议后续更改ProxySQL上的admin密码保证安全）
mysql -u admin -padmin -h 127.0.0.1 -P6032 --prompt='Admin> '
# 进行ProxySQL监控相关的设置
## 更新ProxySQL的global变量，设置用户
UPDATE global_variables SET variable_value='monitor' WHERE variable_name='mysql-monitor_username';
## 更新ProxySQL的global变量，设置密码
UPDATE global_variables SET variable_value='monitor' WHERE variable_name='mysql-monitor_password';	
## 更新ProxySQL的global变量，设置ProxySQL检查Aurora集群状态的时间周期
UPDATE global_variables SET variable_value='2000' WHERE variable_name IN ('mysql-monitor_connect_interval','mysql-monitor_ping_interval','mysql-monitor_read_only_interval');
## 更新ProxySQL的global变量，设置mysql sql版本兼容性为5.7.22（建议按照你实际的数据库版本来）
UPDATE global_variables SET variable_value='5.7.22' WHERE variable_name='mysql-server_version';

# 将用户相关的配置生效并持久化
# 将用户信息热加载到RUNTIME
LOAD MYSQL USERS TO RUNTIME;	
# 将用户信息保存到DISK
SAVE MYSQL USERS TO DISK;

# 将变量信息生效并持久化
# 将变量信息热加载到RUNTIME
LOAD MYSQL VARIABLES TO RUNTIME;
# 将变量信息保存到DISK
SAVE MYSQL VARIABLES TO DISK;

    在ProxySQL内为接着配置Aurora集群信息

    # 登陆ProxySQL（建议后续更改ProxySQL上的admin密码保证安全）
mysql -u admin -padmin -h 127.0.0.1 -P6032 --prompt='Admin> '
# 进行正式的Mirror相关的设置
## 设置Aurora MySQL（5.7）的集群终端节点（RW节点）,注意：按照前面的设计，RW节点被设置为group 1
INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (1,'aurora-57-cluster.cluster-cxf9geexdj8g.us-east-1.rds.amazonaws.com',3306);
## 设置Aurora MySQL（5.7）的集群只读节点（RO节点）,注意：按照前面的设计，RW节点被设置为group 2
INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (2,'aurora-57-cluster.cluster-ro-cxf9geexdj8g.us-east-1.rds.amazonaws.com',3306);
## 设置Aurora MySQL（8.0）的集群终端节点（RW节点）,注意：按照前面的设计，RW节点被设置为group 3
INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (3,'aurora-80-cluster.cluster-cxf9geexdj8g.us-east-1.rds.amazonaws.com',3306);

# 将设置生效并持久化
# 将设置热加载到RUNTIME
LOAD MYSQL SERVERS TO RUNTIME;	
# 将设置保存到DISK
SAVE MYSQL SERVERS TO DISK;

    在ProxySQL内为检查Aurora集群配置信息

    # 登陆ProxySQL（建议后续更改ProxySQL上的admin密码保证安全）
mysql -u admin -padmin -h 127.0.0.1 -P6032 --prompt='Admin> '
# 检查Aurora相关的server信息
select hostgroup_id,hostname,port,status from mysql_servers;

    在ProxySQL内为利用ProxySQL的对Aurora的监控，自动的为Aurora分组；如果innodb_read_only=1则认为是只读并放置在group2，否则可以放置在group 1；这样做是为了实现读写分离和Aurora MySQL（5.7）的自动failover。

    # 登陆ProxySQL（建议后续更改ProxySQL上的admin密码保证安全）
mysql -u admin -padmin -h 127.0.0.1 -P6032 --prompt='Admin> '
# 创建mysql_replication_hostgroups中的分类规则
INSERT INTO mysql_replication_hostgroups (writer_hostgroup,reader_hostgroup,comment,check_type) VALUES (1,2,'RDS-MySQL','innodb_read_only');
# 查看分类效果
select hostgroup_id,hostname,port,status from runtime_mysql_servers;

    我们看到正确的读取了多个集群的拓扑信息

    在ProxySQL内为用户设置连接Aurora集群的密码

    # 登陆ProxySQL（建议后续更改ProxySQL上的admin密码保证安全）
mysql -u admin -padmin -h 127.0.0.1 -P6032 --prompt='Admin> '
# 在ProxySQL内为用户设置连接Aurora集群的密码（我这里2个集群的密码一样，建议后续创建独立用户并更换密码来保证安全）
INSERT INTO mysql_users(username,password,default_hostgroup) VALUES ('root','Secure_123_mys',1);

    在ProxySQL内为2个Aurora集群设置转发规则：

  • DDL&DML语句转发到group 1并mirror到group 3
  • DQL语句转发到group 2并mirror到group 3
    • # 登陆ProxySQL（建议后续更改ProxySQL上的admin密码保证安全）
mysql -u admin -padmin -h 127.0.0.1 -P6032 --prompt='Admin> '
# 规则1: DML类（update from select）的SQL，转发到group 1 并mirror到group 3
INSERT INTO mysql_query_rules (rule_id,active,match_pattern,destination_hostgroup,mirror_hostgroup,apply) VALUES (1,1,'^SELECT.*FOR UPDATE$',1,3,1);
# 规则2: DDL类（create）的SQL，转发到group 1 并mirror到group 3
INSERT INTO mysql_query_rules (rule_id,active,match_pattern,destination_hostgroup,mirror_hostgroup,apply) VALUES (2,1,'^CREATE',1,3,1);
# 规则3: DML类（insert）的SQL，转发到group 1 并mirror到group 3
INSERT INTO mysql_query_rules (rule_id,active,match_pattern,destination_hostgroup,mirror_hostgroup,apply) VALUES (3,1,'^INSERT',1,3,1);
# 规则4: DML类（update）的SQL，转发到group 1 并mirror到group 3
INSERT INTO mysql_query_rules (rule_id,active,match_pattern,destination_hostgroup,mirror_hostgroup,apply) VALUES (4,1,'^UPDATE',1,3,1);
# 规则5: DML类（delete）的SQL，转发到group 1 并mirror到group 3
INSERT INTO mysql_query_rules (rule_id,active,match_pattern,destination_hostgroup,mirror_hostgroup,apply) VALUES (5,1,'^DELETE',1,3,1);

# 规则6: DQL类（select）的SQL，转发到group 2 并mirror到group 3
INSERT INTO mysql_query_rules (rule_id,active,match_pattern,destination_hostgroup,mirror_hostgroup,apply) VALUES (6,1,'^SELECT',2,3,1);

# 将规则生效并持久化
# 将规则热加载到RUNTIME
LOAD MYSQL QUERY RULES TO RUNTIME;
# 将规则保存到DISK
SAVE MYSQL QUERY RULES TO DISK;

    在ProxySQL内检查转发规则:

    select rule_id,active,match_digest,match_pattern,destination_hostgroup,mirror_hostgroup,apply from mysql_query_rules;

    在ProxySQL内检查连接日志

    SELECT * FROM monitor.mysql_server_connect_log ORDER BY time_start_us DESC LIMIT 3;

    一切正常

    测试效果

    在linux终端使用ProxySQL连接Aurora集群，ProxySQL的管理端口为6032（前面用到了），本次是使用ProxySQL的代理端口6033。

    # 通过ProxySQL登陆Aurora
mysql -u root -pSecure_123_mys -h 127.0.0.1 -P6033

# 查库
show databases;
# 建库
create database think_db;
# 切换库
use think_db;
# 建表
create table account (
    username varchar(32) PRIMARY KEY,
    age int not null,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=INNODB;
# 插入
insert into account(username,age) values ("thinktik",18);
# 查询
select * from account where username="thinktik"

    DDL&DML测试

    DQL测试

    通过ProxySQL查看执行统计

    # 登陆ProxySQL（建议后续更改ProxySQL上的admin密码保证安全）
mysql -u admin -padmin -h 127.0.0.1 -P6032 --prompt='Admin> '
select hostgroup,count_star,schemaname,digest_text from stats_mysql_query_digest where schemaname='think_db';

    我们可以看到group 3对DDL、DQL、DML都有执行，这说明mirror正确；group 2只执行了DQL，group 1只执行了DDL和DML，这说明读写分离也正确。

    如果我们直接登陆Aurora MySQL（8.0）检查，也会发现DDL&DML被镜像过来了。

    总结

    通过ProxySQL Mirroring我们能够非常方便的在数据库连接这个环节做流量镜像，进行数据库使用过程中的1:1流量模拟，在不影响线上业务正常运行的情况下，近乎透明的安全的完成数据库升级、调优、性能对比、调试、SQL审计等任务，这在以前是很难做到的。希望本文可以对大家有所帮助。

    Reference

    • ProxySQL Mirroring
    • How to use ProxySQL with open source platforms to split SQL reads and writes on Amazon Aurora clusters
    • 开源软件 ProxySQL 与 AWS RDS 不得不说系列 Blog（一）
    • 开源软件 ProxySQL 与 AWS RDS 不得不说系列 Blog（二）
    • ProxySQL-mirroring.pdf
    • ProxySQL setup hostgroup concern
    三掌柜
    系统运维
    0 0

    相关文章

    服务器端口转发，带你了解服务器端口转发
    服务器开放端口，服务器开放端口的步骤
    产品推荐：7月受欢迎AI容器镜像来了，有Qwen系列大模型镜像
    如何使用 WinGet 下载 Microsoft Store 应用
    百度搜索：蓝易云 – 熟悉ubuntu apt-get命令详解
    百度搜索：蓝易云 – 域名解析成功但ping不通解决方案

    发布评论

    三掌柜

    这个人很懒，什么都没有留下～

    文章8286 评论0