运维篇 读写分离 介绍

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

实现 提供MyCat即可轻易实现上述功能，不仅支持MySQL，也支持Oracle和SQL server

运维篇 读写分离 一主一从准备

一主一从，在前面的a_120_0学过

原理 思考主库中的数据是如何同步到从库中的。主从复制基于主库的二进制日志文件

主库: 一旦发生增删改操作，以及执行DDL语句的时候，就会将所有的数据变更写入到binlog前缀的日志里面，binlog前缀的日志叫二进 制日志(我们前面学过)，也就是在二进制日志当中记录了主库所有的数据变更

从库:涉及到两组线程，来保证主从数据库的数据是一致的

1、IOthread: 发起请求，用于连接主库，实时读取主库当中的二进制日志，并且该线程会将读取到的日志数据写入到自身(从库)的日志里面 这个自身的日志就叫relay.log(中继日志)

2、SQLthread: 读取中继日志里面的数据，把中继日志里面的数据变化，记录到自身数据库(从库)的数据变化

例如 在主库中执行一条insert语句，这条insert语句就会被写入二进制日志，然后被从库的IOthread线程读取，并写入它自己从库里面的中继日志， 最后从库的SQLthread会读取中继日志，当读到这条insert语句的时候，就会在从库中也执行一次这条insert语句

严格一点来讲主从复制的过程的话，如下三步

1、master主库在事务提交时，会把数据变更记录在二进制日志文件binlog中

2、从库读取主库的二进制日志文件binlog，写入到从库的中继日志relay log

3、slave从库重做中继日志的时间，将改变反映它自己的数据

配置主从环境

在前面的a_121_0和a_122_0学过，那里就有相关的配置操作

具体操作如下

1、在第一台服务器(主库)赋予你创建的远程用户的所有权限，例如grant all on . to 'fenli_zhu'@'%';，flush privileges;

2、第一台服务器就是我们的主库，用户为fenli_zhu。第二台服务器是我们的从库，用户为root(自带)。并配置好这两台服务器的主从环境

3、在主库执行如下

create database if not exists duxiefenli_itcast;
use duxiefenli_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');

4、在从库也能查看到duxiefenli_itcast数据库、tb_user表

到这里就说明你的环境是正常的，下节课我们将正式学习一主一从的读写分离操作

运维篇 读写分离 一主一从读写分离

读写分离需要借助MyCat来实现，我们要借助MyCat的两个标签(writeHost、readHost)以及一个属性(balance)进行控制

配置 MyCat控制后台数据库的读写分离和负载均衡由/usr/local/mycat/conf目录的schema.xml文件里面的'datahost标签的balance属性'来控制

balance指的是负载均衡策略，目前取值有四种(0、1、2、3。其中1和3是用于读写分离，3是用于一主一从分离，1是既可以一主一从也可以双主双从)如下 第一种:0(默认)，表示不开启读写分离机制，所有读操作都发送到当前可用的writeHost上。也就是读写操作只会被路由到单台特定服务器 第二种:1，表示全部的readHost与备用的writeHost都参与select语句的负载均衡，主要用于双主双从模式 第三种:2，表示所有的读写操作都随机在writeHost，readHost上分发。也就是读写操作会被随机路由到任意服务器 第四种:3，所有的读请求随机分发到writeHost对应的readHost上执行。writeHost不负担读压力，只负担写压力

具体演示过程

1、在主库(第一台服务器)配置/usr/local/mycat/conf目录的schema.xml文件(我会打包放在模板文件里面)

2、在主库(第一台服务器)配置/usr/local/mycat/conf目录的rule.xml、server.xm文件(我会打包放在模板文件里面)

3、在第一台服务器启动mycat #停止

cd /usr/local/mycat && bin/mycat stop

#启动

cd /usr/local/mycat && bin/mycat start

#通过查看mycat日志文件来验证是否启动成功。日志末尾为successfully表示启动成功、Wrapper Stopped表示启动失败

cd /usr/local/mycat && tail -f logs/wrapper.log(需要大概10秒才能启动成功)

4、确保自己的主库(第一台服务器)、从库(第二台服务器)都有duxiefenli_itcast数据库，没有的话，去上一节课把命令运行一遍

5、在第一台服务器登录进mycat

mysql -h 192.168.127.138 -P 8066 -u root -p123456

6、验证。查看ITCAST_RW逻辑库里面有没有tb_user逻辑表，并在tb_user逻辑表里面进行读和写操作，看会被路由到哪个服务器

