(5.20)mysql中的ProxySQL实现读写分离与读负载均衡【转】

1、ProxySQL 介绍

ProxySQL 是基于 MySQL 的一款开源的中间件的产品,是一个灵活的 MySQL 代理层,可以实现读写分离,支持 Query 路由功能,支持动态指定某个 SQL 进行缓存,支持动态加载(无需重启 ProxySQL 服务),故障切换和一些 SQL 的过滤功能。 
介绍:相关 ProxySQL 的网站

1
2
https://www.proxysql.com/
https://github.com/sysown/proxysql/wiki

  

2、环境准备

全新初始化的实例

IP server-id 数据库版本 备注
10.0.0.160 1603306 MySQL 8.0.18 Master(主库)+ ProxySQL2.0.8
10.0.0.161 1613306 MySQL 8.0.18 Slave1(从库)
10.0.0.162 1623306 MySQL 8.0.18 Slave2(从库)

(5.20)mysql中的ProxySQL实现读写分离与读负载均衡【转】

 

 

ProxySQL 2.0.x 版本支持 MGR 
注:我的 ProxySQL 和 主库放在一起的,我已近配置好了主从复制状态。这是我的实验环境,实际环境注意实际情况。一般的代理环境结构如上图。 
slave 节点的read_only=1 

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
[root@mysql-160 ~]# rpm -ivh proxysql-2.0.8-1-centos7.x86_64.rpm
# 查看相应安装文件路径,有哪些文件
[root@mysql-160 ~]# rpm -ql proxysql
/etc/logrotate.d/proxysql
/etc/proxysql.cnf
/etc/systemd/system/proxysql.service
/usr/bin/proxysql
/usr/share/proxysql/tools/proxysql_galera_checker.sh
/usr/share/proxysql/tools/proxysql_galera_writer.pl

/etc/proxysql.cnf 是 ProxySQL 的配置文件

 启动 ProxySQL

1
2
3
4
5
6
[root@mysql-160 ~]# systemctl start proxysql.service
# 查看是否启动
[root@mysql-160 ~]# netstat -anlp | grep proxysql
[root@mysql-160 ~]# netstat -anlp | grep proxysql
tcp        0      0 0.0.0.0:6032            0.0.0.0:*               LISTEN      2989/proxysql      
tcp        0      0 0.0.0.0:6033            0.0.0.0:*               LISTEN      2989/proxysql

(5.20)mysql中的ProxySQL实现读写分离与读负载均衡【转】

 

 

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

查看 ProxySQL 的版本

 

1
[root@mysql-160 ~]# proxysql --version

管理员登录 ProxySQL

1
[root@mysql-160 ~]# /usr/local/mysql/bin/mysql -uadmin -padmin -h 127.0.0.1 -P 6032

(5.20)mysql中的ProxySQL实现读写分离与读负载均衡【转】

 

 

 可见有五个库: main、disk、stats 、monitor 和 stats_history 
main: 内存配置数据库,即 MEMORY,表里存放后端 db 实例、用户验证、路由规则等信息。main 库中有如下信息:

(5.20)mysql中的ProxySQL实现读写分离与读负载均衡【转】

 

 

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

注: 表名以 runtime_开头的表示 ProxySQL 当前运行的配置内容,不能通过 DML 语句修改。

只能修改对应的不以 runtime 开头的表,然后 “LOAD” 使其生效,“SAVE” 使其存到硬盘以供下次重启加载。 
disk :持久化的磁盘的配置 
stats: 统计信息的汇总 
monitor:一些监控的收集信息,比如数据库的健康状态等 
stats_history: 这个库是 ProxySQL 收集的有关其内部功能的历史指标

4、配置 ProxySQL 所需账户

在 Master (10.0.0.160) 的MySQL 上创建 ProxySQL 的监控账户和对外访问账户

1
2
3
4
5
6
7
8
9
#proxysql 的监控账户
create user ‘monitor‘@‘10.0.0.%‘ identified by ‘123456‘;
grant all privileges on *.* to ‘monitor‘@‘10.0.0.%‘ with grant option;
 
