MySQL运维16-双主双从读写分离

一、双主双从架构介绍

　　在MySQL多主多从的架构配置中和双主双从是一样的，学会了双主双从的架构部署，多主多从的配置也同样就回了。下面以双主双从作为示例演示。其中一个主机maste1用于处理所有写请求，它的从机slave1和另外一台主机master2还有它的从机salve2负责所有读数据请求，当master1主机宕机后，master2主机会立刻切换到负责写请求，master1和master2互为备机，架构如下：

二、双主双从主机配置

　　四台MySQL主机，分别如下

　　192.168.3.91：角色 master1, 启动服务Mycat，MySQL, 关闭防火墙

　　192.168.3.92：角色 slave1， 启动服务MySQL，关闭防火墙

　　192.168.3.93：角色 master2, 启动服务MySQL，关闭防火墙

　　192.168.3.94：角色 slave2,  启动服务MySQL，关闭防火墙

　　说明1：其中192,168.3.91即当MySQL服务器，又充当Mycat的服务器 

三、master1节点配置

　　3.1 修改mysql配置文件

　　　　192.168.3.91这个台服务器当做双主中的一个，修改其配置文件如下：vim /etc/my.cnf

　　　　说明1: server-id=1 是mysql集群服务中的唯一标识

　　　　说明2：binlog-do-db是指定要主从复制的数据库，不指定默认是全部数据库都做主从复制

　　　　说明3：在主服务器上要配置log-slave-updates因为当存在多个主服务器的时候，有可能会存在备用主机，备用主机也是需要通过binlog文件向自己的从机同步数据的，所以要开始log-slave-updates

　　3.2 重启mysql

　　3.3 创建用于主从复制的账号

　　　　要进入mysql交互界面，注意不是进入Mycat

# 创建一个mysql用户为houlei,并设置密码为：Se7eN521,该用户可以在任意主机连接该MySQL服务
mysql> create user 'houlei'@'%' identified with mysql_native_password by 'Se7eN521';
Query OK, 0 rows affected (0.03 sec)
# 为'houlei'@'%' 用户分配主从复制权限
mysql> grant replication slave on *.* to 'houlei'@'%';
Query OK, 0 rows affected (0.01 sec)

　　3.4 查看二进制日志坐标　　　　

四、master2节点配置

　　4.1 修改mysql配置文件

　　　　192.168.3.93这个台服务器当做双主中的另外一个，修改其配置文件如下：vim /etc/my.cnf

　　　　说明1：这里的server-id=3

　　4.2 重启mysql　　

 　　4.3 创建用于主从复制的账号

　　　　要进入mysql交互界面，注意不是进入Mycat

# 创建一个mysql用户为houlei,并设置密码为：Se7eN521,该用户可以在任意主机连接该MySQL服务
mysql> create user 'houlei'@'%' identified with mysql_native_password by 'Se7eN521';
Query OK, 0 rows affected (0.03 sec)
# 为'houlei'@'%' 用户分配主从复制权限
mysql> grant replication slave on *.* to 'houlei'@'%';
Query OK, 0 rows affected (0.01 sec)

　　4.4 查看二进制日志坐标

五、slave1节点配置

　　5.1 修改配置文件

　　　　slave1节点是192.168.3.92, 修改其配置文件如下 vim /etc/my.cnf

　　5.2 重启mysql

　　5.3 slave1关联master1

change master to master_host='192.168.3.91',master_user='houlei',master_password='Se7eN521',master_log_file='binlog.000010',master_log_pos=156;

　　　　说明1：master_host是该从库关联的主库，slave1关联的事192.168.3.91这台master1

　　　　说明2：master_user是关联账号，houlei这个账号是在master1中刚创建的账号，就是用来做主从复制使用的

　　　　说明3：master_password是关联账号的密码

　　　　说明4：master_log_file是开始主从复制的binlog文件名

　　　　说明5：master_log_poss是binlog日志文件位置，这个参数是不用加引号或单引号的。

　　5.4 启动slave1的主从复制

　　　　说明6：start slave 开始主从复制

六、slave2节点配置

　　6.1 修改配置文件

　　　　slave2节点是192.168.3.94，修改其配置文件如下 vim /etc/my.cnf

　　6.2 重启mysql

　　6.3 slave2关联master2

change master to master_host='192.168.3.93',master_user='houlei',master_password='Se7eN521',master_log_file='binlog.000018',master_log_pos=156;

　　　　说明1：master_host是该从库关联的主库，slave2关联的事192.168.3.93这台master2

　　　　说明2：master_user是关联账号，houlei这个账号是在master1中刚创建的账号，就是用来做主从复制使用的

