Mycat的简单使用（二）【主从读写】

快来打我* 2022-10-08 11:19 58阅读 0赞

### Mycat的简单使用（二） ###

*  主从读写分离
 *  双主双从读写分离
 *   *  原理
     *  配置数据库
     *   *  删除上述db1、db2释放压力
         *  创建四个配置文件
         *  配置Master1(M1)
         *  配置Slave1(S1)
         *  创建Master2(M2)配置文件并创建数据库
         *  创建Slave2(S2)配置文件并创建数据库
         *  在两台主机上建立主从复制账户
         *  在从机上配置需要复制的主机
         *  配置两个主机相互备份
         *  验证mysql主从复制
     *  配置mycat读写分离
     *  验证双主切换

# 主从读写分离 #

**分别向主数据库 和从数据库 插入不一样的数据  
在mycat客户端查询数据，发现都是写数据库的内容  
修改Mycat的配置文件schema.xml的节点的balance属性**

**1. 当balance=0 时，不开启读写分离，所有读操作都发生在当前的writeHost上  
2. 当balance=1 ，所有读操作都随机发送到当前的writeHost对应的readHost和备用的writeHos都参与select语句的负载均衡，简单的说当双主双从模式（M1->S1 ,M2-S2 M2和M2互为主备）  
3. 当balance=2，所有的读操作都随机发送到所有的writeHost,readHost上  
4. 当balance=3 ，所有的读操作都只发送到writeHost(db1)的readHost(db2)上**

> **生产当中应该使用 1或者3，此处为了演示切换的效果设置balance="2"**

<?xml version="1.0"?>
    <!DOCTYPE mycat:schema SYSTEM "schema.dtd">
    <mycat:schema xmlns:mycat="http://io.mycat/">
    
        <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
        </schema>
    
        <dataNode name="dn1" dataHost="host1" database="testdb" />
    
    
    
        <dataHost name="host1" maxCon="1000" minCon="10" balance="2" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
            <heartbeat>select user()</heartbeat>
    
            
            <writeHost host="hostM1" url="172.17.0.2:3306" user="root" password="123456">
                
                <readHost host="hostS1" url="172.17.0.3:3306" user="root" password="123456" />
            </writeHost>
        </dataHost>
    
    
    
    
    
    
    </mycat:schema>

**使用客户端连接mycat查询数据**

# 双主双从读写分离 #

## 原理 ##

**一个主机ml用于处理所有写请求，它的从机s1和另一台主机m2还有它的从机s2负责所有读请  
求。当m1主机宕机后，m2主机负责写请求，ml、m2互为备机。架构图如下。**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzg4MzkxNw_size_16_color_FFFFFF_t_70]

## 配置数据库 ##

**此处重新准备数据库,使用docker安装如下四个mysql容器(可以删除db1、db2两个容器以释放虚拟机压力)**

<table> 
 <thead> 
  <tr> 
   <th>序号</th> 
   <th>角色</th> 
   <th>容器名</th> 
   <th>ip地址</th> 
   <th>server-id(my.cnf)</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td>Master1</td> 
   <td>m1</td> 
   <td>172.17.0.2</td> 
   <td>1</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>Slave1</td> 
   <td>s1</td> 
   <td>172.17.0.3</td> 
   <td>2</td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td>Master2</td> 
   <td>m2</td> 
   <td>172.17.0.4</td> 
   <td>3</td> 
  </tr> 
  <tr> 
   <td>4</td> 
   <td>Slave</td> 
   <td>s2</td> 
   <td>172.17.0.5</td> 
   <td>4</td> 
  </tr> 
 </tbody> 
</table>

### 删除上述db1、db2释放压力 ###

[root@mycat ~]# docker stop db1  && docker rm db1 && docker stop db2  && docker rm db2
    db1
    db1
    db2
    db2
    [root@mycat ~]#

### 创建四个配置文件 ###

**创建配置文件目录：**

#创建配置文件
    [root@mycat ~]# mkdir -p /root/dbcnf
    
    #创建四个配置文件
    [root@mycat ~]# touch /root/dbcnf/master1.cnf
    [root@mycat ~]# touch /root/dbcnf/master2.cnf
    [root@mycat ~]# touch /root/dbcnf/slave1.cnf
    [root@mycat ~]# touch /root/dbcnf/slave2.cnf
    [root@mycat ~]#

### 配置Master1(M1) ###

**修改/root/dbcnf/master1.cnf配置文件添加如下配置：**

[mysqld]
    #主服务器唯一ID
    server-id=1
    #启用二进制日志
    log-bin=mysql-bin
    # 设置不要复制的数据库(可设置多个)
    binlog-ignore-db=mysql
    binlog-ignore-db=information_schema
    #设置需要复制的数据库
    binlog-do-db=testdb
    #设置logbin格式
    binlog_format=STATEMENT
    # 在作为从数据库的时候，有写入操作也要更新二进制日志文件
    log-slave-updates
    #表示自增长字段每次递增的量，指自增字段的起始值，其默认值是1，取值范围是1 .. 65535
    auto-increment-increment=2
    # 表示自增长字段从哪个数开始，指字段一次递增多少，他的取值范围是1 .. 65535
    auto-increment-offset=1

**使用上述文件创建容器：**

[root@mycat ~]# docker run --name m1 -p 3316:3306 -v /root/dbcnf/master1.cnf:/etc/mysql/my.cnf -e MYSQL_ROOT_PASSWORD=123456 -d mysql:5.7
    ace8d2729df2f7084c277fba06d18c17725d21a08c5eed6c676c7f890075523f
    [root@mycat ~]# docker ps
    CONTAINER ID   IMAGE       COMMAND                  CREATED         STATUS         PORTS                                                  NAMES
    ace8d2729df2   mysql:5.7   "docker-entrypoint.s…"   5 seconds ago   Up 4 seconds   33060/tcp, 0.0.0.0:3316->3306/tcp, :::3316->3306/tcp   m1
    [root@mycat ~]# docker inspect --format '{ { .NetworkSettings.IPAddress }}' m1
    172.17.0.2
    [root@mycat ~]#

**通过docker命令查看到m1容器的ip地址为172.17.0.2,填入上述准备的表格中**

