mycat2.0教程

一、安装部署

1.1、文件下载（要安装jdk1.8环境才能启动mycat）

wget http://dl.mycat.org.cn/2.0/install-template/mycat2-install-template-1.21.zip
wget http://dl.mycat.org.cn/2.0/1.21-release/mycat2-1.21-release-jar-with-dependencies.jar

1.2 安装Mycat

#程序安装目录为/data，解压并移到到/data目录
cd /data/tools
unzip mycat2-install-template-1.21.zip
mv mycat ../
把bin目录的文件加执行权限
cd /data/mycat/bin
chmod +x *
把所需的jar复制到mycat/lib目录
cd /data/mycat/lib/
cp /data/tools/mycat2-1.21-release-jar-with-dependencies.jar ./

1.3 修改数据源

注意只修改红色字体内容
cd /data/mycat/conf/datasources
把mycat带的数据源配置正确
vim 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://localhost:3306/test?useUnicode=true&serverTimezone=Asia/Shanghai&characterEncoding=UTF-8",
"user":"root",
"weight":0
}

1.4 mycat启动

 

 二、配置文件详解(重置/*+ mycat:resetConfig{} */;）（重新加载配置/*+mycat:loadConfigFromFile{} */）

 

2.1 服务（server）

vim /data/mycat/conf/server.json

 

2.2 用户（user)

2.2.1mycat命令方式创建用户

 

/*+ mycat:createUser{
    "username":"mycat",
    "password":"123456",
    "ip":NULL,
    "transactionType":"xa"
} */;

 

2.2.2 删除用户　

/*+ mycat:dropUser{
    "username":"mycat",
} */;

2.2.3 查询用户信息　

/*+ mycat:dropUser{
    "username":"mycat",
} */;　　　

 

 2.3 数据源（datasource)

{
	"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://localhost:3306/test?useUnicode=true&serverTimezone=Asia/Shanghai&characterEncoding=UTF-8",
	"user":"root",
	"weight":0
}

2.3.1 创建数据源

/*+ mycat:createDataSource{
"name":"dr0",
"url":"jdbc:mysql://192.168.0.160:3306/?useSSL=false&characterEncoding=UTF-8&useJDBCCompliantTimezoneShift=true",
"user":"root",
"password":"123456"
} */;

2.3.2 删除数据源　

/*+ mycat:dropDataSource{
"name":"dr0",
"url":"jdbc:mysql://10.0.9.32:3306/?useSSL=false&characterEncoding=UTF-8&useJDBCCompliantTimezoneShift=true",
"user":"root",
"password":""
} */;

2.3.3 查看数据源信息　

/*+ mycat:showDataSources{} */;

 

2.4 集群（cluster）

{
    "clusterType":"MASTER_SLAVE",
    "heartbeat":{
        "heartbeatTimeout":1000,
        "maxRetry":3,
        "minSwitchTimeInterval":300,
        "slaveThreshold":0
    },
    "masters":[
        "prototypeDs"
    ],
    "maxCon":200,
    "name":"prototype",
    "readBalanceType":"BALANCE_ALL",
    "switchType":"SWITCH"
}

2.4.1 创建和查看集群

/*! mycat:createCluster{"name":"prototype","masters":["prototypeDs"],"replicas":["dr0"]} */;
 
 
/*+ mycat:showClusters{} */;　

　　

2.5 逻辑表（schema)

 #指定db2逻辑库默认的targetName, mycat会自动加载db2下已经有的物理表或者视图作为单表.

/*+ mycat:createSchema{
  "customTables":{},
  "globalTables":{},
  "normalTables":{},
  "schemaName":"db2",
  "shardingTables":{},
  "targetName":"prototype"
} */;

三、myat读写分离（具体再这）

 https://blog.csdn.net/qq_34988304/article/details/134425977

 

四、mycat分库分表

 

4.1 全局表（将数据写入所以分片）

 

4.2 分库分表　

 

 4.3 ER表的分库分表

 

 

 

 

五、分库分表算法

5.1 取模hash

 

create table travelrecord (
 ....
) ENGINE=InnoDB DEFAULT CHARSET=utf8 
dbpartition by MOD_HASH (id) dbpartitions 6
tbpartition by MOD_HASH (id) tbpartitions 6;

5.2 范围hash　　

[数据分片]HASH型分片算法-RANGE_HASH

RANGE_HASH

RANGE_HASH(字段1, 字段2, 截取开始下标)

仅支持数值类型,字符串类型

当时字符串类型时候,第三个参数生效

计算时候优先选择第一个字段,找不到选择第二个字段

如果是字符串则根据下标截取其后部分字符串,然后该字符串hash成数值

根据数值按分片数取余

要求截取下标不能少于实际值的长度

两个字段的数值类型要求一致

create table travelrecord(
...
)ENGINE=InnoDB DEFAULT CHARSET=utf8 
dbpartition by RANGE_HASH(id,user_id,3) dbpartitions 3
tbpartition by RANGE_HASH(id,user_id,3) tbpartitions 3;

5.3 UNI_HASH字符串

HASH型分片算法-UNI_HASH

UNI_HASH

如果分片值是字符串则先对字符串进行hash转换为数值类型

分库键和分表键是同键

分库下标=分片值%分库数量

分表下标=(分片值%分库数量)*分表数量+(分片值/分库数量)%分表数量

分库键和分表键是不同键

分表下标= 分片值%分表数量

分库下标=分片值%分库数量

create table travelrecord (
 ....
) ENGINE=InnoDB DEFAULT CHARSET=utf8 
dbpartition by UNI_HASH (id) dbpartitions 6
tbpartition by UNI_HASH (id) tbpartitions 6;

5.4 YYYYDD

HASH型分片算法-YYYYDD

YYYYDD

仅用于分库

DD是一年之中的天数

(YYYY*366+DD)%分库数

create table travelrecord (
 ....
) ENGINE=InnoDB DEFAULT CHARSET=utf8 
dbpartition by YYYYDD(xxx) dbpartitions 8
tbpartition by xxx(xxx) tbpartitions 12;

5.5 各种类型算法对比