首页 MySQL读写分离
文章
取消

MySQL读写分离

发表于 2023/03/06 更新于 2023/03/08
作者 睦mu
7 分钟阅读

读写分离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-搭建)

    ↩

mysql, 读写分离
数据库 mysql MySQL读写分离 读写分离
本文由作者按照 CC BY 4.0 进行授权
最近更新
文章内容

MySQL分库分布表

MySQL导图