首页 MySQL读写分离
文章
取消

MySQL读写分离

读写分离1

1 介绍

1.1. 介绍

读写分离,简单的说是把对数据库的读和写操作分开,以对应不同的数据库服务器。主数据库提供写操作,从数据库提供读操作,这样能有效的减轻单台数据库的压力。

通过MyCat即可轻易实现上述功能,不仅可以支持MySQL,也可以支持Oracle和SQLServer。

image-20230306215157287

2 一主一从

2.1. 原理2

MySQL的主从复制,是基于二进制文件(binlog)实现的。

主从复制

2.2. 环境准备&搭建3

主从复制环境准备&搭建

3 一主一从读写分离

3.1. 配置

  1. 配置说明

    MyCat控制后台数据库的读写分离和负载均衡由 schema.xml 文件 datahost 标签的 balance 属性控制。

    image-20230306222640054

  2. balance (负载均衡策略)说明

    1
    2
    3
    4
    5
    6
    
    <dataHost name="dhost7" maxCon="1000" minCon="10" balance="2" writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1" slaveThreshold="100">
        <heartbeat>select user()</heartbeat>
        <writeHost host="master" url="jdbc:mysql://127.0.0.1:3306?useSSL=false&amp;serverTimeZone=Asia/Shanghai&amp;characterEncoding=utf8" user="root" password="123456">
    			<readHost host="slave" url="jdbc:mysql://127.0.0.1:3307?useSSL=false&amp;serverTimeZone=Asia/Shanghai&amp;characterEncoding=utf8" user="root" password="123456"/>
    		</writeHost>
    </dataHost>
    
    参数值含义
    0不开启读写分离机制,所有读操作都发送到当前可用的writeHost上,不访问readHost
    1全部的readHost 与备用的writeHost 都参与 select语句的负载均衡(主要针对于双主双从模式)
    2所有的读写操作都随机在 writeHost,readHost上分发
    3所有的读请求随机分发到 writeHost 对应的readHost上执行,writeHost 不负担读压力
  3. 测试 - 略

  4. 问题:主节点Master宕机之后,业务系统就只能够读,而不能写入数据了。--数据库的高可用。

4 双主双从

4.1. 介绍

一个主机 master1 用于处理所有写请求,它的从机 slave1 和另一台主机 master2 以及master2的从机 slave2 负责所有读请求。当master1 主机宕机后master2 主机负责写请求,master1、master2 互为备机。

双主双从

4.2. 准备工作(5台服务器)

编号IP预装软件角色
1192.168.88.1MyCat、MySQLMyCat中间件服务器
2192.168.88.2MySQLMaster1
3192.168.88.3MySQLSlave1
4192.168.88.4MySQLMaster2
5192.168.88.5MySQLSlave2
1
2
3
## 关闭所有服务器防火墙 - 生产环境值开放相应的内网端口
systemctl stop firewalld
systemctl disable firewalld

