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”,负载均衡已生效