先搭载MySQL一主两从

192.168.20.110	MyCAT
192.168.20.111	Master
192.168.20.112	slave1
192.168.20.113	slave2

配置就不写了，比较基础，写一下步骤

1.进入mysql配置文件或者其子配置文件，添加server_id,开启gtidgtid_mode=ON,enforce-gtid-consistency=ON

2.主库创建用于链接的用户并授权

3.在从库向主库发送请求

MyCAT2的主从配置

登录MyCAT 2在MyCAT2里面操作

[root@Master ~]# mysql -uroot -p123456 -P8066 -h192.168.20.110

在Mycat里创建数据库mydb1

mysql> create database mydb1;
Query OK, 0 rows affected (0.35 sec)

修改mydb1.schema.json

[root@MyCAT bin]# vim /usr/local/mycat/conf/schemas/mydb1.schema.json 
{
        "customTables":{},
        "globalTables":{},
        "normalProcedures":{},
        "normalTables":{},
        "schemaName":"mydb1",
        "targetName":"prototype",
        "shardingTables":{},
        "views":{}
}

使用注解方式添加数据源

mysql> /*+ mycat:createDataSource{
    -> "name":"rwSepw",
    -> "url":"jdbc:mysql://192.168.20.111:3306/?useSSL=false&characterEncoding=UTF-8&useJDBCCompliantTimezoneShift=true",
    -> "user":"mycat",
    -> "password":"123456"
    -> } */
    -> ;
Query OK, 0 rows affected (0.03 sec)

mysql> /*+ mycat:createDataSource{
    -> "name":"rwSepr1",
    -> "url":"jdbc:mysql://192.168.20.112:3306/?useSSL=false&characterEncoding=UTF-8&useJDBCCompliantTimezoneShift=true",
    -> "user":"mycat",
    -> "password":"123456"
    -> } */;
Query OK, 0 rows affected (0.06 sec)

mysql> /*+ mycat:createDataSource{
    -> "name":"rwSepr2",
    -> "url":"jdbc:mysql://192.168.20.113:3306/?useSSL=false&characterEncoding=UTF-8&useJDBCCompliantTimezoneShift=true",
    -> "user":"mycat",
    -> "password":"123456"
    -> } */;
Query OK, 0 rows affected (0.02 sec)

查询配置数据源结果

 /*+ mycat:showDataSources{} */;

 更新集群信息,添加dr0从节点.实现读写分离

mysql> /*!mycat:createCluster{"name":"prototype","masters":["rwSepw"],"replicas":["rwSepr1","rwSepr2"]} */;
Query OK, 0 rows affected (0.04 sec)

查看配置集群信息

/*+ mycat:showClusters{} */;

 查看集群配置文件

[root@MyCAT bin]#  cat /usr/local/mycat/conf/clusters/prototype.cluster.json
{
	"clusterType":"MASTER_SLAVE",
	"heartbeat":{
		"heartbeatTimeout":1000,
		"maxRetryCount":3,
		"minSwitchTimeInterval":300,
		"showLog":false,
		"slaveThreshold":0.0
	},
	"masters":[
		"rwSepw"
	],
	"maxCon":2000,
	"name":"prototype",
	"readBalanceType":"BALANCE_ALL",
	"replicas":[
		"rwSepr1",
		"rwSepr2"
	],
	"switchType":"SWITCH"
}[root@MyCAT bin]# 

 readBalanceType
查询负载均衡策略
可选值:
BALANCE_ALL(默认值)
获取集群中所有数据源
BALANCE_ALL_READ
获取集群中允许读的数据源
BALANCE_READ_WRITE
获取集群中允许读写的数据源,但允许读的数据源优先
BALANCE_NONE
获取集群中允许写数据源,即主节点中选择
switchType
NOT_SWITCH:不进行主从切换
SWITCH:进行主从切换

验证读写分离

重新启动Mycat

}[root@MyCAT bin]# ./mycat restart
Stopping mycat2...
Stopped mycat2.
Starting mycat2...
[root@MyCAT bin]# ./mycat status
mycat2 is running (3321).
[root@MyCAT bin]# tail ../logs/wrapper.log 
INFO   | jvm 1    | 2024/03/04 21:47:26 | 	at io.mycat.datasource.jdbc.datasource.DefaultConnection.executeQuery(DefaultConnection.java:84)
INFO   | jvm 1    | 2024/03/04 21:47:26 | 	... 17 common frames omitted
INFO   | jvm 1    | 2024/03/04 21:47:28 | 2024-03-04 21:47:27,959[INFO]io.mycat.vertx.VertxMycatServer.lambda$start$1:166Mycat Vertx server f705da33-7493-4239-9dfc-eedc64f8aa41 started up.

验证读写分离

mysql> CREATE DATABASE db4 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ERROR 2013 (HY000): Lost connection to MySQL server during query
No connection. Trying to reconnect...
Connection id:    0
Current database: *** NONE ***

Query OK, 0 rows affected (0.48 sec)


mysql> use db4;
Database changed
mysql> create table sys_user(
    ->     id bigint primary key,
    ->     username varchar(200) not null,
    ->     address  varchar(500)
    -> );
Query OK, 0 rows affected (0.23 sec)

mysql> /*+ mycat:repairPhysicalTable{} */;
Query OK, 0 rows affected (0.00 sec)

mysql> insert INTO sys_user(id,username,address) values(1,"xiaofang","wuhan");
Query OK, 1 row affected (0.09 sec)

mysql> select * from sys_user;
+----+----------+---------+
| id | username | address |
+----+----------+---------+
|  1 | xiaofang | wuhan   |
+----+----------+---------+
1 row in set (0.02 sec)

 在从库查看表是有的

 MyCAT里查询测试

修改上面3个MySQL数据库中db1库中sys_user表里面的数据，让它不一样，再在MyCAT里面执行查询 结果如下

Master：

mysql> update db4.sys_user set address="wuhan_master";
Query OK, 1 row affected (0.02 sec)

slave1：

mysql> update db4.sys_user
    -> set address="wuhan_slave1";
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

slave2：

mysql> update db4.sys_user set address="wuhan_slave2";
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

 测试结束