ProxySQL--灵活强大的MySQL代理层

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

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)