> **关于mysql中部分配置的说明：  
> 1、关于log-slave-updates()  
> 从库开启log-bin参数，如果直接往从库写数据，是可以记录log-bin日志的，但是从库通过I0线程读取主库二进制日志文件，然后通过SQL线程写入的数据，是不会记录binlog日志的。也就是说从库从主库上复制的数据，是不写入从库的binlog日志的。所以从库做为其他从库的主库时需要在配置文件中添加log-slave-updates参数。  
> 2、关于binlog-format  
> mysql复制主要有三种方式：  
> 基于SQL语句的复制(statement-based replication, SBR)，  
> 基于行的复制(row-based replication, RBR)，  
> 混合模式复制(mixed-based replication, MBR)。对应的，  
> binlog的格式也有三种：STATEMENT，ROW，MIXED。  
> ① STATEMENT模式（SBR）  
> 每一条会修改数据的sql语句会记录到binlog中。优点是并不需要记录每一条sql语句和每一行的数据变化，减少了binlog日志量，节约IO，提高性能。缺点是在某些情况下会导致master-slave中的数据不一致(如sleep()函数， last\_insert\_id()，以及user-defined functions(udf)等会出现问题)  
> ② ROW模式（RBR）  
> 不记录每条sql语句的上下文信息，仅需记录哪条数据被修改了，修改成什么样了。而且不会出现某些特定情况下的存储过程、或function、或trigger的调用和触发无法被正确复制的问题。缺点是会产生大量的日志，尤其是alter table的时候会让日志暴涨。  
> ③ MIXED模式（MBR）  
> 以上两种模式的混合使用，一般的复制使用STATEMENT模式保存binlog，对于STATEMENT模式无法复制的操作使用ROW模式保存binlog，MySQL会根据执行的SQL语句选择日志保存方式。**

### 配置Slave1(S1) ###

**保持原配置/root/dbcnf/slave1.cnf不动即可**

[mysqld]
    
    #从服务器唯一ID
    server-id=2
    #启用中继日志
    relay-log=mysql-relay

**使用上述文件创建容器并查看ip**

[root@mycat ~]# vi /root/dbcnf/slave1.cnf 
    
    [root@mycat ~]# docker run --name s1 -p 3326:3306 -v /root/dbcnf/slave1.cnf:/etc/mysql/my.cnf -e MYSQL_ROOT_PASSWORD=123456 -d mysql:5.7
    78f1f8ce6c8b1d6d4216ac2de432a95b39fee99c3bd689d1326bb9399a255d8c
    
    [root@mycat ~]# docker ps
    CONTAINER ID   IMAGE       COMMAND                  CREATED          STATUS          PORTS                                                  NAMES
    78f1f8ce6c8b   mysql:5.7   "docker-entrypoint.s…"   5 seconds ago    Up 3 seconds    33060/tcp, 0.0.0.0:3326->3306/tcp, :::3326->3306/tcp   s1
    ace8d2729df2   mysql:5.7   "docker-entrypoint.s…"   12 minutes ago   Up 12 minutes   33060/tcp, 0.0.0.0:3316->3306/tcp, :::3316->3306/tcp   m1
    
    [root@mycat ~]# docker inspect --format '{ { .NetworkSettings.IPAddress }}' s1
    172.17.0.3
    [root@mycat ~]#

### 创建Master2(M2)配置文件并创建数据库 ###

**创建/root/dbcnf/master2.cnf配置文件并添加如下配置：**  
**同Master1相似,需要修改server-id(为3)、和auto\_increment\_offset配置**

[mysqld]
    #主服务器唯一ID
    server-id=3 
    #启用二进制日志
    log-bin=mysql-bin
    # 设置不要复制的数据库(可设置多个)
    binlog-ignore-db=mysql
    binlog-ignore-db=information_schema
    #设置需要复制的数据库
    binlog-do-db=testdb
    #设置logbin格式
    binlog_format=STATEMENT
    # 在作为从数据库的时候，有写入操作也要更新二进制日志文件
    log-slave-updates 
    #表示自增长字段每次递增的量，指自增字段的起始值，其默认值是1，取值范围是1 .. 65535
    auto-increment-increment=2 
    # 表示自增长字段从哪个数开始，指字段一次递增多少，他的取值范围是1 .. 65535
    auto-increment-offset=2

**使用上述文件创建容器并查看ip**

[root@mycat ~]# vi /root/dbcnf/master2.cnf
    
    [root@mycat ~]# docker run --name m2 -p 3336:3306 -v /root/dbcnf/master2.cnf:/etc/mysql/my.cnf -e MYSQL_ROOT_PASSWORD=123456 -d mysql:5.7
    d567f78b546e63eecf172b36d648f31868632b632fbad230b570d812078459f8
    
    [root@mycat ~]# docker ps
    CONTAINER ID   IMAGE       COMMAND                  CREATED          STATUS          PORTS                                                  NAMES
    d567f78b546e   mysql:5.7   "docker-entrypoint.s…"   9 seconds ago    Up 4 seconds    33060/tcp, 0.0.0.0:3336->3306/tcp, :::3336->3306/tcp   m2
    78f1f8ce6c8b   mysql:5.7   "docker-entrypoint.s…"   4 minutes ago    Up 3 minutes    33060/tcp, 0.0.0.0:3326->3306/tcp, :::3326->3306/tcp   s1
    ace8d2729df2   mysql:5.7   "docker-entrypoint.s…"   16 minutes ago   Up 16 minutes   33060/tcp, 0.0.0.0:3316->3306/tcp, :::3316->3306/tcp   m1
    
    [root@mycat ~]# docker inspect --format '{ { .NetworkSettings.IPAddress }}' m2
    172.17.0.4
    
    [root@mycat ~]#

### 创建Slave2(S2)配置文件并创建数据库 ###

**创建/root/dbcnf/slave2.cnf配置文件并添加如下配置(同Slave1一样即可),修改server-id**

[mysqld]
    
    #从服务器唯一ID
    server-id=4
    #启用中继日志
    relay-log=mysql-relay

**使用上述文件创建容器并查看ip**

[root@mycat ~]# vim /root/dbcnf/slave2.cnf 
    
    [root@mycat ~]# docker run --name s2 -p 3346:3306 -v /root/dbcnf/slave2.cnf:/etc/mysql/my.cnf -e MYSQL_ROOT_PASSWORD=123456 -d mysql:5.7
    f2706a827e15c2443e4056793a9b58370e528148d279546edc677a833a6611fb
    
    [root@mycat ~]# docker ps
    CONTAINER ID   IMAGE       COMMAND                  CREATED          STATUS          PORTS                                                  NAMES
    f2706a827e15   mysql:5.7   "docker-entrypoint.s…"   5 seconds ago    Up 2 seconds    33060/tcp, 0.0.0.0:3346->3306/tcp, :::3346->3306/tcp   s2
    d567f78b546e   mysql:5.7   "docker-entrypoint.s…"   8 minutes ago    Up 8 minutes    33060/tcp, 0.0.0.0:3336->3306/tcp, :::3336->3306/tcp   m2
    78f1f8ce6c8b   mysql:5.7   "docker-entrypoint.s…"   12 minutes ago   Up 12 minutes   33060/tcp, 0.0.0.0:3326->3306/tcp, :::3326->3306/tcp   s1
    ace8d2729df2   mysql:5.7   "docker-entrypoint.s…"   24 minutes ago   Up 24 minutes   33060/tcp, 0.0.0.0:3316->3306/tcp, :::3316->3306/tcp   m1
    
    [root@mycat ~]# docker inspect --format '{ { .NetworkSettings.IPAddress }}' s2
    172.17.0.5
    [root@mycat ~]#

**至此，四台mysql机器（实际是容器）的环境信息如下：**

<table> 
 <thead> 
  <tr> 
   <th>序号</th> 
   <th>角色</th> 
   <th>容器名</th> 
   <th>容器id</th> 
   <th>ip地址</th> 
   <th>server-id(my.cnf)</th> 
   <th>启用二进制日志</th> 
   <th>启用中继日志</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td>Master1</td> 
   <td>m1</td> 
   <td>ace8d2729df2</td> 
   <td>172.17.0.2</td> 
   <td>1</td> 
   <td>是</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>Slave1</td> 
   <td>s1</td> 
   <td>78f1f8ce6c8b</td> 
   <td>172.17.0.3</td> 
   <td>2</td> 
   <td></td> 
   <td>是</td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td>Master2</td> 
   <td>m2</td> 
   <td>6b3868d2f49d</td> 
   <td>172.17.0.4</td> 
   <td>3</td> 
   <td>是</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>4</td> 
   <td>Slave</td> 
   <td>s2</td> 
   <td>d99c2936a62d</td> 
   <td>172.17.0.5</td> 
   <td>4</td> 
   <td></td> 
   <td>是</td> 
  </tr> 
 </tbody> 
</table>

### 在两台主机上建立主从复制账户 ###

**在两个mysql主机上（M1、M2）建立帐户并授权。`CREATE USER 'slave'@'%' IDENTIFIED BY '123456'; GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'slave'@'%';`**

**具体操作如下:**

> **mysql客户端登录连接m1(IP:172.17.0.2)创建slave用户,并查询master状态**

[root@mycat ~]# mysql -uroot -p123456 -h 172.17.0.2 -P3306
    Welcome to the MariaDB monitor.  Commands end with ; or \g.
    Your MySQL connection id is 2
    Server version: 5.7.34-log MySQL Community Server (GPL)
    
    Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
    
    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
    
    MySQL [(none)]> CREATE USER 'slave'@'%' IDENTIFIED BY '123456';
    Query OK, 0 rows affected (0.00 sec)
    
    MySQL [(none)]> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'slave'@'%';
    Query OK, 0 rows affected (0.01 sec)
    
    MySQL [(none)]> show master status;
    +------------------+----------+--------------+--------------------------+-------------------+
    | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB         | Executed_Gtid_Set |
    +------------------+----------+--------------+--------------------------+-------------------+
    | mysql-bin.000003 |      619 | testdb       | mysql,information_schema |                   |
    +------------------+----------+--------------+--------------------------+-------------------+
    1 row in set (0.00 sec)
    
    MySQL [(none)]>

> **mysql客户端登录连接m2(ip:172.17.0.4)创建slave用户,并查询master状态**

[root@mycat ~]# mysql -uroot -p123456 -h 172.17.0.4 -P3306
    Welcome to the MariaDB monitor.  Commands end with ; or \g.
    Your MySQL connection id is 2
    Server version: 5.7.34-log MySQL Community Server (GPL)
    
    Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
    
    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
    
    MySQL [(none)]> CREATE USER 'slave'@'%' IDENTIFIED BY '123456';
    Query OK, 0 rows affected (0.06 sec)
    
    MySQL [(none)]> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'slave'@'%';
    Query OK, 0 rows affected (0.01 sec)
    
    MySQL [(none)]> show master status;
    +------------------+----------+--------------+--------------------------+-------------------+
    | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB         | Executed_Gtid_Set |
    +------------------+----------+--------------+--------------------------+-------------------+
    | mysql-bin.000003 |      619 | testdb       | mysql,information_schema |                   |
    +------------------+----------+--------------+--------------------------+-------------------+
    1 row in set (0.00 sec)
    
    MySQL [(none)]>

**配置完两个Master后通过查询可以知道Log\_File和Log\_POS的值为下述内容。**

<table> 
 <thead> 
  <tr> 
   <th>序号</th> 
   <th>角色</th> 
   <th>容器名</th> 
   <th>容器id</th> 
   <th>ip地址</th> 
   <th>server-id(my.cnf)</th> 
   <th>启用二进制日志</th> 
   <th>启用中继日志</th> 
   <th>MASTER_LOG_FILE</th> 
   <th>MASTER_LOG_POS</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td>Master1</td> 
   <td>m1</td> 
   <td>ace8d2729df2</td> 
   <td>172.17.0.2</td> 
   <td>1</td> 
   <td>是</td> 
   <td>是</td> 
   <td>mysql-bin.000003</td> 
   <td>619</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>Slave1</td> 
   <td>s1</td> 
   <td>78f1f8ce6c8b</td> 
   <td>172.17.0.3</td> 
   <td>2</td> 
   <td></td> 
   <td>是</td> 
   <td></td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td>Master2</td> 
   <td>m2</td> 
   <td>6b3868d2f49d</td> 
   <td>172.17.0.4</td> 
   <td>3</td> 
   <td>是</td> 
   <td></td> 
   <td>mysql-bin.000003</td> 
   <td>619</td> 
  </tr> 
  <tr> 
   <td>4</td> 
   <td>Slave</td> 
   <td>s2</td> 
   <td>d99c2936a62d</td> 
   <td>172.17.0.5</td> 
   <td>4</td> 
   <td></td> 
   <td>是</td> 
   <td></td> 
   <td></td> 
  </tr> 
 </tbody> 
