MySQL-运维篇 · 4. 读写分离

MySQL-运维篇 · 4. 读写分离

2026年9月18日·#编程学习/mysql学习笔记MySQL/数据库·2019 字 10 分钟
浏览量加载中...
AI 摘要

读写分离原理，基于 MyCat 的一主一从、双主双从搭建与测试

MySQL-运维篇 · 4. 读写分离#

4.1 介绍#

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

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

4.2 一主一从#

4.2.1 原理#

MySQL的主从复制，是基于二进制日志（binlog）实现的。

4.2.2 准备#

主机角色用户名密码
192.168.200.211masterroot1234
192.168.200.212slaveroot1234

备注：主从复制的搭建，可以参考前面课程中 主从复制 章节讲解的步骤操作。

4.3 一主一从读写分离#

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

4.3.1 schema.xml配置#

<!-- 配置逻辑库 -->
<schema name="ITCAST_RW" checkSQLschema="true" sqlMaxLimit="100" dataNode="dn7"></schema>


<dataNode name="dn7" dataHost="dhost7" database="itcast" />


<dataHost name="dhost7" maxCon="1000" minCon="10" balance="1" writeType="0"dbType="mysql" dbDriver="jdbc" switchType="1"  slaveThreshold="100">
    <heartbeat>select user()</heartbeat>


    <writeHost host="master1" url="jdbc:mysql://192.168.200.211:3306?
useSSL=false&amp;serverTimezone=Asia/Shanghai&amp;characterEncoding=utf8"
user="root" password="1234" >
        <readHost host="slave1" url="jdbc:mysql://192.168.200.212:3306?
useSSL=false&amp;serverTimezone=Asia/Shanghai&amp;characterEncoding=utf8"
user="root" password="1234" />
    </writeHost>
</dataHost>

上述配置的具体关联对应情况如下：

writeHost代表的是写操作对应的数据库，readHost代表的是读操作对应的数据库。 所以我们要想实现读写分离，就得配置writeHost关联的是主库，readHost关联的是从库。

而仅仅配置好了writeHost以及readHost还不能完成读写分离，还需要配置一个非常重要的负责均衡的参数 balance，取值有4种，具体含义如下：

参数值含义
0不开启读写分离机制 , 所有读操作都发送到当前可用的writeHost上
1全部的readHost 与 备用的writeHost 都参与select 语句的负载均衡（主要针对于双主双从模式）
2所有的读写操作都随机在writeHost , readHost上分发
3所有的读请求随机分发到writeHost对应的readHost上执行, writeHost不负担读压力

所以，在一主一从模式的读写分离中，balance配置1或3都是可以完成读写分离的。

4.3.2 server.xml配置#

配置root用户可以访问SHOPPING、ITCAST 以及 ITCAST_RW逻辑库。

<user name="root" defaultAccount="true">
    <property name="password">123456</property>
    <property name="schemas">SHOPPING,ITCAST,ITCAST_RW</property>


    <!-- 表级 DML 权限设置 -->
    <!--
    <privileges check="true">
        <schema name="DB01" dml="0110" >
            <table name="TB_ORDER" dml="1110"></table>
        </schema>
    </privileges>
    -->
</user>

4.3.3 测试#

配置完毕MyCat后，重新启动MyCat。

bin/mycat stop
bin/mycat start

然后观察，在执行增删改操作时，对应的主库及从库的数据变化。 在执行查询操作时，检查主库及从库对应的数据变化。

在测试中，我们可以发现当主节点Master宕机之后，业务系统就只能够读，而不能写入数据了。

那如何解决这个问题呢？这个时候我们就得通过另外一种主从复制结构来解决了，也就是我们接下来讲解的双主双从。

4.4 双主双从#

4.4.1 介绍#

一个主机 Master1 用于处理所有写请求，它的从机 Slave1 和另一台主机 Master2 还有它的从机 Slave2 负责所有读请求。当 Master1 主机宕机后，Master2 主机负责写请求，Master1 、Master2 互为备机。架构图如下:

