Mysql ProxySQL的学习

192.168.88.129 安装proxysql

192.168.88.130 mysql主库

192.168.88.131 mysql从库

192.168.88.132 mysql从库

一、环境搭建

1、 下载安装

下载 wget https://github.com/sysown/proxysql/releases/download/v2.0.8/proxysql-2.0.8-1-centos7.x86_64.rpm

 yum localinstall proxysql-2.0.8-1-centos7.x86_64.rpm 
 
##查看安装的路径
rpm -ql proxysql
 
启动proxysql
systemctl start proxysql

 2、proxysql操作

proxysql有两个端口号
6032是ProxySQL的管理端口号， ProxySQL的用户名和密码都是默认的 admin
6033是对外服务的端口号

###连接proxysql的管理后端
mysql -uadmin -padmin -h 127.0.0.1 -P 6032

###proxysql的命令行跟mysql差不多
(admin@127.0.0.1) [(none)]> show databases;
+-----+---------------+-------------------------------------+
| seq | name          | file                                |
+-----+---------------+-------------------------------------+
| 0   | main          |                                     |
| 2   | disk          | /var/lib/proxysql/proxysql.db       |
| 3   | stats         |                                     |
| 4   | monitor       |                                     |
| 5   | stats_history | /var/lib/proxysql/proxysql_stats.db |
+-----+---------------+-------------------------------------+

    main: 内存配置数据库，即 MEMORY，表里存放后端 db 实例、用户验证、路由规则等信息
    disk ：持久化的磁盘的配置 
    stats： 统计信息的汇总  
    monitor：一些监控的收集信息，比如数据库的健康状态等 
    stats_history: 这个库是 ProxySQL 收集的有关其内部功能的历史指标

    注： 表名以 runtime_开头的表示 ProxySQL 当前运行的配置内容，不能通过 DML 语句修改。
    只能修改对应的不以 runtime 开头的表，然后 “LOAD” 使其生效，“SAVE” 使其存到硬盘以供下次重启加载。

    main库下的主要表： 
    mysql_servers: 后端可以连接 MySQL 服务器的列表 
    mysql_users:   配置后端数据库的账号和监控的账号。 
    mysql_query_rules: 指定 Query 路由到后端不同服务器的规则列表。

    ##最常用的同步到缓存和磁盘的命令
    LOAD MYSQL USERS TO RUNTIME;  将内存数据库中的配置加载到 runtime 数据结构
    SAVE MYSQL USERS TO DISK;   将内存数据库中的 MySQL 用户持久化到磁盘数据库中。
    LOAD MYSQL SERVERS TO RUNTIME;  将 MySQL server 从内存数据库中加载到 runtime。
    SAVE MYSQL SERVERS TO DISK;   从内存数据库中将 MySQL server 持久化到磁盘数据库中。
    LOAD MYSQL QUERY RULES TO RUNTIME;  将 MySQL query rules 从内存数据库加载到 runtime 数据结构。
    SAVE MYSQL QUERY RULES TO DISK;  将 MySQL query rules 从内存数据库中持久化到磁盘数据库中。
    LOAD MYSQL VARIABLES TO RUNTIME;  将 MySQL variables 从内存数据库加载到 runtime 数据结构。
    SAVE MYSQL VARIABLES TO DISK;   将 MySQL variables 从内存数据库中持久化到磁盘数据库中。
    LOAD ADMIN VARIABLES TO RUNTIME;   将 admin variables 从内存数据库加载到 runtime 数据结构。
    SAVE ADMIN VARIABLES TO DISK;  将 admin variables 从内存数据库中持久化到磁盘数据库。

二、在mysql中配置 ProxySQL 所需账户

    在 Master (192.168.88.130) 的MySQL 上创建 ProxySQL 的监控账户和对外访问账户
	##配置 ProxySQL 所需账户
	create user 'monitor'@'%' identified with mysql_native_password by '123456';
	grant all privileges on *.* to 'monitor'@'%' with grant option;
 
	#proxysql 的对外访问账户
	create user 'proxysql'@'%' identified with mysql_native_password by '123456';
	grant all privileges on *.* to 'proxysql'@'%' with grant option;

 三、proxysql配置

1、配置集群组 

INSERT INTO main.mysql_replication_hostgroups (writer_hostgroup,reader_hostgroup,comment) VALUES (10,20,'test_proxysql');
		load mysql servers to runtime;
		save mysql servers to disk;
 
写组定义与10，读组定义为20
 
ProxySQL 会根据mysql的read_only 的取值将服务器进行分组。 read_only=0 的mysql，master被分到编号为10的写组，read_only=1 的server，slave则被分到编号20的读组

 ProxySQL 会根据mysql的read_only 的取值将服务器进行分组。 read_only=0 的mysql，master被分到编号为10的写组，read_only=1 的server，slave则被分到编号20的读组