show databases;
use ITCAST_RW;
show tables;
select * from tb_user;
insert into tb_user(id,name,sex) values(4,'张三',0);
select * from tb_user;

如何知道MyCat在我们读数据的时候，到底路由的是哪台服务器，我们可以用下面的思路 分析:由于我们主库的数据会同步到从库，但是从库的数据是不会被同步到主库的，所以我们可以在从库对某条数据进行更新，此时 这条数据在主库和在从库是不一样的，我们在查一下，看查到这条数据是什么，如果是从库的数据，就证明是路由到从库，如下 在从库(第二台服务器)登录进mysql，并执行use duxiefenli_itcast; update tb_user set name = '乌拉' where id = 1;

select * from tb_user; #结论是查询到的数据是从库里面的，所以证明查操作是被路由到从库

如何知道MyCat在我们写数据的时候，到底路由的是哪台服务器，我们可以用下面的思路 分析:一定是路由到主库。原因:如果插入数据是修改的从库的数据，那我们查看主库的时候，主库的数据还是原来的没更新的，但是 我们在查看主库的数据，发现主库是更新过来了的，也就是说明写操作是被路由到主库

insert into tb_user(id,name,sex) values(5,'李四',1);

优点:

1、主库的数据会实时同步到从库

2、主库宕机了，查询业务不会受影响，因为查询操作是只查从库

存在的问题:

1、由于只有一台主库、一台从库，所以当主库宕机了，所有的增删改操作都无法执行(只能执行查操作)，就存在高可用问题

2、下节课会学习双主双从，来解决这里的高可用问题

运维篇 读写分离 双主双从准备

介绍 一个主机master1用于处理所有写请求，它的从机slave1和另一台主机master2还有它的从机slave2负责所有读请求。 当master1主机宕机后，master2主机替代继续负责写请求。也就是master1、master互为备用机，其中一台坏了就另一台补上

准备

1、第一台服务器(192.168.127.138): 安装MySQL、MyCat。 作为MyCat中间件服务器

2、第二台服务器(192.168.127.145): 安装MySQL。作为M1，即主库1

3、第三台服务器(192.168.127.149): 安装MySQL。作为S1， 从库1

4、第四台服务器(192.168.127.150): 安装MySQL。作为M2，即主库2

5、第五台服务器(192.168.127.151): 安装MySQL。作为S2， 即从库2

搭建

配置第一个主库

主库配置(M1-192.168.127.138)

1、修改配置文件(/etc/my.cnf)

xxxxxxxxxx
#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

配置第二个主库

主库配置(M2-192.168.127.150)

1、修改配置文件(/etc/my.cnf)

xxxxxxxxxx
#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

分别在两台主库创建账户并授权

#创建itcast用户，并设置密码，该用户可在任意主机连接主机该MySQL服务

xxxxxxxxxx
create user 'itcast'@'%' IDENTIFIED WITH mysql_native_password BY '228675';

#为itcast用户分配主从复制权限

xxxxxxxxxx
grant replication slave on *.* to 'itcast'@'%';

#查看两台主库的二进制日志坐标

show master status;

配置第一个从库

从库配置(S1-192.168.127.149)

1、修改配置文件(/etc/my.cnf)

xxxxxxxxxx
#mysql服务ID，保证整个集群环境中唯一，取值范围: 1~2^32-1，默认为1
server-id=2

2、重启MySQL服务器

systemctl restart mysqld

配置第二个从库

从库配置(S2-192.168.127.151)

1、修改配置文件(/etc/my.cnf)

xxxxxxxxxx
#mysql服务ID，保证整个集群环境中唯一，取值范围: 1~2^32-1，默认为1
server-id=4

2、重启MySQL服务器

systemctl restart mysqld

分别在两台从库配置关联的主库

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;

目前为止，已经配置好了两组主从复制，但是这两组还没有任何关系，所以我们要将这两台主库也相互复制 第一台主库复制第二台主库，第二台主库也复制第一台主库，并启动，查看启动状态，具体操作在下面的具体操作里面

具体操作

1、在第一台主库(192.168.127.145)执行如下

vim /etc/my.cnf

会出现一个文本编辑页面，使用键盘的向下箭头，在末尾行输入如下五行，然后保存退出

server-id=3
binlog-do-db=db01
binlog-do-db=db02
binlog-do-db=db03
log-slave-updates

重启mysql服务器

systemctl restart mysqld

2、在第二台主库(192.168.127.150)执行如下

vim /etc/my.cnf

会出现一个文本编辑页面，使用键盘的向下箭头，在末尾行输入如下五行，然后保存退出

