Mycat双主双从读写分离配置

1.修改mycat的schema.xml文件

vim /usr/mycat/conf/schema.xml

balance 属性
负载均衡类型，目前的取值有 3 种：

balance=“0”, 不开启读写分离机制，所有读操作都发送到当前可用的 writeHost 上。
balance=“1”，全部的 readHost 与 stand by writeHost 参与 select 语句的负载均衡，简单的说，当双
主双从模式(M1->S1，M2->S2，并且 M1 与 M2 互为主备)，正常情况下，M2,S1,S2 都参与 select 语句的负载
均衡。
balance=“2”，所有读操作都随机的在 writeHost、readhost 上分发。
balance=“3”，所有读请求随机的分发到 wiriterHost 对应的 readhost 执行，writerHost 不负担读压力，
注意 balance=3 只在 1.4 及其以后版本有，1.3 没有。

writeType 属性
负载均衡类型，目前的取值有 3 种：

writeType=“0”, 所有写操作发送到配置的第一个 writeHost，第一个挂了切到还生存的第二个 writeHost，
重新启动后已切换后的为准，切换记录在配置文件中:dnindex.properties .
writeType=“1”，所有写操作都随机的发送到配置的 writeHost，1.5 以后废弃不推荐。switchType 属
性

-1 表示不自动切换。
1 默认值，自动切换。
2 基于 MySQL 主从同步的状态决定是否切换。

switchType 属性
-1 表示不自动切换
1 默认值，自动切换
2 基于 MySQL 主从同步的状态决定是否切换
心跳语句为 show slave status
3 基于 MySQL galary cluster 的切换机制（适合集群）（1.4.1）
心跳语句为 show status like ‘wsrep%’

修改dataHost的 balance=“1”

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

        <schema name="TESTDB" checkSQLschema="true" sqlMaxLimit="100" dataNode="dn1">
        </schema>
        <dataNode name="dn1" dataHost="host1" database="centos7" />
        <dataHost name="host1" maxCon="1000" minCon="10" balance="1"
                          writeType="0" dbType="mysql" dbDriver="native" switchType="3"  slaveThreshold="100">
                <heartbeat>select user()</heartbeat>
                <!-- can have multi write hosts -->
                <writeHost host="hostM1" url="192.168.148.134:3306" user="root"
                                   password="password">
                          <readHost host="hostS1" url="192.168.148.133:3306"  user="root" password="password"/>
                </writeHost>
                 <writeHost host="hostM2" url="192.168.148.136:3306" user="root"
                                   password="password">
                          <readHost host="hostS2" url="192.168.148.137:3306"  user="root" password="password"/>
                </writeHost>
        </dataHost>
</mycat:schema>

2.启动mycat

[root@mycat bin]# /usr/mycat/bin/mycat console
Running Mycat-server...
Removed stale pid file: /usr/mycat/logs/mycat.pid
wrapper  | --> Wrapper Started as Console
wrapper  | Launching a JVM...
jvm 1    | Wrapper (Version 3.2.3) http://wrapper.tanukisoftware.org
jvm 1    |   Copyright 1999-2006 Tanuki Software, Inc.  All Rights Reserved.
jvm 1    | 
jvm 1    | MyCAT Server startup successfully. see logs in logs/mycat.log

3.登录mycat

mysql -umycat -p -P8066 -h127.0.0.1 --default_auth=mysql_native_password

4.验证数据

//查看数据库
mysql>  show databases;
+----------+
| DATABASE |
+----------+
| TESTDB   |
+----------+
1 row in set (0.00 sec)
//使用数据库
mysql> use TESTDB
//查看已有表格
show tables;
+-------------------+
| Tables_in_centos7 |
+-------------------+
| location          |
+-------------------+
//插入数据
insert into location values(@@hostname);

每台实体机上分别查看数据

//master1
mysql> select * from location;
+----------+
| location |
+----------+
| master1  |
+----------+
1 row in set (0.00 sec)
//master2
mysql> select * from location;
+----------+
| location |
+----------+
| master2  |
+----------+
1 row in set (0.00 sec)
//slave1
mysql> select * from location;
+----------+
| location |
+----------+
| slave1   |
+----------+
1 row in set (0.00 sec)
//slave2
mysql> select * from location;
+----------+
| location |
+----------+
| slave2  |
+----------+
1 row in set (0.00 sec)

