mysql进阶2——prosysql实现mysql读写分离

这篇具有很好参考价值的文章主要介绍了mysql进阶2——prosysql实现mysql读写分离。希望对大家有所帮助。如果存在错误或未考虑完全的地方,请大家不吝赐教,您也可以点击"举报违法"按钮提交疑问。

一、读写分离方案类型

基本了解:

  1. 实现数据库读写分离的中间件很多,比如Mycat、Cobar、Atlas、TDDL、Proxysql等等。
  2. ProxySQL 可以灵活配置路由规则,不仅可以实现最简单的读写分离,还可以将读/写都分散到多个不同的组,以及实现分库 sharding (分表sharding的规则比较难写,但也能实现)。

1.1 最简单的读写分离

  • 这种模式的读写分离,严格区分后端的master和slave节点,且slave节点必须设置选项read_only=1。
  • 在ProxySQL上,分两个组,自定义组名,比如定义一个写组HG=10,一个读组HG=20。同时在ProxySQL上开启monitor模块的read_only监控功能,让ProxySQL根据监控到的read_only值来自动调整节点放在HG=10(master会放进这个组)还是HG=20(slave会放进这个组)。
  • 这种模式的读写分离是最简单的,只需在mysql_users表中设置用户的默认路由组为写组HG=10,并在mysql_query_rules中加上两条简单的规则(一个select for update,一个select)即可。
  • 这种读写分离模式,在环境较小时能满足绝大多数需求。但是需求复杂、环境较大时,这种模式就太过死板,因为一切都是monitor模块控制的。
    mysql进阶2——prosysql实现mysql读写分离,mysql,mysql,android,adb,运维,数据库

1.2 多个读组或写组的分离模式

  • 后端做了分库,对某库的查询要路由到特定的主机组。
  • 至于各个主机组是同一个主从集群(下图左边),还是互相独立的主从集群环境(下图右边),要看具体的需求,不过这种读写分离模式都能应付。
  • 在实现这种模式时,前提是不能开启monitor模块的read_only监控功能,也不要设置mysql_replication_hostgroup 表。
    mysql进阶2——prosysql实现mysql读写分离,mysql,mysql,android,adb,运维,数据库

1.如下配置实现的是上图左边的结构:写请求路由给HG=10,对test1库的select语句路由给HG=20,其它select路由给HG=30

mysql_servers: 
+--------------+----------+------+--------+--------+
| hostgroup_id | hostname | port | status | weight |
+--------------+----------+------+--------+--------+
| 10           | host1    | 3306 | ONLINE | 1      |
| 20           | host2    | 3306 | ONLINE | 1      |
| 30           | host3    | 3306 | ONLINE | 1      |
+--------------+----------+------+--------+--------+

mysql_users: 
+----------+-------------------+
| username | default_hostgroup |
+----------+-------------------+
| root     | 10                |
+----------+-------------------+

mysql_query_rules: 
+---------+-----------------------+----------------------+
| rule_id | destination_hostgroup | match_digest         |
+---------+-----------------------+----------------------+
| 1       | 10                    | ^SELECT.*FOR UPDATE$ |
| 2       | 20                    | ^SELECT.*test1\..*   |
| 3       | 30                    | ^SELECT              |
+---------+-----------------------+----------------------+

二、案例

IP 角色 应用 系统平台
192.168.161.129 读写分离解析主机 proxysql、mariadb客户端 CentOS7
192.168.161.130 master mariadb服务端 CentOS7
192.168.161.131 slave mariadb服务端 CentOS7

2.1 初始化操作

1.配置mysql主从,参考文章。

mysql进阶2——prosysql实现mysql读写分离,mysql,mysql,android,adb,运维,数据库

2.安装proxysql服务,参考文章,并添加连接mysql显示信息参数。

export MYSQL_PS1="(\u@\h:\p) [\d]> " 

mysql进阶2——prosysql实现mysql读写分离,mysql,mysql,android,adb,运维,数据库

2.2 mysql主添加proxysql连接用户

1.在mysql主上进行添加,让proxysql服务器能连接到后端mysql集群。

grant all on *.* to 'proadmin'@'192.168.161.129' identified by 'proadmin';
flush privileges;

2.peoxysql服务器上验证。

mysql -uproadmin -pproadmin -h192.168.161.130

mysql进阶2——prosysql实现mysql读写分离,mysql,mysql,android,adb,运维,数据库

2.3 Proxysql添加连接mysql集群参数

  • 以下所有操作,在proxysql服务器上操作。

1.添加mysql集群信息到proxysql自己的mysql_servers表中,包括mysql分组、ip端口、权重。

//连接proxysql自己。
mysql -uadmin -padmin -P6032 -h127.0.0.1

