当前位置: 首页 > 工具软件 > Mycat2 > 使用案例 >

mycat2 分库分表配置

欧阳鸿德
2023-12-01

1. 创建schema

mycat控制台输入

/*+ mycat:createSchema{
	"customTables":{},
	"globalTables":{},
	"normalTables":{},
	"schemaName":"test",
	"shardingTables":{},
	"targetName":"prototype"
} */;
schemaName:物理库的库名
targetName: 物理库所在的集群,默认应该将prototype集群内的MySQL连接 设置为物理库连接

创建成功后,mycat 会映射物理库的所有单表,可以检测是否成功

2.创建数据源

创建两组数据源

/*+ mycat:createDataSource{
"name":"r0",
"url":"jdbc:mysql://127.0.0.1:3306/mysql",
"user":"root",
"password":"xxxx"
} */;

/*+ mycat:createDataSource{
"name":"w0",
"url":"jdbc:mysql://127.0.0.1:3306/mysql",
"user":"root",
"password":"xxxx"
} */;


/*+ mycat:createDataSource{
"name":"r1",
"url":"jdbc:mysql://127.0.0.1:3307/mysql",
"user":"root",
"password":"xxxx"
} */;

/*+ mycat:createDataSource{
"name":"w1",
"url":"jdbc:mysql://127.0.0.1:3307/mysql",
"user":"root",
"password":"xxxx"
} */;

3.添加集群配置

集群c1 为3306的库 c2为3307的库

/*! mycat:createCluster{"name":"c0","masters":["w0"],"replicas":["r0"]} */;
/*! mycat:createCluster{"name":"c1","masters":["w1"],"replicas":["r1"]} */;

4.创建分库分表

执行成功之后,mycat2 会在 配置的数据源中自动创建库和表


/*+ mycat:createTable{
	"schemaName":"创建schema时的schemaName",
	"shardingTable":{
		"createTableSQL":"create table sharding  xxxxxxxxxxxxx  (建表语句)",
		"function":{
        	"properties":{
	          "dbNum":1,
	          "mappingFormat":"c${targetIndex}/数据库名_${dbIndex}/表名_${index}",
	          "tableNum":10,
	          "tableMethod":"mod_hash(id)",
	          "storeNum":1,
	          "dbMethod":"mod_hash(id)"
      		}
    	},
		"partition":{
			
		}
	},
	"tableName":"表名"
} */;
schemaName:创建schema时的schemaName
createTableSQL: mysql建表语句
dbNum: 分库数量
mappingFormat: 映射配置  c${targetIndex}  映射集群 
tableNum:分表数量
tableMethod:分表算法(分表字段)
dbMethod:分库算法(分库字段)
storeNum:等于dbNum,

至此顺利的话,就分库分表成功了

 类似资料: