mysqlproxy与rds_开源软件ProxySQL与AWSRDS不得不说系列Blog（一）：ProxyonAWS测试架构和ProxySQL基本配置...

ProxySQL概述

ProxySQL 是一种基于 GNU 通用公共许可证 (GPL) &＃xff0c;并位于 MySQL 数据库与数据库客户端之间的数据库访问代理中间件。

ProxySQL的性能非常好&＃xff0c;功能也很多&＃xff0c;几乎能够满足中间件所需的绝大多数的功能&＃xff0c;主要包括&＃xff1a;

跨多个数据库的智能负载均衡&＃xff1b;

连接池特性&＃xff0c;在高并发连接下提高数据库访问性能&＃xff1b;

可以对查询语句进行mirror&＃xff0c;实现日志审计功能&＃xff1b;

可以基于用户、基于 Schema 和基于 SQL 语句的规则对 SQL 语句进行路由转发&＃xff0c;实现读/写分离和简单的数据库分片功能&＃xff1b;

Firewall白名单功能可以实现数据库防火墙&＃xff0c;用白名单规则定义允许执行的查询语句&＃xff1b;

本文是系列blog的首篇&＃xff0c;主要介绍ProxySQL on AWS的整体架构和测试环境的基本配置&＃xff0c;各个功能特性将在后续blog中陆续展开。

ProxySQL on AWS架构图

以下为本文的部署架构图&＃xff1a;

因为是测试环境&＃xff0c;所以只配置了一个ProxySQL实例&＃xff0c;应用的访问也采用在ProxySQL上安装MySQL客户端进行模拟操作。如果部署生产环境&＃xff0c;需要加上ProxySQL的cluster配置&＃xff0c;然后配合ELB负载均衡服务器&＃xff0c;对应用提供高可用和负载均衡。

RDS MySQL环境是标准的三个实例部署&＃xff1a;一个Master实例&＃xff0c;两个Replica只读副本。

先决条件

根据以上的架构图&＃xff0c;搭建的环境需要以下&＃xff1a;

创建完成后&＃xff0c;记录下实例的公网IP&＃xff0c;并保存好密钥文件。

在EC2实例所在的VPC&＃xff0c;创建一个AWS RDS MySQL的Master实例和两个replica实例&＃xff0c;版本为7.22。对应的安全组需要配置ProxySQL实例所在网段&＃xff0c;允许3306端口访问策略。具体操作步骤可以参考&＃xff1a;https://docs.amazonaws.cn/AmazonRDS/latest/UserGuide/CHAP_GettingStarted.CreatingConnecting.MySQL.html

其中mytest57为Master实例&＃xff0c;可以进行读写操作&＃xff1b;mytest-r1和mytest-r2是replica&＃xff0c;作为只读副本&＃xff0c;可以进行读操作。

记录三个数据库实例的访问地址&＃xff1a;

mytest57.cs8yfqtavsti.rds.cn-northwest-1.amazonaws.com.cn

mytest57-r1.cs8yfqtavsti.rds.cn-northwest-1.amazonaws.com.cn

mytest57-r2.cs8yfqtavsti.rds.cn-northwest-1.amazonaws.com.cn

记录下创建数据库实例时的用户名和密码&＃xff1a;user01/Welcome1。

记录下为user01创建的数据库schema&＃xff1a;test01。

过程列表

主要的步骤分为以下两个部分&＃xff1a;

在EC2实例下载并安装ProxySQL 2.0.10版本

ProxySQL的基本配置

下载安装ProxySQL 2.0.10

EC2实例的操作系统为Amazon Linux 2&＃xff0c;步骤如下&＃xff1a;

用保存的密钥文件登陆实例

$ssh -i mykey.pem ec2-user&＃64;instance-ip

添加repo配置文件

$sudo vi /etc/yum.repos.d/ProxySQL.repo

[ProxySQL_repo]

name&＃61; ProxySQL YUM repository

baseurl&＃61;https://repo.ProxySQL.com/ProxySQL/ProxySQL-2.0.x/centos/latest

gpgcheck&＃61;1

gpgkey&＃61;https://repo.ProxySQL.com/ProxySQL/repo_pub_key

安装和启动ProxySQL服务

$sudo yum install ProxySQL

$sudo systemctl start ProxySQL

$sudo systemctl enable ProxySQL

查看运行状态

$sudo systemctl status ProxySQL

正常启动后&＃xff0c;服务将处于running状态。

