ProxySQL--灵活强大的MySQL代理层
安装配置
1 wget https://github.com/sysown/proxysql/releases/download/v1.3.6/proxysql-1.3.6-1-centos67.x86_64.rpm
2 yum install perl-DBD-MySQL
3 rpm -ivh proxysql-1.3.6-1-centos67.x86_64.rpm
4 service proxysql start
5 proxysql --version
ProxySQL version 1.3.6-0-g434b376, codename Truls
本地配置文件：
vim /etc/proxysql.cnf
proxysql有个配置文件 /etc/proxysql.cnf，只在第一次启动的时候有用，后续所有的配置修改都是对SQLite数据库操作，并且不会更新到proxysql.cnf文件中。ProxySQL绝大部分配置都可以在线修改，配置存储在 /var/lib/proxysql/proxysql.db 中，后面会介绍它的在线配置的设计方式。
6 登陆管理界面（配置信息从启动进程的配置文件查看）
mysql -u admin -padmin -h127.0.0.1 -P6032 --prompt='proxysql>'
mysql -u admin -padmin -S/tmp/proxysql_admin.sock--prompt='proxysql>'
看到管理接口的端口是6032，账号密码是admin(可以动态修改)只能通过本地连接，客户端接口的端口是6033，账号密码通过管理接口去设置。
库、表说明
通过管理接口登录，需要先装好mysql客户端，并且设置prompt
[client]
prompt = \\u@\\h:\\d\\r:\\m:\\s>
通过管理接口进入ProxySQL
配置读写分离和路由规则
环境（三个后台服务器，一主两从的架构）
Master 10.9.160.248：3306
Slave1 10.9.117.107:3306 设置read_only
Slave2 10.9.150.86:3306 设置read_only
在后端MySQL服务器db的主节点上创建proxy的监控账户和业务账户
grant USAGE,replication client on *.* to 'monitor'@'%'identified by 'monitor'
#grant all privileges on *.* to 'monitor'@'%'identified by 'monitor'
grant allprivileges on *.* to 'msandbox'@'%' identified by 'msandbox';
在proxy管理命令行添加后端服务器列表
proxysql>INSERT INTOmysql_servers(hostgroup_id,hostname,port) VALUES (1,'10.9.160.248',3306);
proxysql>INSERT INTOmysql_servers(hostgroup_id,hostname,port) VALUES (1,'10.9.150.86',3306);
proxysql>INSERT INTOmysql_servers(hostgroup_id,hostname,port) VALUES (1,'10.9.117.107',3306);
注意到这时的hostgroup都为1，这没什么问题
即使生效
LOAD MYSQL SERVERS TO RUNTIME;
配置和注册监控账户
proxysql>update global_variables setvariable_value='monitor' where variable_name='mysql-monitor_password';
proxysql>update global_variables setvariable_value='monitor' where variable_name='mysql-monitor_username';
这里填的账户和密码均为monitor的信息就是步骤1中在后端服务器创建的监控账户
即使生效，并持久化到磁盘
LOAD MYSQL VARIABLES TO RUNTIME;
SAVE MYSQL VARIABLES TO DISK;
查看监控是否生效
proxysql>SELECT * FROMmonitor.mysql_server_connect_log ORDER BY time_start_us DESC LIMIT 10;
+--------------+------+------------------+-------------------------+---------------+
| hostname | port |time_start_us | connect_success_time_us | connect_error |
+--------------+------+------------------+-------------------------+---------------+
| 10.9.160.248 | 3306 | 1497001266520778 |688 | NULL |
| 10.9.150.86 | 3306 | 1497001266510692 |541 | NULL |
| 10.9.117.107 | 3306 | 1497001266500632 | 718 |NULL |
| 10.9.160.248 | 3306 | 1497001206520719 |652 | NULL |
| 10.9.150.86 | 3306 | 1497001206510580 |618 | NULL |
| 10.9.117.107 | 3306 | 1497001206500519 |744 |NULL |
| 10.9.160.248 | 3306 | 1497001146520616 |699 | NULL |
| 10.9.150.86 | 3306 | 1497001146510561 | 557 | NULL |
| 10.9.117.107 | 3306 | 1497001146500487 |759 |NULL |
| 10.9.160.248 | 3306 | 1497001086520458 |595 | NULL |
+--------------+------+------------------+-------------------------+---------------+
10 rows in set (0.00 sec)
配置主从信息
注意点：slave节点需要设置read_only=1。如果后端是PXC/MGR/MariaDB Galera，则无需手动设置，因为会自动设置。
提供2个MySQL节点的配置文件。
# 以下是Master的配置文件
[mysqld]
datadir=/data
socket=/data/mysql.sock
server-id=110
log-bin=/data/master-bin
sync-binlog=1
log-error=/data/error.log
pid-file=/data/mysqld.pid
# 以下是slave的配置文件
[mysqld]
datadir=/data
socket=/data/mysql.sock
server-id=120
relay_log=/data/relay-log
log-error=/data/error.log
pid-file=/data/mysqld.pid
read_only=1
表示该复制组，1为写入组的编号，2为读取组的编号
INSERT INTO mysql_replication_hostgroups VALUES (1,2,’one-master-1-slave’);
即时生效
LOAD MYSQL SERVERS TO RUNTIME;
当生效后，proxysql会去检测后端的数据节点
假设该节点read_only为1，则会将该server移入group 2；
假设该节点read_only为0，则会将该server移入group 1；
这时再查mysql_servers表，就会发现节点的hostgroup_id根据read_only的情况自动变化了
proxysql>select hostgroup_id,hostname frommysql_servers;
+--------------+--------------+
| hostgroup_id | hostname |
+--------------+--------------+
|1 |10.9.160.248 |
|2 |10.9.117.107 |
|2 |10.9.150.86 |
+--------------+--------------+
3 rows in set (0.00 sec)
确认无误后 持久化到磁盘
SAVE MYSQL SERVERS TO DISK;
配置和注册业务账户
插入步骤1中生成的业务账户
INSERT INTOmysql_users(username,password,default_hostgroup) VALUES ('msandbox','msandbox',1);
proxysql>select * from mysql_users\G
*************************** 1. row***************************
username: msandbox
password: msandbox
active: 1
use_ssl: 0
default_hostgroup: 1
default_schema: NULL
schema_locked: 0
transaction_persistent: 0
fast_forward: 0
backend: 1
frontend: 1
max_connections:10000
1 row in set (0.00 sec)
proxysql>update mysql_users settransaction_persistent=1 where username='msandbox';
Query OK, 1 row affected (0.00 sec)
即时生效，持久化
LOAD MYSQL USERS TO RUNTIME;
SAVE MYSQL USERS TO DISK;
说明几个比较重要的列名含义
Active：表示该用户是否生效
default_hostgroup ：表示如果没有配置其他路由规则，那么默认走的就是该值指定的hostgroup
max_connections：该用户能创建的最大连接数
transaction_persistent ：如果为1，则一个完整的SQL只可能路由到一个节点；这点非常重要，主要解决这种情况：一个事务有混合的读操作和写操作组成，事务未提交前，如果事务中的读操作和写操作路由到不同节点，那么读取到的结果必然是脏数据。所以一般情况下，该值应该设置为1，尤其是业务中使用到事务机制的情况（默认为0）
(注意：该值设为1后，sysbench不好测试读写分离的情况，因为sysbench中的oltp脚本中的SQL都是包含在一个个事务中的，所以测试阶段可以将其值改为0
frontend 和backend列目前的版本必须为1，因为目前的proxy和底层的节点共用一套账户认证体系，后续可能会单独分离出来，所以增加了这两个字段。)
这时验证下账户登陆，确定登陆上去的就是预想中的从库
[root@10-9-192-94 ~]# mysql -u msandbox -pmsandbox -h127.0.0.1 -P6033 -e "show slave hosts"
+-----------+------+------+-----------+--------------------------------------+
| Server_id | Host | Port | Master_id |Slave_UUID |
+-----------+------+------+-----------+--------------------------------------+
| 168392043 | | 3306 |168403192 | baaf5e8b-4cbe-11e7-8ca2-e8611f12fd90 |
| 168400470 | | 3306 |168403192 | aed780a0-4cbe-11e7-8ca2-6c92bf15bed0 |
配置读写分离策略
1 除了select ..for update以外的select都路由到从库
2 其他所有操作都路由到主库
proxysql>show create table mysql_query_rules\G
***************************1. row***************************
table:mysql_query_rules
Create Table:CREATE TABLE mysql_query_rules (
rule_id INTEGER PRIMARY KEYAUTOINCREMENTNOT NULL,
active INT CHECK (active IN (0,1))NOT NULLDEFAULT 0,
username VARCHAR,
schemaname VARCHAR,
flagIN INT NOT NULL DEFAULT 0,
client_addr VARCHAR,
proxy_addr VARCHAR,
proxy_port INT,
digest VARCHAR,
match_digest VARCHAR,
match_pattern VARCHAR,
negate_match_pattern INTCHECK(negate_match_pattern IN (0,1)) NOT NULL DEFAULT 0,
flagOUT INT,
replace_pattern VARCHAR,
destination_hostgroup INT DEFAULTNULL,
cache_ttl INT CHECK(cache_ttl >0),
reconnect INT CHECK (reconnect IN(0,1))DEFAULT NULL,
timeout INT UNSIGNED,
retries INT CHECK (retries>=0ANDretries <=1000),
delay INT UNSIGNED,
mirror_flagOUT INT UNSIGNED,
mirror_hostgroup INT UNSIGNED,
error_msg VARCHAR,
log INT CHECK (log IN (0,1)),
apply INT CHECK(apply IN (0,1)) NOTNULLDEFAULT 0,
comment VARCHAR)
1 row in set(0.00 sec)
Active:是否启用这个路由规则
Username：如果不为空，该规则匹配该用户
schemaname ：如果不为空，该规则只匹配该库名称
destination_hostgroup：该路由规则发往哪个组
apply：为1表示该正则匹配后，将不再接受其他匹配，直接转发
match_digest：描述规则的正则表达式，其中1.4版本以后，正则表达式支持PCRE和RE2两种，默认使用pcre，即perl语言支持的正则表达式
cache_ttl：用户查询缓存的时间阈值，单位为毫秒
更多字段说明，参考官方wiki文档
https://github.com/sysown/proxysql/wiki/MySQL-Query-Rules
通过查看该表的表结构，不难看出所有的路由规则是用户完全自定义的，也就是说不仅仅可以实现读写分离，任何SQL类型都可以自定义发送到指定的数据节点执行，比如在实现大部分读写分离的同时，一些对实时性要求很高的SQL，可以定义成发送到master节点运行；
配置insert,update,delete
proxysql>Insert intomysql_query_rules(rule_id,active,username,match_digest,destination_hostgroup,apply) values(9,1,'msandbox','^SELECT .* FORUPDATE$',1,1);
proxysql>insert intomysql_query_rules(rule_id,active,username,match_digest,destination_hostgroup,apply)values(8,1,'msandbox','^INSERT',1,1);
proxysql>insert intomysql_query_rules(rule_id,active,username,match_digest,destination_hostgroup,apply)values(7,1,'msandbox','^UPDATE',1,1);
proxysql>insert intomysql_query_rules(rule_id,active,username,match_digest,destination_hostgroup,apply)values(6,1,'msandbox','^DELETE',1,1);
配置select
proxysql>Insert intomysql_query_rules(rule_id,active,username,match_digest,destination_hostgroup,apply) values(10,1,'msandbox','^SELECT',2,1);
Query OK, 1 row affected (0.00 sec)
刚才通过mysql_users表的配置和解释可知，路由规则以外的SQL都发往mysql_users表中的default_hostgroup字段，即数值1，该组为master节点；
即时生效并持久化
load mysql query rules to runtime;
save mysql query rules to disk;
读写分离测试
清空历史查询记录
proxysql>select* fromstats.stats_mysql_query_digest_reset limit 1;
通过查询stats_mysql_query_digest_reset的效果是这样的：返回stats_mysql_query_digest表的查询结果，并且执行truncate table stats_mysql_query_digest清空stats_mysql_query_digest表
查看SQL历史记录：
proxysql>select* from stats.stats_mysql_query_digest;
Empty set (0.00sec)
修改transaction_persistent为0，使得sysbench适用该场景
update mysql_users set transaction_persistent=0 whereusername='msandbox';
LOAD MYSQL USERS TO RUNTIME;
sysbench压测proxy
sysbench--report-interval=1 --num-threads=4 --max-time=2000--test=sysbench/tests/db/oltp.lua--mysql-user='msandbox'--mysql-password='msandbox' --oltp-table-size=10000--mysql-host=127.0.0.1--mysql-port=6033 --mysql-db=proxytest --max-requests=100000000 prepare
sysbench--report-interval=1 --num-threads=4 --max-time=2000--test=sysbench/tests/db/oltp.lua--mysql-user='msandbox'--mysql-password='msandbox' --oltp-table-size=10000--mysql-host=127.0.0.1--mysql-port=6033 --mysql-db=proxytest --max-requests=100000000 run
压测过程中观察读写分离情况
proxysql>select * from stats.stats_mysql_query_digest;
可以看到查询都是发往group 2，写操作都是发往group 1，即实现了读写分离
另外可以分别登陆三个数据节点show processlist确定proxysql真实实现了读写分离；
关于select ..for update和事务的路由测试，可以手工写几条特定的SQL测试，这里忽略；
通过mysql客户端连接：
[root@node6683 ~]# mysql -umsandbox -pmsandbox -h192.168.66.83 -P6033
测试proxysql的查询缓存
Proxysql的查询缓存和mysql的查询缓存有点类似，但不是一回事；proxysql的查询缓存指的是：如果在指定时间大小范围内发送的SQL一摸一样，那么直接返回结果集，而返回的结果集可能并不是准确的查询结果，所以需要设置合适的时间范围，既能提升性能，又得满足业务需求，即查询结果足够的“新”。这个特性我想可以用于这个方便：针对一些查询频率很高但结果并不需要太精确的业务，可以单独给这些SQL配置查询缓存
proxysql>update mysql_query_rules setcache_ttl=2000 where rule_id=11\G
Query OK, 1 row affected (0.00 sec)
proxysql>LOAD MYSQL QUERY RULES TO RUNTIME; #使配置生效
Query OK, 0 rows affected (0.00sec)
sysbench --report-interval=1--num-threads=4 --max-time=2000--test=sysbench/tests/db/oltp.lua--mysql-user='msandbox'--mysql-password='msandbox' --oltp-table-size=10000--mysql-host=127.0.0.1--mysql-port=6033--mysql-db=proxytest --max-requests=100000000 run
proxysql>select hostgroup,digest_text,sum_time fromstats.stats_mysql_query_digest;
可以看到hostgroup部分为-1，表示这些查询是使用了proxy查询缓存的，耗时为0，这里的0不可能是真的没有耗时，只是不好统计，显示上直接显示成0
测试查询重写
范例 将带distinct的SQL去掉排序
9.1 写入下面这条改写规则，注意规则中大小写是敏感的
proxysql>select * from mysql_query_rules\G
*************************** 1.row***************************
rule_id: 31
active: 1
username:msandbox
schemaname:NULL
flagIN: 0
client_addr:NULL
proxy_addr:NULL
proxy_port:NULL
digest: NULL
match_digest:NULL
match_pattern: DISTINCT (.*) ORDER BY c
negate_match_pattern: 0
flagOUT: NULL
replace_pattern:DISTINCT\1
destination_hostgroup: NULL
cache_ttl:NULL
reconnect:NULL
timeout: NULL
retries: NULL
delay: NULL
mirror_flagOUT:NULL
mirror_hostgroup: NULL
error_msg:NULL
log: NULL
apply: 1
comment: NULL
1 row in set (0.00 sec)
9.2 执行下面这个SQL
可以看到解析计划中没有using filesort，SQL已经被改写了
9.3 执行一把SQL内容，查看规则命中情况和执行列表确认
proxysql>select * from stats_mysql_query_rules ;
+---------+------+
| rule_id | hits |
+---------+------+
| 31 |1 |
+---------+------+
1 row in set (0.00 sec)
proxysql>select hostgroup,digest_textfromstats.stats_mysql_query_digest;
+-----------+--------------------------------------------------------------------+
| hostgroup |digest_text |
+-----------+--------------------------------------------------------------------+
| 1 |SELECT DISTINCT c FROM sbtest1 WHERE id BETWEEN ? and ? order by c |
+-----------+--------------------------------------------------------------------+
1 row in set (0.00 sec)