#proxysql 的对外访问账户
create user ‘proxysql‘@‘10.0.0.%‘ identified by ‘123456‘;
grant all privileges on *.* to ‘proxysql‘@‘10.0.0.%‘ with grant option;
 
flush privileges;

(5.20)mysql中的ProxySQL实现读写分离与读负载均衡【转】

 

 

 注意 8.0.x 用户认证的方式需要修改为 mysql_native_password 
需要在my.cnf 加上这个用户认证方式,再来创建用户

1
2
[mysqld]
default_authentication_plugin=mysql_native_password

(5.20)mysql中的ProxySQL实现读写分离与读负载均衡【转】

 

 

 

5、ProxySQL 的多层配置系统

配置结构如下:

(5.20)mysql中的ProxySQL实现读写分离与读负载均衡【转】

 

 

 

1
https://github.com/sysown/proxysql/wiki/Configuring-ProxySQL

整套配置系统分为三层:顶层为 RUNTIME ,中间层为 MEMORY , 底层也就是持久层 DISK 和 CONFIG FILE 。

RUNTIME : 代表 ProxySQL 当前生效的正在使用的配置,无法直接修改这里的配置,必须要从下一层 “load” 进来。 
MEMORY: MEMORY 层上面连接 RUNTIME 层,下面连接持久层。这层可以正常操作 ProxySQL 配置,随便修改,不会影响生产环境。修改一个配置一般都是现在 MEMORY 层完成的,确认正常之后在加载达到 RUNTIME 和 持久化的磁盘上。

DISK 和 CONFIG FILE:持久化配置信息,重启后内存中的配置信息会丢失,所需要将配置信息保留在磁盘中。重启时,可以从磁盘快速加载回来。

5.1 在不同层次间移动配置

为了将配置持久化到磁盘或者应用到 runtime,在管理接口下有一系列管理命令来实现它们。 
要重新配置 MySQL 用户,可执行下面的其中一个命令:

1
2
3
4
5
6
7
8
9
10
1、LOAD MYSQL USERS FROM MEMORY / LOAD MYSQL USERS TO RUNTIME
 将内存数据库中的配置加载到 runtime 数据结构,反之亦然。
2、SAVE MYSQL USERS TO MEMORY / SAVE MYSQL USERS FROM RUNTIME
 将 MySQL 用户从 runtime 持久化到内存数据库。
3、LOAD MYSQL USERS TO MEMORY / LOAD MYSQL USERS FROM DISK
 从磁盘数据库中加载 MySQL 用户到内存数据库中。
4、SAVE MYSQL USERS FROM MEMORY / SAVE MYSQL USERS TO DISK
 将内存数据库中的 MySQL 用户持久化到磁盘数据库中。
5、LOAD MYSQL USERS FROM CONFIG
 从配置文件中加载 MySQL 用户到内存数据库中。

要处理 MySQL server:

1
2
3
4
5
6
7
8
9
10
1、LOAD MYSQL SERVERS FROM MEMORY / LOAD MYSQL SERVERS TO RUNTIME
 将 MySQL server 从内存数据库中加载到 runtime。
2、SAVE MYSQL SERVERS TO MEMORY / SAVE MYSQL SERVERS FROM RUNTIME
 将 MySQL server 从 runtime 持久化到内存数据库中。
3、LOAD MYSQL SERVERS TO MEMORY / LOAD MYSQL SERVERS FROM DISK
 从磁盘数据库中加载 MySQL server 到内存数据库。
4、SAVE MYSQL SERVERS FROM MEMORY / SAVE MYSQL SERVERS TO DISK
 从内存数据库中将 MySQL server 持久化到磁盘数据库中。
5、LOAD MYSQL SERVERS FROM CONFIG
 从配置文件中加载 MySQL server 到内存数据库中

要处理 MySQL 的查询规则(mysql query rules):

1
2
3
4
5
6
7
8
9
10
1、 LOAD MYSQL QUERY RULES FROM MEMORY / LOAD MYSQL QUERY RULES TO RUNTIME
 将 MySQL query rules 从内存数据库加载到 runtime 数据结构。
2、 SAVE MYSQL QUERY RULES TO MEMORY / SAVE MYSQL QUERY RULES FROM RUNTIME
 将 MySQL query rules 从 runtime 数据结构中持久化到内存数据库。
