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>
        <!--can have multi write hosts -->
        <writeHost host="hostM1" url="172.17.0.2:3306" user="root"   password="123456">
            <!-- can have multi read hosts -->
            <readHost host="hostS1" url="172.17.0.3:3306" user="root" password="123456" />
        </writeHost>
    </dataHost>
</mycat:schema>

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

[root@mycat ~]# mysql -umycat -p123456 -h 127.0.0.1 -P 8066
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]> select * from test_table;
+----+--------------+
| id | name         |
+----+--------------+
|  1 | 订单信息     |
|  2 | 读机器       |
+----+--------------+
2 rows in set (0.01 sec)
MySQL [TESTDB]> select * from test_table;
+----+--------------+
| id | name         |
+----+--------------+
|  1 | 订单信息     |
|  2 | 读机器       |
+----+--------------+
2 rows in set (0.01 sec)
MySQL [TESTDB]> select * from test_table;
+----+--------------+
| id | name         |
+----+--------------+
|  1 | 订单信息     |
|  2 | 读机器       |
+----+--------------+
2 rows in set (0.00 sec)
MySQL [TESTDB]> select * from test_table;
+----+--------------+
| id | name         |
+----+--------------+
|  1 | 订单信息     |
|  2 | 读机器       |
+----+--------------+
2 rows in set (0.00 sec)

双主双从读写分离

原理

一个主机ml用于处理所有写请求，它的从机s1和另一台主机m2还有它的从机s2负责所有读请

求。当m1主机宕机后，m2主机负责写请求，ml、m2互为备机。架构图如下。

配置数据库

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

序号角色容器名 ip地址 server-id(my.cnf)

1 Master1 m1 172.17.0.2 1

2 Slave1 s1 172.17.0.3 2

3 Master2 m2 172.17.0.4 3

4 Slave s2 172.17.0.5 4

删除上述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机器（实际是容器）的环境信息如下：

序号角色容器名容器id ip地址 server-id(my.cnf) 启用二进制日志启用中继日志

1 Master1 m1 ace8d2729df2 172.17.0.2 1 是

2 Slave1 s1 78f1f8ce6c8b 172.17.0.3 2 是

3 Master2 m2 6b3868d2f49d 172.17.0.4 3 是

4 Slave s2 d99c2936a62d 172.17.0.5 4 是

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

在两个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的值为下述内容。

序号角色容器名容器id ip地址 server-id(my.cnf) 启用二进制日志启用中继日志 MASTER_LOG_FILE MASTER_LOG_POS

1 Master1 m1 ace8d2729df2 172.17.0.2 1 是是 mysql-bin.000003 619

2 Slave1 s1 78f1f8ce6c8b 172.17.0.3 2 是

3 Master2 m2 6b3868d2f49d 172.17.0.4 3 是 mysql-bin.000003 619

4 Slave s2 d99c2936a62d 172.17.0.5 4 是

分别记录下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 -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)]>

进入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中查询数据库是否正常同步。

配置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>
     <!-- can have multi write hosts -->
     <writeHost host="hostM1" url="172.17.0.2:3306" user="root" password="123456">
         <!-- can have multi read hosts -->
         <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">
         <!-- can have multi read hosts -->
         <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下主从复制可以用

通过docker ps 或 docker ps|awk '{print $1," ",$13}' 命令可以查看到四个容器对应的容器id（容器内部的主机名即为容器id）

操作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正常插入。实现高可用性。

停止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的简单使用（三）【分库分表】】继续介绍

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

主从读写分离

双主双从读写分离

原理

配置数据库

删除上述db1、db2释放压力

创建四个配置文件

配置Master1(M1)

配置Slave1(S1)

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

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

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

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

配置两个主机相互备份

验证mysql主从复制

配置mycat读写分离

验证双主切换

热门文章

最新文章

相关课程

相关电子书

相关实验场景

热门

活动广场

任务中心

开发者评测

高校计划

乘风者计划

训练营

阿里云MVP

话题

直播

下载

镜像站

技术资料

插件

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

主从读写分离

双主双从读写分离

原理

配置数据库

删除上述db1、db2释放压力

创建四个配置文件

配置Master1(M1)

配置Slave1(S1)

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

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

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

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

配置两个主机相互备份

验证mysql主从复制

配置mycat读写分离

验证双主切换

热门文章

最新文章

相关课程

相关电子书

相关实验场景