ProxySQL.service - High Performance Advanced Proxy for MySQL

Loaded: loaded (/etc/systemd/system/ProxySQL.service; enabled; vendor preset: disabled)

Active: active (running) since 五 2020-04-10 03:04:38 UTC; 3h 54min ago

Process: 1499 ExecStart&＃61;/usr/bin/ProxySQL -c /etc/ProxySQL.cnf (code&＃61;exited, status&＃61;0/SUCCESS)

Main PID: 1501 (ProxySQL)

CGroup: /system.slice/ProxySQL.service

├─1501 /usr/bin/ProxySQL -c /etc/ProxySQL.cnf

└─1502 /usr/bin/ProxySQL -c /etc/ProxySQL.cnf

4月 10 03:04:38 ip-172-31-32-82.cn-northwest-1.compute.internal systemd[1]: Starting High Performance Advanced Proxy for MySQL...

4月 10 03:04:38 ip-172-31-32-82.cn-northwest-1.compute.internal ProxySQL[1499]: 2020-04-10 03:04:38 [INFO] Using config file /etc/ProxySQL.cnf

4月 10 03:04:38 ip-172-31-32-82.cn-northwest-1.compute.internal ProxySQL[1499]: 2020-04-10 03:04:38 [INFO] Using OpenSSL version: OpenSSL 1.1.0h 27 Mar 2018

4月 10 03:04:38 ip-172-31-32-82.cn-northwest-1.compute.internal ProxySQL[1499]: 2020-04-10 03:04:38 [INFO] SSL keys/certificates found in datadir (/var/lib/ProxySQL): loading them.

4月 10 03:04:38 ip-172-31-32-82.cn-northwest-1.compute.internal systemd[1]: Started High Performance Advanced Proxy for MySQL

安装MySQL

由于ProxySQL要用mysql客户端进行管理&＃xff0c;同时后端保护的数据库也需要进行一些测试的操作&＃xff0c;所以在EC2实例上安装mysql。

$sudo yum install MySQL

登陆ProxySQL管理界面

管理端口为6032&＃xff0c;数据库连接的端口为6033。

使用默认管理员和密码都为admin登陆。

$mysql -u admin -padmin -h 127.0.0.1 -P6032 --prompt&＃61;&＃39;Admin> &＃39;

Welcome to the MariaDB monitor. Commands end with ; or \g.

Your MySQL connection id is 33

Server version: 5.7.22 (ProxySQL Admin Module)

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type &＃39;help;&＃39; or &＃39;\h&＃39; for help. Type &＃39;\c&＃39; to clear the current input statement.

Admin> show databases;

&＃43;-----&＃43;---------------&＃43;-------------------------------------&＃43;

| seq | name | file |

&＃43;-----&＃43;---------------&＃43;-------------------------------------&＃43;

| 0 | main | |

| 2 | disk | /var/lib/ProxySQL/ProxySQL.db |

| 3 | stats | |

| 4 | monitor | |

| 5 | stats_history | /var/lib/ProxySQL/ProxySQL_stats.db |

&＃43;-----&＃43;---------------&＃43;-------------------------------------&＃43;

5 rows in set (0.00 sec)

可以看到ProxySQL的默认5个数据库。

用MySQL客户端直接访问RDS MySQL实例

为确认实例可以正常访问RDS MySQL实例&＃xff0c;使用记录用户名密码和RDS Master实例的访问地址&＃xff0c;登陆Master实例进行查看

$mysql -u user01 -pWelcome1 -h mytest57.cs8yfqtavsti.rds.cn-northwest-1.amazonaws.com.cn --prompt&＃61;&＃39;RDS-Direct> &＃39;

Welcome to the MariaDB monitor. Commands end with ; or \g.

Your MySQL connection id is 8418

Server version: 5.7.22-log Source distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type &＃39;help;&＃39; or &＃39;\h&＃39; for help. Type &＃39;\c&＃39; to clear the current input statement.

RDS-Direct> show databases;

&＃43;--------------------&＃43;

| Database |

&＃43;--------------------&＃43;

| information_schema |

| innodb |

| mysql |

| performance_schema |

| sys |

| test01 |

&＃43;--------------------&＃43;

6 rows in set (0.00 sec)

创建ProxySQL用于监控后端数据库的monitor用户

RDS-Direct>CREATE USER &＃39;monitor&＃39;&＃64;&＃39;%&＃39; IDENTIFIED by &＃39;monitor&＃39;;