3、 LOAD MYSQL QUERY RULES TO MEMORY / LOAD MYSQL QUERY RULES FROM DISK
 从磁盘数据库中加载 MySQL query rules 到内存数据库中。
4、 SAVE MYSQL QUERY RULES FROM MEMORY / SAVE MYSQL QUERY RULES TO DISK
 将 MySQL query rules 从内存数据库中持久化到磁盘数据库中。
5、 LOAD MYSQL QUERY RULES FROM CONFIG
 从配置文件中加载 MySQL query rules 到内存数据库中。

  

要处理 MySQL 变量(MySQL variables):

1
2
3
4
5
6
7
8
9
10
1、 LOAD MYSQL VARIABLES FROM MEMORY / LOAD MYSQL VARIABLES TO RUNTIME
 将 MySQL variables 从内存数据库加载到 runtime 数据结构。
2、 SAVE MYSQL VARIABLES TO MEMORY / SAVE MYSQL VARIABLES FROM RUNTIME
 将 MySQL variables 从 runtime 数据结构中持久化到内存数据中。
3、 LOAD MYSQL VARIABLES TO MEMORY / LOAD MYSQL VARIABLES FROM DISK
 从磁盘数据库中加载 MySQL variables 到内存数据库中。
4、 SAVE MYSQL VARIABLES FROM MEMORY / SAVE MYSQL VARIABLES TO DISK
 将 MySQL variables 从内存数据库中持久化到磁盘数据库中。
5、 LOAD MYSQL VARIABLES FROM CONFIG
 从配置文件中加载 MySQL variables 到内存数据库中。

要处理管理变量(admin variables):

1
2
3
4
5
6
7
8
9
10
1、 LOAD ADMIN VARIABLES FROM MEMORY / LOAD ADMIN VARIABLES TO RUNTIME
 将 admin variables 从内存数据库加载到 runtime 数据结构。
2、 SAVE ADMIN VARIABLES TO MEMORY / SAVE ADMIN VARIABLES FROM RUNTIME
 将 admin variables 从 runtime 持久化到内存数据库中。
3、 LOAD ADMIN VARIABLES TO MEMORY / LOAD ADMIN VARIABLES FROM DISK
 从磁盘数据库中加载 admin variables 到内存数据库中。
4、 SAVE ADMIN VARIABLES FROM MEMORY / SAVE ADMIN VARIABLES TO DISK
 将 admin variables 从内存数据库中持久化到磁盘数据库。
5、 LOAD ADMIN VARIABLES FROM CONFIG
 从配置文件中加载 admin variables 到内存数据库中。

(5.20)mysql中的ProxySQL实现读写分离与读负载均衡【转】

 

 

 (5.20)mysql中的ProxySQL实现读写分离与读负载均衡【转】

 

 

 一般在内存那层修改 ,然后保存到运行系统,保存到磁盘数据库系统

1
2
load xxx to runtime;
save xxx to disk;

5.2 管理配置

  • disk and config file 持久化配置文件
1
2
disk -> 是sqlite3 数据库 ,默认位置是$DATADIR/proxysql.db( /var/lib/proxysql/proxysql.db)
config file 是一个传统配置文件:一般不更改
  • 在内存中动态更改配置,如果重启,没进行持久化(save) 则会丢失。

三则之间关系

  • proxysql 启动时,首先去找/etc/proxysql.cnf 找到它的datadir,如果datadir下有proxysql.db 就加载proxysql.db的配置

  • 如果启动proxysql时带有--init标志,会用/etc/proxsql.cnf的配置,把Runtime,disk全部初始化一下

  • 在调用是调用--reload 会把/etc/proxysql.cnf 和disk 中配置进行合并。如果冲突需要用户干预。disk会覆盖config file。

关于传统的配置文件 
传统配置文件默认路径为/etc/proxysql.cnf,也可以在二进制程序proxysql上使用-c或–config来手动指定配置文件。 
默认情况下:几乎不需要手动去配置proxysql.cnf。端口号,管理proxysql用户明吗,可以在这里修改

 

5.2 小结

这里有几个最常用的命令:如何让修改的配置生效(runtime),以及如何持久化到磁盘上(disk)。记住,只要不是加载到 runtime,修改的配置就不会生效。

 

1
2
3
4
5
6
7
8
9
10
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 从内存数据库中持久化到磁盘数据库。

注意:只有加载到 runtime 状态时才会去做最后的有效性验证。在保存到内存数据库或持久化到磁盘上时,都不会发生任何警告或错误。当加载到 runtime 时,如果出现错误,将恢复为之前保存得状态,这时可以去检查错误日志。  

6、配置 ProxySQL 主从分组信息

用到 这个表 mysql_replication_hostgroup,表结构信息如下

1
2
3
4
5
6
7
8
9
admin@127.0.0.1 [main]>show create table mysql_replication_hostgroups\G;
*************************** 1. row ***************************
       table: mysql_replication_hostgroups
Create Table: CREATE TABLE mysql_replication_hostgroups (
    writer_hostgroup INT CHECK (writer_hostgroup>=0) NOT NULL PRIMARY KEY,
    reader_hostgroup INT NOT NULL CHECK (reader_hostgroup<>writer_hostgroup AND reader_hostgroup>=0),
    check_type VARCHAR CHECK (LOWER(check_type) IN (‘read_only‘,‘innodb_read_only‘,‘super_read_only‘,‘read_only|innodb_read_only‘,‘read_only&innodb_read_only‘)) NOT NULL DEFAULT ‘read_only‘,
    comment VARCHAR NOT NULL DEFAULT ‘‘, UNIQUE (reader_hostgroup))
1 row in set (0.00 sec)

(5.20)mysql中的ProxySQL实现读写分离与读负载均衡【转】

 

 

 writer_hostgroup 和reader_hostgroup 写组和读组都要大于0且不能相同,我的环境下,写组定义与10,读组定义为20

6.1 创建组

1
2
3
4
5
6
insert into mysql_replication_hostgroups ( writer_hostgroup, reader_hostgroup, comment) values (10,20,‘proxy‘);
 
load mysql servers to runtime;
save mysql servers to disk;
 
确定三种配置系统是否都已近写入了数据

(5.20)mysql中的ProxySQL实现读写分离与读负载均衡【转】

 

 

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

 

6.2 添加主从服务器节点

用到mysql_servers 这个表

(5.20)mysql中的ProxySQL实现读写分离与读负载均衡【转】

 

 

 

1
2
3
4
5
6
insert into mysql_servers(hostgroup_id,hostname,port) values (10,‘10.0.0.160‘,3306);
insert into mysql_servers(hostgroup_id,hostname,port) values (20,‘10.0.0.161‘,3306);
insert into mysql_servers(hostgroup_id,hostname,port) values (20,‘10.0.0.162‘,3306);
注:严格需要写明comment
load mysql servers to runtime;
save mysql servers to disk;

(5.20)mysql中的ProxySQL实现读写分离与读负载均衡【转】

 

 

 

添加了节点,三台机器都是online 状态

6.3 ProxySQL监控 MySQL 后端节点

为 ProxySQL 配置监控账号

1
2
3
4
5
admin@127.0.0.1 [monitor]>set mysql-monitor_username=‘monitor‘;
Query OK, 1 row affected (0.00 sec)
 
admin@127.0.0.1 [monitor]>set mysql-monitor_password=‘123456‘;
Query OK, 1 row affected (0.00 sec)

上面这两句是修改变量的方式还可以在main库下面用sql语句方式修改

1
2
3
4
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

1
2
3
4
5
6
7
admin@127.0.0.1 [monitor]>load mysql variables to runtime;
Query OK, 0 rows affected (0.01 sec)
 
admin@127.0.0.1 [monitor]>save mysql variables to disk;
Query OK, 139 rows affected (0.00 sec)
 
admin@127.0.0.1 [monitor]>select from monitor.mysql_server_connect_log;

验证监控信息,ProxySQL 监控模块的指标都保存在monitor库的log表中 
以下是连接是否正常的监控,对connect指标的监控 ,在前面可能会有很多connect_error,这是因为没有配置监控信息时的错误,配置后如果connect_error的结果为NULL则表示正常

