mycat注解命令行学习小记

point-break

已于 2023-10-10 14:40:11 修改

阅读量215

点赞数

CC 4.0 BY-SA版权

文章标签： linux mysql

于 2023-10-08 18:01:07 首次发布

本文链接：https://blog.youkuaiyun.com/a124550785/article/details/50165779

文章详细描述了如何在Mycat中设置数据源、创建读写库，以及如何配置逻辑库和分片表，以支持数据库的高可用性和性能优化。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

#创建数据源 
#创建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"
} */;