MySQL运维篇（四）读写分离

一、介绍

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

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

二、 一主一从

1. 原理

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

2. 准备

三、 一主一从读写分离

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

writeHost 代表的是写操作对应的数据库，readHost 代表的是读操作对应的数据库。 所以我们要想实现读写分离，就得配置 writeHost 关联的是主库，readHost 关联的是从库。

而仅仅配置好了 writeHost 以及 readHost 还不能完成读写分离，还需要配置一个非常重要的负责均衡的参数 balance，取值有4种，具体含义如下：

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

四、双主双从

1. 介绍

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

2. 准备

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

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

systemctl stop firewalld

systemctl disable firewalld

3. 搭建

3.1 主库配置（Master1和Master2）

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

3.2 从库配置

（1） 修改配置文件 /etc/my.cnf

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

（2）重启MySQL服务器

systemctl restart mysqld

3.3 从库关联主库

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

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

（1）在 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;

（2）在 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;

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

start slave;

show slave status \G;

3.3.2 两台主库相互复制

Master2 复制 Master1，Master1 复制 Master2。

（1）在 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;

五、双主双从读写分离

1. 配置

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