MySQL-读写分离

Onlooker129

已于 2024-08-04 21:16:48 修改

阅读量607

点赞数 6

分类专栏： MySQL 文章标签： mysql 数据库

于 2024-08-04 21:16:03 首次发布

本文链接：https://blog.csdn.net/m0_73902080/article/details/140911409

版权

MySQL 专栏收录该内容

12 篇文章 1 订阅

订阅专栏

介绍

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

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

一主一从

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

准备：

一主一从读写分离

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

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关联的是从库

最后还需要配置负载均衡的参数balance，取值有四种：

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

配置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>

存在问题：当主节点Master宕机后，业务系统就只能读，不能写

解决方式：双主双从

双主双从

一个主机Matser1用于处理所有写请求，它的从机Slave1和另一台主机Master2及它的从机Slave2负责所有读请求，当Master1主机宕机后，Master2主机负责写请求，Master1、Master2互为备机

准备：

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

systemctl stop firewalld 
systemctl disable firewalld

配置Master1(192.168.200.211)：

修改配置文件 /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

重启MySQL服务器

systemctl restart mysqld

创建用户并授权

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

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

show master status;

配置Master2(192.168.200.213) ：

修改配置文件 /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

重启MySQL服务器

systemctl restart mysqld

创建用户并授权

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

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

show master status;

配置Slave1(192.168.200.212)：

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

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

重启MySQL服务器

systemctl restart mysqld

配置Slave2(192.168.200.214) ：

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

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

重启MySQL服务器

systemctl restart mysqld

两台从库配置关联的主库：

slave1对应的是master1，slave2对应的是master2

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

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

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

start slave;
show slave status \G;

两台主库相互复制：

Master2 复制 Master1，Master1 复制 Master2

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

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

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

start slave;
show slave status \G;

分别在两台主库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');

双主双从读写分离

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

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>

具体的对应情况：

属性说明：

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>

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

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

Onlooker129

关注

6
点赞
踩
18

收藏

觉得还不错? 一键收藏
0
评论
MySQL-读写分离

读写分离，简单地说是把对数据库的读和写操作分开，以对应不同的数据库服务器，主数据库提供写操作，从数据库通过读操作，这样能有效地减轻单台数据库的压力通过MyCat可轻易实现上述功能，不仅可以支持MySQL，也可以支持Oracle和SQL Server。
复制链接

扫一扫

专栏目录