欢迎访问我的博客,你的支持,是我最大的动力!

MySQL王者晋级之路(八)ProxySQL

Mysql-笔记 马从东 6113℃ 评论
目录:
[显示]

ProxySQL

ProxySQL是MySQL中间件,是灵活的MySQL代理层,可以实现读写分离,支持Query路由,支持动态指定某个SQL进行缓存,支持动态加载配置、故障切换和一些SQL过滤功能
同类产品:DBproxy、MyCAT、OneProxy等
项目地址:https://github.com/sysown/proxysql
官网:http://www.proxysql.com/
文档:https://github.com/sysown/proxysql/wiki

环境配置

192.168.10.110 Master(node1) server_id:3306110
192.168.10.120 Slave(node2) read_only=on server_id:3306120
192.168.10.130 Slave(node3) read_only=on server_id:3306130
192.168.10.140 ProxySQL中间件 server_id:  代理
ProxySQL安装与启动

ProxySQL下载地址
https://github.com/sysown/proxysql/releases
https://github.com/sysown/proxysql/releases/download/v1.4.13/proxysql-1.4.13-1-centos7.x86_64.rpm
https://www.percona.com/downloads/proxysql/
https://www.percona.com/downloads/proxysql/proxysql-1.4.12/binary/redhat/7/x86_64/proxysql-1.4.12-1.1.el7.x86_64.rpm
安装
依赖:yum install perl-DBD-MySQL perl-DBI perl-Time-HiRes perl-IO-Socket-SSL
rpm -ivh proxysql-1.4.13-1-centos7.x86_64.rpm
yum install https://github.com/sysown/proxysql/releases/download/v1.4.13/proxysql-1.4.13-1-centos7.x86_64.rpm
配置文件
/etc/proxysql.cnf
如果存在数据库(SQLite数据库)文件,则不会解析配置文件。正常启动期间,ProxySQL仅从持久存储初始化内存配置
启动服务
systemctl start proxysql
systemctl status proxysql
systemctl stop proxysql
systemctl enable proxysql
#6032是管理端口号;6033是对外服务的端口号
#用户名密码默认是admin admin
查看版本
proxysql --version
管理员登录
./mysql -uadmin -padmin -h 127.0.0.1 -P 6032   #注,admin用户只能本地登录
show databases;
#默认有5个库:main、disk、stats、monitor、stats_history
main,内存配置数据库,MEMORY,存放后端db实例、用户验证、路由规则等信息
-mysql_servers,后端可连接MySQL服务器列表
-mysql_users,配置后端数据库的帐号和监控的帐号
-mysql_query_rules,指定Query路由到后端不同服务器的规则列表
#以runtime_开头的表是当前运行的配置内容,不能通过DML语句修改,只能修改不以runtime开头的表,然后LOAD使其生效,SAVE存盘供下次重启加载
disk,持久化磁盘的配置
stats,统计信息汇总
monitor,监控收集的信息,包括数据库的健康状态等
#查看各库的表 show tables from stats;   #若使用show tables; 仅显示main库中的表,无论是否使用了use stats;

构建一主两从架构

同步帐号
create user 'bak'@'192.168.10.%' identified by 'bak123';
grant replication slave on *.* to 'bak'@'192.168.10.%';
flush privileges;
同步方式
异步+GTID
从库配置只读
set global read_only = 1;
set global super_read_only = 1;
show variables like "%read_only%";

配置ProxySQL监控

在master(110)上创建ProxySQL的监控帐号和对外访问帐号

监控帐号用于 ProxySQL 监测后端 MySQL 服务是否正常,所需权限较低,当然,也可以监控帐号与对外访问帐号为同一帐号
对外访问帐号用于提供服务,仅允许的帐号能连接 ProxySQL 再连接到后端 MySQL 服务器