RDS-Direct>GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, PROCESS, REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER ON *.* TO &＃39;monitor&＃39;&＃64;&＃39;%&＃39; WITH GRANT OPTION

RDS-Direct>FLUSH PRIVILEGES;

可以用monitor用户进行测试&＃xff0c;登陆RDS实例。

$mysql -u monitor -pmonitor -h mytest57.cs8yfqtavsti.rds.cn-northwest-1.amazonaws.com.cn --prompt&＃61;&＃39;RDS-Direct> &＃39;

RDS-Direct>

注意&＃xff1a;之后的RDS MySQL数据库操作都将通过访问ProxySQL的6033端口进行操作。

Admin> 的提示符表示在ProxySQL的管理界面进行操作

登陆命令为&＃xff1a;$mysql -u admin -padmin -h 127.0.0.1 -P6032 --prompt&＃61;&＃39;Admin> &＃39;

RDS-Direct>的提示符表示直接访问RDS MySQL数据库进行操作

登陆命令为&＃xff1a;$mysql -u user01 -pWelcome1 -h mytest57.cs8yfqtavsti.rds.cn-northwest-1.amazonaws.com.cn --prompt&＃61;&＃39;RDS-Direct> &＃39;

RDS>的提示符表示通过ProxySQL代理访问RDS MySQL数据库进行操作

登陆命令为&＃xff1a;$mysql -u user01 -pWelcome1 -h 127.0.0.1 -P6033 --prompt&＃61;&＃39;RDS> &＃39;

ProxySQL的基本配置

为使用ProxySQL可以作为代理客户端连接&＃xff0c;需要进行基本的配置&＃xff0c;主要是加入后端的数据库服务器地址&＃xff0c;配置主机组、数据库用户等&＃xff0c;保证可以接受客户端连接&＃xff0c;通过ProxSQL正常访问数据库。

在ProxySQL配置加入RDS数据库三个实例的访问地址&＃xff0c;作为后端的数据库服务器

mysql_servers 表中包括将由 ProxySQL 代理的所有后端数据库服务器&＃xff1b;mysql_replication_hostgroups 表包含自己识别和划分只读终端节点的配置。

$mysql -u admin -padmin -h 127.0.0.1 -P6032 --prompt&＃61;&＃39;Admin> &＃39;

Admin> delete from mysql_servers where hostgroup_id in (10,20);

Admin> delete from mysql_replication_hostgroups where writer_hostgroup&＃61;10;

Admin> INSERT INTO mysql_servers (hostname,hostgroup_id,port,weight,max_connections) VALUES (&＃39;mytest57.cs8yfqtavsti.rds.cn-northwest-1.amazonaws.com.cn&＃39;,10,3306,1000,2000);

Admin> INSERT INTO mysql_servers (hostname,hostgroup_id,port,weight,max_connections) VALUES (&＃39;mytest57-r1.cs8yfqtavsti.rds.cn-northwest-1.amazonaws.com.cn&＃39;,20,3306,1000,2000);

Admin> INSERT INTO mysql_servers (hostname,hostgroup_id,port,weight,max_connections) VALUES (&＃39;mytest57-r2.cs8yfqtavsti.rds.cn-northwest-1.amazonaws.com.cn&＃39;,20,3306,1000,2000);

Admin> INSERT INTO mysql_replication_hostgroups (writer_hostgroup,reader_hostgroup,comment,check_type) VALUES (10,20,&＃39;RDS-MySQL&＃39;,&＃39;read_only&＃39;);

Admin> LOAD MYSQL SERVERS TO RUNTIME;

Admin> SAVE MYSQL SERVERS TO DISK;

注&＃xff1a;以上mysql_replication_hostgroups 的配置&＃xff0c;是对于RDS MySQL的&＃xff0c;如果后端数据库是Amazon Aurora &＃xff0c;则check_typ字段需要改为“innodb_read_only”来进行配置。

配置ProxySQL的后端用户和查询规则

在ProxySQL 配置中定义后端用户user01&＃xff0c;其中test01是为user01创建的默认schema。

Admin> delete from mysql_users where username&＃61;&＃39;user01&＃39;;

Admin> insert into mysql_users (username,password,active,default_hostgroup,default_schema,transaction_persistent) values (&＃39;user01&＃39;,&＃39;Welcome1&＃39;,1,10,&＃39;test01&＃39;,1);

Admin> LOAD MYSQL USERS TO RUNTIME;

