Mycat2搭建

下载安装文件

目前GW无法下载，本人从优快云花了30元买了下载链接

mycat2-1.20-jar-with-dependencies-2021-12-23.zip

mycat2-install-template-1.20.zip

安装

解压缩mycat2-install-template-1.20.zip

将mycat2-1.20-jar-with-dependencies-2021-12-23.zip解压到mycat\lib目录

安装Jdk

本人安装的open jdk 11.0.12 实际上是微软构建的jdk版本，实测兼容mycat2

配置映射

配置数据源（修改红色部分）

conf/datasources/prototypeDs.datasource.json

{

       "dbType":"mysql",

       "idleTimeout":60000,

       "initSqls":[],

       "initSqlsGetConnection":true,

       "instanceType":"READ_WRITE",

       "maxCon":1000,

       "maxConnectTimeout":3000,

       "maxRetryCount":5,

       "minCon":1,

       "name":"prototypeDs",

       "password":"123456",

       "type":"JDBC",

       "url":"jdbc:mysql://172.29.99.21:3307/mysql?useUnicode=true&serverTimezone=Asia/Shanghai&characterEncoding=UTF-8",

       "user":"root",

       "weight":0

}

注：mycat2需要一个mysql数据源，如果需要配置其他数据库，可以另外添加数据源

配置db1数据源

conf/datasources/db1.datasource.json

{

       "dbType":"mysql",

       "idleTimeout":60000,

       "initSqls":[],

       "initSqlsGetConnection":true,

       "instanceType":"READ_WRITE",

       "maxCon":1000,

       "maxConnectTimeout":3000,

       "maxRetryCount":5,

       "minCon":1,

       "name":"db1",

       "password":"123456",

       "type":"JDBC",

        "url":"jdbc:mysql://172.29.99.21:3306/testdb?useUnicode=true&serverTimezone=Asia/Shanghai&characterEncoding=UTF-8",

       "user":"root",

       "weight":0

}

配置db2数据源

conf/datasources/db2.datasource.json

{

       "dbType":"mysql",

       "idleTimeout":60000,

       "initSqls":[],

       "initSqlsGetConnection":true,

       "instanceType":"READ_WRITE",

       "maxCon":1000,

       "maxConnectTimeout":3000,

       "maxRetryCount":5,

       "minCon":1,

       "name":"db2",

       "password":"123456",

       "type":"JDBC",

        "url":"jdbc:mysql://172.29.99.21:3307/testdb?useUnicode=true&serverTimezone=Asia/Shanghai&characterEncoding=UTF-8",

       "user":"root",

       "weight":0

}

配置映射，可以不用添加表，客户端也能读取到

conf/schemas/testdb.schema.json

{

       "customTables":{},

       "globalTables":{},

       "normalTables":{},

       "schemaName":"db1",

       "shardingTables":{},

       "targetName":" prototype",

       "views":{}

}

配置mycat2 server （只需要增加红色部分）

conf/server.json

"server":{

    "bufferPool":{

    },

    "idleTimer":{

      "initialDelay":3,

      "period":60000,

      "timeUnit":"SECONDS"

    },

    "ip":"0.0.0.0",

    "mycatId":1,

    "port":8066,

    "serverVersion":"8.0.28-mycat-2.0",

    "reactorNumber":8,

    "tempDirectory":null,

    "timeWorkerPool":{

      "corePoolSize":0,

      "keepAliveTime":1,

      "maxPendingLimit":65535,

      "maxPoolSize":2,

      "taskTimeout":5,

      "timeUnit":"MINUTES"

},

配置登录账号 根据需要修改密码

conf/user/root.user.json

{

       "dialect":"mysql",

       "ip":null,

       "password":"123456",

       "transactionType":"xa",

       "username":"root"

}

需要授予mysql数据源xa权限

GRANT XA_RECOVER_ADMIN ON *.* TO 'root'@'%';

flush privileges;

修改加密方式mysql_native_password

alter user 'root'@'%' identified by 123456 password expire never;

alter user 'root'@'%' identified with mysql_native_password by 123456;

flush privileges;

启动mycat2

使用管理员权限打开cmd 进入mycat2\bin目录

安装服务  mycat install

启动服务  mycat start

停止服务  mycat stop

卸载服务  mycat remove

重启服务  mycat restart

至此，可以使用mysql客户端连接mycat了，访问端口8066。

推荐使用navicat或者SQL yog，可以看到mysql数据库，其他工具可能不能显示mysql数据库，但不影响使用

添加表

mycat2采用注解的方式添加配置

登录mysql客户端

/*+ mycat:createTable{

"schemaName":"testdb",

"shardingTable":{

"createTableSQL":"CREATE TABLE `t_student` (

`id` bigint NOT NULL AUTO_INCREMENT COMMENT 'id',

`name` varchar(150) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '姓名',

`age` int DEFAULT NULL COMMENT '年级',

`tenant_code` varchar(20) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '租户',

PRIMARY KEY (`id`),

KEY `student_01` (`name`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='学生表'; ",

"function":{

"clazz":"io.mycat.router.mycat1xfunction.PartitionByFileMap",

"properties":{

"defaultNode":"0",

"type":"String",

"columnName":"tenant_code"

},

"ranges":{

"KDY":"0",

"CY":"1"

}

},

"partition":{

"schemaNames":"testdb",

"tableNames":"t_student",

"targetNames":"db1,db2"

}

},

"tableName":"t_student"

} */;

我在配置过程中参考以下文档

友情链接：

Mycat2安装配置(窗外流星) (yuque.com)

Mycat2 中文教程_w3cschool

Mycat2: Mycat2是Mycat社区开发的一款分布式关系型数据库(中间件)。它支持分布式SQL查询，兼容MySQL通信协议，以Java生态支持多种后端数据库，通过数据分片提高数据查询处理能力 (gitee.com)