【MySQL】读写分离（Mycat）

---

前言

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

1️⃣Mycat将应用程序的insert/update/delete操作路由到主库master，然后同步到从库slave
2️⃣Mycat将应用程序的select操作路由到从库slave。

---

一、一主一从

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

主从复制原理以及环境搭建详见👉点击即可

🌰测试

create database itcast;

use itcast;

create table tb_user(
	id int(11) not null,
	name varchar(50) not null,
	sex varchar(1),
	primary key (id)
)engine=innodb default charset=utf8;

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');

二、一主一从读写分离

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

balance属性：负载均衡策略，目前取值有4种

balance取值为1或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="master" url="jdbc:mysql://192.168.88.135:3306?useSSL=false&amp;serverTimezone=Asia/Shanghai&amp;characterEncoding=utf8" user="root" password="123456" >
        <readHost host="slave" url="jdbc:mysql://192.168.88.132:3306?useSSL=false&amp;serverTimezone=Asia/Shanghai&amp;characterEncoding=utf8" user="root" password="123456" >
        </readHost>
    </writeHost>
</dataHost>

2️⃣ server.xml为root用户提供访问权限

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

存在问题：主节点Master宕机后，业务系统就只能够读，而不能写入数据了，然而就用双主双从来解决

三、双主双从

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

🌮准备工作：需要准备5台服务器，具体的服务器及软件安装情况如下（示例）

# 关闭以上服务器的防火墙
systemctl stop firewalld
systemctl disable firewalld

🌮搭建

• 主库配置（Master1）

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

#  重启服务器
systemctl restart mysqld

• 主库配置（Master2）

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

#  重启服务器
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）

# 修改配置文件 /etc/my.cnf
# mysql服务ID，保证整个集群环境中唯一，取值范围：1~2^32-1，默认为1
server-id=2

#  重启服务器
systemctl restart mysqld

• 从库配置（Slave2）

# 修改配置文件 /etc/my.cnf
# mysql服务ID，保证整个集群环境中唯一，取值范围：1~2^32-1，默认为1
server-id=4

#  重启服务器
systemctl restart mysqld

• 两台从库配置关联的主库（slave1对应的是master1，slave2对应的是master2）

# 分别在两个从库中执行以下命令
CHANGE MASTER TO MASTER_HOST='xxx.xxx.xxx.xxx',MASTER_USER='xxx',MASTER_PASSWORD='xxx',MASTER_LOG_FILE='xxx',MASTER_LOG_POS='xxx';

# 启动两台从库主从复制， 开启同步操作
start slave;  
# 查看主从同步状态
show slave status\G; 

• 两台主库相互复制

# Master2复制Master1，Master1复制Master2
CHANGE MASTER TO MASTER_HOST='xxx.xxx.xxx.xxx',MASTER_USER='xxx',MASTER_PASSWORD='xxx',MASTER_LOG_FILE='xxx',MASTER_LOG_POS='xxx';

# 启动两台主库主从复制， 开启同步操作(互相为彼此的主从库)
start slave;  
# 查看主从同步状态
show slave status\G;

• 测试

# 分别在主库Master1、Master2上执行DDL、DML语句，查看涉及到的数据库服务器的数据同步情况
create database db01;

use db01;

create table tb_user(
	id int(11) not null,
	name varchar(50) not null,
	sex varchar(1),
	primary key (id)
)engine=innodb default charset=utf8;

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文件dataNode标签的balance属性控制，通过writeType及switchType来完成失败自动切换的

🌮配置

配置解读

1. balance="1"：代表全部的readHost和stand by writeHost参与select语句的负载均衡，简单地说，当双主双从模式（M1->S1,M2->S2,并且M1与M2互为主备），正常情况下，M1负责写入，M2,S1,S2都参与select语句的负载均衡
2. writeType：0代表写操作都转发到第1台writeHost，writeHost1挂了，会切换到writeHost2上；1代表所有的写操作都随机地发送到配置的`writeHost上；
3. switchType：-1：不自动切换；1：自动切换

🌮测试：登录Mycat，测试查询及更新操作，判定是否能够进行读写分离，以及读写分离的策略是否正确。当主库挂掉一个之后，是否能够自动切换。

# 重启Mycat，并查看日志是否报错
bin/mycat stop
bin/mycat start
tail -f logs/wrapper.log

在Mycat中多次执行查询操作，修改S1、S2中数据以便于区分，可以看到M2、S1、S2中随机出现。执行插入操作四台数据均更新。挂掉M1，继续执行插入语句，插入到M2节点，保证了服务器的高可用

---

总结

✍读写分离是为了降低单台服务器的访问压力，写走主库，读走从库

✍读写分离基于主从复制，MySQL主从复制是基于二进制日志binlog实现的，读写分离是通过Mycat的schema.xml文件datahost标签的balance属性来实现的