Mysql之读写分离详解 -

本文以主从复制为基础，mycat为工具，如不熟悉，可先查看主从复制， mycat详解。

之前我们讲过，主从复制是基于binlog的，当时我们留了一个设想，就是读写分离，DML、DDL操作主库，DQL操作从库。但是总归不能有我们应用程序去自行判断吧。
还好，我们又整理了mycat。接下来我们的思路就是以mycat为中间件，应用程序只需要连接mycat即可。

回顾主从复制原理

zhucongfuzhideyuanli

主库当中一旦发生增、删、改以及发生DDL语句的时候，会把所有的数据变更写入到binlog日志里面
从库中有两个线程，其中IOthread会读取master中的binlog日志，当读取到binlog日志后，iothread会继续把日志写入到slave自身的日志 relay log(中继日志)。
从库中的另外一个线程SQLthread，会读取自身的relay log(中继日志)的数据，把中继日志中的数据变化，反应到当前slave数据库中的数据变化(重做)。从而保证了主从同步。

一主一从读写分离

环境准备

使用服务器搭载mycat，另起两个mysql容器作为一主(33061)和一从(33062)服务。主从搭建可查看主从复制章节。

分别在主、从上建库建表

create database study default character set utf8mb4 collate utf8mb4_unicode_ci;
use study;
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;

配置主从结构

要开启binlog日志，我的docker中是没开启的。 /etc/my.cnf

[mysqld]
log-bin=mysql-bin            # 启用二进制日志，指定日志文件的前缀
server-id=1                  # 为 MySQL 服务器分配唯一的 ID，每个 MySQL 实例应该有不同的 server-id

主从如何配置可以查看往期博客。

注意的点：

1：由于两个docker之间通信，不能使用127.0.0.1了，需要使用内部ip，内部ip可以通过：docker inspect -f '{{range .NetworkSettings.Networks}}{{.IPAddress}}{{end}}' container_name_or_id 来。
2：如果docker下是相同自定义的网段，甚至可以通过container_name or id来访问，但需要搭建自定义网络。
3：也可以通过主机名来访问，如124.223.47.250.
4：我们这次通过内部ip吧：CHANGE MASTER TO MASTER_HOST='172.17.0.1',MASTER_PORT=33061,MASTER_USER='milo01',MASTER_PASSWORD="123456",MASTER_LOG_FILE='mysql-bin.000002',MASTER_LOG_POS=1413;

配置mycat

配置schema.xml

    <schema name="study" checkSQLschema="true" sqlMaxLimit="100" dataNode="dn7"></schema>
        
    <dataNode name="dn7" dataHost="dhost7" database="study" />
        
    <dataHost name="dhost7" maxCon="1000" minCon="10" balance="3" writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1" slaveThreshold="100">
        <heartbeat>select user()</heartbeat>
        <writeHost host="master" url="jdbc:mysql://127.0.0.1:33061?userSSL=false&amp;serverTimezone=Asia/Shanghai&amp;characterEncoding=utf8"user="root" password="123456" >
                   
            <readHost host="master" url="jdbc:mysql://127.0.0.1:33062?userSSL=false&amp;serverTimezone=Asia/Shanghai&amp;characterEncoding=utf8" user="root" password="123456" />
        
        </writeHost>
    </dataHost>

由上可知，我们在dataHost中配置了writeHost和readHost来区别读还是写的。其中注意的是，有个balance需要介绍下：

参数值	含义
0	不开启读写分离机制，所有读操作都可以发送到当前可用的writeHost上面
1	全部的readHost与备用的writeHost都参与select语句的负载均衡，主要是针对多主多从
2	所有的读写操作都随机在writeHost，readHost上分发
3	所有的读请求随机分发到writeHost对应的readHost上执行，writeHost不负担读压力

最后紧接着在 server.xml中再配置study加入到root的user里面去。

测试

在主从复制之前，主数据库中已经有一条数据，后配置主从复制后再插入一条数据就同步到从数据库了。即：

主：

mysql> select * from tb_user;
+----+------+------+
| id | name | sex  |
+----+------+------+
|  1 | Tom  | 1    |
|  5 | milo | 1    |
+----+------+------+
2 rows in set (0.02 sec)

从：

mysql> select * from tb_user;
+------+------+-----+
| id   | name | sex |
+------+------+-----+
|    5 | milo | 1   |
+------+------+-----+
1 row in set (0.01 sec)

mycat查询

只有一条，说明查询的是从数据库