</table>

**分别记录下File和Position的值后,执行完此步骤后不要再操作主服务器MySQL，防止主服务器状态值变化**

### 在从机上配置需要复制的主机 ###

**Slave1(s1) 复制 Master1(m1)，Slave2(s2) 复制 Master2(m2)**  
`CHANGE MASTER TO MASTER_HOST='主机的IP地址', MASTER_USER='slave', MASTER_PASSWORD='123123', MASTER_LOG_FILE='mysql-bin.具体数字',MASTER_LOG_POS=具体值;`

> **进入Slave1(s1)执行 复制主机的命令,并启动从机的slave功能**

`CHANGE MASTER TO MASTER_HOST='172.17.0.2', MASTER_USER='slave', MASTER_PASSWORD='123456', MASTER_LOG_FILE='mysql-bin.000003',MASTER_LOG_POS=619;`

[root@mycat ~]# mysql -uroot -p123456 -h172.17.0.3 -P3306
    Welcome to the MariaDB monitor.  Commands end with ; or \g.
    Your MySQL connection id is 2
    Server version: 5.7.34 MySQL Community Server (GPL)
    
    Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
    
    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
    
    MySQL [(none)]> CHANGE MASTER TO MASTER_HOST='172.17.0.2',
        -> MASTER_USER='slave',
        -> MASTER_PASSWORD='123456',
        -> MASTER_LOG_FILE='mysql-bin.000003',MASTER_LOG_POS=619;
    Query OK, 0 rows affected, 2 warnings (0.04 sec)
    
    MySQL [(none)]> start slave;
    Query OK, 0 rows affected (0.00 sec)
    
    
    MySQL [(none)]> show slave status\G;
    *************************** 1. row ***************************
                   Slave_IO_State: Waiting for master to send event
                      Master_Host: 172.17.0.2
                      Master_User: slave
                      Master_Port: 3306
                    Connect_Retry: 60
                  Master_Log_File: mysql-bin.000003
              Read_Master_Log_Pos: 619
                   Relay_Log_File: mysql-relay.000002
                    Relay_Log_Pos: 320
            Relay_Master_Log_File: mysql-bin.000003
                 Slave_IO_Running: Yes
                Slave_SQL_Running: Yes
                  Replicate_Do_DB: 
              Replicate_Ignore_DB: 
               Replicate_Do_Table: 
           Replicate_Ignore_Table: 
          Replicate_Wild_Do_Table: 
      Replicate_Wild_Ignore_Table: 
                       Last_Errno: 0
                       Last_Error: 
                     Skip_Counter: 0
              Exec_Master_Log_Pos: 619
                  Relay_Log_Space: 523
                  Until_Condition: None
                   Until_Log_File: 
                    Until_Log_Pos: 0
               Master_SSL_Allowed: No
               Master_SSL_CA_File: 
               Master_SSL_CA_Path: 
                  Master_SSL_Cert: 
                Master_SSL_Cipher: 
                   Master_SSL_Key: 
            Seconds_Behind_Master: 0
    Master_SSL_Verify_Server_Cert: No
                    Last_IO_Errno: 0
                    Last_IO_Error: 
                   Last_SQL_Errno: 0
                   Last_SQL_Error: 
      Replicate_Ignore_Server_Ids: 
                 Master_Server_Id: 1
                      Master_UUID: 306d7a6c-cf33-11eb-8ea3-0242ac110002
                 Master_Info_File: /var/lib/mysql/master.info
                        SQL_Delay: 0
              SQL_Remaining_Delay: NULL
          Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
               Master_Retry_Count: 86400
                      Master_Bind: 
          Last_IO_Error_Timestamp: 
         Last_SQL_Error_Timestamp: 
                   Master_SSL_Crl: 
               Master_SSL_Crlpath: 
               Retrieved_Gtid_Set: 
                Executed_Gtid_Set: 
                    Auto_Position: 0
             Replicate_Rewrite_DB: 
                     Channel_Name: 
               Master_TLS_Version: 
    1 row in set (0.00 sec)
    
    ERROR: No query specified
    
    MySQL [(none)]>

> **进入Slave2(s2)执行 复制主机的命令,并启动从机的slave功能**

**复制的是M2（IP：172.17.0.4）**  
`CHANGE MASTER TO MASTER_HOST='172.17.0.4', MASTER_USER='slave', MASTER_PASSWORD='123456', MASTER_LOG_FILE='mysql-bin.000003',MASTER_LOG_POS=619;`

