利用ProxySQL实现读写分离

  1. 环境准备:
    准备三台主机,一台ProxySQL服务器:192.168.8.7,另外两台主机实现主从复制192.168.8.17,27

    注意:slave节点需要设置read_only=1

  2. 安装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;
  1. 添加监控后端节点的用户,连接每个节点的read_only值来自动调整主从节点是属于读组还是写组
#在master上执行
MySQL> grant replication client on *.* to monitor@'192.168.8.%' identified 
by 'magedu';
#ProxySQL上配置监控
MySQL [(none)]> set mysql-monitor_username='monitor';
MySQL [(none)]> set mysql-monitor_password='magedu'; #加载到RUNTIME,并保存到disk
MySQL [(none)]> load mysql variables to runtime;
MySQL [(none)]> save mysql variables to disk;
  1. 查看监控
    监控模块的指标保存在monitor库的log表中
#查看监控连接是否正常的 (对connect指标的监控),如果connect_error的结果为NULL则表示正
常
MySQL> select * from mysql_server_connect_log;
#查看监控心跳信息 (对ping指标的监控):
MySQL> select * from mysql_server_ping_log;
  1. 设置分组信息
    需要修改的是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     |
  1. 配置访问数据库的SQL 用户
#在master节点上创建访问用户
MySQL> grant all on *.* to sqluser@'192.168.8.%' identified by 'magedu'; #在ProxySQL配置,将用户sqluser添加到mysql_users表中, default_hostgroup默认组设置
为写组10,当读写分离的路由规则不符合时,会访问默认组的数据库
MySQL> insert into mysql_users(username,password,default_hostgroup) 
values('sqluser','magedu',10);
MySQL> load mysql users to runtime;
MySQL> save mysql users to disk;
#使用sqluser用户测试是否能路由到默认的10写组实现读、写数据
mysql -usqluser -pmagedu -P6033 -h127.0.0.1 -e 'select @@server_id'
mysql -usqluser -pmagedu -P6033 -h127.0.0.1 -e 'create database testdb'
mysql -usqluser -pmagedu testdb -P6033 -h127.0.0.1 -e 'create table t(id 
int)'
  1. 在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
  1. 测试ProxySQL
#读操作是否路由给20的读组
mysql -usqluser -pmagedu -P6033 -h127.0.0.1 -e 'select @@server_id'
#测试写操作,以事务方式进行测试
mysql -usqluser -pmagedu -P6033 -h127.0.0.1 \
-e 'start transaction;select @@server_id;commit;select @@server_id'
mysql -usqluser -pmagedu -P6033 -h127.0.0.1 -e 'insert testdb.t values (1)'
mysql -usqluser -pmagedu -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 -pmagedu -P6033 -h127.0.0.1 -e 'select @@server_id'
#测试写操作,以事务方式进行测试
mysql -usqluser -pmagedu -P6033 -h127.0.0.1 \
-e 'start transaction;select @@server_id;commit;select @@server_id'
mysql -usqluser -pmagedu -P6033 -h127.0.0.1 -e 'insert testdb.t values (1)'
mysql -usqluser -pmagedu -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;
  • 0
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
要使用 ProxySQL 配置 MySQL读写分离,可以按照以下步骤进行操作: 1. 安装 ProxySQL:首先,你需要安装 ProxySQL 的软件。你可以从 ProxySQL 的官方网站下载适用于你的操作系统的安装程序,并按照官方文档中的说明进行安装。 2. 配置主服务器和从服务器:在主服务器和从服务器上进行以下配置: - 确保主服务器和从服务器都已正确配置和运行,并且数据库实例正常工作。 - 确保主服务器和从服务器都可以通过网络连接到 ProxySQL。 3. 连接到 ProxySQL:使用以下命令连接到 ProxySQL 的命令行界面: ``` mysql -u admin -p -h proxy_server_ip -P proxy_server_port --prompt='ProxySQLAdmin>' ``` 将 "admin" 替换为 ProxySQL 的管理员用户名,将 "proxy_server_ip" 替换为 ProxySQL 服务器的 IP 地址,将 "proxy_server_port" 替换为 ProxySQL 服务器的端口号。 4. 添加主服务器和从服务器:在 ProxySQL 的命令行界面中,使用以下命令添加主服务器和从服务器: ``` INSERT INTO mysql_servers (hostgroup_id, hostname, port) VALUES (1, 'master_server_ip', 3306); INSERT INTO mysql_servers (hostgroup_id, hostname, port) VALUES (2, 'slave_server_ip', 3306); ``` 将 "master_server_ip" 替换为主服务器的 IP 地址,将 "slave_server_ip" 替换为从服务器的 IP 地址。 5. 添加主从关系:在 ProxySQL 的命令行界面中,使用以下命令添加主从关系: ``` INSERT INTO mysql_replication_hostgroups (writer_hostgroup, reader_hostgroup) VALUES (1, 2); ``` 这将指定主服务器和从服务器的关系,其中 "writer_hostgroup" 是主服务器的组 ID,"reader_hostgroup" 是从服务器的组 ID。 6. 保存更改并重新加载配置:在 ProxySQL 的命令行界面中,使用以下命令保存更改并重新加载配置: ``` LOAD MYSQL SERVERS TO RUNTIME; SAVE MYSQL SERVERS TO DISK; LOAD MYSQL SERVERS TO RUNTIME; SAVE MYSQL SERVERS TO DISK; ``` 7. 配置应用程序:在应用程序的数据库连接配置中,将读操作的请求连接到 ProxySQL 服务器的 IP 地址和端口号。 请注意,上述步骤仅提供了一个基本的配置示例,你可能需要根据你的环境和需求进行调整。此外,ProxySQL 还提供了许多其他的功能和配置选项,如负载均衡、故障转移等。你可以参考 ProxySQL 的官方文档以了解更多详细信息。

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值