mysql> select * from tb_user;
+------+------+-----+
| id   | name | sex |
+------+------+-----+
|    5 | milo | 1   |
+------+------+-----+
1 row in set (0.02 sec)

mycat插入

插入后，主库有三条数据，即开启主从复制，则从库有两条。

mysql> insert into tb_user(id,name,sex) values(2,'Trigger','0');
Query OK, 1 row affected (0.08 sec)
 OK!

mysql> select * from tb_user;
+------+---------+-----+
| id   | name    | sex |
+------+---------+-----+
|    2 | Trigger | 0   |
|    5 | milo    | 1   |
+------+---------+-----+
2 rows in set (0.00 sec)

如果主库宕机了，还是可以查询的，因为从库还在。但不能写入了。

双主双从

按照我们刚刚上面的一主一从，如果主节点宕机了，就直接瘫痪了，不符合高性能架构。

双主双从需要四个节点。总体架构图如下：

shuangzhushuangcongjiagoutuY

四台主机，其中m1和m2为双主节点相互备份。s1为m1的从节点，s2为m2的从节点。
一般来说都是有一个主机m1负责所有的写操作，其余的m2，s1和s2复杂所有读请求。
当m1宕机后，m2还活着的话立马顶替上去负责写请求。

环境准备

当前服务器作为mycat。另起四个容器 mysql1，mysql2，mysql3，mysql4。
其中mysql1和mysql3都是主节点，mysql2是mysql1的从节点，mysql4是mysql3的从节点。
注意点： mysql1和mysql3需要开启binlog日志。

[root@VM-4-9-centos ~]# docker ps
CONTAINER ID   STATUS         PORTS                                                    NAMES
f352fc7ca765   Up 5 seconds   33060/tcp, 0.0.0.0:33064->3306/tcp, :::33064->3306/tcp   mysql04
7bbf27471736   Up 3 hours     33060/tcp, 0.0.0.0:33061->3306/tcp, :::33061->3306/tcp   mysql01
cd36779864d6   Up 45 hours    33060/tcp, 0.0.0.0:33063->3306/tcp, :::33063->3306/tcp   mysql03
6fa70c3455a5   Up 2 hours     33060/tcp, 0.0.0.0:33062->3306/tcp, :::33062->3306/tcp   mysql02

主库配置

修改my.cnf配置

主库需要修改my.cnf如下：
同步一个数据库，study。binlog-do-db=study

创建账号

适用于主从复制的

#创建milo01用户，设置密码
CREATE USER 'milo01'@'%' IDENTIFIED WITH mysql_native_password BY '123456';

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

分别查看写入的二进制坐标

show master status;

从库配置

修改配置文件，增加server_id
重启服务
关联主库

我们上面关联了mysql01和mysql02，现在把mysql04关联上mysql03
CHANGE MASTER TO MASTER_HOST='124.223.47.250',MASTER_PORT=33063,MASTER_USER='milo01',MASTER_PASSWORD="123456",MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=591;

开启同步 start slave;

再查看是否关联成功。 show slave status\G;

此时，我们把从库mysql02关联上了主库mysql01，把从库mysql04关联上了mysql03，但是mysql01和mysql03还没有任何关系。

两个主库相互复制

现在是需要把mysql01和mysql03相互复制。

操作mysql01关联mysql03

查看mysql03的二进制坐标

show master status;
在mysql01中操作

1：mysql01需要从mysql03进行数据同步，则继续在mysql01中执行：
CHANGE MASTER TO MASTER_HOST='124.223.47.250',MASTER_PORT=33063,MASTER_USER='milo01',MASTER_PASSWORD="123456",MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=1942;

2：开启同步start slave;

3：再查看是否关联成功。 show slave status\G;

    Master_User: milo01
    Master_Port: 33063
    Connect_Retry: 60
    Master_Log_File: mysql-bin.000001
    Read_Master_Log_Pos: 1942
    Relay_Log_File: 7bbf27471736-relay-bin.000002
    Relay_Log_Pos: 320
    Relay_Master_Log_File: mysql-bin.000001
    Slave_IO_Running: Yes
    Slave_SQL_Running: Yes

操作mysql03关联mysql01

查看mysql01的二进制坐标

show master status;

在mysql03中操作

1：mysql03需要从mysql01进行数据同步，则继续在mysql03中执行：
CHANGE MASTER TO MASTER_HOST='124.223.47.250',MASTER_PORT=33061,MASTER_USER='milo01',MASTER_PASSWORD="123456",MASTER_LOG_FILE='mysql-bin.000003',MASTER_LOG_POS=154;