create user 'monitor'@'192.168.10.%' identified by 'monitor123';
grant all privileges on *.* to 'monitor'@'192.168.10.%' with grant option;
create user 'zs'@'192.168.10.%' identified by 'zs123';
grant all privileges on *.* to 'zs'@'192.168.10.%' with grant option;
flush privileges;
ProxySQL的多层配置系统

配置系统分为三层:顶层为RUNTIME,中间层为MEMORY,底层即持久层为DISK和CONFIG FILE

各层转换命令mysql usersmysql serversmysql query rulesmysql variablesscheduleradmin variables
[1],load mysql users from memory/load mysql users to runtime
[2],save mysql users to memory/save mysql users from runtime
[3],load mysql users to memory/load mysql users from disk
[4],save mysql users from memory/save mysql users to disk
[5],load mysql users from config
#memory可简写为mem;runtime可简写为run
#注意,值只有加载到运行时,才会进行最终验证,如报错

RUNTIME,当前生效的配置,无法直接修改,必须从下一层load进来
MEMORY,可以正常操作配置,随便修改,不会影响生产环境。修改一个配置一般都是先在MEMORY层完成的,确认正常后再加载到RUNTIME和持久化到磁盘上
DISK和CONFIG FILE,持久化配置信息,重启后内存中的配置信息会丢失,所以需要将配置保留在磁盘中,重启时,可以从磁盘快速加载回来

用超管用户登录ProxySQL添加主从服务器列表

./mysql -uadmin -padmin -h 127.0.0.1 -P 6032
insert into mysql_servers(hostgroup_id,hostname,port,max_connections) values (10,'192.168.10.110',3306,300);
insert into mysql_servers(hostgroup_id,hostname,port,max_connections) values (20,'192.168.10.120',3306,300);
insert into mysql_servers(hostgroup_id,hostname,port,max_connections) values (20,'192.168.10.130',3306,300);
select * from mysql_servers;
load mysql servers to runtime;
save mysql servers to disk;
#登录ProxySQL后,进行任何操作,都需要load * * to runtime,再执行save * * to disk
#加载完成,三台机器都是ONLINE状态

为ProxySQL配置监控帐号

set mysql-monitor_username='monitor';
set mysql-monitor_password='monitor123';
load mysql variables to runtime;
save mysql variables to disk;
#show variables; 显示全部变量
验证监控信息
select * from monitor.mysql_server_connect_log order by time_start_us desc limit 6;
select * from monitor.mysql_server_ping_log order by time_start_us desc limit 6;
配置ProxySQL主从分组信息

使用表 mysql_replication_hostgroups

查看表结构
show create table mysql_replication_hostgroups\G
字段:writer_hostgroup int,reader_hostgroup int,comment varchar
writer_hostgroup写入组的编号
reader_hostgroup读取组的编号
这里使用10为写入组,20为读取组

insert into mysql_replication_hostgroups values (10,20,'proxy');
load mysql servers to runtime;
save mysql servers to disk;
#select * from mysql_replication_hostgroups;

ProxySQL会根据server的read_only取值将服务器进行分组。值为0时将被划入编号10的分组,值为1时将被划入编号为20的分组
select * from mysql_servers\G #注意hostgroup_id的值

配置对外访问帐号,默认指定主库,并对该用户开启事务持久化保护
注:mysql_users表中transaction_persistent字段默认为0,建议在创建完用户后设置为1,避免发生脏读、幻读等现象

insert into mysql_users(username,password,default_hostgroup,max_connections,default_schema,active) values ('zs','zs123',10,100,'test',1);
#update mysql_users set transaction_persistent=1 where username='zs';
load mysql users to runtime;
save mysql users to disk;
#select * from mysql_users\G

验证登录
./mysql -uzs -pzs123 -h 192.168.10.140 -P 6033
show slave hosts;
select @@hostname;
登录到数据库master上,注:对外端口号是6033

配置读写分离策略

