MYSQL读写分离应用、使用ProxySQL实现读写分离

最新推荐文章于 2025-06-12 09:51:06 发布

zerocdn

最新推荐文章于 2025-06-12 09:51:06 发布

阅读量176

点赞数

分类专栏： MYSQL 文章标签： mysql linux

本文链接：https://blog.csdn.net/zerocdn/article/details/104477827

版权

MYSQL 专栏收录该内容

13 篇文章

订阅专栏

MYSQL读写分离的应用

mysql-proxy：Oracle，https://downloads.mysql.com/archives/proxy/
Atlas：Qihoo https://github.com/Qihoo360/Atlas/blob/master/README_ZH.md
dbproxy：美团，https://github.com/Meituan-Dianping/DBProxy
Cetus：网易乐得，https://github.com/Lede-Inc/cetus
Amoeba：https://sourceforge.net/projects/amoeba/
Cobar：阿里巴巴，Amoeba的升级版
Mycat：基于Cobar， http://www.mycat.io/
ProxySQL：https://proxysql.com/

这里介绍一下ProxySQL

ProxySQL实现读写分离

MySQL中间件，两个版本：官方版和percona版，percona版是基于官方版基础上修改，C++语言开发，轻量级但性能优异(支持处理千亿级数据)，具有中间件所需的绝大多数功能，包括：
多种方式的的读/写分离
定制基于用户、基于schema、基于语句的规则对SQL语句进行路由
缓存查询结果
后端节点监控

安装

基于YUM仓库安装
cat <<EOF | tee /etc/yum.repos.d/proxysql.repo
[proxysql_repo]
name= ProxySQL YUM repository
baseurl= http://repo.proxysql.com/ProxySQL/proxysql-1.4.x/centos/$releasever
gpgcheck=1
gpgkey=http://repo.proxysql.com/ProxySQL/repo_pub_key
EOF

基于RPM下载安装：https://github.com/sysown/proxysql/releases
ProxySQL组成
服务脚本：/etc/init.d/proxysql
配置文件：/etc/proxysql.cnf
主程序：/usr/bin/proxysql

使用

准备：实现读写分离前，先实现主从复制
注意：slave节点需要设置read_only=1

启动ProxySQL：service proxysql start
启动后会监听两个默认端口
6032：ProxySQL的管理端口
6033：ProxySQL对外提供服务的端口

使用mysql客户端连接到ProxySQL的管理接口6032，默认管理员用户和密码都是admin：
mysql -uadmin -padmin -P6032 -h127.0.0.1

说明：在main和monitor数据库中的表， runtime_开头的是运行时的配置，不能修改，只能修改非runtime_表，修改后必须执行LOAD … TO RUNTIME才能加载到RUNTIME生效，执行save … to disk将配置持久化保存到磁盘

监控模块的指标保存在monitor库的log表中

查看监控连接是否正常的 (对connect指标的监控)：(如果connect_error的结果为NULL则表示正常)
select * from mysql_server_connect_log;

查看监控心跳信息 (对ping指标的监控)：
select * from mysql_server_ping_log;

查看read_only和replication_lag的监控日志
select * from mysql_server_read_only_log;
select * from mysql_server_replication_lag_log;

ProxySQL配置步骤参考

在搭建好主从复制的基础上再来配置ProxySQL，从节点上一定要开启read_only

1.准备工作，在主服务器上添加用于监控和发送读写请求的用户给ProxySQL使用

grant replication client on *.* to proxy1@'192.168.1.%'identified by 'test11';
grant replication client on *.* to proxy2@'192.168.1.%'identified by 'test11';
因为已经搭建了主从复制，从服务器上会自动同步这个账号

2.安装启动完毕后用mysql客户端连接ProxySQL，默认用户名密码是admin
mysql -uadmin -padmin -P6032 -h127.0.0.1

3.在main数据库中有个mysql_servers表用来记录mysql信息，往里面插入数据

这里的IP中10是主，11是从，设置为不同的分组
insert into mysql_servers(hostgroup_id,hostname,port)values(10,'192.168.1.10',3306);
insert into mysql_servers(hostgroup_id,hostname,port)values(20,'192.168.1.11',3306);
数据分别是分组ID，IP，端口

4.设置proxy1为监控用的账户

set mysql-monitor_username='proxy1';
set mysql-monitor_password='test11';

5.保存和重新加载配置，检查配置是否正常

重新加载配置
load mysql variables to runtime;

保存配置
save mysql variables to disk;

检查连接状态是否正常
select * from mysql_server_connect_log;

6.设置分组信息
需要修改的是main库中的mysql_replication_hostgroups表，该表有3个字段：
writer_hostgroup，reader_hostgroup，comment, 指定写组的id为10，读组的id为20将mysql_replication_hostgroups表的修改加载到RUNTIME生效。

insert into mysql_replication_hostgroups values(10,20,"test");
load mysql servers to runtime;
save mysql servers to disk

7.配置发送分离语句用的账户
将用户proxy2添加到mysql_users表中， default_hostgroup默认组设置为写组10，当读写分离的路由规则不符合时，会访问默认组的数据库

insert into mysql_users(username,password,default_hostgroup)values('proxy2','test11',10);
load mysql users to runtime;
save mysql users to disk;

测试是否能路由到默认的10写组实现读、写数据
mysql -uproxy2 -ptest11 -P6033 -h127.0.0.1 -e 'select @@server_id'
mysql -uproxy2 -ptest11 -P6033 -h127.0.0.1 -e 'create database test'
mysql -uproxy2 -ptest11 -P6033 -h127.0.0.1 -e 'use test;create table t(id int)'

8.配置路由规则，实现读写分离
与规则有关的表：mysql_query_rules和mysql_query_rules_fast_routing，后者是前者的扩展表，1.4.7之后支持

插入路由规则：将select语句分离到20的读组，select语句中有一个特殊语句SELECT…FOR UPDATE它会申请写锁，应路由到10的写组

注意：因ProxySQL根据rule_id顺序进行规则匹配，select … for update规则的rule_id必须要小于普通的select规则的rule_id

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);

load mysql query rules to runtime;
save mysql query rules to disk;

测试读操作是否路由给20的读组
mysql -uproxy2 -ptest11 -P6033 -h127.0.0.1 -e 'select @@server_id'

测试写操作，以事务方式进行测试
mysql -uproxy2 -ptest11 -P6033 -h127.0.0.1 \
-e 'start transaction;select @@server_id;commit;select @@server_id'

mysql -uproxy2 -ptest11 -P6033 -h127.0.0.1 -e 'insert testdb.t values (1)'

mysql -uproxy2 -ptest11 -P6033 -h127.0.0.1 -e 'select id from testdb.t'

路由的信息查询stats库中的stats_mysql_query_digest表

SELECT hostgroup hg,sum_time, count_star, digest_text FROM stats_mysql_query_digest ORDER BY sum_time DESC;