2：开启同步start slave;

3：再查看是否关联成功。 show slave status\G;

 Master_User: milo01
 Master_Port: 33061
 Connect_Retry: 60
 Master_Log_File: mysql-bin.000003
 Read_Master_Log_Pos: 154
 Relay_Log_File: cd36779864d6-relay-bin.000002
 Relay_Log_Pos: 320
 Relay_Master_Log_File: mysql-bin.000003
 Slave_IO_Running: Yes
 Slave_SQL_Running: Yes

此时，双主双从全部关联成功

双主双从读写分离

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

配置schema

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">

    <schema name="study" checkSQLschema="true" sqlMaxLimit="100" dataNode="dn7"></schema>
    
    <dataNode name="dn7" dataHost="dhost7" database="study" />
    
    <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://127.0.0.1:33061?userSSL=false&amp;serverTimezone=Asia/Shanghai&amp;characterEncoding=utf8" user="root"  password="123456" >
                   
            <readHost host="slave1" url="jdbc:mysql://127.0.0.1:33062?userSSL=false&amp;serverTimezone=Asia/Shanghai&amp;characterEncoding=utf8" user="root"password="123456" >
            </readHost>
        
        </writeHost>
        
        <writeHost host="master2" url="jdbc:mysql://127.0.0.1:33063?userSSL=false&amp;serverTimezone=Asia/Shanghai&amp;characterEncoding=utf8" user="root" password="123456" >
                   
            <readHost host="slave2" url="jdbc:mysql://127.0.0.1:33064?userSSL=false&amp;serverTimezone=Asia/Shanghai&amp;characterEncoding=utf8" user="root" password="123456" >
            </readHost>
        
        </writeHost>
        
    </dataHost>
    
</mycat:schema>

balance我们说过了，看上面。
writeType：

0：写操作都转发到第一台writeHost，writeHost1挂了后，会自动切换writeHost2上。

1：所有的写操作都随机发送到配置的writeHost上面。
switchType 当writeHost挂了之后，会不会自动切换到writeHost2上面。

-1:不自动切换

1：自动切换

配置完成后，重启

双主双重测试

查询操作

可以把id为1的数据修改名字，那么使用mycat多次查询则会出现：

mysql> select * from tb_user where id=1;
+------+------+-----+
| id   | name | sex |
+------+------+-----+
|    1 | Tom  | 1   |
+------+------+-----+
1 row in set (0.01 sec)

mysql> select * from tb_user where id=1;
+------+--------+-----+
| id   | name   | sex |
+------+--------+-----+
|    1 | Tom_s2 | 1   |
+------+--------+-----+
1 row in set (0.01 sec)

mysql> select * from tb_user where id=1;
+------+--------+-----+
| id   | name   | sex |
+------+--------+-----+
|    1 | Tom_s1 | 1   |
+------+--------+-----+
1 row in set (0.00 sec)

也就是说明，查询会查mysql02，mysql03，mysql04的数据库。

写入操作

在mycat上插入 insert into tb_user(id,name,sex)values(7,'guijiaoqi',3);
单独去mysql01,mysql02,mysql03,mysql04上都同步插入。

断掉当前mysql01的主库

docker stop mysql01

在mycat上测试查询操作没有问题。但都是同一条数据，没有改变。

mysql> select * from tb_user where id=1;
+------+--------+-----+
| id   | name   | sex |
+------+--------+-----+
|    1 | Tom_s2 | 1   |
+------+--------+-----+
1 row in set (0.01 sec)

在mycat上测试插入操作。
```
mysql> insert into tb_user(id,name,sex)values(8,'baage',2);
Query OK, 1 row affected (0.01 sec)
 OK!
```
插入成功，但mysql02没有数据，只有mysql03和mysql04有插入新的数据，说明已经自动切换到mysql03作为最新主库。

mysql01重新连接后

docker start mysql01

此时，mysql01和mysql02自动连接上来。但是，作为插入的已经切换成了mysql03作为主库了。

Mysql之读写分离详解

回顾主从复制原理

一主一从读写分离

环境准备

配置mycat

配置schema.xml

测试

双主双从

环境准备

主库配置

从库配置

两个主库相互复制

操作mysql01关联mysql03

操作mysql03关联mysql01

双主双从读写分离

配置schema

配置完成后，重启

双主双重测试

查询操作

写入操作

断掉当前mysql01的主库

mysql01重新连接后

分类

归档

友链