(5.20)mysql中的ProxySQL实现读写分离与读负载均衡【转】

 

 

 对心跳信息的监控(对ping 指标的监控)

1
admin@127.0.0.1 [monitor]>select from mysql_server_ping_log limit 10;

(5.20)mysql中的ProxySQL实现读写分离与读负载均衡【转】

 

 

看看read_only的日志监控

 

1
admin@127.0.0.1 [monitor]>select from mysql_server_read_only_log limit 10;

(5.20)mysql中的ProxySQL实现读写分离与读负载均衡【转】

 

 

 Monitor 模块就会开始监控后端的read_only值,当监控到read_only值,就会按照read_only的值将某些节点自动移到读写组 
一些监控的状态斗志在log相关,都在monitor库下面的 global_variables 变量。

6.4 为 ProxySQL 配置对外访问账号

SQL 请求所使用的用户配置,都需要在 MySQL 节点创建上。在步骤4:配置ProxySQL 账户,我创建的对外访问账户是:用户:proxysql,密码:123456 
配置mysql_users 这个表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
表结构信息如下:
admin@127.0.0.1 [main]>show create table mysql_users\G;
*************************** 1. row ***************************
       table: mysql_users
Create Table: CREATE TABLE mysql_users (
    username VARCHAR NOT NULL,
    password VARCHAR,
    active INT CHECK (active IN (0,1)) NOT NULL DEFAULT 1,
    use_ssl INT CHECK (use_ssl IN (0,1)) NOT NULL DEFAULT 0,
    default_hostgroup INT NOT NULL DEFAULT 0,
    default_schema VARCHAR,
    schema_locked INT CHECK (schema_locked IN (0,1)) NOT NULL DEFAULT 0,
    transaction_persistent INT CHECK (transaction_persistent IN (0,1)) NOT NULL DEFAULT 1,
    fast_forward INT CHECK (fast_forward IN (0,1)) NOT NULL DEFAULT 0,
    backend INT CHECK (backend IN (0,1)) NOT NULL DEFAULT 1,
    frontend INT CHECK (frontend IN (0,1)) NOT NULL DEFAULT 1,
    max_connections INT CHECK (max_connections >=0) NOT NULL DEFAULT 10000,
    comment VARCHAR NOT NULL DEFAULT ‘‘,
    PRIMARY KEY (username, backend),
    UNIQUE (username, frontend))
1 row in set (0.00 sec)

配置mysql_users 表,将proxysql用户添加到该表中。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
insert into mysql_users (username,password,default_hostgroup) values (‘proxysql‘,‘123456‘,10);
 
load mysql users to runtime;
save mysql users to disk;
admin@127.0.0.1 [admin]>select from mysql_users\G
*************************** 1. row ***************************
              username: proxysql
              password: 123456
                active: 1  #是否启用该账户,为0表示禁用
               use_ssl: 0
     default_hostgroup: 10
        default_schema: NULL
         schema_locked: 0
transaction_persistent: 1  #事务透明化
          fast_forward: 0
               backend: 1
              frontend: 1
       max_connections: 10000  #用户链接10000,可以修改
               comment:
1 row in set (0.00 sec)

(5.20)mysql中的ProxySQL实现读写分离与读负载均衡【转】

 

 

 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 : 10.0.0.160

在161(从库)端,用 proxysql 用户 测试是否sql路由能默认到 hostgroup_id=10,它是一个写组(读写数据)。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
proxysql@10.0.0.160 [(none)]>show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)
 
proxysql@10.0.0.160 [(none)]>select @@server_id;
+-------------+
| @@server_id |
+-------------+
|     1603306 |
+-------------+
1 row in set (0.00 sec)
#通过proxysql用户,创建一个keme库
proxysql@10.0.0.160 [(none)]>create database keme;
Query OK, 1 row affected (0.03 sec)

在slave2:10.0.0.162 上去验证一下,是否同步过去keme这个库

(5.20)mysql中的ProxySQL实现读写分离与读负载均衡【转】

 

 

到这里主从分组信息都配置完了、

 

7、配置读写分离策略:路由规则

配置读写分离,就是配置ProxySQL 路由规则,ProxySQL 的路由规则非常灵活,可以基于用户,基于schema,以及单个sql语句实现路由规则定制。