配置读写分离策略使用mysql_query_rules表。表中match_pattern字段代表设置的规则,destination_hostgroup字段代表默认指定的分组,apply代表真正执行应用规则

规则设定:
所有以select开头的语句全部分配到编号20的读组中
select for update会产生写锁,对数据查询时效性要求高,分配到编号10的写组中
其他所有操作都默认路由到写组中

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 * from mysql_query_rules\G
测试读写分离

使用ProxySQL业务端口6033进行连接
./mysql -uzs -pzs123 -h 192.168.10.140 -P 6033

监控查询状态
登录管理端口

./mysql -uadmin -padmin -h 127.0.0.1 -P 6032
select * from stats_mysql_query_digest;
select hostgroup,schemaname,username,digest_text from stats_mysql_query_digest;
select hostgroup,schemaname,username,digest_text,last_seen from stats_mysql_query_digest order by last_seen desc limit 6;

注:经测试^SELECT.*FOR UPDATE$并不会转发到10组,原因未知
^SELECT会匹配所有select语句,优先级最高,精确匹配也不会起作用

生产环境不建议将所有select请求都转发到只读从节点
正确的方式是,配置默认所有流量都到master,然后通过stats_mysql_query_digest找到最消耗资源的select语句,然后精准的通过mysql_query_rules转发到只读节点
找出最消耗资源的前5条select语句
基于总执行时间:

SELECT digest,SUBSTR(digest_text,0,25),count_star,sum_time FROM stats_mysql_query_digest WHERE digest_text LIKE 'SELECT%' ORDER BY sum_time DESC LIMIT 5;

基于最大执行时间:
1秒=1000毫秒=1000000微秒,输出结果为微秒

SELECT digest,SUBSTR(digest_text,0,25),count_star,sum_time,sum_time/count_star avg_time, min_time, max_time FROM stats_mysql_query_digest WHERE digest_text LIKE 'SELECT%' ORDER BY max_time DESC LIMIT 5;

基于数量:

SELECT digest,SUBSTR(digest_text,0,25),count_star,sum_time FROM stats_mysql_query_digest WHERE digest_text LIKE 'SELECT%' ORDER BY count_star DESC LIMIT 5;

找出总执行时间最长且最小执行时间大于1ms的前5

SELECT digest,SUBSTR(digest_text,0,20),count_star,sum_time,sum_time/count_star avg_time, min_time, max_time FROM stats_mysql_query_digest WHERE digest_text LIKE 'SELECT%' AND min_time > 1000 ORDER BY sum_time DESC LIMIT 5;
##
SELECT digest,SUBSTR(digest_text,0,25),count_star,sum_time,sum_time/count_star avg_time, ROUND(sum_time*100.00/(SELECT SUM(sum_time) FROM stats_mysql_query_digest),3) pct FROM stats_mysql_query_digest WHERE digest_text LIKE 'SELECT%' AND sum_time/count_star > 1000000 ORDER BY sum_time DESC LIMIT 5;
##
SELECT digest,SUBSTR(digest_text,0,25),count_star,sum_time,sum_time/count_star avg_time, ROUND(sum_time*100.00/(SELECT SUM(sum_time) FROM stats_mysql_query_digest WHERE digest_text LIKE 'SELECT%'),3) pct FROM stats_mysql_query_digest WHERE digest_text LIKE 'SELECT%' AND sum_time/count_star > 15000 ORDER BY sum_time DESC LIMIT 5;

检查这些查询是否能使用缓存,如果可以缓存则可以使用ProxySQL cache
将需要移到slave的select语句写到规则里面去

INSERT INTO mysql_query_rules (rule_id,active,digest,destination_hostgroup,apply) VALUES (1,1,'0x05C71647C92DBDA7',20,1);

将所有select count(*)转发到slave

INSERT INTO mysql_query_rules (rule_id,active,match_digest,destination_hostgroup,apply) VALUES (1,1,'^SELECT COUNT\(\*\)',20,1);
调整后端服务器权重