　　　　说明3：master_password是关联账号的密码

　　　　说明4：master_log_file是开始主从复制的binlog文件名

　　　　说明5：master_log_poss是binlog日志文件位置，这个参数是不用加引号或单引号的。

　　　　说明6：该语句是在slave2的mysql交互界面执行的

 　　6.4 启动slave2的主从复制 

七、两台主库相互复制

　　以上6章节我们等于部署了master1-slave1和master2-slave2两套一主一从，但是目前位置这两个一主一从还没有任何关系，所以接下来我们就要配置两台主机上的关联了。即master1复制master2的同时master2也复制master2.

　　在master1上执行：

change master to master_host='192.168.3.93',master_user='houlei',master_password='Se7eN521',master_log_file='binlog.000018',master_log_pos=156;

 start slave;

show slave status\G;

　　说明1：以上代码其实就是将master1配置为master2的从库　　

　　然后在master2上执行：

change master to master_host='192.168.3.91',master_user='houlei',master_password='Se7eN521',master_log_file='binlog.000010',master_log_pos=156;

start slave;

show slave status\G;

　　说明2：以上代码其实就是将master2配置为master1的从库　　

八、双主双从测试

　　分别在两台主库master1,master2上执行DDL和DML语句，查看涉及到的数据库服务器的数据同步情况

　　在master1上操作：

　　8.1、创建数据库test_db1

mysql> create database test_db1;
Query OK, 1 row affected (0.01 sec)

　　　　说明1：我们在192.168.3.91的master1主库上创建了数据库test_db1,然后再slave1，和master2,slave2上立刻就复制创建了test_db1出来

　　2、我们在master2上的test_db1的数据库中创建一个tb_test表

create table tb_test(id int auto_increment primary key, name varchar(20));

　　　　说明2：在master2上进行创建表同样会自动复制到master1和其他从表中

　　　　说明3：至此双主双从算是配置好了

九、双主双从的读写分离

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

　　9.1 配置schema.xml　

　　　　说明1：在配置schema逻辑库的时候就要指定数据节点dataNode的值，因为如果逻辑库下的所有表都要实现读写分离，则不需要配置table标签

　　　　说明2：dn1数据节点下对应的mysql的database为test_db1

　　　　说明3：在dataHost标签的配置中要注意，balance值为"1"，writeType值为"0"，switchType的值为"1",解释如下：

• • balance="1": 关于balance所有值的解释在上一遍文章中有解释，需要的请通过合集找到上一篇文章查看，这里直接是balance="1"的情况：代表全部的readHost和备用主机的writeHost参与select语句的负载均衡，简单的说，当双主双从模式(master1->slave1,master2->slave2,并且master1和master2互为主备)，正常情况下master2，slave1，slave2都要参与select语句的负载均衡
  • writeType：0：代表写操作都转发到第一台writerHost,writeHost1挂了，会切换到writeHost2上。1：代表所有写操作都随机的发送到配置的witerHost上
  • switchType：-1：代表不自动切换，1：代表自动切换

　　　　说明4：双主双从即配置两个writeHost和readHost组合，如果是多主多从即配置多个writeHost和readHost组合即可。

　　9.2 配置server.xml

　　　　说明5：给root用户增加test_db1数据库的操作权限

　　9.3 双主双从读写分离测试

　　　　首先重启Mycat

　　　　登录Mycat

　　　　查看逻辑库和逻辑表

　　　　插入测试数据

insert into tb_test(name) values ("张三");
insert into tb_test(name) values ("李四");
insert into tb_test(name) values ("王五");

　　　　说明6：在Mycat中插入数据master1,slave1,master2,slave2中都添加了数据.

　　　　查询数据测试：同样的直接查询我们是没办法确定查询的数据到底是来自哪里，所以我们还是修改从库中的数据，将slave1中的“张三”改了“张三s1”，将slave2中的“张三”改为“张三s2”

　　　　说明7：查询结果是张三的说明数据来源master2

　　　　说明8：查询结果是张三s1的说明数据来源是slave1

　　　　说明9：查询结果是张三s2的说明数据来源是slave2

　　　　说明10：以上测试说明了双主双从实现了读写分离

十、双主双从的高可用测试

　　测试一台主机宕机下，双主双从是否可以继续运行。

　　10.1、现将master1(192.168.3.91)的服务关闭

　　10.2 进入Mycat进行增删改查测试

　　　　说明1：仍然可以查询，说明可以进行读数据

　　　　说明2：master1挂了的情况下，我们的双主双从架构的MySQL依然可读可写，所以高可用也没问题

　　　　总结：在双主双从的基础上，多主多从同样的实现思路。