[root@mycat ~]# mysql -uroot -p123456 -h172.17.0.5 -P3306
    Welcome to the MariaDB monitor.  Commands end with ; or \g.
    Your MySQL connection id is 2
    Server version: 5.7.34 MySQL Community Server (GPL)
    
    Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
    
    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
    
    MySQL [(none)]> CHANGE MASTER TO MASTER_HOST='172.17.0.4',
        -> MASTER_USER='slave',
        -> MASTER_PASSWORD='123456',
        -> MASTER_LOG_FILE='mysql-bin.000003',MASTER_LOG_POS=619;
    Query OK, 0 rows affected, 2 warnings (0.05 sec)
    
    
    
    MySQL [(none)]> start slave;
    Query OK, 0 rows affected (0.00 sec)
    
    MySQL [(none)]> show slave status\G;
    *************************** 1. row ***************************
                   Slave_IO_State: Waiting for master to send event
                      Master_Host: 172.17.0.4
                      Master_User: slave
                      Master_Port: 3306
                    Connect_Retry: 60
                  Master_Log_File: mysql-bin.000003
              Read_Master_Log_Pos: 619
                   Relay_Log_File: mysql-relay.000002
                    Relay_Log_Pos: 320
            Relay_Master_Log_File: mysql-bin.000003
                 Slave_IO_Running: Yes
                Slave_SQL_Running: Yes
                  Replicate_Do_DB: 
              Replicate_Ignore_DB: 
               Replicate_Do_Table: 
           Replicate_Ignore_Table: 
          Replicate_Wild_Do_Table: 
      Replicate_Wild_Ignore_Table: 
                       Last_Errno: 0
                       Last_Error: 
                     Skip_Counter: 0
              Exec_Master_Log_Pos: 619
                  Relay_Log_Space: 523
                  Until_Condition: None
                   Until_Log_File: 
                    Until_Log_Pos: 0
               Master_SSL_Allowed: No
               Master_SSL_CA_File: 
               Master_SSL_CA_Path: 
                  Master_SSL_Cert: 
                Master_SSL_Cipher: 
                   Master_SSL_Key: 
            Seconds_Behind_Master: 0
    Master_SSL_Verify_Server_Cert: No
                    Last_IO_Errno: 0
                    Last_IO_Error: 
                   Last_SQL_Errno: 0
                   Last_SQL_Error: 
      Replicate_Ignore_Server_Ids: 
                 Master_Server_Id: 3
                      Master_UUID: 687b8bda-cf38-11eb-956e-0242ac110004
                 Master_Info_File: /var/lib/mysql/master.info
                        SQL_Delay: 0
              SQL_Remaining_Delay: NULL
          Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
               Master_Retry_Count: 86400
                      Master_Bind: 
          Last_IO_Error_Timestamp: 
         Last_SQL_Error_Timestamp: 
                   Master_SSL_Crl: 
               Master_SSL_Crlpath: 
               Retrieved_Gtid_Set: 
                Executed_Gtid_Set: 
                    Auto_Position: 0
             Replicate_Rewrite_DB: 
                     Channel_Name: 
               Master_TLS_Version: 
    1 row in set (0.00 sec)
    
    ERROR: No query specified
    
    MySQL [(none)]>

**`Slave_IO_Running`、`Slave_SQL_Running`两个参数都是Yes，则说明主从配置成功！  
如果显示Connecting可以再执行一次`show slave status\G;`**

### 配置两个主机相互备份 ###

**Master2(db3) 复制 Master1(db1)，Master1(db1) 复制 Master2(db3) 步骤如下：**  
**1、在Master1中配置配置复制命令  
2、启动Master1的主从复制功能  
3、查看Master1的slave状态  
4、在Master2中配置配置复制命令  
5、启动Master2的主从复制功能  
6、查看Master2的slave状态**

**具体执行过程如下：**  
**在Master1（IP：172.17.0.2）中配置配置复制命令,复制Master2（IP：172.17.0.4）**

[root@mycat ~]# mysql -uroot -p123456 -h172.17.0.2 -P3306
    Welcome to the MariaDB monitor.  Commands end with ; or \g.
    Your MySQL connection id is 4
    Server version: 5.7.34-log MySQL Community Server (GPL)
    
    Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
    
    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
    
    MySQL [(none)]> CHANGE MASTER TO MASTER_HOST='172.17.0.4',
        -> MASTER_USER='slave',
        -> MASTER_PASSWORD='123456',
        -> MASTER_LOG_FILE='mysql-bin.000003',MASTER_LOG_POS=619;
    Query OK, 0 rows affected, 2 warnings (0.04 sec)
    
    MySQL [(none)]> start slave;
    Query OK, 0 rows affected (0.00 sec)
    
    MySQL [(none)]> show slave status\G;
    *************************** 1. row ***************************
                   Slave_IO_State: Waiting for master to send event
                      Master_Host: 172.17.0.4
                      Master_User: slave
                      Master_Port: 3306
                    Connect_Retry: 60
                  Master_Log_File: mysql-bin.000003
              Read_Master_Log_Pos: 619
                   Relay_Log_File: ace8d2729df2-relay-bin.000002
                    Relay_Log_Pos: 320
            Relay_Master_Log_File: mysql-bin.000003
                 Slave_IO_Running: Yes
                Slave_SQL_Running: Yes
                  Replicate_Do_DB: 
              Replicate_Ignore_DB: 
               Replicate_Do_Table: 
           Replicate_Ignore_Table: 
          Replicate_Wild_Do_Table: 
      Replicate_Wild_Ignore_Table: 
                       Last_Errno: 0
                       Last_Error: 
                     Skip_Counter: 0
              Exec_Master_Log_Pos: 619
                  Relay_Log_Space: 534
                  Until_Condition: None
                   Until_Log_File: 
                    Until_Log_Pos: 0
               Master_SSL_Allowed: No
               Master_SSL_CA_File: 
               Master_SSL_CA_Path: 
                  Master_SSL_Cert: 
                Master_SSL_Cipher: 
                   Master_SSL_Key: 
            Seconds_Behind_Master: 0
    Master_SSL_Verify_Server_Cert: No
                    Last_IO_Errno: 0
                    Last_IO_Error: 
                   Last_SQL_Errno: 0
                   Last_SQL_Error: 
      Replicate_Ignore_Server_Ids: 
                 Master_Server_Id: 3
                      Master_UUID: 687b8bda-cf38-11eb-956e-0242ac110004
                 Master_Info_File: /var/lib/mysql/master.info
                        SQL_Delay: 0
              SQL_Remaining_Delay: NULL
          Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
               Master_Retry_Count: 86400
                      Master_Bind: 
          Last_IO_Error_Timestamp: 
         Last_SQL_Error_Timestamp: 
                   Master_SSL_Crl: 
               Master_SSL_Crlpath: 
               Retrieved_Gtid_Set: 
                Executed_Gtid_Set: 
                    Auto_Position: 0
             Replicate_Rewrite_DB: 
                     Channel_Name: 
               Master_TLS_Version: 
    1 row in set (0.00 sec)
    
    ERROR: No query specified

**在Master2（IP：172.17.0.4）中配置配置复制命令,复制Master1（IP：172.17.0.2）**