读写分离设置成功后,可以调整权重,让某台服务器承受更多的读操作

修改mysql_servers表中字段weigh的值
updata mysql_servers set weight=10 where hostname='192.168.10.130';
load mysql servers to runtime;
save mysql servers to disk;

查询缓存

使用digest作为匹配条件,设置cache_ttl,过期时间为2s(2000ms)

在只有一个后端 MySQL 时,也可使用缓存功能,仅需配置mysql_servers 、mysql_users 、mysql_query_rules 三张表即可

每个查询缓存记录的key是根据username + schemaname +SQL做hash运算出来的
这里的SQL是完整的包含参数SQL语句,而非参数化后的语句,如果SQL语句进行了重写,则使用重写后的完整的SQL语句参与hash运算
即相同digest的语句只要参数不相同,会分别缓存

找到需要缓存的语句对应的digest
SELECT count_star,sum_time,hostgroup,digest,digest_text FROM stats_mysql_query_digest_reset ORDER BY sum_time DESC;
添加规则
INSERT INTO mysql_query_rules (rule_id,active,digest,cache_ttl,apply) VALUES (5,1,'0xE8930CB2CC9E68D7',2000,1);
保存
LOAD MYSQL QUERY RULES TO RUNTIME;SAVE MYSQL QUERY RULES TO DISK;
查看效果
SELECT count_star,sum_time,hostgroup,digest,digest_text FROM stats_mysql_query_digest ORDER BY sum_time DESC;
若发现hostgroup值为-1,则说明返回的是缓存中的数据
注,语法中的大小写不一样,对应的digest编号是不一样的

指标数据

SELECT * FROM stats_mysql_global WHERE Variable_Name LIKE 'Query_Cache%';
Query_Cache_Memory_bytes,存储的结果集的总大小,不包括元数据
Query_Cache_count_GET,针对查询缓存执行的GET请求总数
Query_Cache_count_GET_OK,针对查询缓存执行成功的GET请求总数,结果集存在且未过期
Query_Cache_count_SET,插入到缓存的次数,过期更新缓存的次数
Query_Cache_bytes_IN,写入缓存的数据量
Query_Cache_bytes_OUT,读出缓存的数据量
Query_Cache_Purged,清除的条目数
Query_Cache_Entries,查询缓存当前条目数

调整缓存最大使用内存大小

查看当前大小
SHOW VARIABLES LIKE 'mysql-query_cache%';
mysql-query_cache_size_MB=256
调整大小为128MB
SET mysql-query_cache_size_MB=128;
LOAD MYSQL VARIABLES TO RUNTIME;
save mysql variables to disk;
相关参数
mysql-threshold_resultset_size = 4194304 (4MB)
定义可缓冲的最大结果集大小

查询缓存的限制:
1)只能使用cache_ttl使查询缓存失效
2)没有清空查询缓存的命令
3)mysql-query_cache_size_MB不是严格限制,只会触发自动消除过期条目
4)查询缓存不支持预准备语句

ProxySQL文档要点
全局变量

管理变量,控制管理界面行为,以admin-开头
mysql变量,控制代理的MySQL功能,以mysql-开头
修改命令:
UPDATE global_variables SET variable_value=1900 WHERE variable_name='admin-refresh_interval'; 或者
SET admin-refresh_interval = 1700;
显示所有全局变量
show variables;
设置admin变量后,需要执行 LOAD ADMIN VARIABLES TO RUNTIME;save admin variables to disk;
设置mysql变量后,需要执行 LOAD MYSQL VARIABLES TO RUNTIME;save mysql variables to disk;

