gitsilence 的个人博客

分库分表工具

学习地址：https://www.yuque.com/ccazhw/ml3nkf

安装程序包：http://dl.mycat.org.cn/2.0/install-template/mycat2-install-template-1.21.zip

Jar包：http://dl.mycat.org.cn/2.0/1.21-release/mycat2-1.21-release-jar-with-dependencies.jar

解压安装程序包，把jar包丢到 lib 目录下

配置数据源

conf/datasources 目录下

原型库

1、prototypeDs.datasource.json

{
"dbType":"mysql",
"idleTimeout":60000,
"initSqls":[],
"initSqlsGetConnection":true,
"instanceType":"READ_WRITE",
"maxCon":1000,
"maxConnectTimeout":30000,
"maxRetryCount":5,
"minCon":1,
"name":"prototypeDs",
"password":"123456",
"type":"JDBC",
"url":"jdbc:mysql://localhost:3306/test?useUnicode=true&serverTimezone=Asia/Shanghai&characterEncoding=UTF-8",
"user":"root",
"weight":0
}

2、node_0.datasource.json

3、node_1.datasource.json

集群配置

conf/clusters 目录下

集群名.cluster.json

例如：

prototype.cluster.json

{
	"clusterType":"MASTER_SLAVE",
	"heartbeat":{
		"heartbeatTimeout":1000,
		"maxRetry":3,
		"minSwitchTimeInterval":300,
		"slaveThreshold":0
	},
	"masters":[
		"prototypeDs"
	],
	"maxCon":200,
	"name":"prototype",
	"readBalanceType":"BALANCE_ALL",
	"switchType":"SWITCH"
}

conf/schema 目录放的表的 ddl

mycat 启动命令

bin/mycat start

建表语句

• 第一种

  create table test (
      id int primary key, 
      name varchar(100)
  ) ENGINE = INNODB CHARSET = utf8mb4 dbpartition BY MOD_HASH ( id ) dbpartitions 1 tbpartition BY MOD_HASH ( id ) tbpartitions 3;
  

根据 id hash 之后根据设置数量取余

例如库名为 my_db

dbpartitions 3

tbpartitions 3

库被分成 my_db_01、my_db_02、my_db_03 （待验证）

每个库有3账号表，为test_01、test_02、test_03 (待验证)

• 第二种

  // 分片表（其他表更换建表语句）
   /*+ mycat:createTable{
    "schemaName":"aite",
    "shardingTable":{
  "createTableSQL": "CREATE TABLE `test` (
  	id int PRIMARY KEY,
  	name VARCHAR(100)
  ) ENGINE = InnoDB CHARSET = utf8mb4;",
  "function":{
          "properties":{
            "dbNum":3,
            "mappingFormat":"node_${dbIndex}/my_db_${dbIndex}/test_${index}",
            "tableNum":1,
            "tableMethod":"mod_hash(id)",
            "storeNum":1,
            "dbMethod":"mod_hash(id)"
        }
      },
      "partition":{
  		}
    },
    "tableName":"test"
  } */;
  

可以直接丢在 navicat 中执行。

查看表 test 分布在哪几个节点, 哪几个 db

/*+ mycat:showTopology{
	"schemaName":"my_db",
	"tableName":"test"
} */;

新建数据源

/*+ mycat:createDataSource{
"name":"node_0",
"url":"jdbc:mysql://127.0.0.1:3309/test?useSSL=false&characterEncoding=UTF-8&useJDBCCompliantTimezoneShift=true",
"user":"root",
"password":"123456"
} */;

查看数据源列表

/*+ mycat:showDataSources{} */;