Amazon RDS ProxySQL 探索(一)

:::info
💡 在日常开发中，开发者们会涉及到数据库的连接，在使用Amazon RDS数据库时，若使用集群模式或者多数据库时，会出现一写多读多个Endpoint，在实际开发中，开发者们配置数据库连接通常希望走一个endpoint，让其自动分离读写

:::

Amazon RDS & ProxySQL架构图

本文架构图，作为入门演示环境，仅采用单个ProxySQL以及Amazon Aurora (MySQL Compatible) 5.7.44集群(1写1读)

AWS RDS MySQL的创建

Step1.创建MySQL数据库[读写]

需要创建至少2个节点的数据库集群，这样会产生读写节点

ProxySQL设置

Step1.安装ProxySQL

以Debian12为例，安装ProxySQL

ProxySQL下载地址

https://proxysql.com/documentation/installing-proxysql/

Debian 12安装ProxySQL

wget https://github.com/sysown/proxysql/releases/download/v2.7.3/proxysql_2.7.3-debian12_amd64.deb
dpkg -i proxysql_2.7.3-debian12_amd64.deb

开机并启动ProxySQL

systemctl enable proxysql
# 验证状态
systemctl status proxysql

Step2.RDS MySQL创建monitor用户

CREATE USER 'monitor'@'%' IDENTIFIED BY 'monitor';
GRANT USAGE, REPLICATION CLIENT ON *.* TO 'monitor'@'%';

Step3.ProxySQL添加用户，设定规则

INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (10,'write_node',3306);
INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (20,'read_node',3306);insert into mysql_users (username,password,default_hostgroup) values ('root','123456',20);insert into mysql_query_rules(rule_id,active,match_pattern,destination_hostgroup,apply) values (1,1,'^select.*for update$',10,1);insert into mysql_query_rules(rule_id,active,match_pattern,destination_hostgroup,apply) values (2,1,'^select',20,1);load mysql query rules to runtime;
save mysql query rules to disk;
load mysql variables to runtime;
save mysql variables to disk;
LOAD MYSQL USERS TO RUNTIME;	
SAVE MYSQL USERS TO DISK;