管理员变量
设置管理员帐号,默认只能本地登录,若需远程登录,需设置二级用户
admin-admin_credentials="admin:admin;radminuser:radminpass"
设置监听端口,可以是socket,不能动态设置
admin-mysql_ifaces='0.0.0.0:6032;/tmp/proxysql_admin.sock'
设置为true时,admin模块只读
admin-read_only=false
更新查询规则统计信息和命令计数器信息的刷新间隔,微秒
admin-refresh_interval=2000
查询用户
admin-stats_credentials=stats:stats
管理历史统计
历史统计信息默认刷新间隔为60秒
管理员界面
是否启用web服务器
admin-web_enabled=false
admin-web_port=6080
登录帐户使用admin-stats_credentials定义的用户
MySQL变量
mysql-client_found_rows=true 连接到后端时会设置客户端标志
mysql-commands_stats=true 启用每命令查询统计信息
mysql-connect_retries_delay=1 连接重试延迟,毫秒
mysql-connect_retries_on_failure=10 失败重试次数,超过后会返回错误信息
mysql-default_sql_mode 设置默认sql_mode,需与后端一致
mysql-interfaces='0.0.0.0:6033' 业务端口,支持socket
mysql-interfaces='0.0.0.0:6033;/tmp/proxysql_admin.sock'
mysql-max_connections=2048 可以处理的最大客户端连接数
mysql-monitor_username=monitor
mysql-monitor_password=monitor123
Monitor模块连接后端的帐号,只需要usage权限(ping和检查read_only),若要监视复制滞后,还需要replication client权限
mysql-server_version=5.5.30 代理将使用该服务器版本响应客户端,而无论后端服务器版本为多少

配置后端服务器

mysql_servers表中max_replication_lag仅适用于slave,控制是否检查复制滞后,当值为0时,不会进行检查,单位为秒
同一台服务器可以放到不同的组中
在后端服务器启用压缩,设置compression=1
正常禁用后端服务器,设置status值为OFFLINE_SOFT,仍将使用活动事务和连接,但不会向该节点发送新流量
立即禁用后端服务器,设置status值为OFFLINE_HARD,所有当前流量将立即终止,并不会发送新流量
重新启用离线、禁用的后端服务器,设置status值为ONLINE

配置用户

mysql_users表
ProxySQL支持散列密码,在生产环境,更应该只使用散列密码而不是使用明文
default_hostgroup设置用户默认的主机组
max_connections最大连接数
transaction_persistent,此值为1时即为该用户启动事务,会禁用跨主机组的路由

密码管理

密码可存储为纯文本和哈希密码
ProxySQL散列密码与MySQL服务器中密码格式一致,以*开头
在MySQL服务器是运行select password('password')复制粘贴结果
admin-hash_passwords=true 支持散列密码,在runtime态将以散列方式存储
SAVE MYSQL USERS FROM RUNTIME; 执行该命令后,内存中将变成散列而不再是明文

调度

类似于cron的实现,具有毫秒级的粒度

允许ProxySQL定期运行自定义脚本以实现多种用途
SHOW TABLES LIKE '%scheduler%';
配置的地方在scheduler表,字段有:
id,调度程序唯一标识符
active,为1时处于活动状态
interval_ms,作用启动频率,毫秒,最小为100毫秒
filename,要执行的可执行文件完整路径
arg1~arg5,传递给作用的参数
comment,注释
调度程序实现调用fork()然后执行execve(),若失败,则返回错误报告给错误日志

load scheduler to runtime;save scheduler to disk;

查询日志

ProxySQL可以记录通过的查询。使用查询规则配置日志记录,可以控制记录的粒度

1、设置全局日志

SET mysql-eventslog_filename='queries.log';
LOAD MYSQL VARIABLES TO RUNTIME;
SAVE MYSQL VARIABLES TO DISK;

2、配置记录规则
记录所有日志:

INSERT INTO mysql_query_rules (rule_id, active, match_digest, log,apply) VALUES (1,1,'.',1,0);
#INSERT INTO mysql_query_rules (rule_id, active, username, match_pattern, log,apply) VALUES (1, 1, 'Bob', '.', 1, 0);仅记录用户Bob的记录
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;

