目录
mycat 的使用
/      

mycat 的使用

分库分表工具

学习地址: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{} */;

标题:mycat 的使用
作者:gitsilence
地址:http://blog.lacknb.cn/articles/2023/10/30/1698667459261.html