server-id=3
binlog-do-db=db01
binlog-do-db=db02
binlog-do-db=db03
log-slave-updates

重启mysql服务器

systemctl restart mysqld

3、在第一台主库(192.168.127.145)，登录mysql，并创建itcast远程用户

mysql -u root -p
create user 'itcast'@'%' IDENTIFIED WITH mysql_native_password BY '228675';
grant replication slave on *.* to 'itcast'@'%';
show master status; #例如查到的结果File对应的是binlog.000002，Position是663。我们要记住这两个参数，后面会用

4、在第二台主库(192.168.127.150)，登录mysql，并创建itcast远程用户

mysql -u root -p
create user 'itcast'@'%' IDENTIFIED WITH mysql_native_password BY '228675';
grant replication slave on *.* to 'itcast'@'%';
show master status;#例如查到的结果File对应的是binlog.000002，Position是663。我们要记住这两个参数，后面会用

5、在第一台从库(192.168.127.149)执行如下

x
vim /etc/my.cnf

会出现一个文本编辑页面，使用键盘的向下箭头，在末尾行输入如下一行，然后保存退出

xxxxxxxxxx
server-id=2

重启mysql服务器

systemctl restart mysqld

6、在第二台从库(192.168.127.151)执行如下

vim /etc/my.cnf

会出现一个文本编辑页面，使用键盘的向下箭头，在末尾行输入如下一行，然后保存退出

xxxxxxxxxx
server-id=4

重启mysql服务器

systemctl restart mysqld

7、在第一台从库(192.168.127.149)，去关联第一台主库，相关的信息在前面的第3步我们记住的参数就是用到这里

mysql -u root -p
change replication source to source_host='192.168.127.145',source_user='itcast',source_password='228675',source_log_file='binlog.000002',source_log_pos=663;
start slave;#启动主从复制
show slave status\G;#查看启动状态，如果Slave_Io_Running为yes、Slave_SQL_Running为yes，则表示启动成功

8、在第二台从库(192.168.127.151)，去关联第二台主库，相关的信息在前面的第4步我们记住的参数就是用到这里

mysql -u root -p
change replication source to source_host='192.168.127.150',source_user='itcast',source_password='228675',source_log_file='binlog.000002',source_log_pos=663;
start slave;#启动主从复制
show slave status\G;#查看启动状态，如果Slave_Io_Running为yes、Slave_SQL_Running为yes，则表示启动成功

9、在第一台主库(192.168.127.145)，把这台主库当作'从库'去复制第二台主库

mysql -u root -p
change replication source to source_host='192.168.127.150',source_user='itcast',source_password='228675',source_log_file='binlog.000002',source_log_pos=663;
start slave;#启动主从复制
show slave status\G;#查看启动状态，如果Slave_Io_Running为yes、Slave_SQL_Running为yes，则表示启动成功

10、在第二台主库(192.168.127.150)，把这台主库当作'从库'去复制第一台主库

mysql -u root -p
change replication source to source_host='192.168.127.145',source_user='itcast',source_password='228675',source_log_file='binlog.000002',source_log_pos=663;
start slave;#启动主从复制
show slave status\G;#查看启动状态，如果Slave_Io_Running为yes、Slave_SQL_Running为yes，则表示启动成功

11、测试。在第一台主库执行如下语句，查看涉及到的数据库服务器的数据同步情况(也就是看一下第二台主库、第一台从库、第二台从库有没有数据)

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来实现，我们要借助MyCat的一个标签(datahost)以及一个属性(balance)进行控制。通过writeType、switchType来 完成失败自动切换的

配置 MyCat控制后台数据库的读写分离和负载均衡由/usr/local/mycat/conf目录的schema.xml文件里面的'datahost标签的balance属性'来控制

balance指的是负载均衡策略，目前取值有四种(0、1、2、3。其中1和3是用于读写分离，3是用于一主一从分离，1是既可以一主一从也可以双主双从)如下 第一种:0(默认)，表示不开启读写分离机制，所有读操作都发送到当前可用的writeHost上。也就是读写操作只会被路由到单台特定服务器 第二种:1，表示全部的readHost与备用的writeHost都参与select语句的负载均衡，主要用于双主双从模式 第三种:2，表示所有的读写操作都随机在writeHost，readHost上分发。也就是读写操作会被随机路由到任意服务器 第四种:3，所有的读请求随机分发到writeHost对应的readHost上执行。writeHost不负担读压力，只负担写压力

