proxySQL安装使用

一、介绍

ProxySQL是基于MySQL的一款开源的中间件的产品，是一个灵活的MySQL代理
层，可以实现读写分离，支持 Query路由功能，支持动态指定某个SQL进行缓
存，支持动态加载配置信息（无需重启 ProxySQL 服务），支持故障切换和
SQL的过滤功能。

相关 ProxySQL 的网站：
https://www.proxysql.com/
https://github.com/sysown/proxysql/wiki

基本架构

安装ProxySQL

rpm -ivh proxysql-2.0.10-1-centos7.x86_64.rpm
yum install perl-DBD* -y
yum installperl-DBI* -y
systemctl start proxysql
#下载失败换成国内源

登录

mysql -uadmin -padmin -h127.0.0.1 -P6032

ProxySQL 中管理结构自带系统库

main:
main 库中有如下信息：
mysql_servers: 后端可以连接 MySQL 服务器的列表
mysql_users: 配置后端数据库的账号和监控的账号。
mysql_query_rules: 指定 Query 路由到后端不同服务器的规则列表
mysql_replication_hostgroups : 节点分组配置信息
注： 表名以 runtime_开头的表示ProxySQL 当前运行的配置内容，不能直接修改。不带runtime_是下文图中Mem相关的配置。
disk ：
持久化的磁盘的配置
stats：
统计信息的汇总
monitor：
监控的收集信息，比如数据库的健康状态等
stats_history:
ProxySQL 收集的有关其内部功能的历史指标

配置系统分为三层。

顶层 		runtime
中间层		memory
底层 		disk

在不同层次之间移动配置

1. user相关配置
## MEM 加载到runtime
LOAD MYSQL USERS TO RUNTIME;
## runtime 保存至 MEM
SAVE MYSQL USERS TO MEMORY;
总结：
日常配置其实大部分时间在MEM配置，然后load到RUNTIME，然后SAVE到
DIsk。cfg很少使用。
例如 ：
load mysql xxx to runtime;
save mysql xxx to disk;
注意：
只有load到 runtime 状态时才会验证配置。在保MEM或disk时，都不
会发生任何警告或错误。当load到 runtime 时，如果出现错误，将恢复为之
前保存得状态，这时可以去检查错误日志。

1.ProxySQL应用——基于SQL的读写分离

创建一个读写规则。10是写组、20是读组。默认以read_only来识别。如果是有read_only代表分配到读组
insert into  mysql_replication_hostgroups(writer_hostgroup,reader_hostgroup,comment) values(10,20,'proxy一组');
load mysql servers to runtime;
save mysql servers to disk;
俩从库设置为
set global read_only=1;
set global super_read_only=1;

2. 添加主机到ProxySQL

insert into mysql_servers(hostgroup_id,hostname,port)
values (10,'10.0.0.51',3306);
insert into mysql_servers(hostgroup_id,hostname,port)
values (20,'10.0.0.52',3306);
insert into mysql_servers(hostgroup_id,hostname,port)
values (20,'10.0.0.53',3306);
load mysql servers to runtime;
save mysql servers to disk;

3. 创建监控用户，并开启监控

# 主库创建监控用户
create user monitor@'%' identified with mysql_native_password by '123';
grant replication client on *.* to monitor@'%';
# proxySQL修改variables表
set mysql-monitor_username='monitor';
set mysql-monitor_password='123';
或者 ：
UPDATE global_variables SET variable_value='monitor' WHERE variable_name='mysql-monitor_username';
UPDATE global_variables SET variable_value='123' WHERE variable_name='mysql-monitor_password';
load mysql variables to runtime;
save mysql variables to disk;

# 查询监控日志
db03 [(none)]>select * from mysql_server_connect_log;
db03 [(none)]>select * from mysql_server_ping_log;
db03 [(none)]>select * from mysql_server_read_only_log;
db03 [(none)]>select * from
mysql_server_replication_lag_log;

4.配置应用用户

create user root@'%' identified with mysql_native_password by '123';
grant all on *.* to root@'%';
insert into mysql_users(username,password,default_hostgroup) values('root','123',10);
load mysql users to runtime;
save mysql users to disk;
早期版本，需要开启事务持续化。
update mysql_users set transaction_persistent=1 where username='root';
load mysql users to runtime;
save mysql users to disk;

5. 实用的读写规则

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;
注： select … for update规则的rule_id必须要小于普通的select规则
的rule_id，ProxySQL是根据rule_id的顺序进行规则匹配。

6. 测试读写分离

[root@db03 ~]# mysql -uroot -p123 -P 6033 -h 127.0.0.1 -e "begin;select @@server_id;commit"

[root@db03 ~]# mysql -uroot -p123 -P 6033 -h 127.0.0.1 -e "select @@server_id;"

db03 [(none)]>select * from stats_mysql_query_digest\G
https://proxysql.com/documentation/proxysql-read-write-split-howto

ProxySQL应用扩展——花式路由规则

1. 基于端口的路由
## 修改ProxySQL监听SQL流量的端口号，监听多端口上。
set mysql-interfaces='0.0.0.0:6033;0.0.0.0:6034';
save mysql variables to disk;

## 重启生效
systemctl restart proxysql

## 设定路由规则
delete from mysql_query_rules; # 为了测试，先清空已有规则
insert into mysql_query_rules(rule_id,active,proxy_port,destination_hostgroup,apply) values(1,1,6033,10,1), (2,1,6034,20,1);
load mysql query rules to runtime;
save mysql query rules to disk;
说明：
除了基于端口进行分离，还可以基于监听地址(修改字段proxy_addr即
可)，也可以基于客户端地址(修改字段client_addr字段即可)。
2. 基于用户的路由
nsert into mysql_users(username,password,default_hostgroup) values('writer','123',10),('reader','123',20);
load mysql users to runtime;
save mysql users to disk;

delete from mysql_query_rules; # 为了测试，先清空已有规则

insert into mysql_query_rules(rule_id,active,username,destination_hostgroup,apply) values(1,1,'writer',10,1),(2,1,'reader',20,1);
load mysql query rules to runtime;
save mysql query rules to disk;
3. 配置规则将DROP语句屏蔽。
insert into mysql_query_rules(rule_id,active,match_pattern,destination_hostgroup,apply) values (3,1,'^drop',30,1);
load mysql query rules to runtime;
save mysql query rules to disk;