4.4.2 准备#

我们需要准备5台服务器，具体的服务器及软件安装情况如下：

编号IP预装软件角色
1192.168.200.210MyCat、MySQLMyCat中间件服务器
2192.168.200.211MySQLM1
3192.168.200.212MySQLS1
4192.168.200.213MySQLM2
5192.168.200.214MySQLS2

关闭以上所有服务器的防火墙：

  • systemctl stop firewalld
  • systemctl disable firewalld

4.4.3 搭建#

4.4.3.1 主库配置#

1). Master1(192.168.200.211)

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

#mysql 服务ID，保证整个集群环境中唯一，取值范围：1 – 2^32-1，默认为1
server-id=1
#指定同步的数据库
binlog-do-db=db01
binlog-do-db=db02
binlog-do-db=db03
# 在作为从数据库的时候，有写入操作也要更新二进制日志文件
log-slave-updates

B. 重启MySQL服务器

systemctl  restart  mysqld

C. 创建账户并授权

#创建itcast用户，并设置密码，该用户可在任意主机连接该MySQL服务
CREATE  USER  'itcast'@'%' IDENTIFIED WITH mysql_native_password BY 'Root@123456';
#为 'itcast'@'%' 用户分配主从复制权限
GRANT  REPLICATION  SLAVE  ON  *.*  TO  'itcast'@'%';

通过指令，查看两台主库的二进制日志坐标

show  master  status ;

2). Master2(192.168.200.213)

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

#mysql 服务ID，保证整个集群环境中唯一，取值范围：1 – 2^32-1，默认为1
server-id=3
#指定同步的数据库
binlog-do-db=db01
binlog-do-db=db02
binlog-do-db=db03
# 在作为从数据库的时候，有写入操作也要更新二进制日志文件
log-slave-updates

B. 重启MySQL服务器

systemctl  restart  mysqld

C. 创建账户并授权

#创建itcast用户，并设置密码，该用户可在任意主机连接该MySQL服务
CREATE  USER  'itcast'@'%' IDENTIFIED WITH mysql_native_password BY 'Root@123456';
#为 'itcast'@'%' 用户分配主从复制权限
GRANT  REPLICATION  SLAVE  ON  *.*  TO  'itcast'@'%';

通过指令，查看两台主库的二进制日志坐标

show  master  status ;

4.4.3.2 从库配置#

1). Slave1(192.168.200.212)

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

#mysql 服务ID，保证整个集群环境中唯一，取值范围：1 – 232-1，默认为1
server-id=2

B. 重新启动MySQL服务器

systemctl  restart  mysqld

2). Slave2(192.168.200.214)

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

#mysql 服务ID，保证整个集群环境中唯一，取值范围：1 – 232-1，默认为1
server-id=4

B. 重新启动MySQL服务器

systemctl  restart  mysqld

4.4.3.3 从库关联主库#

1). 两台从库配置关联的主库

需要注意slave1对应的是master1，slave2对应的是master2。

A. 在 slave1(192.168.200.212)上执行

CHANGE MASTER TO MASTER_HOST='192.168.200.211', MASTER_USER='itcast',
MASTER_PASSWORD='Root@123456', MASTER_LOG_FILE='binlog.000002',
MASTER_LOG_POS=663;

B. 在 slave2(192.168.200.214)上执行

CHANGE MASTER TO MASTER_HOST='192.168.200.213', MASTER_USER='itcast',
MASTER_PASSWORD='Root@123456', MASTER_LOG_FILE='binlog.000002',
MASTER_LOG_POS=663;

C. 启动两台从库主从复制，查看从库状态

start slave;
show  slave  status \G;

2). 两台主库相互复制

Master2 复制 Master1，Master1 复制 Master2。

A. 在 Master1(192.168.200.211)上执行

