在业务应用不方便进行读写分离调用的时候，可以考虑使用ProxySQL实现全局的透明读写分离。本文描述如何配置一个单机非集群版本的ProxySQL的MySQL 代理。后端数据库为AWS Aurora MySQL数据库。
一、安装和常用命令
1、安装和部署
首先准备一个Amazon Linux 2系统，生产环境上规格可以选择 c5.large 等基础规格。测试环境可以使用 t3a.medium 等规格。
以root身份执行如下命令：
yum update -y
reboot执行reboot的原因是确保内核等均为最新。继续执行以下命令。
yum install perl-DBD-mysql gnutls
wget https://s3.cn-north-1.amazonaws.com.cn/myworkshop-lxy/proxysql/proxysql2-2.0.12-1.1.el7.x86_64.rpm
rpm -ivh proxysql2-2.0.12-1.1.el7.x86_64.rpm
service proxysql start安装完成。
2、测试管理端口连接
执行如下命令：
mysql -u admin -padmin -h 127.0.0.1 -P6032 --prompt='Admin> '如果可以返回以下信息，表示安装完成。
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.5.30 (ProxySQL Admin Module)
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
Admin>以上看到的版本5.5.30并不是后台真实的MySQL实例的版本，这只是ProxySQL本身模拟的版本，因此是正常的。数据库解析SQL主要是看后端的MySQL版本是5.6、5.7还是8.0，与ProxySQL的显示的没有直接关系。
3、查询当前配置
查询当前server、查询转发规则、查询当前用户：
SELECT * FROM mysql_servers;
SELECT * from mysql_query_rules;
SELECT * FROM mysql_users;以上查询结果应该均为空，表示是一个全新装好的服务器。
二、配置ProxySQL
1、设置默认管理员账号和对外服务端口
ProxySQL默认有两个端口，管理端口在6032，服务端口在6033。
ProxySQL的管理员账户在6032端口，默认是用户名是admin，密码也是admin。系统设计上，不允许管理员用户与MySQL普通用户重名。这就带来了一个使用场景的问题，即应用程序和后端MySQL经常使用admin作为用户名，这样将导致冲突无法登录。由此，我们在这里另外创建一个后续要使用的管理员账号叫做proxyadmin。然后我们将对外服务端口从6033改为程序代码常用的3306。本步骤非常重要，配置错误将导致无法登录ProxySQL的管理端口。
执行如下命令以默认账号admin/admin连接到ProxySQL的6032管理端口。
mysql -u admin -padmin -h 127.0.0.1 -P6032 --prompt='Admin> '执行如下命令：
SET admin-admin_credentials='proxyadmin:proxyadmin';
load admin variables to runtime;
save admin variables to disk;
SET mysql-interfaces='0.0.0.0:3306';
SAVE MYSQL VARIABLES TO DISK;
PROXYSQL RESTART;执行完毕后，服务会中断，输入quit，退出到Linux Console，然后重新连接到6032的管理端口。重新连接时候，就可以使用新的proxyadmin用户名和密码登录了。命令如下：
mysql -u proxyadmin -pproxyadmin -h 127.0.0.1 -P6032 --prompt='Admin>登录成功就表示配置成功。此时服务端口3306还不能登录，有待后续配置。
2、新增后端节点
假设ProxySQL的后台是AWS Aurora for MySQL数据库，有一个Writer节点（主节点）和一个Reader节点（只读节点），那么执行如下命令，新增两个节点，且分别配置两个节点的组ID为1和2。其中hostgroup_id=1是可写入的主节点。
INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (1,'proxy-test-cluster.cluster-cfvonviibzua.rds.cn-north-1.amazonaws.com.cn',3306);
INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (2,'proxy-test-cluster.cluster-ro-cfvonviibzua.rds.cn-north-1.amazonaws.com.cn',3306);
LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;配置成功后可运行如下命令检查配置正确：
SELECT * FROM mysql_servers;正常的话应返回类似如下的信息：
Admin> SELECT * FROM mysql_servers;
+--------------+----------------------------------------------------------------------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+----------------------------------------------------------------------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 1 | proxy-test-cluster.cluster-cfvonviibzua.rds.cn-north-1.amazonaws.com.cn | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
| 2 | proxy-test-cluster.cluster-ro-cfvonviibzua.rds.cn-north-1.amazonaws.com.cn | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
+--------------+----------------------------------------------------------------------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
2 rows in set (0.00 sec)
Admin>3、新增流量规则
首先定义默认节点，如果规则没有显式声明，则所有流量送往主节点。接下来定义规则，将所有查询语句送往只读节点，其中Select for update特殊对待还送往主节点。执行如下命令：
UPDATE mysql_users SET default_hostgroup=1;
INSERT INTO mysql_query_rules (rule_id,active,match_digest,destination_hostgroup,apply)
VALUES
(1,1,'^SELECT.*FOR UPDATE$',1,1),
(2,1,'^SELECT',2,1);
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;配置成功后可运行如下命令检查配置正确：
SELECT * from mysql_query_rules;正常的话应返回类似如下的信息：
Admin> SELECT * from mysql_query_rules;
+---------+--------+----------+------------+--------+-------------+------------+------------+--------+----------------------+---------------+----------------------+--------------+---------+-----------------+-----------------------+-----------+--------------------+---------------+-----------+---------+---------+-------+-------------------+----------------+------------------+-----------+--------+-------------+-----------+---------------------+-----+-------+---------+
| rule_id | active | username | schemaname | flagIN | client_addr | proxy_addr | proxy_port | digest | match_digest | match_pattern | negate_match_pattern | re_modifiers | flagOUT | replace_pattern | destination_hostgroup | cache_ttl | cache_empty_result | cache_timeout | reconnect | timeout | retries | delay | next_query_flagIN | mirror_flagOUT | mirror_hostgroup | error_msg | OK_msg | sticky_conn | multiplex | gtid_from_hostgroup | log | apply | comment |
+---------+--------+----------+------------+--------+-------------+------------+------------+--------+----------------------+---------------+----------------------+--------------+---------+-----------------+-----------------------+-----------+--------------------+---------------+-----------+---------+---------+-------+-------------------+----------------+------------------+-----------+--------+-------------+-----------+---------------------+-----+-------+---------+
| 1 | 1 | NULL | NULL | 0 | NULL | NULL | NULL | NULL | ^SELECT.*FOR UPDATE$ | NULL | 0 | CASELESS | NULL | NULL | 1 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 1 | NULL |
| 2 | 1 | NULL | NULL | 0 | NULL | NULL | NULL | NULL | ^SELECT | NULL | 0 | CASELESS | NULL | NULL | 2 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 1 | NULL |
+---------+--------+----------+------------+--------+-------------+------------+------------+--------+----------------------+---------------+----------------------+--------------+---------+-----------------+-----------------------+-----------+--------------------+---------------+-----------+---------+---------+-------+-------------------+----------------+------------------+-----------+--------+-------------+-----------+---------------------+-----+-------+---------+
2 rows in set (0.00 sec)
Admin>4、设置用户
前文已经将服务端口从ProxySQL安装好之后默认的6033修改为3306了。但此时还不能使用，需要配置账号。目前版本的ProxySQL不支持前后端分离账号，ProxySQL所有的认证体系都是同一套。
这意味着：
- ProxySQL登录后端Aurora MySQL的身份验证
- 用户应用程序登录ProxySQL 3306端口发起访问的身份验证
必须是相同的用户名和密码。这也是当前ProxySQL的一个小小的局限。由此，我们继续配置。
执行如下命令：
INSERT INTO mysql_users(username,password,default_hostgroup) VALUES ('admin','wp123456',1);
LOAD MYSQL USERS TO RUNTIME;
SAVE MYSQL USERS TO DISK;这里就添加了一个用户名为admin和密码是wp123456的用户。配置完毕后，执行如下命令，验证是否正常：
SELECT * FROM mysql_users;显示结果类似如下则表示正常：
Admin> SELECT * FROM mysql_users;
+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+---------+
| username | password | active | use_ssl | default_hostgroup | default_schema | schema_locked | transaction_persistent | fast_forward | backend | frontend | max_connections | comment |
+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+---------+
| admin | wp123456 | 1 | 0 | 1 | NULL | 0 | 1 | 0 | 1 | 1 | 10000 | |
+----------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+---------+
1 row in set (0.00 sec)
Admin>另外请注意，ProxySQL是支持使用hash后的密码而不是明文密码的，相关配置请参考ProxySQL官网的文档。
5、健康检查
本文使用AWS Aurora MySQL作为后单数据库，Aurora本身是以一个主节点一个只读节点的方式作为高可用的，且Aurora的Endpoint名称是自动做failover切换的。因此，无需使用ProxySQL自带的健康检查和切换功能。此部分配置可以跳过。
6、验证登录
现在退出ProxySQL的管理员console，使用普通的MySQL客户端连接到ProxySQL所在节点的IP的3306端口，测试下访问。命令如下。
mysql -h proxysql的服务器ip -uadmin -pwp123456返回结果如下则表示成功。
[ec2-user@ip-172-31-11-6 ~]$ mysql -h 172.31.31.146 -uadmin -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 40
Server version: 5.5.30 (ProxySQL)
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MySQL [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
MySQL [(none)]>则表示连接成功且可以正常执行查询。
三、测试
1、安装Sysbench
启动一个新的EC2应用服务器，独立于ProxySQL的EC2，作为专门的应用客户端施加压力。系统启动完成后，以root身份执行如下命令：
amazon-linux-extras install epel
yum update -y
reboot执行reboot的原因是确保内核等均为最新。
yum install sysbench mysql -y安装Sysbench完成。
执行性能测试前请注意，AWS上不同规格的RDS承载能力是不一样的。最大连接数可以通过登录Aurora MySQL后使用如下命令查询。
show variables where Variable_name like "%conn%";例如本次实验使用的规格较小，返回信息如下，其中最大连接数为90。
MySQL [(none)]> show variables where Variable_name like "%conn%";
+-----------------------------------------------+-----------------+
| Variable_name | Value |
+-----------------------------------------------+-----------------+
| aurora_max_connections_limit | 16000 |
| character_set_connection | utf8 |
| collation_connection | utf8_general_ci |
| connect_timeout | 10 |
| disconnect_on_expired_password | ON |
| init_connect | |
| max_connect_errors | 100 |
| max_connections | 90 |
| max_user_connections | 0 |
| performance_schema_session_connect_attrs_size | 0 |
+-----------------------------------------------+-----------------+
10 rows in set (0.00 sec)
MySQL [(none)]>测试中请注意，这个参数可以调大，但是默认数值根据AWS的RDS机型规格不一样，默认值有所不同。请注意后续读写压力测试时候的最大连接数都不要超过这个界限。ProxySQL内置的默认最大连接数是2048，但是Proxy后端的Aurora MySQL最大连接只有90，因此在不修改90的情况下，在后续的测试中只使用50个并发来做压力测试。
2、准备测试数据
在压力测试节点上，运行如下命令建立测试数据，其中的host地址需要替换为ProxySQL所在的内网IP地址。EC2的内网IP地址是不会改变的，因此可以不使用域名，直接使用IP地址。
sysbench --db-driver=mysql --mysql-host=172.31.31.146 --mysql-port=3306 --mysql-user=admin --mysql-password=wp123456 --mysql-db=wordpress --table_size=10000 --tables=20 --threads=2 oltp_read_write prepare这里需要注意的是，执行插入测试数据时候，会写入大量的数据，此时数据是写入到主节点的，并通过Aurora的复制技术传输到只读节点。由此，主节点和辅助节点都有一定的CPU占用，这是正常的。数据导入完成后，等待一段时间，让CPU和负载逐渐平缓，并记录测试开始时间，稍后将从RDS控制面板上查询数据库性能。
2、写入数据测试
在压力测试节点上，运行如下命令：
sysbench --db-driver=mysql --mysql-host=172.31.31.146 --mysql-port=3306 --mysql-user=admin --mysql-password=changeme --mysql-db=wordpress --table_size=10000 --tables=20 --events=0 --time=300 --threads=50 --report-interval=10 oltp_write_only run运行测试300秒结束后，通过AWS RDS的控制台上的监控数据可以看出，写入节点产生了50个并发连接，且在300秒时间内对CPU造成了较大的压力，写入任务顺利的完成。如下截图。
而此时作为对比，只读节点上并未接受到数据库连接，而且CPU也仅有很小的波动用于处理复制流量，因此可以确认写入流量成功的分离不会对只读节点进行操作。如下截图。
测试完成。
3、读取数据测试
由于ProxySQL还会对节点保持长连接，因此我们需要先重启服务重制连接，以获得一个干净的环境，便于更简单的通过监控数据来判定流量是否分离成功，然后再进行下一个测试。登录到ProxySQL的服务器，执行如下命令：
mysql -u proxyadmin -pproxyadmin -h 127.0.0.1 -P6032 --prompt='Admin>
PROXYSQL RESTART;由此将通过重启服务重置所有连接。重置完毕后，在等待一段时间（5-10分钟），等到AWS RDS控制台上的监控数据已经恢复到较低水平后，再发起下一个测试。由此，将可以更简单的快速识别流量是否是读写分类正常施压造成的。
在压力测试节点上，运行如下命令：
sysbench --db-driver=mysql --mysql-host=172.31.31.146 --mysql-port=3306 --mysql-user=admin --mysql-password=changeme --mysql-db=wordpress --table_size=10000 --tables=20 --events=0 --time=300 --threads=50 --report-interval=10 oltp_read_only run测试过程中，只读节点的压力如下截图。
在测试过程中，通过AWS控制台，查看RDS的Aurora主实例和只读实例的监控数据，发现两个实例都发生了数据库连接，连接数均达到了50个连接被建立。如下截图。
对比这两张图各自体现的负载却不相同，主实例负载很低，只读实例负载很高。从现象上判断，大部分读取压力被发送到了只读节点，依然有少量请求被发送到了负责写入的主节点。如何排查并确认这一点呢？
登录到ProxySQL的6032端口，执行如下命令：
select * from stats_mysql_processlist limit 10;以上这条命令，将显示当前正在通过ProxySQL查询的进程的情况，并且只显示10行。注意，这个是显示当前正在跑的SQL语句的瞬时值，每次运行这条语句时候，每一秒输出的结果都不一样，且只显示10行。如果sysbench已经执行完毕结束了，那么将不会查询到。
Admin> select * from stats_mysql_processlist limit 10;
+----------+-----------+-------+-----------+--------------+----------+-----------+---------------+------------+----------------------------------------------------------------------------+----------+---------+---------+-----------------------------------------------------------+--------------+---------------+
| ThreadID | SessionID | user | db | cli_host | cli_port | hostgroup | l_srv_host | l_srv_port | srv_host | srv_port | command | time_ms | info | status_flags | extended_info |
+----------+-----------+-------+-----------+--------------+----------+-----------+---------------+------------+----------------------------------------------------------------------------+----------+---------+---------+-----------------------------------------------------------+--------------+---------------+
| 0 | 52 | admin | wordpress | 172.31.29.78 | 60924 | 2 | 172.31.31.146 | 43224 | proxy-test-cluster.cluster-ro-cfvonviibzua.rds.cn-north-1.amazonaws.com.cn | 3306 | Prepare | 1 | SELECT c FROM sbtest20 WHERE id=? | 0 | NULL |
| 3 | 53 | admin | wordpress | 172.31.29.78 | 60928 | 2 | 172.31.31.146 | 43214 | proxy-test-cluster.cluster-ro-cfvonviibzua.rds.cn-north-1.amazonaws.com.cn | 3306 | Execute | 2 | SELECT c FROM sbtest16 WHERE id=? | 0 | NULL |
| 1 | 54 | admin | wordpress | 172.31.29.78 | 60926 | 2 | 172.31.31.146 | 43222 | proxy-test-cluster.cluster-ro-cfvonviibzua.rds.cn-north-1.amazonaws.com.cn | 3306 | Execute | 0 | SELECT c FROM sbtest13 WHERE id=? | 0 | NULL |
| 1 | 55 | admin | wordpress | 172.31.29.78 | 60930 | 2 | 172.31.31.146 | 43244 | proxy-test-cluster.cluster-ro-cfvonviibzua.rds.cn-north-1.amazonaws.com.cn | 3306 | Execute | 1 | SELECT c FROM sbtest2 WHERE id=? | 0 | NULL |
| 1 | 56 | admin | wordpress | 172.31.29.78 | 60932 | 2 | 172.31.31.146 | 43384 | proxy-test-cluster.cluster-ro-cfvonviibzua.rds.cn-north-1.amazonaws.com.cn | 3306 | Execute | 3 | SELECT c FROM sbtest6 WHERE id BETWEEN ? AND ? ORDER BY c | 0 | NULL |
| 2 | 57 | admin | wordpress | 172.31.29.78 | 60934 | 2 | 172.31.31.146 | 43390 | proxy-test-cluster.cluster-ro-cfvonviibzua.rds.cn-north-1.amazonaws.com.cn | 3306 | Execute | 0 | SELECT c FROM sbtest20 WHERE id=? | 0 | NULL |
| 1 | 58 | admin | wordpress | 172.31.29.78 | 60936 | 2 | 172.31.31.146 | 43240 | proxy-test-cluster.cluster-ro-cfvonviibzua.rds.cn-north-1.amazonaws.com.cn | 3306 | Execute | 3 | SELECT c FROM sbtest5 WHERE id=? | 0 | NULL |
| 0 | 59 | admin | wordpress | 172.31.29.78 | 60938 | 2 | NULL | NULL | NULL | NULL | Sleep | 0 | NULL | NULL | NULL |
| 3 | 60 | admin | wordpress | 172.31.29.78 | 60940 | 1 | 172.31.31.146 | 49642 | proxy-test-cluster.cluster-cfvonviibzua.rds.cn-north-1.amazonaws.com.cn | 3306 | Execute | 0 | COMMIT | 0 | NULL |
| 2 | 61 | admin | wordpress | 172.31.29.78 | 60942 | 2 | NULL | NULL | NULL | NULL | Sleep | 0 | NULL | NULL | NULL |
+----------+-----------+-------+-----------+--------------+----------+-----------+---------------+------------+----------------------------------------------------------------------------+----------+---------+---------+-----------------------------------------------------------+--------------+---------------+
10 rows in set (0.01 sec)
Admin>保持Sysbench一直处于read_only测试的情况下，反复多次执行上一条语句查询当前正在执行的SQL，且限制输出10行。反复执行几次，就可以看到其中的输出内容包括如下一条：
| 3 | 60 | admin | wordpress | 172.31.29.78 | 60940 | 1 | 172.31.31.146 | 49642 | proxy-test-cluster.cluster-cfvonviibzua.rds.cn-north-1.amazonaws.com.cn | 3306 | Execute | 0 | COMMIT | 0 | NULL |这条命令的info字段显示正在执行的命令是commit命令，就是为什么部分流量被发往主节点的原因。因为Sysbench是一个完整的测试工具，其中使用了在只读测试中，也包含了commit语句，因此根据前文Proxy配置的规则，commit语句将被发往主节点进行解析。所以，才造成了在监控数据上看，主节点也拥有了50个并发连接，但是大部分压力都是在只读节点的原因。
由于Readonly测试不会写入数据，因此在跑Sysbench脚本时候，执行commit命令其实是没有意义的。因此可以禁用掉事务，进行完全的只读查询。
4、不带事务的只读测试
由于ProxySQL还会对节点保持长连接，因此我们需要先重启服务重置连接，以获得一个干净的环境，便于更简单的通过监控数据来判定流量是否分离成功。然后再进行下一个测试。登录到ProxySQL的服务器，执行如下命令：
mysql -u proxyadmin -pproxyadmin -h 127.0.0.1 -P6032 --prompt='Admin>
PROXYSQL RESTART;由此将通过重启服务重置所有连接。重置完毕后，在等待一段时间（5-10分钟），等到AWS RDS控制台上的监控数据已经恢复到较低水平后，再发起下一个测试。由此，将可以更简单的快速识别流量是否是读写分类正常施压造成的。
这里对上一个只读测试脚本，增加–skip_trx=on的参数。到压力服务器上，执行如下命令：
sysbench --db-driver=mysql --mysql-host=172.31.31.146 --mysql-port=3306 --mysql-user=admin --mysql-password=changeme --mysql-db=wordpress --table_size=10000 --tables=20 --events=0 --time=300 --threads=50 --report-interval=10 --skip_trx=on oltp_read_only run测试后，再次通过RDS控制台检查主实例和只读实例的负载，可以发现负载被完全路由到了只读实例，主实例不会承担负载，也不会有建立连接。只读实例的负载如下截图。
如下截图是主实例完全是空闲状态的曲线。主实例负载如下截图。
不带事务的只读测试可以反映出ProxySQL的用法，即需要打开Auto-Commit，让每一条SQL都成为一个独立的日志。如果关闭了Auto-Commit，那么一个事务里边可能包含5条语句，不同的语句可能被ProxySQL解析和送往不同的数据库节点，这将导致严重问题。而打开了Auto-Commit后，每一条Insert和Update都是一条独立事务，都会被解析到主节点完成写入，也就保持了事务完整性。
至此测试完成。