mycat上查看数据

mysql> select * from location;
+----------+
| location |
+----------+
| master2  |
+----------+
1 row in set (0.01 sec)

mysql> select * from location;
+----------+
| location |
+----------+
| slave2   |
+----------+
1 row in set (0.00 sec)

mysql> select * from location;
+----------+
| location |
+----------+
| slave2   |
+----------+
1 row in set (0.00 sec)

mysql> select * from location;
+----------+
| location |
+----------+
| slave1   |
+----------+
1 row in set (0.01 sec)

mysql> select * from location;
+----------+
| location |
+----------+
| master2  |
+----------+
1 row in set (0.01 sec)

可以看到,master2,slave1,slave2都有作为读主机出现
此时master1为写主机

5.测试双主切换

master1上

[root@master1 ~]# systemctl stop mysqld.service 
[root@master1 ~]# systemctl status mysqld.service 
● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: inactive (dead) since 三 2022-04-13 00:33:05 CST; 20s ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
  Process: 1521 ExecStart=/usr/sbin/mysqld $MYSQLD_OPTS (code=exited, status=0/SUCCESS)
  Process: 1030 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
 Main PID: 1521 (code=exited, status=0/SUCCESS)
   Status: "Server shutdown complete"

4月 13 00:33:03 master1 mysqld[1521]: 2022-04-12T16:33:03.976262Z 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 43  user: 'root'.
4月 13 00:33:03 master1 mysqld[1521]: 2022-04-12T16:33:03.986751Z 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 44  user: 'root'.
4月 13 00:33:04 master1 mysqld[1521]: 2022-04-12T16:33:03.997422Z 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 37  user: 'root'.
4月 13 00:33:04 master1 mysqld[1521]: 2022-04-12T16:33:04.008225Z 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 45  user: 'root'.
4月 13 00:33:04 master1 mysqld[1521]: 2022-04-12T16:33:04.019027Z 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 46  user: 'root'.
4月 13 00:33:04 master1 mysqld[1521]: 2022-04-12T16:33:04.023009Z 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 38  user: 'root'.
4月 13 00:33:04 master1 mysqld[1521]: 2022-04-12T16:33:04.029128Z 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 39  user: 'root'.
4月 13 00:33:04 master1 mysqld[1521]: 2022-04-12T16:33:04.040287Z 0 [Warning] [MY-010909] [Server] /usr/sbin/mysqld: Forcing close of thread 47  user: 'root'.
4月 13 00:33:05 master1 mysqld[1521]: 2022-04-12T16:33:05.587799Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.28)  MySQL Community Server - GPL.
4月 13 00:33:05 master1 systemd[1]: Stopped MySQL Server.

mycat上插入数据测试

mysql> insert into location values(@@hostname);
Query OK, 1 row affected, 1 warning (0.00 sec)

可以成功插入

测试读主机

mysql> select * from location;
+----------+
| location |
+----------+
| slave2   |
| slave2   |
+----------+
2 rows in set (0.00 sec)

mysql> select * from location;
+----------+
| location |
+----------+
| slave2   |
| slave2   |
+----------+
2 rows in set (0.00 sec)

mysql> select * from location;
+----------+
| location |
+----------+
| slave2   |
| slave2   |
+----------+
2 rows in set (0.00 sec)

slave1仅作为master1的备份,未关联master2的操作,此时读主机均为slave2

6.重启master1的mysql服务进行测试

master1上

[root@master1 ~]# systemctl start mysqld.service

mycat上

mysql> insert into location values(@@hostname);
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> select * from location;
+----------+
| location |
+----------+
| master1  |
| master1  |
| master1  |
+----------+
3 rows in set (0.01 sec)

mysql> select * from location;
+----------+
| location |
+----------+
| slave1   |
| slave1   |
| slave1   |
+----------+
3 rows in set (0.00 sec)

mysql> select * from location;
+----------+
| location |
+----------+
| slave1   |
| slave1   |
| slave1   |
+----------+
3 rows in set (0.00 sec)

mysql> select * from location;
+----------+
| location |
+----------+
| slave2   |
| slave2   |
| slave2   |
+----------+
3 rows in set (0.01 sec)