CHANGE MASTER TO MASTER_HOST='192.168.200.213', MASTER_USER='itcast',
MASTER_PASSWORD='Root@123456', MASTER_LOG_FILE='binlog.000002',
MASTER_LOG_POS=663;

B. 在 Master2(192.168.200.213)上执行

CHANGE MASTER TO MASTER_HOST='192.168.200.211', MASTER_USER='itcast',
MASTER_PASSWORD='Root@123456', MASTER_LOG_FILE='binlog.000002',
MASTER_LOG_POS=663;

C. 启动两台从库主从复制，查看从库状态

start slave;
show  slave  status \G;

经过上述的三步配置之后，双主双从的复制结构就已经搭建完成了。 接下来，我们可以来测试验证一下。

4.4.4 测试#

分别在两台主库Master1、Master2上执行DDL、DML语句，查看涉及到的数据库服务器的数据同步情况。

create database db01;
use 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 tb_user(id,name,sex) values(1,'Tom','1');
insert into tb_user(id,name,sex) values(2,'Trigger','0');
insert into tb_user(id,name,sex) values(3,'Dawn','1');
insert into tb_user(id,name,sex) values(4,'Jack Ma','1');
insert into tb_user(id,name,sex) values(5,'Coco','0');
insert into tb_user(id,name,sex) values(6,'Jerry','1');
  • 在Master1中执行DML、DDL操作，看看数据是否可以同步到另外的三台数据库中。
  • 在Master2中执行DML、DDL操作，看看数据是否可以同步到另外的三台数据库中。

完成了上述双主双从的结构搭建之后，接下来，我们再来看看如何完成这种双主双从的读写分离。

4.5 双主双从读写分离#

4.5.1 配置#

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

1). schema.xml

配置逻辑库：

<schema name="ITCAST_RW2" checkSQLschema="true" sqlMaxLimit="100" dataNode="dn7"></schema>

配置数据节点：

<dataNode name="dn7" dataHost="dhost7" database="db01" />

配置节点主机：

<dataHost name="dhost7" maxCon="1000" minCon="10" balance="1" writeType="0"dbType="mysql" dbDriver="jdbc" switchType="1"  slaveThreshold="100">
    <heartbeat>select user()</heartbeat>


    <writeHost host="master1" url="jdbc:mysql://192.168.200.211:3306?
useSSL=false&amp;serverTimezone=Asia/Shanghai&amp;characterEncoding=utf8"
user="root" password="1234" >
       <readHost host="slave1" url="jdbc:mysql://192.168.200.212:3306?
useSSL=false&amp;serverTimezone=Asia/Shanghai&amp;characterEncoding=utf8"
user="root" password="1234" />
    </writeHost>


    <writeHost host="master2" url="jdbc:mysql://192.168.200.213:3306?
useSSL=false&amp;serverTimezone=Asia/Shanghai&amp;characterEncoding=utf8"
user="root" password="1234" >
       <readHost host="slave2" url="jdbc:mysql://192.168.200.214:3306?
useSSL=false&amp;serverTimezone=Asia/Shanghai&amp;characterEncoding=utf8"
user="root" password="1234" />
    </writeHost>
</dataHost>

具体的对应情况如下：

属性说明：

balance=“1”

  • 代表全部的 readHost 与 stand by writeHost 参与 select 语句的负载均衡，简

单的说，当双主双从模式(M1->S1，M2->S2，并且 M1 与 M2 互为主备)，正常情况下，M2,S1,S2 都参与 select 语句的负载均衡 ;

writeType

  • 0 : 写操作都转发到第1台writeHost, writeHost1挂了, 会切换到writeHost2上;
  • 1 : 所有的写操作都随机地发送到配置的writeHost上 ;

switchType

  • -1 : 不自动切换
  • 1 : 自动切换

2). user.xml

配置root用户也可以访问到逻辑库 ITCAST_RW2。

