mycat2主从配置实现读写分离

mycat2主从配置实现读写分离

在https://blog.csdn.net/zhangxue_wei/article/details/130840504基础上继续搭建

1.创建mycat数据源，可以在navcat里直接执行
1.1读数据源m1

/*+ 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://192.168.139.128:3306/xuewei?useUnicode=true&serverTimezone=UTC&characterEncoding=UTF-8",
  "user":"root",
  "weight":0
} */;

1.2 写数据源m1s1

/*+ 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://192.168.139.130:3306/xuewei?useUnicode=true&serverTimezone=UTC&characterEncoding=UTF-8",
  "user":"root",
  "weight":0
} */;

1.3 创建完成查询创建情况
命令：/*+ mycat:showDataSources{} */

2.配置mycat集群
masters：读数据源，可以设置多个搭建双主双从
replicas：写数据源，可以设置多个搭建双主双从
name：集群名称prototype

/*! 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"
} */;

查看集群
/*+ mycat:showClusters{} */

3.在mycat里创建数据库xuewei和表t_demo,两台实体库也会自动创建了库和表

4.修改逻辑库的数据源为集群prototype，当我们创建好数据库xuewei，在路径/usr/local/mycat/conf/schemas下会自动创建一个配置文件xuewei.schema.json

添加行内容 “targetName”:“prototype”,

{
	"customTables":{},
	"globalTables":{},
	"normalProcedures":{},
	"normalTables":{
		"t_demo":{
			"createTableSQL":"CREATE TABLE `xuewei`.`t_demo` (\n\t`id` int NOT NULL AUTO_INCREMENT,\n\t`name` varchar(255) NULL,\n\tPRIMARY KEY (`id`)\n)",
			"locality":{
				"schemaName":"xuewei",
				"tableName":"t_demo",
				"targetName":"prototype"
			}
		}
	},
	"schemaName":"xuewei",
	"shardingTables":{},
	"targetName":"prototype",
	"views":{}
}

5、测试
mycat里添加一条数据

修改从机数据为lisi，从机不会复制到主机

然后进行多次访问，结果会出现不一样的数据

由此可见集群属性 “readBalanceType”:“BALANCE_ALL”,负载均衡已生效