ProxySQL
ProxySQL 介绍
ProxySQL： MySQL中间件
两个版本：官方版和percona版，percona版是基于官方版基础上修改
C++语言开发，轻量级但性能优异，支持处理千亿级数据
具有中间件所需的绝大多数功能，包括：
- 多种方式的读/写分离
- 定制基于用户、基于schema、基于语句的规则对SQL语句进行路由
- 缓存查询结果
- 后端节点监控
官方站点：https://proxysql.com/
官方手册：https://github.com/sysown/proxysql/wiki
ProxySQL 安装
基于YUM仓库安装
cat <<EOF | tee /etc/yum.repos.d/proxysql.repo
[proxysql_repo]
name= ProxySQL YUM repository
baseurl=http://repo.proxysql.com/ProxySQL/proxysql-1.4.x/centos/\$releasever
gpgcheck=1
gpgkey=http://repo.proxysql.com/ProxySQL/repo_pub_key
EOF
#基于RPM下载安装：https://github.com/sysown/proxysql/releases
yum install proxysql
ProxySQL组成
- 服务脚本：/etc/init.d/proxysql
- 配置文件：/etc/proxysql.cnf
- 主程序：/usr/bin/proxysql
- 基于SQLITE的数据库文件：/var/lib/proxysql/
启动ProxySQL：
service proxysql start
启动后会监听两个默认端口
- 6032：ProxySQL的管理端口
- 6033：ProxySQL对外提供服务的端口
连接ProxySQL的管理端口
使用mysql客户端连接到ProxySQL的管理接口6032，默认管理员用户和密码都是admin：
mysql -uadmin -padmin -P6032 -h127.0.0.1
数据库说明：
- main 是默认的"数据库"名，表里存放后端db实例、用户验证、路由规则等信息。
- 表名以 runtime开头的表示proxysql当前运行的配置内容，不能通过dml语句修改，只能修改对应的不以 runtime_ 开头的（在内存）里的表，然后 LOAD 使其生效， SAVE 使其存到硬盘以供下次重启加载
- disk 是持久化到硬盘的配置，sqlite数据文件
- stats 是proxysql运行抓取的统计信息，包括到后端各命令的执行次数、流量、processlist、查询种类汇总/执行时间，等等
- monitor 库存储 monitor 模块收集的信息，主要是对后端db的健康/延迟检查
说明：
- 在main和monitor数据库中的表， runtime开头的是运行时的配置，不能修改，只能修改非runtime表
- 修改后必须执行LOAD … TO RUNTIME才能加载到RUNTIME生效
- 执行save … to disk 才将配置持久化保存到磁盘，即保存在proxysql.db文件中
- global_variables 有许多变量可以设置，其中就包括监听的端口、管理账号等
- 参考: https://github.com/sysown/proxysql/wiki/Global-variables
实战案例：利用 ProxySQL 实现读写分离
-
环境准备：
准备三台主机，一台ProxySQL服务器:192.168.8.7，另外两台主机实现主从复制192.168.8.17,27
注意：slave节点需要设置read_only=1 -
安装ProxySQL，并向ProxySQL中添加MySQL节点，以下操作不需要use main也可成功
MySQL> show tables; MySQL > select * from sqlite_master where name='mysql_servers'\G MySQL > select * from mysql_servers; MySQL > insert into mysql_servers(hostgroup_id,hostname,port) values(10,'192.168.8.17',3306); MySQL > insert into mysql_servers(hostgroup_id,hostname,port) values(10,'192.168.8.27',3306); MySQL > load mysql servers to runtime; MySQL > save mysql servers to disk; -
添加监控后端节点的用户，连接每个节点的read_only值来自动调整主从节点是属于读组还是写组
#在master上执行
MySQL> grant replication client on *.* to monitor@'192.168.8.%' identified by 'ayaka';
#ProxySQL上配置监控
MySQL [(none)]> set mysql-monitor_username='monitor';
MySQL [(none)]> set mysql-monitor_password='ayaka';
#加载到RUNTIME，并保存到disk
MySQL [(none)]> load mysql variables to runtime;
MySQL [(none)]> save mysql variables to disk;
- 查看监控
监控模块的指标保存在monitor库的log表中
#查看监控连接是否正常的 (对connect指标的监控），如果connect_error的结果为NULL则表示正常
MySQL> select * from mysql_server_connect_log;
#查看监控心跳信息 (对ping指标的监控)：
MySQL> select * from mysql_server_ping_log;
- 设置分组信息
需要修改的是main库中的mysql_replication_hostgroups表，该表有3个字段：
writer_hostgroup，reader_hostgroup，comment, 指定写组的id为10，读组的id为20
MySQL> insert into mysql_replication_hostgroups values(10,20,"test");
#将mysql_replication_hostgroups表的修改加载到RUNTIME生效
MySQL> load mysql servers to runtime;
MySQL> save mysql servers to disk;
#Monitor模块监控后端的read_only值，按照read_only的值将节点自动移动到读/写组
MySQL> select hostgroup_id,hostname,port,status,weight from mysql_servers;
+--------------+--------------+------+--------+--------+
| hostgroup_id | hostname | port | status | weight |
+--------------+--------------+------+--------+--------+
| 10 | 192.168.8.17 | 3306 | ONLINE | 1 |
| 20 | 192.168.8.27 | 3306 | ONLINE | 1 |
- 配置访问数据库的SQL 用户
#在master节点上创建访问用户
MySQL> grant all on *.* to sqluser@'192.168.8.%' identified by 'ayaka';
#在ProxySQL配置，将用户sqluser添加到mysql_users表中， default_hostgroup默认组设置为写组10，当读写分离的路由规则不符合时，会访问默认组的数据库
MySQL> insert into mysql_users(username,password,default_hostgroup)
values('sqluser','ayaka',10);
MySQL> load mysql users to runtime;
MySQL> save mysql users to disk;
#使用sqluser用户测试是否能路由到默认的10写组实现读、写数据
mysql -usqluser -payaka -P6033 -h127.0.0.1 -e 'select @@server_id'
mysql -usqluser -payaka -P6033 -h127.0.0.1 -e 'create database testdb'
mysql -usqluser -payaka testdb -P6033 -h127.0.0.1 -e 'create table t(id int)'
- 在proxysql上配置路由规则，实现读写分离
与规则有关的表：mysql_query_rules和mysql_query_rules_fast_routing，后者是前者的扩展表，1.4.7之后支持
插入路由规则：将select语句分离到20的读组，select语句中有一个特殊语句SELECT...FOR UPDATE它会申请写锁，应路由到10的写组
MySQL> insert into mysql_query_rules
(rule_id,active,match_digest,destination_hostgroup,apply)VALUES
(1,1,'^SELECT.*FOR UPDATE$',10,1),(2,1,'^SELECT',20,1);
MySQL> load mysql query rules to runtime;
MySQL> save mysql query rules to disk;
#注意：因ProxySQL根据rule_id顺序进行规则匹配，select ... for update规则的rule_id必须要小于普通的select规则的rule_id
- 测试ProxySQL
#读操作是否路由给20的读组
mysql -usqluser -payaka -P6033 -h127.0.0.1 -e 'select @@server_id'
#测试写操作，以事务方式进行测试
mysql -usqluser -payaka -P6033 -h127.0.0.1 \
-e 'start transaction;select @@server_id;commit;select @@server_id'
mysql -usqluser -payaka -P6033 -h127.0.0.1 -e 'insert testdb.t values (1)'
mysql -usqluser -payaka -P6033 -h127.0.0.1 -e 'select id from testdb.t'
#路由的信息：查询stats库中的stats_mysql_query_digest表
MySQL > SELECT hostgroup hg,sum_time, count_star, digest_text FROM
stats_mysql_query_digest ORDER BY sum_time DESC;
#测试读操作是否路由给20的读组
mysql -usqluser -payaka -P6033 -h127.0.0.1 -e 'select @@server_id'
#测试写操作，以事务方式进行测试
mysql -usqluser -payaka -P6033 -h127.0.0.1 \
-e 'start transaction;select @@server_id;commit;select @@server_id'
mysql -usqluser -payaka -P6033 -h127.0.0.1 -e 'insert testdb.t values (1)'
mysql -usqluser -payaka -P6033 -h127.0.0.1 -e 'select id from testdb.t'
#路由的信息：查询stats库中的stats_mysql_query_digest表
MySQL > SELECT hostgroup hg,sum_time, count_star, digest_text FROM
stats_mysql_query_digest ORDER BY sum_time DESC;