//添加mysql主机信息到mysql_servers 表中。hostgroup_id 10表示写组,20表示读组。
insert into mysql_servers(hostgroup_id,hostname,port,weight,comment) values(10,'192.168.161.130',3306,1,'Write Group');
insert into mysql_servers(hostgroup_id,hostname,port,weight,comment) values(20,'192.168.161.131',3306,1,'Read Group');

//保存配置。
load mysql servers to run;
save mysql servers to disk;

//查看配置
select hostgroup_id,hostname,port,weight,comment from mysql_servers;

mysql进阶2——prosysql实现mysql读写分离,mysql,mysql,android,adb,运维,数据库
2.添加mysql上已创建的连接mysql用户信息添加到proxysql自己的mysql_users表中。这里添加的用户就是之前在mysql主上添加的那个用户。

//default_hostgroup表示默认组设置为写组。当读写分离的路由规则不符合时,会访问默认组的数据库。
insert into mysql_users(username,password,default_hostgroup,transaction_persistent)values('proadmin','proadmin',10,1);

//保存配置。
load mysql users to run;
save mysql users to disk;

//查看。
select username,password,default_hostgroup,transaction_persistent from mysql_users;

mysql进阶2——prosysql实现mysql读写分离,mysql,mysql,android,adb,运维,数据库

(admin@127.0.0.1:6032) [(none)]> select * from mysql_users \G
*************************** 1. row ***************************
              username: proadmin       # 后端mysql实例的用户名
              password: proadmin       # 后端mysql实例的密码
                active: 1              # active=1表示用户生效,0表示不生效
               use_ssl: 0
     default_hostgroup: 10             # 用户默认登录到哪个hostgroup_id下的实例
        default_schema: NULL           # 用户默认登录后端mysql实例时连接的数据库,这个地方为NULL的话,则由全局变量mysql-default_schema决定,默认是information_schema
         schema_locked: 0
transaction_persistent: 1              # 如果设置为1,连接上ProxySQL的会话后,如果在一个hostgroup上开启了事务,那么后续的sql都继续维持在这个hostgroup上,不论是否会匹配上其它路由规则,直到事务结束。虽然默认是0
          fast_forward: 0              # 忽略查询重写/缓存层,直接把这个用户的请求透传到后端DB。相当于只用它的连接池功能,一般不用,路由规则 .* 就行了
               backend: 1
              frontend: 1
       max_connections: 10000          # 该用户允许的最大连接数
               comment: 

2.4 添加健康检测用户

1.在mysql主上操作,给proxysql服务器添加一个只读用户,可自定义。

grant select on *.* to 'qingjun'@'192.168.161.129' identified by 'citms';
flush privileges;

2.在proxysql上操作,修改变量,设置健康检测的账号。

//连接proxysql自己。
mysql -uadmin -padmin -P6032 -h127.0.0.1

set mysql-monitor_username='qingjun';
set mysql-monitor_password='citms';
load mysql variables to runtime;
save mysql variables to disk;

2.5 添加读写分离的路由规则

注意事项:

  1. 规则id不能是1、2、3开头,因为规则匹配是根据id以小到大的优先级匹配,id越小的规则应该越是精确匹配。
//规则id由小到大,必须是精准匹配——>模糊匹配。
insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply) values(9,1,'^SELECT.*FOR DROP$',10,1);
insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply) values(10,1,'^SELECT.*FOR UPDATE$',10,1);
insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply)values(20,1,'^SELECT.*school\..*',20,1);
insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply)values(29,1,'^DESC.*',20,1);
insert into mysql_query_rules(rule_id,active,match_digest,destination_hostgroup,apply)values(30,1,'^SHOW.*',20,1);

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

select rule_id,active,match_digest,destination_hostgroup,apply  from mysql_query_rules;

mysql进阶2——prosysql实现mysql读写分离,mysql,mysql,android,adb,运维,数据库

2.6 验证

1.使用mysql第一次授权的可读可写账户登录proxysql,进去修改mysql集群数据。

//我这里把连接mysql集群端口改成了3306,默认是6032
mysql -uproadmin -pproadmin -P3306 -h127.0.0.1


//proxysql上的操作命令。
show databases;
create database baimu;
create database school;
select * from school.abc;

2.使用proxysql服务端admin账户登录,查看读写分离效果。

mysql -uadmin -padmin -P6032 -h127.0.0.1 -e 'select * from stats_mysql_query_digest\G' |less

mysql进阶2——prosysql实现mysql读写分离,mysql,mysql,android,adb,运维,数据库文章来源地址https://www.toymoban.com/news/detail-613632.html

到了这里,关于mysql进阶2——prosysql实现mysql读写分离的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处: 如若内容造成侵权/违法违规/事实不符,请点击违法举报进行投诉反馈,一经查实,立即删除!