[root@mycat ~]# mysql -uroot -p123456 -h172.17.0.4 -P3306
    Welcome to the MariaDB monitor.  Commands end with ; or \g.
    Your MySQL connection id is 5
    Server version: 5.7.34-log MySQL Community Server (GPL)
    
    Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
    
    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
    
    MySQL [(none)]> CHANGE MASTER TO MASTER_HOST='172.17.0.2',
        -> MASTER_USER='slave',
        -> MASTER_PASSWORD='123456',
        -> MASTER_LOG_FILE='mysql-bin.000003',MASTER_LOG_POS=619;
    Query OK, 0 rows affected, 2 warnings (0.03 sec)
    
    MySQL [(none)]> start slave;
    Query OK, 0 rows affected (0.00 sec)
    
    MySQL [(none)]> show slave status\G;
    *************************** 1. row ***************************
                   Slave_IO_State: Waiting for master to send event
                      Master_Host: 172.17.0.2
                      Master_User: slave
                      Master_Port: 3306
                    Connect_Retry: 60
                  Master_Log_File: mysql-bin.000003
              Read_Master_Log_Pos: 619
                   Relay_Log_File: 6b3868d2f49d-relay-bin.000002
                    Relay_Log_Pos: 320
            Relay_Master_Log_File: mysql-bin.000003
                 Slave_IO_Running: Yes
                Slave_SQL_Running: Yes
                  Replicate_Do_DB: 
              Replicate_Ignore_DB: 
               Replicate_Do_Table: 
           Replicate_Ignore_Table: 
          Replicate_Wild_Do_Table: 
      Replicate_Wild_Ignore_Table: 
                       Last_Errno: 0
                       Last_Error: 
                     Skip_Counter: 0
              Exec_Master_Log_Pos: 619
                  Relay_Log_Space: 534
                  Until_Condition: None
                   Until_Log_File: 
                    Until_Log_Pos: 0
               Master_SSL_Allowed: No
               Master_SSL_CA_File: 
               Master_SSL_CA_Path: 
                  Master_SSL_Cert: 
                Master_SSL_Cipher: 
                   Master_SSL_Key: 
            Seconds_Behind_Master: 0
    Master_SSL_Verify_Server_Cert: No
                    Last_IO_Errno: 0
                    Last_IO_Error: 
                   Last_SQL_Errno: 0
                   Last_SQL_Error: 
      Replicate_Ignore_Server_Ids: 
                 Master_Server_Id: 1
                      Master_UUID: 306d7a6c-cf33-11eb-8ea3-0242ac110002
                 Master_Info_File: /var/lib/mysql/master.info
                        SQL_Delay: 0
              SQL_Remaining_Delay: NULL
          Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
               Master_Retry_Count: 86400
                      Master_Bind: 
          Last_IO_Error_Timestamp: 
         Last_SQL_Error_Timestamp: 
                   Master_SSL_Crl: 
               Master_SSL_Crlpath: 
               Retrieved_Gtid_Set: 
                Executed_Gtid_Set: 
                    Auto_Position: 0
             Replicate_Rewrite_DB: 
                     Channel_Name: 
               Master_TLS_Version: 
    1 row in set (0.00 sec)
    
    ERROR: No query specified
    
    MySQL [(none)]>

### 验证mysql主从复制 ###

**使用mysql客户端分别连接m1、s1、m2、s2，在M1中创建数据库create database testdb,在s1、m2、s2中查询数据库是否正常同步。**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzg4MzkxNw_size_16_color_FFFFFF_t_70 1]

## 配置mycat读写分离 ##

**修改`<dataHost>`的`balance`属性，通过此属性配置读写分离的类型  
修改 Mycat 的配置文件`schema.xml`,在原有基础上增加`writeHost`以及对应的`readHost`**

<?xml version="1.0"?>
    <!DOCTYPE mycat:schema SYSTEM "schema.dtd">
    <mycat:schema xmlns:mycat="http://io.mycat/">
    
        <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
        </schema>
        <dataNode name="dn1" dataHost="host1" database="testdb" />
    
           <dataHost name="host1" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100" >
          <heartbeat>select 1</heartbeat>
    
         
         <writeHost host="hostM1" url="172.17.0.2:3306" user="root" password="123456">
             
             <readHost host="hostS1" url="172.17.0.3:3306" user="root" password="123456" />
         </writeHost>
    
    
        <writeHost host="hostM2" url="172.17.0.4:3306" user="root" password="123456">
             
             <readHost host="hostS2" url="172.17.0.5:3306" user="root" password="123456" />
         </writeHost>
     </dataHost>
    
    </mycat:schema>

*  **balance=“1”: 全部的readHost与stand by writeHost参与select语句的负载均衡。**
 *  **writeType=“0”: 所有写操作发送到配置的第一个writeHost，第一个挂了切到还生存的第二个**
 *  **writeType=“1”，所有写操作都随机的发送到配置的 writeHost，1.5 以后废弃不推荐**
 *  **writeHost，重新启动后以切换后的为准，切换记录在配置文件中:dnindex.properties 。**
 *  **switchType="1"**
    
     *  **1默认值，自动切换。**
     *  **-1 表示不自动切换**
     *  **2 基于 MySQL 主从同步的状态决定是否切换。**

> **操作1  
> 登录mycat创建数据库，成功后登录m1、s1、m2、s2查看表use testdb;,show tables;  
> 可以看到四个数据库都出现了表结构,说明在mycat下主从复制可以用**

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzg4MzkxNw_size_16_color_FFFFFF_t_70 2]  
**通过`docker ps` 或 `docker ps|awk '{print $1," ",$13}'` 命令可以查看到四个容器对应的容器id（容器内部的主机名即为容器id）**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzg4MzkxNw_size_16_color_FFFFFF_t_70 3]

> **操作2  
> 在mycat中插入能区分的数据，比如插入时带着主机名字的变量,然后查询数据,发现数据库中主机名字在M2、S1、S2之间切换,说明读写分离生效**

