mycat2相对mycat1来说升级还挺多的，但是全网资料太少了，这里尽可能详细的将读写分离说清楚，目前这套配置已经在我司生产环境引用，暂时没发现问题。
一、 下载和安装
1.1下载
下需要两个包（两个包的版本要保持一致）：
1、 主程序安装包
mycat2-install-template-1.21.zip
2、 依赖包
mycat2-1.21-release-jar-with-dependencies.jar
2.2安装
java -version #检查是否安装
yum -y list java* # 查看JDK软件包列表
yum install java-1.8.0-openjdk java-1.8.0-openjdk-devel#安装JDK软件包
java -version
#配置环境变量
which java #查看JDK的安装路径显示：/usr/bin/java
ls -lrt /usr/bin/java
显示：lrwxrwxrwx. 1 root root 22 Aug 17 15:12 /usr/bin/java -> /etc/alternatives/java
ls -lrt /etc/alternatives/java
显示：
lrwxrwxrwx. 1 root root 46 Aug 17 15:12 /etc/alternatives/java -> /usr/lib/jvm/java-1.8.0-openjdk-1.8.0.171-8.b10.el7_5.x86_64/bin/java
#配置JDK环境变量
export JAVA_HOME=/usr/lib/jvm/java-1.8.0-openjdk-1.8.0.171-8.b10.el7_5.x86_64
CLASSPATH=.:$JAVA_HOME/jre/lib/rt.jar:$JAVA_HOME/lib/dt.jar:$JAVA_HOME/lib/tools.jar
export PATH=$PATH:$JAVA_HOME/bin
source /etc/profile #使环境变量生效
cp mycat2-install-template-1.21.zip /data #拷贝主程序到安装目录/data
cd /data
unzip mycat2-install-template-1.21.zip #解压主程序, 会自动生成mycat文件夹。
cp mycat2-1.21-release-jar-with-dependencies.jar /data/mycat/lib #将依赖包拷贝至 /data/mycat/lib下边
chmod +x /data/mycata #授予/data/mycat/bin 文件夹里边可执行权限：
二、 用户配置
这里的用户名用来登录mycat和mysql没有关系，mycat根据后边datasource里边的用户信息来登录mysql.
cd /data/myca /conf/users
vim root.user.json
{
"dialect":"mysql",
"ip":null,
"password":"VMC#V4sMVMC#V4sM",
"transactionType":"xa",
"username":"root"
}
三、 读写分离配置
2.1prototypeDs原型库配置
cd /data/mycat/mycat/conf/datasources
vim prototypeDs.datasource.json
{
// 数据库类型
"dbType":"mysql",
"idleTimeout":60000,
"initSqls":[],
"initSqlsGetConnection":true,
// 数据库读写类型：READ、WRITE、READ_WRITE。原型库对数据库需要是可读可写的
"instanceType":"READ_WRITE",
"maxCon":1000,
"maxConnectTimeout":3000,
"maxRetryCount":5,
"minCon":1,
// 数据源名称，这里不要修改
"name":"prototypeDs",
// 数据库密码
"password":"123456",
"type":"JDBC",
// 数据库连接
"url":"jdbc:mysql://192.168.10.80:3306/mycat?useUnicode=true&serverTimezone=Asia/Shanghai&characterEncoding=UTF-8",
// 数据库用户
"user":"root",
"weight":0
}
#其中mycat为原型库库名，库名可以自己定义，用来预存mycat需要用到的一些信息。mycat会自行建立，如果建立失败，可以手动先在数据库建立后再启动mycat2，推荐自行建立。
#编码推荐utf8mb4_0900_ai_ci也可以utf8mb4_general_ci
手动建立sql:
CREATE DATABASE IF NOT EXISTS `mycat`;
USE `mycat`;
DROP TABLE IF EXISTS `analyze_table`;
CREATE TABLE `analyze_table` (
`table_rows` bigint(20) NOT NULL,
`name` varchar(64) NOT NULL
) ENGINE = InnoDB CHARSET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci;
DROP TABLE IF EXISTS `config`;
CREATE TABLE `config` (
`key` varchar(22) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
`value` longtext,
`version` bigint(20) DEFAULT NULL,
`secondKey` longtext,
`deleted` tinyint(1) DEFAULT '0'
) ENGINE = InnoDB CHARSET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci;
DROP TABLE IF EXISTS `replica_log`;
CREATE TABLE `replica_log` (
`name` varchar(22) DEFAULT NULL,
`dsNames` text,
`time` datetime DEFAULT NULL
) ENGINE = InnoDB CHARSET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci;
DROP TABLE IF EXISTS `spm_baseline`;
CREATE TABLE `spm_baseline` (
`id` bigint(22) NOT NULL AUTO_INCREMENT,
`fix_plan_id` bigint(22) DEFAULT NULL,
`constraint` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
`extra_constraint` longtext,
PRIMARY KEY (`id`),
UNIQUE KEY `constraint_index` (`constraint`(22)),
KEY `id` (`id`)
) ENGINE = InnoDB CHARSET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci;
DROP TABLE IF EXISTS `spm_plan`;
CREATE TABLE `spm_plan` (
`id` bigint(22) NOT NULL AUTO_INCREMENT,
`sql` longtext,
`rel` longtext,
`baseline_id` bigint(22) DEFAULT NULL,
KEY `id` (`id`)
) ENGINE = InnoDB CHARSET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci;
DROP TABLE IF EXISTS `sql_log`;
CREATE TABLE `sql_log` (
`instanceId` bigint(20) DEFAULT NULL,
`user` varchar(64) DEFAULT NULL,
`connectionId` bigint(20) DEFAULT NULL,
`ip` varchar(22) DEFAULT NULL,
`port` bigint(20) DEFAULT NULL,
`traceId` varchar(22) NOT NULL,
`hash` varchar(22) DEFAULT NULL,
`sqlType` varchar(22) DEFAULT NULL,
`sql` longtext,
`transactionId` varchar(22) DEFAULT NULL,
`sqlTime` bigint(20) DEFAULT NULL,
`responseTime` datetime DEFAULT NULL,
`affectRow` int(11) DEFAULT NULL,
`result` tinyint(1) DEFAULT NULL,
`externalMessage` tinytext,
PRIMARY KEY (`traceId`)
) ENGINE = InnoDB CHARSET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci;
DROP TABLE IF EXISTS `variable`;
CREATE TABLE `variable` (
`name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
`value` varchar(22) DEFAULT NULL,
PRIMARY KEY (`name`)
) ENGINE = InnoDB CHARSET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci;
DROP TABLE IF EXISTS `xa_log`;
CREATE TABLE `xa_log` (
`xid` bigint(20) NOT NULL,
PRIMARY KEY (`xid`)
) ENGINE = InnoDB CHARSET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci;
2.2datasource数据源配置（两主一从）
1.2.1数据源164（读写）服务器配置
复制一下prototypeDs.datasource.json
#/information_return 为需要被访问的数据库名称。
#如果instanceType是READ_WRITE的类型,被设置到replicas,对该集群是READ的。但是被设置到masters则为READ_WRITE
{
"dbType":"mysql",
"idleTimeout":60000,
"initSqls":[],
"initSqlsGetConnection":true,
"instanceType":"READ_WRITE",
"maxCon":1000,
"maxConnectTimeout":3000,
"maxRetryCount":5,
"minCon":1,
//name字段在后边集群中会用到
"name":"164",
"password":"Zht@2650896",
"type":"JDBC",
"url":"jdbc:mysql://19.50.67.164:3306/information_return?useUnicode=true&serverTimezone=Asia/Shanghai&characterEncoding=UTF-8",
"user":"xinxishenbao",
"weight":0
}
1.2.2数据源174（读写）服务器配置
{
"dbType":"mysql",
"idleTimeout":60000,
"initSqls":[],
"initSqlsGetConnection":true,
"instanceType":"READ_WRITE",
"maxCon":1000,
"maxConnectTimeout":3000,
"maxRetryCount":5,
"minCon":1,
"name":"174",
"password":"Zht@2650896",
"type":"JDBC",
"url":"jdbc:mysql://19.50.67.174:3306/information_return?useUnicode=true&serverTimezone=Asia/Shanghai&characterEncoding=UTF-8",
"user":"xinxishenbao",
"weight":0
}
1.2.3数据源169（只读）服务器配置
{
"dbType":"mysql",
"idleTimeout":60000,
"initSqls":[],
"initSqlsGetConnection":true,
"instanceType":"READ",
"maxCon":1000,
"maxConnectTimeout":3000,
"maxRetryCount":5,
"minCon":1,
"name":"169",
"password":"Zht@2650896",
"type":"JDBC",
"url":"jdbc:mysql://19.50.67.169:3306/information_return?useUnicode=true&serverTimezone=Asia/Shanghai&characterEncoding=UTF-8",
"user":"xinxishenbao",
"weight":0
}
2.3cluster集群配置
复制prototype.cluster.json
cd /data/ mycat/conf/clusters
{
"clusterType":"MASTER_SLAVE",
"heartbeat":{
"heartbeatTimeout":1000,
"maxRetry":3,
"minSwitchTimeInterval":300,
"slaveThreshold":0
},
"masters":[
"164",
"174"
],
"replicas":[
"169"
],
"maxCon":200,
//name在逻辑表映射的时候会用到
"name":"xinxishenbao",
"readBalanceType":"BALANCE_READ_WRITE",
//由于从数据库是只读的，所以不切换主从。
"switchType":"NOT_SWITCH"
}
readBalanceType可选值:
BALANCE_ALL(默认值)
获取集群中所有数据源
BALANCE_ALL_READ
获取集群中允许读的数据源
BALANCE_READ_WRITE
获取集群中允许读写的数据源,但允许读的数据源优先
BALANCE_NONE
获取集群中允许写数据源,即主节点中选择
2.4 schema 逻辑库映射
cd /data/mycat/mycat/conf/schemas
vim xinxishenbo.schema.json
{
"customTables": {},
"globalTables": {},
"normalTables": {},
//逻辑库名，也是mysql中对应的物理数据库名
"schemaName": "information_return",
"shardingTables": {},
//对应cluster集群中的name字段。如果不做集群则对应DataSource中的name字段。
"targetName": "xinxishenbao"
}
如果需要映射多个库，则配置多个数据源，建立多个XX.schema.json进行映射
四、 mycat启动命令
./bin/mycat start
# 查看状态
./bin/mycat status
# 停止
./bin/mycat stop
# 暂停
./bin/mycat pause
# 重启
./bin/mycat restart
# 前台运行
./bin/mycat console
# 查看日志文件
tail -f /home/papis/mycat2/mycat/logs/wrapper.log
五、 问题
5.1 读写分离索引不显示
部分mycat虚拟表不全，表信息显示不全面，可能出现视图显示在表里边，或者索引无法显示。可配置information_schema.schema.json添加如下内容，重复部分进行覆盖即可。（框架里有用到某些系统表）
{
"customTables": {},
"globalTables": {},
"normalTables": {
"statistics": {
"locality": {
"schemaName": "information_schema",
"tableName": "statistics",
"targetName": "prototype"
}
},
"referential_constraints": {
"locality": {
"schemaName": "information_schema",
"tableName": "referential_constraints",
"targetName": "prototype"
}
},
"key_column_usage": {
"locality": {
"schemaName": "information_schema",
"tableName": "key_column_usage",
"targetName": "prototype"
}
},
"table_constraints": {
"locality": {
"schemaName": "information_schema",
"tableName": "table_constraints",
"targetName": "prototype"
}
},
"columns": {
"locality": {
"schemaName": "information_schema",
"tableName": "columns",
"targetName": "prototype"
}
}
},
"schemaName": "information_schema",
"shardingTables": {},
"views": {}
}
5.2 视图被当做表处理的问题
mycat2里面视图会被当做逻辑表显示，但是不影响物理库中的视图和表结构。
5.3 新建表无法自动刷新的问题
mycat2加载后如果数据库有结构变动（比如新建表），无法自动更新，需要手动重启mycat，或者在命令行中输入/*+mycat:loadConfigFromFile{} */刷新。