Mysql 读写分离

一、简介

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

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

二、一主一从

2.1 原理

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

2.2 准备

主机	角色	用户名	密码
192.168.229.163	master	root	1234
192.168.229.164	slave	root	1234

        主从复制的搭建，可以参考我前面写的这篇博客。搭建好的样子如下图所示：

        此时我们创建一个数据库 itcast，并创建一张表和一些数据，记得在主库运行以下的 sql 语句，如下：

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 属性控制。

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.229.163:3306?useSSL=false&amp;serverTimezone=Asia/Shanghai&amp;characterEncoding=utf8" user="root" password="1234" >
		<readHost host="slave1" url="jdbc:mysql://192.168.229.164: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 都是可以完成读写分离的。

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>

3.3 测试

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

bin/mycat stop
bin/mycat start

        可以看到我们已经完成了读写分离的操作，如下图： 

3.4 验证 

3.4.1 读操作走从节点

        首先我们知道，主库的数据是会同步到从库，而从库的数据不会同步到主库，接下来我们手动的更新下从库里面的数据，如下图：

        而此时主库里面的数据，依然是 Tom，如下图：

        接下来我们再次通过 mycat 进行查询，如下图，可以看到，查询的就是从节点的数据。

3.4.2 写操作走主节点

        因为 163 和 164 数据库我们配置了主从复制，只有往 163 数据库插入数据的时候，164 数据库才会自动的也插入数据，这个应该很好理解。如果插入的是从节点的数据，它是不会同步到主节点的。而此时主节点是有数据的

3.4.3 随机分发

        当 balance = 2 时，所有的读写操作都随机在 writeHost，readHost 上分发，我们测试下，如下图：

        修改完毕后，记得重启 mycat，然后随机多次查询数据库表，如下图，可以看到同样的查询语句，返回的数据是不一样的。

3.4.4 主节点宕机

        情况一：mycat、163 和 164 数据库都正常运行，此时关闭主节点的 164 数据库，此时是可以执行查询操作的，但是无法执行插入操作，如下图：

        情况二：只启动 164 节点，然后启动 mycat，使 163 节点的数据库处于关闭状态，此时无论查询还是插入操作都是无法执行的，如下图：

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

四、双主双从

4.1 介绍

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

4.2 准备

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

编号	IP	预装软件	角色
1	192.168.229.165	MyCat 、 MySQL	MyCat 中间件服务器
2	192.168.229.163	MySQL	M1
3	192.168.229.164	MySQL	S1
4	192.168.229.166	MySQL	M2
5	192.168.229.167	MySQL	S2

        注意：我们在搭建的时候，需要需要把服务器的防火墙全部关闭掉。

4.3 搭建

4.3.1 主库配置

Master1（192.168.229.163）

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

        2、重启 mysql 服务器

systemctl restart mysqld

        3、创建账户并授权

# 创建 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.229.166）

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

        2、重启 mysql 服务器

systemctl restart mysqld

        3、创建账户并授权

# 创建 itcast 用户，并设置密码，该用户可在任意主机连接该 MySQL 服务
CREATE USER 'itcast'@'%' IDENTIFIED WITH mysql_native_password BY 'Root@123456';

# 为 'itcast'@'%' 用户分配主从复制权限
GRANT REPLICATION SLAVE ON *.* TO 'itcast'@'%';

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

show master status ;

4.3.2 从库配置

Slave1(192.168.229.164)

        1、修改配置文件 /etc/my.cnf，内容如下所示：

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

        2、重启 mysql 服务器

systemctl restart mysqld

 Slave2(192.168.229.167)

        1、修改配置文件 /etc/my.cnf，内容如下所示：

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

        2、重启 mysql 服务器

systemctl restart mysqld

4.3.3 从库关联主库

配置两台从库关联主库

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

        1、在 slave1（192.168.229.164）上执行

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

        2、在 slave2（192.168.229.167）上执行

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

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

start slave;
show slave status \G;

两台主库相互复制

        即 Master2 复制 Master1，Master1 复制 Master2。

        1、在 Master1（192.168.229.163）上执行

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

       启动主从复制，并查看状态，如下：

start slave;
show slave status \G;

        2、在 Master2（192.168.229.166）上执行

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

       启动主从复制，并查看状态，如下：

start slave;
show slave status \G;

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

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

        1、在 Master1 中创建表结构，看看其他的节点是否生效

        2、在 Master2 中插入数据，看看其他的表里面是否有数据了

4.5 双写双从读写分离

4.5.1 配置

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

         在 schema.xml 中配置逻辑库，如下：

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

        在 schema.xml 中配置数据节点，如下：

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

        在 schema.xml 中配置主机，如下：

<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.229.163:3306?useSSL=false&amp;serverTimezone=Asia/Shanghai&amp;characterEncoding=utf8" user="root" password="1234" >
		<readHost host="slave1" url="jdbc:mysql://192.168.229.164:3306?useSSL=false&amp;serverTimezone=Asia/Shanghai&amp;characterEncoding=utf8" user="root" password="1234" />
	</writeHost>
	<writeHost host="master2" url="jdbc:mysql://192.168.229.166:3306?useSSL=false&amp;serverTimezone=Asia/Shanghai&amp;characterEncoding=utf8" user="root" password="1234" >
		<readHost host="slave2" url="jdbc:mysql://192.168.229.167: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：自动切换，即当主节点挂掉后，自动切换到主节点的备选节点上

        在 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，测试查询及更新操作，判定是否能够进行读写分离，以及读写分离的策略是否正确。

        1、当主库挂掉一个之后，另一个节点会自动切换。

        2、当主库的两个节点都挂掉之后，就无法从 mycat 里面插入和查询数据了，如下图：