balance="1" 代表全部的readHost与stand by writeHost参与select语句的负载均衡，简单的说，当双主双从模式(M1->S1，M2-S2，并且M1与M2互为主备)，正常 情况下，M2、S1、S2都参与select语句的负载均衡。简单说就是M1是负责写操作，M2和S1和S2都负责读操作(当是查询业务，就由这三台服务器负载)

writeType (1)0，表示写操作都转发到第一台writeHost，当writeHost1挂了，才会切换到writeHost2上 (2)1，表示所有的写操作都随机地发送到配置的writeHost上

switchType (1)-1，不自动切换 (2)1，自动切换

具体演示过程

1、第一台服务器(192.168.127.138)配置/usr/local/mycat/conf目录的schema.xml文件(我会打包放在模板文件里面)

2、第一台服务器(192.168.127.138)配置/usr/local/mycat/conf目录的rule.xml、server.xm文件(我会打包放在模板文件里面)

3、第一台服务器(192.168.127.138)启动mycat #停止

cd /usr/local/mycat && bin/mycat stop

#启动

cd /usr/local/mycat && bin/mycat start

#通过查看mycat日志文件来验证是否启动成功。日志末尾为successfully表示启动成功、Wrapper Stopped表示启动失败

cd /usr/local/mycat && tail -f logs/wrapper.log(需要大概10秒才能启动成功)

4、确保自己的主库1、从库1、主库2、从库2都有db01数据库，没有的话，去上一节课末尾的命令运行一遍

5、在第一台服务器登录进mycat，并进行测试，当执行查询及更新操作，能否进行读写分离，以及读写分离的策略是否正确，当主库挂掉一个之后，第二个主库能否切换生效

mysql -h 192.168.127.138 -P 8066 -u root -p123456
use ITCAST_RW2;
select * from tb_user;

如何知道MyCat在我们读数据的时候，到底路由的是哪台服务器，我们可以用下面的思路 分析: M1的数据会同步到S1，M2的数据会同步到S2。但是S1的数据不会同步到M1，S2的数据不会同步到M2。所以在S1和S2上修改表的数据，如下 在第一台从库上登录进mysql，执行一些语句使得表数据发生变化:

mysql -u rooe -p
use db01;
update tb_user set name = '乌拉S1' where id = 1;

在第二台从库上登录进mysql，执行一些语句使得表数据发生变化:

mysql -u rooe -p
use db01;
update tb_user set name = '乌拉S2' where id = 1;

如果第一台服务器(192.168.127.138)的Mycat查到的数据是'乌拉S1'，说明数据来源于第一台从库，同理'乌拉S2'就证明来源于第二台从库，如果都不是说明就来源于两个主库

xxxxxxxxxx
select * from tb_user;

上面那行多执行几次，会发现即有'乌拉S1'，又有'乌拉S1'，说明数据是随机来源于两台从库，原因:我们在schema.xml中设置的balance值为1，表示读请求会随机路由到从库

如何知道MyCat在我们写数据的时候，到底路由的是哪台服务器，我们可以用下面的思路 分析:一定是路由到主库。原因:如果插入数据是修改的从库的数据，那我们查看主库的时候，主库的数据还是原来的没更新的，但是 我们在查看主库的数据，发现主库是更新过来了的，也就是说明写操作是被路由到主库 insert into tb_user(id,name,sex) values(10,'李四',1); 上面那行，写入操作的时候，先写入的是M1服务器(当M1服务器没坏的时候，如果M1坏了，就先写入的是M2，然后实时同步到S1、S2)，然后实时同步到M2、S1、S2 原因: 我们在schema.xml中设置的balance值为1，表示写请求必须是主库来处理，优先是M1

6、模拟M1故障，也就是M1宕机了

在第一台主库(192.168.127.145)停止Mysql服务

systemctl stop mysqld;

在第一台服务器(192.168.127.138)登录进MyCat，看看还能不能执行写入操作

mysql -h 192.168.127.138 -P 8066 -u root -p123456
insert into tb_user(id,name,sex) values(11,'王五',1);

上面那行，是可以执行的，原因:我们在schema.xml中设置的balance值为1，表示写请求必须是主库来处理，我们主库有两个，一个坏了就由另一个工作 select * from tb_user;#数据确实写入了，该数据会同步到S1、S2

保证了数据库的高可用

笔记已全部完成，由b站up主: 涣沷a靑惷，编写。有任何问题可联系解决，转载需经过'涣沷a靑惷'的本人同意 祝前程似锦，很荣幸能成为你逐梦的旅途一盏明灯