ProxySQL 是基于 MySQL 的一款开源的中间件的产品,是一个灵活的 MySQL 代理层,可以实现读写分离,支持 Query 路由功能,支持动态指定某个 SQL 进行缓存,支持动态加载(无需重启 ProxySQL 服务),故障切换和一些 SQL 的过滤功能。
多层配置结构
disk -> 是sqlite3 数据库 ,默认位置是$DATADIR/proxysql.db( /var/lib/proxysql/proxysql.db)
config file 是一个传统配置文件:一般不更改
内置库
main:内存配置数据库,即 MEMORY,表里存放后端 db 实例、用户验证、路由规则等信息
disk :持久化的磁盘的配置
stats: 统计信息的汇总
monitor:一些监控的收集信息,比如数据库的健康状态等
stats_history: 这个库是 ProxySQL 收集的有关其内部功能的历史指标
安装
wget https://github.com/sysown/proxysql/releases/tag/v2.0.17
rpm -ivh proxysql-2.0.13-1-centos7.x86_64.rpm --force --nodeps
systemctl start proxysql
netstat -antp|grep proxysql
基于SQL的读写分离
1、在mysql_replication_hostgroups中配置读写组
mysql -uadmin -padmin -h 127.0.0.1 -P 6032
insert into mysql_replication_hostgroups(writer_hostgroup,reader_hostgroup,comment) values(10,20,'proxy');
load mysql servers to runtime;
save mysql servers to disk;
2、后端从节点添加只读参数
set global read_only=1;
3、创建监控用户 主库创建监控用户:
create user monitor@'%' identified 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;
注:默认主库也是可以读的,可以设置权重或者从mysql_servers中删除
4、添加主机到proxySQL
insert into mysql_servers(hostgroup_id,hostname,port) values (10,'10.0.0.100',3306);
insert into mysql_servers(hostgroup_id,hostname,port) values (20,'10.0.0.101',3306);
insert into mysql_servers(hostgroup_id,hostname,port) values (20,'10.0.0.102',3306);
load mysql servers to runtime;
save mysql servers to disk;
4、查看监控日志
select * from mysql_server_connect_log;
select * from mysql_server_ping_log;
select * from mysql_server_read_only_log;
select * from mysql_server_replication_lag_log;
5、创建应用用户
主库:
create user dev@'%' identified by '123';
grant all on *.* to dev@'%';
proxySQL中:
insert into mysql_users(username,password,default_hostgroup) values('dev','123',10);
load mysql users to runtime;
save mysql users to disk;
注:早期版本mysql_users 表中的 transaction_persistent值为0 需要修改为1,保证事务完整性
6、读写规则
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顺序进行规则匹配的。
7、测试
mysql -udev -p123 -P6033 -h127.0.0.1 -e "begin;select @@server_id;commit;"
mysql -udev -p123 -P6033 -h127.0.0.1 -e "select @@server_id;"
select *select * from stats_mysql_query_digest;
标签:monitor,简介,ProxySQL,hostgroup,servers,mysql,id,select
From: https://blog.51cto.com/u_14218719/7266012