先搭载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
测试结束