3、查看日志

日志文件位置:/var/lib/proxysql/queries.log.00000001
另,ProxySQL日志位置:/var/lib/proxysql/proxysql.log SQLite数据、PID文件也位于该文件夹下面
日志查看方式:
./tools/eventslog_reader_sample /var/lib/proxysql/queries.log.00000001
记录内容为thread_id/username/schemaname/client/HID/server/starttime/endtime/duration/digest 实际用处不大
工具地址:https://github.com/sysown/proxysql/tree/v1.4.4/tools
main库

mysql_query_rules表
rule_id,规则唯一ID,规则按rule_id顺序处理
username,匹配用户username过滤条件,非NULL时,仅当使用正确的用户建立连接时,查询才会匹配
schemaname,在mariadb/mysql中schemaname等效于dbname

flagIN、flagOUT、apply,定义规则链
开始时从flagIN为0的开始,当匹配时,检查flagOUT,若flagOUT非空,则查询会标记为flagOUT的标记,
如果flagOUT与flagIN不同,则会忽略当前链,继续找其他的链
如果flagOUT与flagIN匹配,会重新评估,这会一直进行,直到没有匹配的规则,或者遇到apply设置为1的规则
apply设置为1时,意味着这是最后一条规则

digest,使用stats_mysql_query_digest返回的特定摘要匹配查询
match_pattern,与查询文本匹配的正则表达式
negate_match_pattern,设置为1时,相当于对match_pattern或match_digest做NOT运算
re_modifiers,控制重写引擎的行为,当为CASELESS时,匹配不区分大小写
destination_hostgroup,将匹配查询路由到指定主机组
cache_ttl,缓存查询结果的毫秒数
apply,当设置为1时,在匹配和处理此规则后,不再评估进一步的查询,不会评估mysql_query_rules_fast_routing规则

首先会检查 flagIN=0 的规则,以rule_id的顺序;如果都没匹配上,则走这个用户的 default_hostgroup
当匹配一条规则后,会检查 flagOUT
如果不为NULL,并且 flagIN != flagOUT ,则进入以flagIN为上一个flagOUT值的新规则链
如果不为NULL,并且 flagIN = flagOUT,则应用这条规则
如果为NULL,或者 apply=1,则结束,应用这条规则
如果最终没有匹配到,则找到这个用户的 default_hostgroup
stats库

stats_mysql_query_rules,匹配查询规则的次数
stats_mysql_commands_counters,计算每种类型的SQL执行次数
stats_mysql_processlist,模拟show processlist命令的结果集
stats_mysql_connection_pool,连接池使用情况统计
stats_mysql_query_digest,查询概况统计
stats_mysql_query_digest_reset,查询它会自动将内部统计信息重置
stats_mysql_global,全局统计信息,如查询总数、成功连接总数等

其他

终止连接
查询连接
select * from stats_mysql_processlist
终止连接
kill connection 1

对MySQL 8的支持
从MySQL8.0.4开始,默认认证插件由mysql_native_password变更为caching_sha2_password,ProxySQL不支持caching_sha2_password,所以,在启动MySQL时,要配置使用mysql_native_password
ProxySQL不接受来自客户端的使用caching_sha2_password的连接。可以在my.cnf中[mysql]或[client]中添加default-auth=mysql_native_password,或者使用命令行,如
mysql --default-auth=mysql_native_password -h 127.0.0.1 -P6032 -u admin -padmin

MySQL架构设计中的经验:
1)根据公司现有业务设计合理架构
2)选择成熟架构方案
3)因地制宜,根据实际设备情况做出选择
4)考虑方案的可行性
5)越简单越好,越适合公司越好

附:/etc/proxysql.cnf文件,使用时,需要删除 /var/lib/proxysql 目录下*.db 文件

 

转载请注明:轻风博客 » MySQL王者晋级之路(八)ProxySQL

喜欢 (0)or分享 (0)