注意:我这只是试验,只是配置了几个简单的路由规则,实际情况配置路由规则,不应该是就根据所谓的读、写操作来进行读写分离,而是从收集(慢日志)的各项指标找出压力大,执行频繁的语句单独写规则,做缓存等等。比如 先在测试几个核心sql语句,分析性能提升的百分比,在逐渐慢慢完善路由规则。 
生产中使用读写分离 :建议基于hash code 值做读写分离,不要建太多的规则

和查询规则有关的表有两个:mysql_query_rulesmysql_query_rules_fast_routing 
表mysql_query_rules_fast_routing是mysql_query_rules的扩展,并在以后评估快速路由策略和属性(仅在ProxySQL 1.4.7+中可用)。

介绍一下改表mysql_query_rules的几个字段: 
active:是否启用这个规则,1表示启用,0表示禁用 
match_pattern 字段就是代表设置规则 
destination_hostgroup 字段代表默认指定的分组, 
apply 代表真正执行应用规则。

7.1 创建规则

这里我创建两个规则: 
1、把所有以select 开头的语句全部分配到读组中,读组编号是20 
2、把 select .. for update 语句,这是一个特殊的select语句,会产生一个写锁(排他锁),把他分到编号为10 的写组中,其他所有操作都会默认路由到写组中

1
2
3
4
5
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 … for update规则的rule_id必须要小于普通的select规则的rule_id,因为ProxySQL是根据rule_id的顺序进行规则匹配的。  

7.2 测试读写分离

由于是新实例,先在master 10.0.0.160 实例上,生成几条数据

(5.20)mysql中的ProxySQL实现读写分离与读负载均衡【转】

 

 

 

测试读 
通过proxysql 连接看看读操作,是否路由给了读组:

1
[root@mysql-160 ~]# mysql -uproxysql -p123456 -P 6033 -h 127.0.0.1 -e "select @@server_id;"

 (5.20)mysql中的ProxySQL实现读写分离与读负载均衡【转】

 

 

 只要是select 操作都分给了读组

测试写

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
proxysql@127.0.0.1 [keme]>select @@server_id;
+-------------+
| @@server_id |
+-------------+
|     1623306 |
+-------------+
1 row in set (0.00 sec)
 
proxysql@127.0.0.1 [keme]>start transaction;
Query OK, 0 rows affected (0.00 sec)
 
proxysql@127.0.0.1 [keme]>select from beta1 for update;
+----+----------+
| id | name     |
+----+----------+
|  1 | zhangsan |
|  2 | yj       |
|  3 | ss       |
|  4 | hc       |
|  5 | km       |
+----+----------+
5 rows in set (0.00 sec)
 
proxysql@127.0.0.1 [keme]>select @@server_id;
+-------------+
| @@server_id |
+-------------+
|     1603306 |
+-------------+
1 row in set (0.00 sec)

 (5.20)mysql中的ProxySQL实现读写分离与读负载均衡【转】

 

 

 在测试insert 操作

(5.20)mysql中的ProxySQL实现读写分离与读负载均衡【转】

 

 

 

也分配到写组了,一切都正常。

如果想在 ProxySQL 中查看SQL请求路由信息stats_mysql_query_digest

1
admin@127.0.0.1 [stats]>select hostgroup,schemaname,username,digest_text,count_star from  stats_mysql_query_digest;

(5.20)mysql中的ProxySQL实现读写分离与读负载均衡【转】

count_start 统计sql 语句次数,可以分析哪些 sql ,频繁执行

读写分离设置成功后,还可以调权重,比如让某台机器承受更多的读操作 

列:

1
2
3
update mysql_servers set weight=10 hostname=‘10.0.0.161‘
load mysql servers to runtime;
save mysql servers to disk;

文章参考

https://www.cnblogs.com/f-ck-need-u/p/9278839.html
https://github.com/sysown/proxysql/wiki

 

转自:https://www.cnblogs.com/keme/p/10972415.html#auto_id_17

(5.20)mysql中的ProxySQL实现读写分离与读负载均衡【转】

上一篇:SqlIO优化


下一篇:CloudBeaver 参考架构