#创建数据源
#创建dbw写库,指向集群中的master服务
/*+ mycat:createDataSource{"name":"dbW","url":"jdbc:mysql://172.17.0.5:3306/mysql?useSSL=false&characterEncoding=UTF-8&useJDBCCompliantTimezoneShift=true","user":"root","password":"123456"} */;
#创建dbR读库,指向集群中的slave服务。如果有多个从库,依次继续创建
/*+ mycat:createDataSource{"name":"dbR","url":"jdbc:mysql://172.17.0.6:3306/mysql?useSSL=false&characterEncoding=UTF-8&useJDBCCompliantTimezoneShift=true","user":"root","password":"123456"} */;
#查询配置数据库结果
/*+ mycat:showDataSources{} */;
#更新集群信息
/*! mycat:createCluster{"name":"WRSplitCluster","masters":["dbW"],"replicas":["dbR"]} */;
#查看配置集群信息
/*+ mycat:showClusters{} */;
#1、在mycat2的服务中声明一个逻辑库
create database wrdb;
#2、在mycat2的部署目录下,找到对应的配置文件,conf/schema/wrdb.schema.json。在其中增加targetName:WRSplitCluster属性。指向真实的集群。{"customTables":{},"globalTables":{},"normalProcedures":{},"normalTables":{},schemaName":"wrdb","targetName":"WRSplitCluster","shardingTables":{},"views":{}}
#3、手动修改配置文件之后,需要重启MyCat2服务,让配置文件生效。
#单表创建语句(其他表更换建表语句)
/*+ mycat:createTable{
"normalTable":{
"createTableSQL":"CREATE TABLE `migrations` (
`version` varchar(255) NOT NULL,
PRIMARY KEY (`version`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8",
"locality":{
"schemaName":"wrdb", //物理库
"tableName":"migrations", //物理表
"targetName":"WRSplitCluster" //集群,单表默认在prototype
}
},
"schemaName":"wrdb",//逻辑库
"tableName":"migrations" //逻辑表
} */;
#指定数据库分片表(其他表更换建表语句)
/*+ mycat:createTable{
"schemaName":"wrdb",
"shardingTable":{
"createTableSQL":"CREATE TABLE `travelrecord` (
`id` bigint NOT NULL AUTO_INCREMENT,
`user_id` varchar(100) DEFAULT NULL,
`traveldate` date DEFAULT NULL,
`fee` decimal(10,0) DEFAULT NULL,
`days` int DEFAULT NULL,
`blob` longblob,
PRIMARY KEY (`id`),
KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 DBPARTITION BY MOD_HASH(id) DBPARTITIONS 1 TBPARTITION BY MOD_HASH(id) TBPARTITIONS 4",
"function":{
"properties":{
"dbNum":1,
"mappingFormat":"WRSplitCluster/wrdb/travelrecord_${index}",
"tableNum":4,
"tableMethod":"mod_hash(id)",
"storeNum":1,
"dbMethod":"mod_hash(id)"
}
},
"partition":{
}
},
"tableName":"travelrecord"
} */;
分片表(其他表更换建表语句)
/*+ mycat:createTable{
"schemaName":"my_db",
"shardingTable":{
"createTableSQL":"CREATE TABLE `travelrecord` (
`id` bigint NOT NULL AUTO_INCREMENT,
`user_id` varchar(100) DEFAULT NULL,
`traveldate` date DEFAULT NULL,
`fee` decimal(10,0) DEFAULT NULL,
`days` int DEFAULT NULL,
`blob` longblob,
PRIMARY KEY (`id`),
KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 DBPARTITION BY MOD_HASH(id) DBPARTITIONS 1 TBPARTITION BY MOD_HASH(id) TBPARTITIONS 4",
"function":{
"properties":{
"dbNum":1,
"mappingFormat":"c${targetIndex}/my_db_${dbIndex}/travelrecord_${index}",
"tableNum":4,
"tableMethod":"mod_hash(id)",
"storeNum":1,
"dbMethod":"mod_hash(id)"
}
},
"partition":{
}
},
"tableName":"travelrecord"
} */;