MYCAT2搭建

资料参考

mycat2权威指南 地址：Mycat2权威指南 · 语雀

Mycat2中文教程 地址：Mycat2 中文教程_w3cschool

安装

cd   /usr/local
# 下载tar包壳
wget   http://dl.mycat.org.cn/2.0/install-template/mycat2-install-template-1.20.zip
# 下载运行jar包，用http://dl.mycat.org.cn/2.0/   找到最新的jar包
wget   http://dl.mycat.org.cn/2.0/1.22-release/mycat2-1.22-release-jar-with-dependencies.jar
 
# 解压tar包壳（yum install -y unzip zip）
unzip   mycat2-install-template-1.20.zip
# 将运行jar包放到tar包壳的 lib 目录下
mv   mycat2-1.22-release-jar-with-dependencies.jar mycat/lib/
# 修改tar包壳bin/ 目录下所有文件的权限
chmod   777 mycat/bin/*

配置root用户权限

GRANT   ALL PRIVILEGES ON *.* TO 'root'@'%';
FLUSH   PRIVILEGES;

修改数据源

{
"dbType":   "mysql",
"idleTimeout":   60000,
"initSqls":   [],
"initSqlsGetConnection":   true,
"instanceType":   "READ_WRITE",
"maxCon":   1000,
"maxConnectTimeout":   3000,
"maxRetryCount":   5,
"minCon":   10,
"name":   "db0",
"password":   "<your_mysqldb_password>",
"type":   "JDBC",
"url":   "jdbc:mysql://10.0.27.38:3306?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&useSSL=false",
"user":   "root",
"weight":   0,
"queryTimeout":5000
}

启动

cd mycat/bin
./mycat   start
./mycat   status
./mycat   start 启动
./mycat   stop 停止
./mycat   console 前台运行
./mycat   install 添加到系统自动启动（暂未实现）
./mycat   remove 取消随系统自动启动（暂未实现）
./mycat   restart 重启服务
./mycat   pause 暂停
./mycat   status 查看启动状态

集群设置

/*+ mycat:createDataSource{
"name":"dw0",
"url":"jdbc:mysql://10.0.27.38:3306",
"user":"root",
"password":"123456"
} */;
/*+ mycat:createDataSource{
"name":"dr0",
"url":"jdbc:mysql://10.0.27.38:3306",
"user":"root",
"password":"123456"
} */;
/*! mycat:createCluster{"name":"c0","masters":["dw0"],"replicas":["dr0"]} */;

分表·

mycat2在mycat逻辑库里进行建表，会自动把建表语句写入schema配置文件，不需要自己去配置

create   database insigma_road_detection_v5;
 
-- 创建分表，DBPARTITION指分库数，TBPARTITION指分表数，MOD_HASH(area_code)指area_code字段按照hash方式分表
CREATE   TABLE insigma_road_detection_v5.`detection_gps_info`  (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `dataset_id` varchar(20) CHARACTER SET   utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT 'id',
  `assistant_task_id` varchar(32) CHARACTER   SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '任务id',
  `box_id` varchar(16) CHARACTER SET utf8mb4   COLLATE utf8mb4_general_ci NOT NULL COMMENT '编号',
  `collection_time` datetime(0) NOT NULL   COMMENT '定位采集时间，格式：yyyy-MM-dd HH:mm:ss',
  `latitude_amap` varchar(16) CHARACTER SET   utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '高德坐标维度',
  `longitude_amap` varchar(16) CHARACTER SET   utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '高德坐标经度',
  `latitude_84` varchar(16) CHARACTER SET   utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '维度',
  `longitude_84` varchar(16) CHARACTER SET   utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '经度',
  `altitude` varchar(16) CHARACTER SET   utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '海拔',
  `bearing` varchar(16) CHARACTER SET utf8mb4   COLLATE utf8mb4_general_ci NOT NULL COMMENT '方向角',
  `speed` varchar(16) CHARACTER SET utf8mb4   COLLATE utf8mb4_general_ci NOT NULL COMMENT '速度',
  `program_padding` char(1) CHARACTER SET   utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '0' COMMENT '是否',
  `gmt_created` datetime(0) NULL DEFAULT   CURRENT_TIMESTAMP(0) COMMENT '创建时间',
  `task_type` int(2) NULL DEFAULT 1 COMMENT '数据类型',
  `area_code` varchar(10) CHARACTER SET   utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '区域编码',
  `gmt_modified` datetime(0) NULL DEFAULT   CURRENT_TIMESTAMP(0) ON UPDATE CURRENT_TIMESTAMP(0) COMMENT '修改时间',
  `is_deleted` tinyint(4) NULL DEFAULT 0   COMMENT '逻辑删除标志位',
  PRIMARY KEY (`ID`) USING BTREE,
  UNIQUE INDEX   `UK_BOX_ID_COLLECTION_TIME_PROGRAM_PADDING`(`box_id`, `collection_time`,   `program_padding`, `task_type`) USING BTREE,
  INDEX `IX_DATASET_ID`(`dataset_id`,   `task_type`) USING BTREE,
  INDEX   `IX_ASSISTANT_TASK_ID`(`assistant_task_id`) USING BTREE
)   ENGINE = InnoDB DEFAULT CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci   COMMENT = 'gps表' ROW_FORMAT = Dynamic 
DBPARTITION   BY MOD_HASH(area_code) DBPARTITIONS 1
TBPARTITION   BY MOD_HASH(area_code) TBPARTITIONS 10;

通过查看物理库，发现已经创建好分表了

 默认是使用雪花算法作为全局序列，也可以通过设置做到自增，不过要id作为分表条件才能实现insert id为null自增