devbox
你好赵伟
这个屌丝很懒，什么也没留下！
关注作者
热门标签
热门文章
当前位置:   article > 正文

Mysql ProxySQL的学习_windows下使用mysql-proxy监控sql语句是否执行成功

作者：你好赵伟 | 2024-07-27 11:13:16

赞

踩

windows下使用mysql-proxy监控sql语句是否执行成功

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

  1.  yum localinstall proxysql-2.0.8-1-centos7.x86_64.rpm 
  2.  
  3. ##查看安装的路径
  4. rpm -ql proxysql
  5.  
  6. 启动proxysql
  7. systemctl start proxysql

 2、proxysql操作

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

  1. ###连接proxysql的管理后端
  2. mysql -uadmin -padmin -h 127.0.0.1 -P 6032
  1. ###proxysql的命令行跟mysql差不多
  2. (admin@127.0.0.1) [(none)]> show databases;
  3. +-----+---------------+-------------------------------------+
  4. | seq | name          | file                                |
  5. +-----+---------------+-------------------------------------+
  6. | 0   | main          |                                     |
  7. | 2   | disk          | /var/lib/proxysql/proxysql.db       |
  8. | 3   | stats         |                                     |
  9. | 4   | monitor       |                                     |
  10. | 5   | stats_history | /var/lib/proxysql/proxysql_stats.db |
  11. +-----+---------------+-------------------------------------+

    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 所需账户

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

 三、proxysql配置

1、配置集群组 

  1. INSERT INTO main.mysql_replication_hostgroups (writer_hostgroup,reader_hostgroup,comment) VALUES (10,20,'test_proxysql');
  2. 		load mysql servers to runtime;
  3. 		save mysql servers to disk;
  4.  
  5. 写组定义与10，读组定义为20
  6.  
  7. 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、添加主从服务器节点

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

3、配置proxySql监控mysql的账号

  1. set mysql-monitor_username='monitor';
  2. set mysql-monitor_password='123456';
  3. ##或者在main库中操作
  4. UPDATE global_variables SET variable_value='monitor' WHERE variable_name='mysql-monitor_username';
  5. UPDATE global_variables SET variable_value='123456' WHERE variable_name='mysql-monitor_password';
  6.  
  7. load mysql variables to runtime;
  8. save mysql variables to disk;

4、 判断是否连接成功mysql

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

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

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

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

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

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

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

参考博文

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

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/你好赵伟/article/detail/889961?site
推荐阅读
相关标签

Copyright © 2003-2013 www.wpsshop.cn 版权所有，并保留所有权利。

  

闽ICP备14008679号