1.ProxySQL 介绍
|
1 |
ProxySQL 是基于 MySQL 的一款开源的中间件的产品，是一个灵活的 MySQL 代理层，可以实现读写分离，支持 Query 路由功能，支持动态指定某个 SQL 进行缓存，支持动态加载（无需重启 ProxySQL 服务），故障切换和一些 SQL 的过滤功能。 |
2、环境准备
|
1 2 |
ProxySQL 2.0.x 版本支持 MGR 注：我的 ProxySQL 和 主库放在一起的，我已近配置好了主从复制状态。这篇只讲ProxySQL安装与高可用配置 |
3.ProxySQL 安装与启动
下载
|
1 |
wget https://github.com/sysown/proxysql/releases/download/v2.0.8/proxysql-2.0.8-1-centos7.x86_64.rpm |
安装 ProxySQL
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
rpm -ivh proxysql-2.0.8-1-centos7.x86_64.rpm 注：可能会遇到这个错误 错误：依赖检测失败： perl(DBD::mysql) 被 proxysql-2.0.8-1.x86_64 需要 perl(DBI) 被 proxysql-2.0.8-1.x86_64 需要 解决办法： yum -y install perl perl-devel libaio libaio-devel perl-Time-HiRes perl-DBD-MySQL yum -y install rsync perl l perl-Digest-MD5 # 查看相应安装文件路径，有哪些文件 [root@xjjh-test1 ruanjian]# rpm -ql proxysql /etc/logrotate.d/proxysql /etc/proxysql.cnf #ProxySQL 的配置文件 /etc/systemd/system/proxysql.service /usr/bin/proxysql /usr/share/proxysql/tools/proxysql_galera_checker.sh /usr/share/proxysql/tools/proxysql_galera_writer.pl |
ProxySQL Cluster集群配置：(让所有proxysql同步)
首先修改配置文件，重新初始化启动cluster集群
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 |
cp /etc/proxysql.cnf /etc/proxysql.cnf.bak 修改配置文件中的如下内容(直接复制覆盖这两项即可)： admin_variables= { admin_credentials="admin:admin;cluster_kevin:123456" # mysql_ifaces="127.0.0.1:6032;/tmp/proxysql_admin.sock" mysql_ifaces="0.0.0.0:6032" # refresh_interval=2000 # debug=true cluster_username="cluster_kevin" cluster_password="123456" cluster_check_interval_ms=200 cluster_check_status_frequency=100 cluster_mysql_query_rules_save_to_disk=true cluster_mysql_servers_save_to_disk=true cluster_mysql_users_save_to_disk=true cluster_proxysql_servers_save_to_disk=true cluster_mysql_query_rules_diffs_before_sync=3 cluster_mysql_servers_diffs_before_sync=3 cluster_mysql_users_diffs_before_sync=3 cluster_proxysql_servers_diffs_before_sync=3 } proxysql_servers = ( { hostname="192.168.6.121" port=6032 weight=1 comment="ProxySQL-node1" }, { hostname="192.168.6.122" port=6032 weight=1 comment="ProxySQL-node2" }, { hostname="192.168.6.123" port=6032 weight=1 comment="ProxySQL-node3" } ) |
这里要特别注意：
|
1 2 3 4 5 |
如果已存在"proxysql.db"文件(在/var/lib/proxysql目录下)，则ProxySQL服务只有在第一次启动时才会去读取proxysql.cnf文件并解析;后面启动会就不会读取proxysql.cnf文件了! 如果想要让proxysql.cnf文件里的配置在重启proxysql服务后生效(即想要让proxysql重启时读取并解析proxysql.cnf配置文件)，则需要先删除/var/lib/proxysql/proxysql.db数据库文件，然后再重启proxysql服务。这样就相当于初始化启动proxysql服务了，会再次生产一个纯净的proxysql.db数据库文件(如果之前配置了proxysql相关路由规则等，则就会被抹掉)。 所以你先要删除/var/lib/proxysql/proxysql.db数据库文件，然后再重启/启动proxysql服务。 |
启动 ProxySQL
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
systemctl start proxysql.service netstat -anlp | grep proxysql [root@xjjh-test1 ~]# netstat -anlp | grep proxysql tcp 0 0 0.0.0.0:6032 0.0.0.0:* LISTEN 12233/proxysql tcp 0 0 0.0.0.0:6033 0.0.0.0:* LISTEN 12233/proxysql 6032 是 ProxySQL的管理端口号 6033是对外服务的端口号 ProxySQL 的用户名和密码都是默认的 admin 查看 ProxySQL 的版本 proxysql --version |
管理员登录 ProxySQL
|
1 |
mysql -uadmin -padmin -h 127.0.0.1 -P 6032 --prompt='admin>' #建议用这个，好区分你在哪个应用里边 |
观察集群状况 （在三台节点上都可以查看）
|
1 |
select * from proxysql_servers; |
|
1 |
select * from stats_proxysql_servers_metrics; |
|
1 |
select hostname,port,comment,Uptime_s,last_check_ms from stats_proxysql_servers_metrics; |
|
1 |
select hostname,name,checksum,updated_at from stats_proxysql_servers_checksums; |
然后继续向下
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 |
mysql> 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 | +-----+---------------+-------------------------------------+ 5 rows in set (0.00 sec) 可见有五个库： main、disk、stats 、monitor 和 stats_history main: 内存配置数据库，即 MEMORY，表里存放后端 db 实例、用户验证、路由规则等信息。 main 库中有如下信息： mysql> show tables; +--------------------------------------------+ | tables | +--------------------------------------------+ | global_variables | | mysql_aws_aurora_hostgroups | | mysql_collations | | mysql_galera_hostgroups | | mysql_group_replication_hostgroups | | mysql_query_rules | | mysql_query_rules_fast_routing | | mysql_replication_hostgroups | | mysql_servers | | mysql_users | | proxysql_servers | | runtime_checksums_values | | runtime_global_variables | | runtime_mysql_aws_aurora_hostgroups | | runtime_mysql_galera_hostgroups | | runtime_mysql_group_replication_hostgroups | | runtime_mysql_query_rules | | runtime_mysql_query_rules_fast_routing | | runtime_mysql_replication_hostgroups | | runtime_mysql_servers | | runtime_mysql_users | | runtime_proxysql_servers | | runtime_scheduler | | scheduler | +--------------------------------------------+ 24 rows in set (0.00 sec) 注:表名以 runtime_开头的表示 ProxySQL 当前运行的配置内容，不能通过 DML 语句修改。只能修改对应的不以 runtime 开头的表，然后 “LOAD” 使其生效，“SAVE” 使其存到硬盘以供下次重启加载。 disk ：持久化的磁盘的配置 stats： 统计信息的汇总 monitor：一些监控的收集信息，比如数据库的健康状态等 stats_history: 这个库是 ProxySQL 收集的有关其内部功能的历史指标 库下的主要表： mysql_servers: 后端可以连接 MySQL 服务器的列表 mysql_users: 配置后端数据库的账号和监控的账号。 mysql_query_rules: 指定 Query 路由到后端不同服务器的规则列表。 备注：如果需要创建一个新用户marion，密码为123456，则可执行如下操作(这里只是介绍proxy添加用户测试，可创建可不创建) proxysql> update global_variables set variable_value='admin:admin;marion:123456' where variable_name='admin-admin_credentials'; proxysql> load admin variables to runtime; proxysql> save admin variables to disk; |
|
1 |
备注：备注：无论执行任何操作，都需要执行load *** to runtime（从memory加载到runtime）、save *** to disk（持久化到磁盘）这个下面会讲到。 |
4.ProxySQL结构图
|
1 2 3 4 5 6 |
整套配置系统分为三层：顶层为 RUNTIME ,中间层为 MEMORY , 底层也就是持久层 DISK 和 CONFIG FILE 。 RUNTIME ： 代表 ProxySQL 当前生效的正在使用的配置，无法直接修改这里的配置，必须要从下一层 “load” 进来。 MEMORY： MEMORY 层上面连接 RUNTIME 层，下面连接持久层。这层可以正常操作 ProxySQL 配置，随便修改，不会影响生产环境。修改一个配置一般都是现在 MEMORY 层完成的，确认正常之后在加载达到 RUNTIME 和 持久化的磁盘上。 DISK 和 CONFIG FILE：持久化配置信息，重启后内存中的配置信息会丢失，所需要将配置信息保留在磁盘中。重启时，可以从磁盘快速加载回来。 |
一般在内存那层修改 ,然后保存到运行系统(runtime)，然后再持久化保存到磁盘数据库系统
|
1 2 3 4 5 6 7 8 |
load xxx servers to runtime; save xxx servers to disk; disk and config file 持久化配置文件 disk -> 是sqlite3 数据库 ，默认位置是$DATADIR/proxysql.db(/var/lib/proxysql/proxysql.db) config file 是一个传统配置文件：一般不更改 在内存中动态更改配置，如果重启，没进行持久化, 则会丢失。 |
三则之间关系：
|
1 2 3 4 5 6 7 8 9 |
1.proxysql 启动时，首先去找/etc/proxysql.cnf 找到它的datadir,如果datadir下有proxysql.db 就加载proxysql.db的配置 2.如果启动proxysql时带有--init标志，会用/etc/proxsql.cnf的配置，把Runtime，disk全部初始化一下 3.在调用是调用--reload 会把/etc/proxysql.cnf 和disk 中配置进行合并。如果冲突需要用户干预。disk会覆盖config file。 关于传统的配置文件 传统配置文件默认路径为/etc/proxysql.cnf，也可以在二进制程序proxysql上使用-c或–config来手动指定配置文件。 默认情况下：几乎不需要手动去配置proxysql.cnf。端口号，管理proxysql用户密码，可以在这里修改 |
|
1 2 3 4 5 6 7 8 9 10 11 12 |
这里有几个最常用的命令：如何让修改的配置生效(runtime)，以及如何持久化到磁盘上(disk)。记住，只要不是加载到 runtime，修改的配置就不会生效。 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 从内存数据库中持久化到磁盘数据库。 |
5.配置 ProxySQL 所需账户(先配置mysql中的用户)
|
1 2 3 4 5 6 7 8 9 |
1.在 MGR主Master (192.168.6.122) 的MySQL (注意这里是登入mysql) 上创建 ProxySQL 的监控账户和对外访问账户 #创建proxysql的监控账户(monitor) grant all privileges on *.* to 'monitor'@'%' identified by '123456' with grant option; #创建proxysql的对外访问账户(proxysql) grant all privileges on *.* to 'proxysql'@'%' identified by '123456' with grant option; flush privileges; |
6.创建分组，配置主从分布信息
|
1 2 3 4 5 6 7 8 9 10 11 |
这个表 mysql_replication_hostgroup writer_hostgroup 和reader_hostgroup 写组和读组都要大于0且不能相同，我的环境下，写组定义与10，读组定义为20 <1>.创建组 mysql -uadmin -padmin -h 127.0.0.1 -P 6032 --prompt='admin>' insert into mysql_replication_hostgroups ( writer_hostgroup, reader_hostgroup, comment) values (10,20,'proxy'); load mysql servers to runtime; save mysql servers to disk; 确定三层配置系统是否都已近写入了数据 |
|
1 2 3 4 5 6 7 8 |
确定三层配置系统是否都已近写入了数据: select * from mysql_replication_hostgroups; #MEMORY select * from main.mysql_replication_hostgroups; #MEMORY select * from runtime_mysql_replication_hostgroups;#runtime select * from main.runtime_mysql_replication_hostgroups; #runtime select * from disk.runtime_mysql_replication_hostgroups;#disk 这个目测执行不成功，具体后面细究 |
|
1 2 3 |
ProxySQL 会根据server 的read_only 的取值将服务器进行分组。 read_only=0的server，被分到编号为10的写组， read_only=1的server，slave则被分到编号20的读组 |
8、添加主从服务器节点：
用到mysql_servers 这个表
|
1 2 3 4 5 6 7 8 9 10 11 |
mysql -uadmin -padmin -h 127.0.0.1 -P 6032 --prompt='admin>' insert into mysql_servers(hostgroup_id,hostname,port,weight,comment) values (10,'192.168.6.122',3306,2,'slave1'); insert into mysql_servers(hostgroup_id,hostname,port,weight,comment) values (20,'192.168.6.121',3306,1,'master'); insert into mysql_servers(hostgroup_id,hostname,port,weight,comment) values (20,'192.168.6.123',3306,3,'slave2'); 注：严格需要写明comment load mysql servers to runtime; save mysql servers to disk; |
查看：
|
1 |
select * from mysql_servers; |
|
1 |
添加了节点，三台机器都是online 状态 |
9.为proxySQL配置监控账号 (在proxySQL中设置)
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
注:这里的监控账号，mysql中的上面已经创建，这里是在proxysql中创建 mysql -uadmin -padmin -h 127.0.0.1 -P 6032 --prompt='admin>' set mysql-monitor_username='monitor'; set mysql-monitor_password='123456'; 上面这两句是修改变量的方式还可以在main库下面用sql语句方式修改 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'; 修改后，保存到runtime和disk load mysql variables to runtime; save mysql variables to disk; |
|
1 2 3 4 |
验证监控信息，ProxySQL 监控模块的指标都保存在monitor库的log表中。 以下是连接是否正常的监控，对connect指标的监控 ，在前面可能会有很 多connect_error，这是因为没有配置监控信息时的错误，配置后如果 connect_error的结果为NULL则表示正常 |
对心跳信息的监控(对ping 指标的监控)
|
1 |
select * from mysql_server_ping_log limit 10; |
看看read_only的日志监控
|
1 2 3 4 |
select * from mysql_server_read_only_log limit 10; Monitor 模块就会开始监控后端的read_only值，当监控到read_only值，就会按照read_only的值将某些节点自动移到读写组 一些监控的状态斗志在log相关，都在monitor库下面的 global_variables 变量。 |
10.为 ProxySQL 配置对外访问账号
|
1 2 3 4 5 6 7 8 9 |
SQL 请求所使用的用户配置，都需要在 MySQL 节点创建上. 在步骤4：配置ProxySQL 账户，我创建的对外访问账户是：用户：proxysql，密码：123456 注意：这里可以是明文密码，也可以是hash密码。如果想使用hash密码，可以先在某个MySQL节点上执行select password(PASSWORD)，然后将加密结果复制到该字段。 配置mysql_users 这个表 mysql -uadmin -padmin -h 127.0.0.1 -P 6032 --prompt='admin>' insert into mysql_users (username,password,default_hostgroup) values ('proxysql','123456',10); LOAD MYSQL USERS TO RUNTIME; save MYSQL USERS to disk; |
|
1 2 3 4 5 |
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.6.122 |
测试：(三台节点，任意一个节点，使用proxysql登录，创建数据库,看看三台节点是否都会被路由到，看看其他mysql节点是否同时创建)
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
我这边用121 slave节点 proxysql 用户登录proxysql,创建数据库,测试是否sql路由能默认到 hostgroup_id=10，它是一个写组（读写数据） mysql -uproxysql -p123456 -P 6033 -h 127.0.0.1 --prompt='proxysql>' show databases; +--------------------+ | Database | +--------------------+ | information_schema | | fms | | mysql | | nacos | | performance_schema | | shooter | | sys | +--------------------+ mysql> create database keme; Query OK, 1 row affected (0.00 sec) |
查看其他2台节点，是否创建keme数据库
11.配置读写分离策略：路由规则
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
这里我创建两个规则： 1、把所有以select 开头的语句全部分配到读组中，读组编号是20 2、把 select .. for update 语句，这是一个特殊的select语句，会产生一个写锁(排他锁)，把他分到编号为10 的写组中，其他所有操作都会默认路由到写组中。 mysql -uadmin -padmin -h 127.0.0.1 -P 6032 --prompt='admin>' #注意这里有个坑，如果你是原生sql语句,请用下面这2句匹配,匹配方式:match_pattern 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); #如果你是框架生成的SQL居多，请用下面这2句，匹配方式:match_digest insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply) VALUES (1,1,'^SELECT.*FOR UPDATE$',10,1); insert into mysql_query_rules(rule_id,active,match_digest,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的顺序进行规则匹配的。 |
关于匹配sql语句方式选择，请看大佬写的这篇 传送门
12.测试读写分离
<1> 使用proxysql账户登录并创建一张表，并插入几条数据
|
1 2 3 4 5 6 |
mysql -uproxysql -p123456 -P 6033 -h 127.0.0.1 --prompt='proxysql>' use shooter; insert into book (c1,c2) values (41,'赵丹'); select * from book; select * from book for update; |
如果想在 ProxySQL 中查看SQL请求路由信息stats_mysql_query_digest
|
1 2 3 4 5 6 7 8 |
通过proxysql 连接看看读写操作去了哪里，是否路由给了读组: 1121 R 1122 W 1123 R 登录管理账号： mysql -uadmin -padmin -h 127.0.0.1 -P 6032 --prompt='admin>' #查询sql请求路由信息 select hostgroup,schemaname,username,digest_text,count_star from stats_mysql_query_digest; |
|
1 2 |
可以看到，执行insert 和 select for update的时候 走的是122(10)的主Master机器 ,select 语句走的是 121(20)机器 (读写分离)。 上图中 count_start 统计sql 语句执行次数，可以分析哪些 sql ，频繁执行。 |
|
1 2 3 4 5 6 |
读写分离设置成功后，还可以调节点权重，比如让某台机器承受更多的读操作 例： update mysql_servers set weight=10 hostname='192.168.6.121'； load mysql servers to runtime; save mysql servers to disk; |
常用查询语句
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 |
1.查看主从分布表 select * from mysql_replication_hostgroups; admin>select * from mysql_replication_hostgroups; +------------------+------------------+------------+---------+ | writer_hostgroup | reader_hostgroup | check_type | comment | +------------------+------------------+------------+---------+ | 10 | 20 | read_only | proxy | +------------------+------------------+------------+---------+ 1 row in set (0.00 sec) 2.查看主从服务器连接状况表 admin>select * from mysql_servers; +--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ | hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment | +--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ | 20 | 192.168.6.121 | 3306 | 0 | ONLINE | 10 | 0 | 1000 | 0 | 0 | 0 | | | 10 | 192.168.6.122 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | | 20 | 192.168.6.123 | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | +--------------+---------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ 3 rows in set (0.00 sec) 3.统计各种SQL类型的执行次数和时间 select * from stats_mysql_commands_counters 4.查看连接后端MySQL的连接池信息 select * from stats_mysql_connection_pool; admin>select * from stats_mysql_connection_pool; +-----------+---------------+----------+--------+----------+----------+--------+---------+-------------+---------+-------------------+-----------------+-----------------+------------+ | hostgroup | srv_host | srv_port | status | ConnUsed | ConnFree | ConnOK | ConnERR | MaxConnUsed | Queries | Queries_GTID_sync | Bytes_data_sent | Bytes_data_recv | Latency_us | +-----------+---------------+----------+--------+----------+----------+--------+---------+-------------+---------+-------------------+-----------------+-----------------+------------+ | 10 | 192.168.6.122 | 3306 | ONLINE | 0 | 1 | 1 | 0 | 1 | 7 | 0 | 161 | 263 | 545 | | 20 | 192.168.6.121 | 3306 | ONLINE | 0 | 1 | 1 | 0 | 1 | 2 | 0 | 36 | 84 | 239 | | 20 | 192.168.6.123 | 3306 | ONLINE | 1 | 0 | 1 | 0 | 1 | 7 | 0 | 125 | 129 | 519 | | 20 | 192.168.6.122 | 3306 | ONLINE | 0 | 1 | 1 | 0 | 1 | 5 | 0 | 162 | 36 | 545 | +-----------+---------------+----------+--------+----------+----------+--------+---------+-------------+---------+-------------------+-----------------+-----------------+------------+ 4 rows in set (0.01 sec) 5.查看路由规则表： select rule_id,active,match_pattern,destination_hostgroup,apply from mysql_query_rules; admin>select rule_id,active,match_pattern,destination_hostgroup,apply from mysql_query_rules; +---------+--------+----------------------+-----------------------+-------+ | rule_id | active | match_pattern | destination_hostgroup | apply | +---------+--------+----------------------+-----------------------+-------+ | 1 | 1 | ^select.*for update$ | 10 | 1 | | 2 | 1 | ^select | 20 | 1 | +---------+--------+----------------------+-----------------------+-------+ 2 rows in set (0.00 sec) 6.表stats_mysql_global：与MySQL相关的代理级别的全局统计 select * from stats_mysql_global 表stats_mysql_processlist：类似MySQL的show processlist的命令，查看各线程的状态 select * from stats_mysql_processlist 表stats_mysql_query_rules：统计路由命中次数 rule_id:路由规则的id与main.mysql_query_rules的id对应 hits:此路由规则的匹配总数，如果当前传入的查询符合规则，则会记录一次命中 7.monitor：存储monitor模块收集的信息，主要是对后端db的健康/延迟检查 查看monitor数据库中的表：proxysql> show tables from monitor; select hostgroup,schemaname,username,digest_text,count_star from stats_mysql_query_digest; 8.查看请求路由信息 select hostgroup,schemaname,username,digest_text,count_star from stats_mysql_query_digest; |
设置proxysql故障转移
安装故障转移脚本之前，先要在MGR数据库执行一段检测集群状态的sql视图脚本和探测语句(将下面脚本保存为sql脚本，在MGR主节点执行即可)
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 |
USE sys; DELIMITER $$ CREATE FUNCTION IFZERO(a INT, b INT) RETURNS INT DETERMINISTIC RETURN IF(a = 0, b, a)$$ CREATE FUNCTION LOCATE2(needle TEXT(10000), haystack TEXT(10000), offset INT) RETURNS INT DETERMINISTIC RETURN IFZERO(LOCATE(needle, haystack, offset), LENGTH(haystack) + 1)$$ CREATE FUNCTION GTID_NORMALIZE(g TEXT(10000)) RETURNS TEXT(10000) DETERMINISTIC RETURN GTID_SUBTRACT(g, '')$$ CREATE FUNCTION GTID_COUNT(gtid_set TEXT(10000)) RETURNS INT DETERMINISTIC BEGIN DECLARE result BIGINT DEFAULT 0; DECLARE colon_pos INT; DECLARE next_dash_pos INT; DECLARE next_colon_pos INT; DECLARE next_comma_pos INT; SET gtid_set = GTID_NORMALIZE(gtid_set); SET colon_pos = LOCATE2(':', gtid_set, 1); WHILE colon_pos != LENGTH(gtid_set) + 1 DO SET next_dash_pos = LOCATE2('-', gtid_set, colon_pos + 1); SET next_colon_pos = LOCATE2(':', gtid_set, colon_pos + 1); SET next_comma_pos = LOCATE2(',', gtid_set, colon_pos + 1); IF next_dash_pos < next_colon_pos AND next_dash_pos < next_comma_pos THEN SET result = result + SUBSTR(gtid_set, next_dash_pos + 1, LEAST(next_colon_pos, next_comma_pos) - (next_dash_pos + 1)) - SUBSTR(gtid_set, colon_pos + 1, next_dash_pos - (colon_pos + 1)) + 1; ELSE SET result = result + 1; END IF; SET colon_pos = next_colon_pos; END WHILE; RETURN result; END$$ CREATE FUNCTION gr_applier_queue_length() RETURNS INT DETERMINISTIC BEGIN RETURN (SELECT sys.gtid_count( GTID_SUBTRACT( (SELECT Received_transaction_set FROM performance_schema.replication_connection_status WHERE Channel_name = 'group_replication_applier' ), (SELECT @@global.GTID_EXECUTED) ))); END$$ CREATE FUNCTION gr_member_in_primary_partition() RETURNS VARCHAR(3) DETERMINISTIC BEGIN RETURN (SELECT IF( MEMBER_STATE='ONLINE' AND ((SELECT COUNT(*) FROM performance_schema.replication_group_members WHERE MEMBER_STATE != 'ONLINE') >= ((SELECT COUNT(*) FROM performance_schema.replication_group_members)/2) = 0), 'YES', 'NO' ) FROM performance_schema.replication_group_members JOIN performance_schema.replication_group_member_stats USING(member_id)); END$$ CREATE VIEW gr_member_routing_candidate_status AS SELECT sys.gr_member_in_primary_partition() as viable_candidate, IF( (SELECT (SELECT GROUP_CONCAT(variable_value) FROM performance_schema.global_variables WHERE variable_name IN ('read_only', 'super_read_only')) != 'OFF,OFF'), 'YES', 'NO') as read_only, sys.gr_applier_queue_length() as transactions_behind, Count_Transactions_in_queue as 'transactions_to_cert' from performance_schema.replication_group_member_stats;$$ DELIMITER ; |
|
1 2 3 4 5 6 7 8 9 10 11 12 |
mysql -uroot -p123456 < /root/addition_to_sys.sql 在三台节点mysql查看该视图 mysql> select * from sys.gr_member_routing_candidate_status; +------------------+-----------+---------------------+----------------------+ | viable_candidate | read_only | transactions_behind | transactions_to_cert | +------------------+-----------+---------------------+----------------------+ | YES | YES | 0 | 0 | +------------------+-----------+---------------------+----------------------+ 1 row in set (0.20 sec) |
然后，请在Github地址下载相应的脚本
|
1 |
https://github.com/ZzzCrazyPig/proxysql_groupreplication_checker |
- proxysql_groupreplication_checker.sh：用于multi-primary（多主）模式，可以实现读写分离，以及故障切换，同一时间点多个节点可以多写。
- gr_mw_mode_cheker.sh：用于multi-primary(多主)模式，可以实现读写分离，以及故障切换，不过在同一时间点只能有一个节点能写。
- gr_sw_mode_checker.sh：用于single-primary(单主)模式，可以实现读写分离，以及故障切换。
|
1 2 3 4 |
由于我这里实验的环境是single-primary单主模式，所以选择gr_sw_mode_checker.sh脚本 如果GIT打不开,请从网盘下载这几个脚本 链接：https://pan.baidu.com/s/1j_juZoKqkk2PEDvwVwTvEQ 提取码：9bzn |
<1>. 将下载的脚本proxysql_groupreplication_checker.sh放到目录/var/lib/proxysql/下，并增加可以执行的权限:
|
1 |
chmod a+x /var/lib/proxysql/gr_sw_mode_checker.sh |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
最后，在proxysql的scheduler表里面加载如下记录，然后加载到RUNTIME使其生效，同时还可以持久化到磁盘: 执行语句: mysql -uadmin -padmin -h 127.0.0.1 -P 6032 --prompt='admin>' 写入节点不可以被用作读取用这句： insert into scheduler(active,interval_ms,filename,arg1,arg2,arg3,arg4) values(1,5000,'/var/lib/proxysql/gr_sw_mode_checker.sh',10,20,0,'/var/lib/proxysql/gr_sw_mode_checker.log'); 写节点也可以用做读用这句: insert into scheduler(active,interval_ms,filename,arg1,arg2,arg3,arg4) values(1,5000,'/var/lib/proxysql/gr_sw_mode_checker.sh',10,20,1,'/var/lib/proxysql/gr_sw_mode_checker.log'); LOAD SCHEDULER TO RUNTIME; SAVE SCHEDULER TO DISK; #这条sql目测不在集群同步范围内,需要3个节点都要执行 |
scheduler表解析
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
Create Table: CREATE TABLE scheduler ( id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, active INT CHECK (active IN (0,1)) NOT NULL DEFAULT 1, interval_ms INTEGER CHECK (interval_ms>=100 AND interval_ms<=100000000) NOT NULL, filename VARCHAR NOT NULL, arg1 VARCHAR, arg2 VARCHAR, arg3 VARCHAR, arg4 VARCHAR, arg5 VARCHAR, comment VARCHAR NOT NULL DEFAULT '') active:启用调度程序来调度我们提供的脚本 interval_ms:循环一次调用（例如：5000（ms）= 5s表示每5s调用一次脚本） 文件名：代表脚本文件路径 filename:代表脚本文件路径 arg1~arg4：脚本接收到的输入参数 arg1:是要写入的hostgroup_id(用来执行写操作的组id) arg2:是要读取的hostgroup_id(用来执行读操作的组id) arg3:写入节点是否可以被用作读取? 默认1(YES: default) or 0(NO) arg4:日志文件路径 |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
查看表中的字段可使用命令proxysql> show create table scheduler\G 查看表中的数据可使用命令proxysql> select * from scheduler\G 问题排查可查看日志文件/var/lib/proxysql/gr_sw_mode_checker.log 备注2：脚本gr_sw_mode_checker.sh用法 gr_sw_mode_cheker.sh <hostgroup_id write> <hostgroup_id read> [write node can be read : 1(YES: default) or 0(NO)] [log_file] a、arg1：hostgroup_id write b、arg2：hostgroup_id read c、arg3：写入节点是否可以被用作读取? 默认1(YES: default) or 0(NO) d、arg4：log_file, default: './checker.log' |
|
1 |
schedule信息加载后，就会分析当前的环境，mysql_servers中当前只有192.168.6.122是可以写的,192.168.6.121和192.168.6.123是用来读的 |
查看MGR状态信息：
|
1 |
select hostgroup_id,hostname,port,status,weight,comment from mysql_servers; |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 |
切到Mysql中：查看哪个是主机Master select * from performance_schema.replication_group_members; select if((select @@server_uuid)=(select variable_value from performance_schema.global_status where variable_name='group_replication_primary_member'),1,0) as is_primary_mode,@@server_id; master节点： +-----------------+-------------+ | is_primary_mode | @@server_id | +-----------------+-------------+ | 1 | 1122 | +-----------------+-------------+ 1 row in set (0.11 sec) 备注：is_primary_mode的值为1说明此节点为master节点 slave1节点： +-----------------+-------------+ | is_primary_mode | @@server_id | +-----------------+-------------+ | 0 | 1121 | +-----------------+-------------+ 1 row in set (0.11 sec) slave2节点 +-----------------+-------------+ | is_primary_mode | @@server_id | +-----------------+-------------+ | 0 | 1123 | +-----------------+-------------+ 1 row in set (0.19 sec) |
如果你一开始没有设置集群，而是单机配置 ，现在需要集群，则需要删除db文件使用配置文件proxysql.cnf重新初始化，当然也可以可以使用sql命令写入配置文件中的这些信息（最好用第一种，第二种目测有些瑕疵测试中）
具体操作如下：
1.添加proxysql实例节点间通讯的账号
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
update global_variables set variable_value='admin:admin;cluster_kevin:123456' where variable_name='admin-admin_credentials'; update global_variables set variable_value='cluster_kevin' where variable_name='admin-cluster_username'; update global_variables set variable_value='123456' where variable_name='admin-cluster_password'; update global_variables set variable_value=200 where variable_name='admin-cluster_check_interval_ms'; update global_variables set variable_value=100 where variable_name='admin-cluster_check_status_frequency'; update global_variables set variable_value='true' where variable_name='admin-cluster_mysql_query_rules_save_to_disk'; update global_variables set variable_value='true' where variable_name='admin-cluster_mysql_servers_save_to_disk'; update global_variables set variable_value='true' where variable_name='admin-cluster_mysql_users_save_to_disk'; update global_variables set variable_value='true' where variable_name='admin-cluster_proxysql_servers_save_to_disk'; update global_variables set variable_value=3 where variable_name='admin-cluster_mysql_query_rules_diffs_before_sync'; update global_variables set variable_value=3 where variable_name='admin-cluster_mysql_servers_diffs_before_sync'; update global_variables set variable_value=3 where variable_name='admin-cluster_mysql_users_diffs_before_sync'; update global_variables set variable_value=3 where variable_name='admin-cluster_proxysql_servers_diffs_before_sync'; load admin variables to runtime; save admin variables to disk; |
2.插入节点信息入库
|
1 2 3 4 5 6 7 8 9 |
insert into proxysql_servers (hostname,port,weight,comment) values ("192.168.6.121",6032,1,"ProxySQL-node1"); insert into proxysql_servers (hostname,port,weight,comment) values ("192.168.6.122",6032,1,"ProxySQL-node2"); insert into proxysql_servers (hostname,port,weight,comment) values ("192.168.6.123",6032,1,"ProxySQL-node3"); load proxysql servers to runtime; save proxysql servers to disk; LOAD MYSQL USERS TO RUNTIME save MYSQL USERS to disk; |
测试意外宕机，故障转移
|
1 2 |
Mysql MGR当master节点意外宕机或下线，在满足大多数节点存活的情况下，group内部发起选举，选出下一个可用的读节点，提升其为下一个master节点。master节点选举根据group内剩余存活节点的UUID按字典升序排列，然后选择排在最前的节点作为新的master节点。is_primary_mode的值为1说明slave2节点已经被选举为新的master节点. |
|
1 2 3 |
在上面的读写分离规则中，我设置了192.168.6.122为可写节点，192.168.6.121,192.168.6.123为只读节点. |
如果此时192.168.6.122宕机的话，应用能不能直接连到其它的节点进行写操作？
现手动将192.168.6.122 mysql关闭：
然后再mysql中查看当前group组情况：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
SELECT MEMBER_ID, MEMBER_HOST, MEMBER_PORT, MEMBER_STATE, IF(global_status.VARIABLE_NAME IS NOT NULL, 'PRIMARY', 'SECONDARY') AS MEMBER_ROLE FROM performance_schema.replication_group_members LEFT JOIN performance_schema.global_status ON global_status.VARIABLE_NAME = 'group_replication_primary_member' AND global_status.VARIABLE_VALUE = replication_group_members.MEMBER_ID; |
|
1 |
可以看到，122宕机后,121按照权重设置优先级，被选举为了新的master |
那么proxysql中会是怎样的呢？
|
1 2 3 4 |
mysql -uadmin -padmin -h 127.0.0.1 -P 6032 --prompt='admin>' select * from mysql_servers; #可以看到121的group_id变成了写组id = 10, |
我们再模拟写入和查询看看语句的去向
|
1 2 3 4 5 |
mysql -uproxysql -p123456 -h 127.0.0.1 -P 6033 --prompt='proxysql>' insert into shooter.book (c1,c2) values (46,'赵丹'); select * from shooter.book; select * from shooter.book for update; |
查看sql请求路由信息
|
1 2 3 4 |
因为我们设置了写机器,不接受读操作,所以这个时候查询语句只会去 123机器. 注意这个语句只是来执行sql的，他并不能查询sql语句落在了哪个机器。别和我犯同样错误。蛋疼大半天。 mysql -uproxysql -p123456 -P 6033 -h 127.0.0.1 -e "select @@server_id;" |
再次查看sql路由请求信息：
|
1 |
select hostgroup,schemaname,username,digest_text,count_star from stats_mysql_query_digest; |
|
1 |
可以看到,数据执行依然正常，并没有因为某个机器宕机，而停止！ |
恢复宕机机器，测试proxysql是否自动恢复，并将节点自动恢复为online
当前节点状态
|
1 |
select hostgroup,username,digest_text,count_star from stats_mysql_query_digest; |
可以看到122节点已经宕机，现在我启动122mysql 加入MGR组复制
|
1 2 3 4 5 6 7 8 9 10 11 |
如果其他节点不断有数据写入： 这里恢复MGR组,先去其他节点导出一份完整数据,导入到122节点,然后再恢复,否则出错的几率，加入不进组的概率会很大，因为之前宕机的是主(Master),再重新加入组要以slave的身份，如果你reset master 以slave身份仍然不能正常加入，你还是乖乖去其他节点导出一份完整数据(带gtid的)，然后导入到122机器，然后直接启动组就ok mysqldump -uroot -p123456 --all-databases --triggers --routines --events --skip-lock-tables > all.sql 启动组： #注意这个允许本地不想交的命令必须执行，否则proxysql之前的查询主从节点健康的视图你会无法使用,自然proxysql就无法识别122已经恢复正常。 set global group_replication_allow_local_disjoint_gtids_join=ON;组复制允许本地不相交 SET SESSION binlog_format = 'ROW'; SET GLOBAL binlog_format = 'ROW'; START GROUP_REPLICATION; |
组启动成功后，查看下MGR组状态
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
SELECT MEMBER_ID, MEMBER_HOST, MEMBER_PORT, MEMBER_STATE, IF(global_status.VARIABLE_NAME IS NOT NULL, 'PRIMARY', 'SECONDARY') AS MEMBER_ROLE FROM performance_schema.replication_group_members LEFT JOIN performance_schema.global_status ON global_status.VARIABLE_NAME = 'group_replication_primary_member' AND global_status.VARIABLE_VALUE = replication_group_members.MEMBER_ID; |
然后查看122机器的，proxysql视图当前是否正常
|
1 |
select * from sys.gr_member_routing_candidate_status; |
然后登陆proxysql的管理账号，查看节点是否已经恢复
|
1 |
select * from mysql_servers; |
可以看到122节点已经恢复了
再次执行查询，看是否会路由到122节点
|
1 |
mysql -uproxysql -p123456 -P 6033 -h 127.0.0.1 -e "select @@server_id;" |
可以看到，122节点已经加入任务节点，并且正常工作
测试查询缓存：
|
1 2 3 4 5 6 7 8 |
ProxySQL的查询缓存和MySQL的查询缓存有点类似，但不完全一样，ProxySQL的查询缓存如果在指定时间大小范围内发送的SQL一摸一样，那么直接返回结果集，而返回的结果集可能并不是准确的查询结果，所以需要设置合适的时间范围，既能提升性能，又得满足业务需求，即查询结果足够的“新”。针对一些查询频率很高但结果并不需要太精确的业务，可以单独给这些SQL配置查询缓存。 启用查询缓存： admin> update mysql_query_rules set cache_ttl=2000 where active=1 and destination_hostgroup=20; admin> load mysql query rules to runtime; admin> save mysql query rules to disk; |
proxysql压测请看这个大佬文章最后使用 sysbench 进行压测
|
1 |
https://blog.51cto.com/qiuyue/2413300?source=drh |
- 本文固定链接: https://www.yoyoask.com/?p=3560
- 转载请注明: shooter 于 SHOOTER 发表
这个作者貌似有点懒，什么都没有留下。