[root@mycat ~]# mysql -umycat -p123456 -h127.0.0.1 -P8066
    Welcome to the MariaDB monitor.  Commands end with ; or \g.
    Your MySQL connection id is 4
    Server version: 5.6.29-mycat-1.6-RELEASE-20161028204710 MyCat Server (OpenCloundDB)
    
    Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
    
    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
    
    MySQL [(none)]> use TESTDB
    Reading table information for completion of table and column names
    You can turn off this feature to get a quicker startup with -A
    
    Database changed
    MySQL [TESTDB]> INSERT INTO user_info(NAME) VALUES(@@hostname);
    Query OK, 1 row affected, 1 warning (0.01 sec)
    
    MySQL [TESTDB]> select * from user_info;
    +----+--------------+
    | id | name         |
    +----+--------------+
    | 11 | 78f1f8ce6c8b |
    +----+--------------+
    1 row in set (0.00 sec)
    
    MySQL [TESTDB]> select * from user_info;
    +----+--------------+
    | id | name         |
    +----+--------------+
    | 11 | 6b3868d2f49d |
    +----+--------------+
    1 row in set (0.00 sec)
    
    MySQL [TESTDB]> select * from user_info;
    +----+--------------+
    | id | name         |
    +----+--------------+
    | 11 | 78f1f8ce6c8b |
    +----+--------------+
    1 row in set (0.00 sec)
    
    MySQL [TESTDB]> select * from user_info;
    +----+--------------+
    | id | name         |
    +----+--------------+
    | 11 | 78f1f8ce6c8b |
    +----+--------------+
    1 row in set (0.01 sec)
    
    MySQL [TESTDB]> select * from user_info;
    +----+--------------+
    | id | name         |
    +----+--------------+
    | 11 | d99c2936a62d |
    +----+--------------+
    1 row in set (0.01 sec)
    
    MySQL [TESTDB]> select * from user_info;
    +----+--------------+
    | id | name         |
    +----+--------------+
    | 11 | 78f1f8ce6c8b |
    +----+--------------+
    1 row in set (0.01 sec)
    
    
    MySQL [TESTDB]> select * from user_info;
    +----+--------------+
    | id | name         |
    +----+--------------+
    | 11 | 78f1f8ce6c8b |
    +----+--------------+
    1 row in set (0.00 sec)
    
    MySQL [TESTDB]> select * from user_info;
    +----+--------------+
    | id | name         |
    +----+--------------+
    | 11 | 78f1f8ce6c8b |
    +----+--------------+
    1 row in set (0.00 sec)
    
    MySQL [TESTDB]> select * from user_info;
    +----+--------------+
    | id | name         |
    +----+--------------+
    | 11 | 6b3868d2f49d |
    +----+--------------+
    1 row in set (0.00 sec)
    
    MySQL [TESTDB]> select * from user_info;
    +----+--------------+
    | id | name         |
    +----+--------------+
    | 11 | d99c2936a62d |
    +----+--------------+
    1 row in set (0.00 sec)

## 验证双主切换 ##

**模拟Master1（M1）异常（此处可以停止mysql服务或者停止容器）,测试mycat能否自动切换到Master2正常插入。实现高可用性。**

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzg4MzkxNw_size_16_color_FFFFFF_t_70 4]  
**停止m1容器**

root@mycat ~]# docker ps
    CONTAINER ID   IMAGE       COMMAND                  CREATED       STATUS       PORTS                                                  NAMES
    d99c2936a62d   mysql:5.7   "docker-entrypoint.s…"   3 hours ago   Up 3 hours   33060/tcp, 0.0.0.0:3346->3306/tcp, :::3346->3306/tcp   s2
    6b3868d2f49d   mysql:5.7   "docker-entrypoint.s…"   3 hours ago   Up 3 hours   33060/tcp, 0.0.0.0:3336->3306/tcp, :::3336->3306/tcp   m2
    78f1f8ce6c8b   mysql:5.7   "docker-entrypoint.s…"   3 hours ago   Up 3 hours   33060/tcp, 0.0.0.0:3326->3306/tcp, :::3326->3306/tcp   s1
    ace8d2729df2   mysql:5.7   "docker-entrypoint.s…"   4 hours ago   Up 4 hours   33060/tcp, 0.0.0.0:3316->3306/tcp, :::3316->3306/tcp   m1
    [root@mycat ~]# docker stop m1
    m1
    [root@mycat ~]# docker ps
    CONTAINER ID   IMAGE       COMMAND                  CREATED       STATUS       PORTS                                                  NAMES
    d99c2936a62d   mysql:5.7   "docker-entrypoint.s…"   3 hours ago   Up 3 hours   33060/tcp, 0.0.0.0:3346->3306/tcp, :::3346->3306/tcp   s2
    6b3868d2f49d   mysql:5.7   "docker-entrypoint.s…"   3 hours ago   Up 3 hours   33060/tcp, 0.0.0.0:3336->3306/tcp, :::3336->3306/tcp   m2
    78f1f8ce6c8b   mysql:5.7   "docker-entrypoint.s…"   3 hours ago   Up 3 hours   33060/tcp, 0.0.0.0:3326->3306/tcp, :::3326->3306/tcp   s1
    [root@mycat ~]#

**插入数据,并查询**

[root@mycat ~]# mysql -umycat -p123456 -h127.0.0.1 -P8066
    Welcome to the MariaDB monitor.  Commands end with ; or \g.
    Your MySQL connection id is 6
    Server version: 5.6.29-mycat-1.6-RELEASE-20161028204710 MyCat Server (OpenCloundDB)
    
    Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
    
    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
    
    MySQL [(none)]> use TESTDB
    Reading table information for completion of table and column names
    You can turn off this feature to get a quicker startup with -A
    
    Database changed
    MySQL [TESTDB]> INSERT INTO user_info(NAME) VALUES(concat('valid-switch--',@@hostname));
    Query OK, 1 row affected, 1 warning (0.01 sec)
    
    MySQL [TESTDB]> select * from user_info;
    +----+----------------------------+
    | id | name                       |
    +----+----------------------------+
    | 11 | d99c2936a62d               |
    | 12 | valid-switch--d99c2936a62d |
    +----+----------------------------+
    2 rows in set (0.01 sec)
    
    MySQL [TESTDB]> select * from user_info;
    +----+----------------------------+
    | id | name                       |
    +----+----------------------------+
    | 11 | d99c2936a62d               |
    | 12 | valid-switch--d99c2936a62d |
    +----+----------------------------+
    2 rows in set (0.00 sec)
    
    MySQL [TESTDB]> select * from user_info;
    +----+----------------------------+
    | id | name                       |
    +----+----------------------------+
    | 11 | d99c2936a62d               |
    | 12 | valid-switch--d99c2936a62d |
    +----+----------------------------+
    2 rows in set (0.00 sec)