<user name="root" defaultAccount="true">
    <property name="password">123456</property>
    <property name="schemas">SHOPPING,ITCAST,ITCAST_RW2</property>


    <!-- 表级 DML 权限设置 -->
    <!--
    <privileges check="true">
        <schema name="DB01" dml="0110" >
            <table name="TB_ORDER" dml="1110"></table>
        </schema>
    </privileges>
    -->
</user>

4.5.2 测试#

登录MyCat，测试查询及更新操作，判定是否能够进行读写分离，以及读写分离的策略是否正确。

当主库挂掉一个之后，是否能够自动切换。

笔记整理规范
MySQL-运维篇 · 3. 分库分表

相关阅读

1MySQL-运维篇 · 3. 分库分表编程学习/mysql学习笔记2MySQL-运维篇 · 2. 主从复制编程学习/mysql学习笔记3MySQL-运维篇 · 1. 日志编程学习/mysql学习笔记4MySQL-进阶篇 · 7. MySQL管理编程学习/mysql学习笔记5MySQL-进阶篇 · 6. InnoDB引擎编程学习/mysql学习笔记

评论区

[ 标签 ]
# AI37# AI 编程2# AI工具1# Ajax2# Apifox1# AstrBot3# Astro2# CC Switch1# CDN2# Claude Code1# claudecode2# ClaudeCode1# Cloudflare2# CloudFlare2# CloudFlare-ImgBed3# coc3# CSS6# DeepSeek6# deepseek2# DELETE1# Docker1# EdgeOne3# Gist1# git1# GitHub1# hexo-circle-of-friends1# HTML6# HTTP5# ImageManager1# Java23# java13# JavaScript5# JDBC3# JSON2# JUnit1# LangChain25# Logback1# Maven6# Muse Spark1# Mybatis1# MyBatis4# MySQL28# MySql1# NapCat1# Node.js1# obsidian2# Obsidian5# OpenCode4# ORM1# PathVariable1# PicGo1# PyCharm1# Python65# RequestBody1# RequestMapping1# RESTful风格1# skills1# Slf4j1# SpringBoot11# SQL2# Streamlit5# Svelte2# TailwindCSS1# Telegram3# Tlias2# Vercel1# vscode2# Vue7# Waline3# WebDAV1# Web基础6# Web开发6# WinSCP1# YAML1# 三层架构1# 中二宣言1# 书籍1# 使用文档10# 写作1# 函数2# 刷步数1# 前端32# 动态1# 动漫1# 包1# 单词2# 博客7# 博客工作流1# 博客开发2# 参数接收1# 友链1# 反思2# 图床6# 地图1# 备份2# 大模型1# 奇思妙想1# 存储1# 学习方法6# 学校1# 宝塔面板3# 宝宝10# 对象1# 导航栏1# 工具2# 开发1# 开发工具1# 开发规范1# 开心1# 异常处理1# 影视2# 微信1# 性能优化2# 总结1# 想法15# 感受1# 感悟11# 指南1# 提示词工程1# 插件5# 故障排除1# 效率工具2# 教程10# 数据分析9# 数据库27# 数据结构1# 文件操作2# 斩神1# 日常92# 日志框架1# 朋友圈1# 朱元璋1# 模块1# 模板1# 正则表达式2# 测试1# 游戏2# 爬虫7# 生活迁移1# 电影2# 电脑1# 碎碎念1# 视觉识别1# 类1# 类型注解1# 网络基础2# 网络教室1# 羊毛2# 脚本2# 脚本工具1# 自动化2# 蓝奏云1# 订阅推荐2# 记录2# 评论系统1# 词根1# 词缀1# 说说1# 足迹1# 跑步2# 路径参数1# 转载2# 运动1# 部落冲突1# 配置1# 随机图1# 面向对象5# 音乐3# 音标1# 饮食1# 驼峰命名1# 高德地图1
[ 公告 ]

如果你喜欢，那么欢迎来到我的世界！

了解更多
[ 音乐 ]
封面

音乐

暂未播放

0:000:00
暂无歌词
找不到相关结果。
[ contents ]
[ 全部文章 ]