Admin> SAVE MYSQL USERS TO DISK;

以下的查询规则配置完成后&＃xff0c;可以实现简单的读写分离。

Admin> delete from mysql_query_rules where rule_id in (50,51);

Admin> INSERT INTO mysql_query_rules (rule_id,active,match_digest,destination_hostgroup,apply) VALUES (50,1,&＃39;^SELECT.*FOR UPDATE$&＃39;,10,1), (51,1,&＃39;^SELECT&＃39;,20,1);

Admin> LOAD MYSQL QUERY RULES TO RUNTIME;

Admin> SAVE MYSQL QUERY RULES TO DISK;

配置后端的监控用户monitor&＃xff0c;配置监控间隔和后端数据库版本

Admin> UPDATE global_variables SET variable_value&＃61;&＃39;monitor&＃39; WHERE variable_name&＃61;&＃39;mysql-monitor_username&＃39;;

Admin> UPDATE global_variables SET variable_value&＃61;&＃39;monitor&＃39; WHERE variable_name&＃61;&＃39;mysql-monitor_password&＃39;;

Admin> UPDATE global_variables SET variable_value&＃61;&＃39;2000&＃39; WHERE variable_name IN (&＃39;mysql-monitor_connect_interval&＃39;,&＃39;mysql-monitor_ping_interval&＃39;,&＃39;mysql-monitor_read_only_interval&＃39;);

Admin> UPDATE global_variables SET variable_value&＃61;&＃39;5.7.22&＃39; WHERE variable_name&＃61;&＃39;mysql-server_version&＃39;;

Admin> LOAD MYSQL VARIABLES TO RUNTIME;

Admin> SAVE MYSQL VARIABLES TO DISK;

查看ProxySQL监控状态&＃xff0c;确认后端数据库服务器是否配置成功

Admin> SELECT * FROM monitor.mysql_server_connect_log ORDER BY time_start_us DESC LIMIT 10;

Admin> SELECT * FROM monitor.mysql_server_ping_log ORDER BY time_start_us DESC LIMIT 10;

可以看到成功的监控到后端服务器的连接和ping的时间。如果配置有问题&＃xff0c;会出现timeout的信息。

使用MySQL客户端&＃xff0c;通过ProxySQL代理访问后端RDS数据库

$mysql -u user01 -pWelcome1 -h 127.0.0.1 -P6033 -e "SELECT 1"

&＃43;---&＃43;

| 1 |

&＃43;---&＃43;

| 1 |

&＃43;---&＃43;

$mysql -u user01 -pWelcome1 -h 127.0.0.1 -P6033 -e "SELECT &＃64;&＃64;port"

&＃43;--------&＃43;

| &＃64;&＃64;port |

&＃43;--------&＃43;

| 3306 |

&＃43;--------&＃43;

通过显示的结果&＃xff0c;可以看的MySQL客户端已经可以正常的通过ProxySQL代理&＃xff0c;对后端RDS MySQL数据库进行各类查询操作。

小结

至此&＃xff0c;ProxySQL on AWS的测试环境已经搭建好&＃xff0c;可以开始进行各项功能测试了。

本文是对RDS MySQL数据库环境进行配置和测试&＃xff0c;如果后端数据库是用AWS Aurora或在EC2自建MySQL&＃xff0c;配置稍微有所不同&＃xff0c;具体可参见ProxySQL文档&＃xff1a;https://github.com/sysown/proxysql/wiki。

ProxySQL默认使用4个thread接受客户端的连接&＃xff0c;考虑到资源使用情况&＃xff0c;对单个节点的实例大小和配置也要根据实际情况进行调整。

如果将ProxySQL部署在生产环境&＃xff0c;可以用ProxySQL的cluster功能&＃xff0c;多个ProxySQL节点同时工作&＃xff0c;配合ELB负载均衡服务&＃xff0c;保证业务的高可用。或者也可以考虑使用AWS的自动扩展组功能&＃xff0c;对ProxySQL节点配置ASG策略&＃xff0c;保证多个节点可以同时工作。

本篇作者

AWS 解决方案架构师。负责基于 AWS 云计算解决方案架构的咨询和设计&＃xff0c;同时致力于 AWS 云服务在国内半导体行业的应用和推广。在加入 AWS 前&＃xff0c;拥有超过18年的IT项目经验&＃xff0c;曾就职于Oracle&＃xff0c;主要服务于大中型企事业单位客户。