4.3. 双主双从搭建

  1. 主库配置(Master1)

    • 修改配置文件/etc/my.cnf
      1
      2
      3
      4
      5
      6
      7
      8
      9
      
         ## 主从复制配置
         # 服务id
         server-id=1
         #指定同步的数据库
         binlog-do-db=db01
         binlog-do-db=db02
         binlog-do-db=db03
         # 在作为主数据库的时候,有写入操作也要更新二进制文件
         log-slave-updates
      
    • 重启mysql服务

      1
      
         systemctl restart mysqld
      
  2. 主库配置(Master2)

    • 修改配置文件/etc/my.cnf

      1
      2
      3
      4
      5
      6
      7
      8
      9
      
      ## 主从复制配置
      # 服务id
      server-id=3
      #指定同步的数据库
      binlog-do-db=db01
      binlog-do-db=db02
      binlog-do-db=db03
      # 在作为主数据库的时候,有写入操作也要更新二进制文件
      log-slave-updates
      
    • 重启mysql服务

  3. 搭建两台主库

    • 两台主库创建账户并授权

      ## 创建用户
      create user 'itcast'@'%' identified with mysql_native_password;
      ## 分配权限
      grant  replication slave on *.* to 'itcast'@'%';
      
    • 分别查看两台主库的二进制日志坐标

      show master status;
      
  4. 从库配置(Slave1)

    • 修改配置文件/etc/my.cnf

      1
      2
      3
      4
      
      # 主从复制
      ## mysql 服务ID,保证整个集群环境中唯一,取值范围:1 - 2的32次方 -1
      ## 和主库不同即可
      server-id=2
      
    • 重启mysql服务

  5. 从库配置(Slave2)
    • 修改配置文件/etc/my.cnf

      1
      2
      3
      4
      
      # 主从复制
      ## mysql 服务ID,保证整个集群环境中唯一,取值范围:1 - 2的32次方 -1
      ## 和主库不同即可
      server-id=4
      
    • 重启mysql服务

  6. 搭建

    • 两台从库配置关联主库

      ## Slave1 对应 Master1
      change master to master_host ='192.168.88.2',master_port =3306,master_user ='itcast',master_password ='123456', master_log_file ='binlog.000037',master_log_pos =370;
      ## Slave2 对应 Master2
      change master to master_host ='192.168.88.4',master_port =3306,master_user ='itcast',master_password ='123456', master_log_file ='binlog.000002',master_log_pos =1052;
      
    • 启动两台从库主从复制,查看从库状态

      start slave;
      show slave status;
      
  7. 两台主库相互复制

    • Master2复制Master1,Master1复制Master2

      ##Master2复制Master1
      change master to master_host ='192.168.88.2',master_port =3066,master_user ='itcast',master_password ='123456', master_log_file ='binlog.000037',master_log_pos =370;
      ##Master1复制Master2
      change master to master_host ='192.168.88.4',master_port =3066,master_user ='itcast',master_password ='123456', master_log_file ='binlog.000002',master_log_pos =1052;
      
    • 启动两台从库主从复制,查看从库状态

      start slave;
      show slave status;
      
  8. 测试

    create schema db01;
    create table tb_user(
        id   int(11)     not null primary key,
        name varchar(50) not null,
        sex  varchar(1)
    ) engine = innodb  default charset = utf8mb4;
    insert into db01.tb_user(id, name, sex)
    values (1, 'Tom', '1'),(2, 'Trigger', '0'),(3, 'Dawn', '1'),(4, 'Jack Ma', '1'),(5, 'Coco', '0'),(6, 'Jerry', '1');
    

5 双主双从读写分离

5.1. 配置

MyCat控制后台数据库的读写分离和负载均衡由schema.xml文件dataHost标签的balance属性控制,通过writeType和switchType来完成失败自动切换。

image-20230308143910321

dataHost配置说明:

image-20230308144700931

  • balance='1':

    代表全部的readHost与stand by writeHost 参与 select 语句的负载均衡,简单的说,当双主双从模式(M1->S1,M2->S2,并且M1与M2互为主备),正常情况下,M2,S1,S2都参与Select语句的负载均衡。

  • writeType:
    • 0:写操作都转发到第一台writeHost,writeHost1挂了,会切换到writeHost2上;
    • 1:所有的写操作都随机地发送到配置的writeHost上;
  • switchType:
    • -1:writeHost1挂掉后,不自动切换到writeHost2
    • 1:writeHost1挂掉后,自动切换到writeHost2

5.2. 测试-略

  1. 参考黑马程序员 

  2. [MySQL主从复制 #2-原理睦mu的博客 (sn-mumu.github.io)](https://sn-mumu.github.io/posts/MySQL主从复制/#2-原理)

  3. [MySQL主从复制 #3-搭建睦mu的博客 (sn-mumu.github.io)](https://sn-mumu.github.io/posts/MySQL主从复制/#3-搭建)

本文由作者按照 CC BY 4.0 进行授权

MySQL分库分布表

MySQL导图