http://t.csdnimg.cn/KzwDy(mysql主从搭建)
前提,先搭建好MySQL的主从配置,登录MyCAT 2在MyCAT2里面操作,也就是连接8066这个端口。
一、创建数据源
1.创建数据源
添加读写的数据源 /*+ mycat:createDataSource{ "dbType":"mysql", "idleTimeout":60000, "initSqls":[], "initSqlsGetConnection":true, "instanceType":"READ_WRITE", "maxCon":1000, "maxConnectTimeout":3000, "maxRetryCount":5, "minCon":1, "name":"m1", "password":"123456", "type":"JDBC", "url":"jdbc:mysql://127.0.0.1:3307/db1?useUnicode=true&serverTimezone=UTC&characterEncoding=UTF-8", "user":"root", "weight":0 } */; |
添加读的数据源 /*+ mycat:createDataSource{ "dbType":"mysql", "idleTimeout":60000, "initSqls":[], "initSqlsGetConnection":true, "instanceType":"READ", "maxCon":1000, "maxConnectTimeout":3000, "maxRetryCount":5, "minCon":1, "name":"m1s1", "password":"123456", "type":"JDBC", "url":"jdbc:mysql://127.0.0.1:3308/db1?useUnicode=true&serverTimezone=UTC&characterEncoding=UTF-8", "user":"root", "weight":0 } */; |
2.查询数据源
/*+ mycat:showDataSources{} */ |
3.创建集群
/*! mycat:createCluster{ "clusterType":"MASTER_SLAVE", "heartbeat":{ "heartbeatTimeout":1000, "maxRetry":3, "minSwitchTimeInterval":300, "slaveThreshold":0 }, "masters":[ "m1" ], "maxCon":2000, "name":"prototype", "readBalanceType":"BALANCE_ALL", "replicas":[ "m1s1" ], "switchType":"SWITCH" } */; |
4.查询集群
/*+ mycat:showClusters{} */ |
5.创建逻辑库
CREATE DATABASE db1 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci; |
6.修改逻辑库的数据源
修改conf/schemas/db1.schema.json vim /data/mycat/conf/schemas/db1.schema.json 在里面添加 "targetName":"prototype", |
7.查看集群的配置文件
我们查看集群配置的结果,发现文件里面自动帮我们添加了。
二、测试读写分离是否成功(在MyCAT里面测试)
重启mycat
在MyCAT里面创建一个sys_user表:
CREATE TABLE SYS_USER( ID BIGINT PRIMARY KEY, USERNAME VARCHAR(200) NOT NULL, ADDRESS VARCHAR(500) ) |
2)通过注释生成物理库和物理表:
如果物理表不存在,在 MyCAT2 能正常启动的情况下,根据当前配置自动创建分片表,全局表和物理表:
/*+ mycat:repairPhysicalTable{} */; |
3)查看后端物理库:发现物理库和物理表都生成了。
4)在MyCAT里面向sys_user表添加一条数据:
INSERT INTO SYS_USER(ID,USERNAME,ADDRESS) VALUES(1,"XIAOMING","WUHAN"); |
5)修改MySQL里面的让数据不一样:
6)在MyCAT里面查询数据,会发现每次查询的结果不一样: