mycat2 讀寫分離配置（詳解）
mycat2相對mycat1來說升級還挺多的，但是全網資料太少了，這里盡可能詳細的將讀寫分離說清楚，目前這套配置已經在我司生產環(huán)境應用，日UV6W左右，暫時沒發(fā)現問題。
一、 下載和安裝
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
#配置環(huán)境變量
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環(huán)境變量
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 #使環(huán)境變量生效
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 文件夾里邊可執(zhí)行權限：二、 用戶配置
這里的用戶名用來登錄mycat和mysql沒有關系，mycat根據后邊datasource里邊的用戶信息來登錄mysql.
cd /data/myca /conf/users
vim root.user.json
{
"dialect":"mysql",
"ip":null,
"password":"XXXXXXX",
"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
獲取集群中允許讀寫的數據源,但允許讀的數據源優(yōu)先
BALANCE_NONE
獲取集群中允許寫數據源,即主節(jié)點中選擇
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
# 查看狀態(tài)
./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{} */刷新。
每篇隨筆都來自工作經驗并親自試驗，確保每位讀者能夠接觸到正確的知識。部分隨筆多平臺發(fā)布，謝絕轉載！！！
浙公網安備 33010602011771號