领支付宝红包 赞助服务器费用

相关文章

  • 项目实现读写分离操作(mysql)

    Master(主库)----(数据同步)— Slave(从库) Mysql主从复制 mysql主从复制 介绍 mysql主从复制是一个异步的复制过程,底层是基于mysql数据库自带的 二进制日志 功能。就是一台或多台mysql数据库(Slave,即从库)从另一台mysql数据库(Master,即主库)进行日志的复制然后再解析日

    2024年02月05日
    浏览(40)
  • mycat实现mysql读写分离

    mycat集群+Haproxy+Keepalived+mysql1主2从 环境规划 centos7.9 1主2从,读写分离 名称 ip 端口 mysql-master 192.168.1.220 3306 mysql-slave1 192.168.1.221 3306 mysql-slave2 192.168.1.222 3306 mycat-1 192.168.1.221 8066 mycat-2 192.168.1.222 8066 haproxy-1 192.168.1.221 18066 haproxy-2 192.168.1.222 18066 keepalived-1 192.168.1.221 keepalived-2 1

    2024年01月19日
    浏览(42)
  • laravel实现mysql读写分离(二)

    Laravel5 读写分离主要有两个过程: 第一步,根据 database.php 配置,创建写库和读库的链接 connection 第二步,调用 select 时先判断使用读库还是写库,而 insert/update/delete 统一使用写库 主要文件:Illuminate/Database/Connectors/ConnectionFactory.php 1:判断 database.php 是否配置了读写分离数据库

    2024年02月03日
    浏览(79)
  • SQL运维-读写分离

           

    2024年02月14日
    浏览(67)
  • MariaDB MaxScale实现mysql8读写分离

    MaxScale 是干什么的? MaxScale是maridb开发的一个mysql数据中间件,其配置简单,能够实现读写分离,并且可以根据主从状态实现写库的自动切换,对多个从服务器能实现负载均衡。 MaxScale 实验环境 中间件 192.168.142.139 MaxScale 22.08.4 主服务器 192.168.142.146 mysql 8.0.30 从服务器 192.16

    2024年03月09日
    浏览(45)
  • 使用ProxySql实现Mysql的读写分离 详细安装步骤 亲测可行

    主机ip 说明 192.168.168.109 ProxySql 192.168.168.77 mysql master(主) 192.168.168.78 mysql slave(从)   在192.168.168.109机器上操作 在192.168.168.109机器上操作 报错 在192.168.168.109机器上操作 在192.168.168.109机器上操作 在192.168.168.109机器上操作 在192.168.168.109机器上操作 管理员登录 ProxySQL 前提也需

    2024年01月18日
    浏览(69)
  • 分库分表之基于Shardingjdbc+docker+mysql主从架构实现读写分离 (三)

            本篇主要说明:                 1. 因为这个mysql版本是8.0,所以当其中一台mysql节点挂掉之后,主从同步,甚至双向数据同步都失效了,所以本篇主要记录下当其中的节点挂掉之后如何再次生效。 另外推荐大家使用mysql5.7的版本,这样当其他节点失效后就不

    2024年02月14日
    浏览(39)
  • 分库分表之基于Shardingjdbc+docker+mysql主从架构实现读写分离(二)

            说明:如果实现了docker部署mysql并完成主从复制的话再继续,本篇文章主要说明springboot配置实现Shardingjdbc进行读写分离操作。 如果没实现docker部署mysql实现主从架构的话点击我 application.yml配置设置连接池全局属性 shardingjdbc读写分离配置(qiyu-db-sharding.yaml) 同时这

    2024年02月15日
    浏览(41)
  • 基于k8s搭建mysql5.7主从集群实现读写分离

    一,准备工作 1,拥有一个运行中的k8s集群 2,拥有一个ceph分布式存储集群,此mysql集群基于ceph块存储,部署方案可参考我前面的rook部署ceph方案 二,集群搭建 1,创建存储类storageclass       将此 StorageClass 定义保存为 storageclass.yaml : 创建存储类   2,MySQL 部署包含一个 Con

    2024年02月08日
    浏览(42)
  • python 实现对MySQL 主从复制与读写分离 --2023博客之星候选--城市赛道

    在Python中,可以使用 pymysql 库来实现对MySQL主从复制和读写分离的代码封装。以下是一个简单的示例: 在上述示例中,创建了一个名为 MySQLReplication 的类,该类用于封装主从数据库连接和执行查询的功能。在类的初始化方法中,传入主数据库和从数据库的配置信息,并创建连

    2024年02月11日
    浏览(45)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

请作者喝杯咖啡吧~博客赞助

支付宝扫一扫领取红包,优惠每天领

二维码1

领取红包

二维码2

领红包