**通过实验发现插入操作成功，且查询时能访问到`M2`对应的`S2`的机器(如上图所示)  
此时重新启动`M1`后登录Mycat查询数据，发现查询的内容是Master1、Salve1、Salve2其中Master1变为备份主机了。**

[root@mycat ~]# mysql -umycat -p123456 -h127.0.0.1 -P8066
    Welcome to the MariaDB monitor.  Commands end with ; or \g.
    Your MySQL connection id is 7
    Server version: 5.6.29-mycat-1.6-RELEASE-20161028204710 MyCat Server (OpenCloundDB)
    
    Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
    
    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
    
    MySQL [(none)]> use TESTDB
    Reading table information for completion of table and column names
    You can turn off this feature to get a quicker startup with -A
    
    Database changed
    MySQL [TESTDB]> select * from user_info;
    +----+----------------------------+
    | id | name                       |
    +----+----------------------------+
    | 11 | d99c2936a62d               |
    | 12 | valid-switch--d99c2936a62d |
    +----+----------------------------+
    2 rows in set (0.00 sec)
    
    MySQL [TESTDB]> select * from user_info;
    +----+--------------+
    | id | name         |
    +----+--------------+
    | 11 | 78f1f8ce6c8b |
    +----+--------------+
    1 row in set (0.01 sec)
    
    MySQL [TESTDB]> select * from user_info;
    +----+----------------------------+
    | id | name                       |
    +----+----------------------------+
    | 11 | d99c2936a62d               |
    | 12 | valid-switch--d99c2936a62d |
    +----+----------------------------+
    2 rows in set (0.01 sec)
    
    MySQL [TESTDB]> select * from user_info;
    +----+--------------+
    | id | name         |
    +----+--------------+
    | 11 | 78f1f8ce6c8b |
    +----+--------------+
    1 row in set (0.00 sec)
    
    MySQL [TESTDB]> select * from user_info;
    +----+----------------------------+
    | id | name                       |
    +----+----------------------------+
    | 11 | d99c2936a62d               |
    | 12 | valid-switch--d99c2936a62d |
    +----+----------------------------+
    2 rows in set (0.00 sec)
    
    MySQL [TESTDB]> select * from user_info;
    +----+--------------+
    | id | name         |
    +----+--------------+
    | 11 | 78f1f8ce6c8b |
    +----+--------------+
    1 row in set (0.01 sec)
    
    MySQL [TESTDB]> select * from user_info;
    +----+--------------+
    | id | name         |
    +----+--------------+
    | 11 | 78f1f8ce6c8b |
    +----+--------------+
    1 row in set (0.00 sec)
    
    MySQL [TESTDB]> select * from user_info;
    +----+----------------------------+
    | id | name                       |
    +----+----------------------------+
    | 11 | ace8d2729df2               |
    | 12 | valid-switch--ace8d2729df2 |
    +----+----------------------------+
    2 rows in set (0.01 sec)
    
    MySQL [TESTDB]> select * from user_info;
    +----+--------------+
    | id | name         |
    +----+--------------+
    | 11 | 78f1f8ce6c8b |
    +----+--------------+
    1 row in set (0.00 sec)
    
    MySQL [TESTDB]> select * from user_info;
    +----+----------------------------+
    | id | name                       |
    +----+----------------------------+
    | 11 | ace8d2729df2               |
    | 12 | valid-switch--ace8d2729df2 |
    +----+----------------------------+
    2 rows in set (0.01 sec)
    
    MySQL [TESTDB]> select * from user_info;
    +----+----------------------------+
    | id | name                       |
    +----+----------------------------+
    | 11 | d99c2936a62d               |
    | 12 | valid-switch--d99c2936a62d |
    +----+----------------------------+
    2 rows in set (0.00 sec)
    
    MySQL [TESTDB]> select * from user_info;
    +----+----------------------------+
    | id | name                       |
    +----+----------------------------+
    | 11 | ace8d2729df2               |
    | 12 | valid-switch--ace8d2729df2 |
    +----+----------------------------+
    2 rows in set (0.00 sec)
    
    MySQL [TESTDB]>

> **通过上述查询发现查询时可以落到M1、S1、S2上。双主切换成功。**

> **由于篇幅已经挺长了，就写一篇【Mycat的简单使用（三）【分库分表】】继续介绍**

> **版权声明：**  
> **原创博主：牛哄哄的柯南**  
> **博主原文链接：[https://keafmd.blog.csdn.net/][https_keafmd.blog.csdn.net]**

**看完如果对你有帮助，感谢点赞支持！  
如果你是电脑端，看到右下角的 “一键三连” 了吗，没错点它\[哈哈\]**  
![在这里插入图片描述][20201023201048810.gif_pic_center]  
**加油！**

**共同努力！**

**Keafmd**

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzg4MzkxNw_size_16_color_FFFFFF_t_70]: /images/20221005/a9c70ac52ab64e3a8764215c49572752.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzg4MzkxNw_size_16_color_FFFFFF_t_70 1]: /images/20221005/65c3d26b9be4405faf013c4340af76ba.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzg4MzkxNw_size_16_color_FFFFFF_t_70 2]: /images/20221005/4de6a07673fb4bf7b6d2c4605bb4ea22.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzg4MzkxNw_size_16_color_FFFFFF_t_70 3]: /images/20221005/803ca7cda5a943e39e8674a85fd4edca.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzg4MzkxNw_size_16_color_FFFFFF_t_70 4]: /images/20221005/a2c524b7266c4482809098ec310889c0.png
[https_keafmd.blog.csdn.net]: https://keafmd.blog.csdn.net/
[20201023201048810.gif_pic_center]: /images/20221005/571bb2478a2344628552bc2cf169f7e3.png