2、添加主从服务器节点

insert into main.mysql_servers(hostgroup_id,hostname,port) values (10,'192.168.88.130',3306);
		insert into main.mysql_servers(hostgroup_id,hostname,port) values (20,'192.168.88.131',3306);
		insert into main.mysql_servers(hostgroup_id,hostname,port) values (20,'192.168.88.132',3306);
		load mysql servers to runtime;
		save mysql servers to disk;

3、配置proxySql监控mysql的账号

set mysql-monitor_username='monitor';
set mysql-monitor_password='123456';
##或者在main库中操作
UPDATE global_variables SET variable_value='monitor' WHERE variable_name='mysql-monitor_username';
UPDATE global_variables SET variable_value='123456' WHERE variable_name='mysql-monitor_password';
 
load mysql variables to runtime;
save mysql variables to disk;

4、 判断是否连接成功mysql

select * from monitor.mysql_server_connect_log; 
connect_error的信息为null则表示成功

5、 为 ProxySQL 配置对外访问账号

也就是我们的app应用程序访问ProxySQL的账户密码，
SQL 请求所使用的用户配置，都需要在 MySQL 节点创建上
insert into main.mysql_users (username,password,default_hostgroup) values ('proxysql','123456',10);
load mysql users to runtime;
save mysql users to disk;
 
mysql_users 表有不少字段，最主要的三个字段username,password,default_hostgroup 
username: 前端链接ProxySQL ，以及ProxySQL 将SQL 语句路由给MySQL所使用的的用户名 
password：用户名对应的密码，。可以是明文密码，也可以是hash密码。如果想使用hash密码，可以先在某个MySQL节点上执行select password(PASSWORD)，然后将加密结果复制到该字段。 
default_hostgroup：该用户名默认的路由目标。例如，指定root用户的该字段值为10时，则使用 proxysql 用户发送的SQL语句默认情况下将路由到hostgroup_id=10 组中的某个节点。 
我这里 hostgroup_id = 10的组中只有一个节点就是 master : 192.168.88.130
 
测试登录的话要用6033端口
mysql -uproxysql -p123456 -P 6033 -h127.0.0.1

6、 配置路由规则（最简单的读写分离）

路由相关的表 mysql_query_rules 和 mysql_query_rules_fast_routing
介绍一下改表mysql_query_rules的几个字段： 
active：是否启用这个规则，1表示启用，0表示禁用 
match_pattern 字段就是代表设置规则 
destination_hostgroup 字段代表默认指定的分组， 
apply 代表真正执行应用规则。
		
创建路由规则 
1、把所有以select 开头的语句全部分配到读组中，读组编号是20 
2、把 select .. for update 语句，这是一个特殊的select语句，会产生一个写锁(排他锁)，把他分到编号为10 的写组中，其他所有操作都会默认路由到写组中
insert into main.mysql_query_rules(rule_id,active,match_pattern,destination_hostgroup,apply) values (1,1,'^select.*for update$',10,1);
 
insert into main.mysql_query_rules(rule_id,active,match_pattern,destination_hostgroup,apply) values (2,1,'^select',20,1);
 
select … for update规则的rule_id必须要小于普通的select规则的rule_id，因为ProxySQL是根据rule_id的顺序进行规则匹配的。
 
load mysql query rules to runtime;
save mysql query rules to disk;
 
 
测试 ^select是否生效
mysql -uproxysql -p123456 -P 6033 -h 127.0.0.1 -e "select @@server_id;"
查看server_id是否都是从库的server_id
 
测试 ^select.*for update$ 是否生效，可以在proxysql中执行，
select * from raft_log for update; （任意一个数据库，任意一张表）
然后到stats_mysql_query_digest表查看语句对应的是哪个hostgroup
select hostgroup,schemaname,username,digest_text,count_star from  main.stats_mysql_query_digest;

7、读写分离另外一种操作

根据stats_mysql_query_digest表中的digest 加入到 mysql_query_rules中，只要是digest对应的sql就会走从库
 
查找按总执行时间排序的前 5 个查询，并且最短执行时间至少为 1 毫秒
	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;
 
然后把digest = 0xDA65260DF35B8D13 插入到mysql_query_rules中
	INSERT INTO mysql_query_rules (rule_id,active,digest,destination_hostgroup,apply) VALUES
	(5, 1,'0xDA65260DF35B8D13', 20, 1);
 
清空mysql_query_rules表执行
SELECT 1 FROM stats_mysql_query_digest_reset LIMIT 1

参考博文

https://www.cnblogs.com/keme/p/12290977.html 学习