MySQL分库分表与MyCAT

原创已于 2025-08-20 17:58:48 修改 · 836 阅读

4 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #数据库 #分库分表 #mycat #一主一从 #主从集群

于 2025-08-19 21:26:19 首次发布

MySQL数据库专栏收录该内容

17 篇文章

订阅专栏

十、MySQL分库分表与MyCAT

分库分表

（一）什么是分库分表？

1、分库：拆分数据库，当单台数据库服务存储容量不足时，或者并发量过高，单机资源不足（容量、内存、CPU不足）。

2、分表：拆分数据表，当单表数据量过大时，索引（B+树）查询效率会低，2千万数据左右。（高度为3层时，总记录数约为 1170（根指针）× 1170（中间节点指针）× 16（叶子记录）≈ 2190万条）

（二）怎么分库分表？

方法：

垂直分库：将原本一个库中的表拆分到不同的库中。

垂直分表：将原来的表按字段拆分成多个表。

水平分库：按照数据内容拆分，例如按年月拆分数据、按照数据范围拆分、取模运算拆分

水平分表：按照内容分表

水平分库分表策略

分库分表策略一般有几种，使用与不同的场景：

range范围

hash取模

range+hash取模混合

2.1range范围

range，即范围策略划分表。比如我们可以将表的主键，按照从0~1000万的划分为一个表，1000~2000万划分到另外一个表。如下图：

当然，有时候我们也可以按时间范围来划分，如不同年月的订单放到不同的表，它也是一种range的划分策略。

这种方案的优点：

这种方案有利于扩容，不需要数据迁移。假设数据量增加到5千万，我们只需要水平增加一张表就好啦，之前0~4000万的数据，不需要迁移。

缺点：

这种方案会有热点问题，因为订单id是一直在增大的，也就是说最近一段时间都是汇聚在一张表里面的。比如最近一个月的订单都在1000万~2000万之间，平时用户一般都查最近一个月的订单比较多，请求都打到order_1表啦，这就导致表的数据热点问题。

2.2hash取模

hash取模策略：指定的路由key（一般是user_id、订单id作为key）对分表总数进行取模，把数据分散到各个表中。

比如原始订单表信息，我们把它分成4张分表：

比如id=1，对4取模，就会得到1，就把它放到第1张表，即t_order_0;
id=3，对4取模，就会得到3，就把它放到第3张表，即t_order_2;

这种方案的优点：

hash取模的方式，不会存在明显的热点问题。

缺点：

如果一开始按照hash取模分成4个表了，未来某个时候，表数据量又到瓶颈了，需要扩容，这就比较棘手了。比如你从4张表，又扩容成8张表，那之前id=5的数据是在（5%4=1，即第一张表），现在应该放到（5%8=5，即第5张表），也就是说历史数据要做迁移了。

2.3range+hash取模混合

既然range存在热点数据问题，hash取模扩容迁移数据比较困难，我们可以综合两种方案一起嘛，取之之长，弃之之短。

比较简单的做法就是，在拆分库的时候，我们可以先用range范围方案，比如订单id在04000万的区间，划分为订单库1，id在4000万8000万的数据，划分到订单库2,将来要扩容时，id在8000万~1.2亿的数据，划分到订单库3。然后订单库内，再用hash取模的策略，把不同订单划分到不同的表。

（三）MyCAT 2

·使用MyCAT分布式集群中间件，可以实现分库、分表、读写分离、负载均衡、故障（屏蔽）转移。

·MyCAT支持MySQL网络协议，可以使用MySQL的客户端进行操作。MyCAT是一个传统的数据库表，支持标准的SQL语句进行数据的操作。

官网网址：http://www.mycat.org.cn/

·作用：1）能满足数据库数据大量存储；

2）提高了查询性能；

3）实现读写分离，分库分表。

·使用java语言开发，需要安装java运行环境（jdk）

MyCAT2的安装⭐⭐⭐

# 安装JDK1.8

CentOS7：yum -y install java-1.8.0-openjdk.x86_64

Ubuntu22.04：apt install openjdk-8-jdk-headless

# 测试安装，显示java版本是1.8.0则为安装成功

java -version

# 安装MyCAT2

# 创建/usr/local/src

mkdir -p /usr/local/src

# 进入/usr/local/src目录

cd /usr/local/src

# 三种下载路径（两个安装包）

# 官方下载路径(经测试,目前失效了)

# 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

# 第三方下载

# wget https://download.topunix.com/MySQL/Software-Cluster/Software-Mycat/Mycat2/mycat2-install-template-1.20.zip

# wget https://download.topunix.com/MySQL/Software-Cluster/Software-Mycat/Mycat2/mycat2-1.21-release-jar-with-dependencies.jar

# 局域网下载（192.168.56.200）

wget http://192.168.56.200/Software/mycat2-install-template-1.21.zip

wget http://192.168.56.200/Software/mycat2-1.21-release-jar-with-dependencies.jar

# 解压并移动到/usr/local目录下

# 下载解压软件，已经安装的跳过

yum -y install unzip或者apt install unzip

# 解压Mycat2

unzip mycat2-install-template-1.21.zip

mv mycat ../

# 修改权限

cd /usr/local/mycat/

chmod +x bin/*

# 把所需的jar复制到mycat/lib目录，mycat2-1.21-release-jar-with-dependencies.jar是MyCAT2依赖文件，缺失会导致MyCAT启动失败

cp /usr/local/src/mycat2-1.21-release-jar-with-dependencies.jar /usr/local/mycat/lib/

# 启动mysql并登录root，验证底层 MySQL 服务是否正常可用，然后退出

service mysqld start

mysql -uroot -proot123

\q或者exit

# 配置物理库地址

vim /usr/local/mycat/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": "root123",

"type": "JDBC",

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

"user": "root",

"weight": 0

}

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++

# 配置MyCAT和MySQL的环境变量

vim /etc/profile

在后面添加+++++++++++++++++++++++++++++++++++++++++++++++++

export MYCAT_PATH=/usr/local/mycat

export MYSQL_PATH=/usr/local/mysql

export PATH="$MYSQL_PATH/bin:$MYCAT_PATH/bin:$PATH"

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++

# ESC:wq退出文件:重新加载配置文件

source /etc/profile

# 启动mycat

cd /usr/local/mycat/bin

./mycat restart

# 查看logs/wrapper.log出现一串信息证明启动成功

cat /usr/local/mycat/logs/wrapper.log

# 连接MyCAT

MyCAT的默认用户是:root

初始密码是:123456

端口号是:8066

mysql -uroot -p123456 -P8066 -h127.0.0.1或者mycli -uroot -P8066 -h"127.0.0.1"

cd /usr/local/mycat/bin

./mycat start 启动

./mycat stop 停止

./mycat console 前台运行

./mycat install 添加到系统自动启动

./mycat remove 取消随系统自动启动

./mycat restart 重启

./mycat pause 暂停

./mycat status 查看启动状态

补充内容——配置文件常见格式：

（1）ini：xx.ini/xx.conf/xx.cnf/xx.repo

[mysqld]

base_dir=/usr/local/mysql

date_dir=/usr/local/mysql/data

...

（2）xml：标签语言，java项目，大数据。

Apache、TomCat

<?xml version="1.0" encoding="UTF-8"?>

<host>localhost</host>

<user>admin</user>

</credentials>

</database>

</config>

（3）json：JavaScript对象表示法。API 实现前后端数据交互。

MyCAT,

json

{

"database": {

"host": "localhost",

"port": 3306,

"credentials": {

"user": "admin",

"password": "123456"

"tables": ["users", "orders"]

}

（4）yaml：采用缩进表示层级关系，语法简洁，可读性强，支持复杂数据结构，如列表、字典等.

Doctor，KBS

# 数据库配置

database:

host: localhost

port: 3306

credentials:

user: admin

password: 123456

tables:

- users

- orders

终端提示符的颜色

MyCAT概念：

数据源：MyCAT用于连接后端的MySQL数据库，连接信息

·用户名、密码、IP

·datasources：设置数据源

# 创建数据源

/*+ mycat:createDataSource{

"dbType":"mysql",

"idleTimeout":60000,

"initSqls":[],

"initSqlsGetConnection":true,

"instanceType":"READ_WRITE",

"maxCon":1000,

"maxConnectTimeout":3000,

"maxRetryCount":5,

"minCon":1,

"name":"m1",

"password":"root123",

"type":"JDBC",

"url":"jdbc:mysql://127.0.0.1:3306?useUnicode=true&serverTimezone=UTC&characterEncoding=UTF-8",

"user":"root",

"weight":0}

*/;

# 显示数据源

/*+ mycat:showDataSources{} */\G;

# 删除数据源

/*+ mycat:dropDataSource{

"dbType":"mysql",

"idleTimeout":60000,

"initSqls":[],

"initSqlsGetConnection":true,

"instanceType":"READ_WRITE",

"maxCon":1000,

"maxConnectTimeout":3000,

"maxRetryCount":5,

"minCon":1,

"name":"m1",

"type":"JDBC",

"weight":0}

*/;

MyCAT集群设置：cluster

对后端MySQL进行组合设置，例如主、从、只读、读写、分库、分表

·clusters：设置集群

# 创建集群

/*! mycat:createCluster{

"clusterType":"MASTER_SLAVE",

"heartbeat":{

"heartbeatTimeout":1000,

"maxRetry":3,

"minSwitchTimeInterval":300,

"slaveThreshold":0

"masters":[ "m1" //主节点 ],

"maxCon":2000,

"name":"c0",

"readBalanceType":"BALANCE_ALL",

"replicas":[ "dc2" //从节点 ],

"switchType":"SWITCH"}

*/;

# 显示集群

/*+ mycat:showClusters{} */;

# 删除集群

/*! mycat:dropCluster{ "name":"c0"} */;

MyCAT用户设置：users

配置文件位置/usr/local/mycat/conf/users/{用户名}.user.json。这个配置文件主要是用来配置MyCAT的登录用户的，也就是我们连接8066这个端口的用户信息。

{

"dialect": "mysql",

"ip": null,

"password": "123456",

"transactionType": "proxy",

"username": "root",

"isolation": 3

}

配置说明

1）dialect：数据库(方言)类型

2）ip：配置白名单使用，一般写null 如果要限制这个用户

3）password：配置MyCAT用户的密码（明文）

4）isolation：设置初始化的事务隔离级别

READ_UNCOMMITTED：1

READ_COMMITTED：2

REPEATED_READ：3

默认SERIALIZABLE：4

5）transactionType默认值：

proxy（本地事务，在涉及大于1个数据库的事务，commit阶段失败会导致不一致，但是兼容性最好）

可选值：xa（事务，需要确认存储节点集群类型是否支持XA） |

MyCAT逻辑库设置：schemas

·作用：配置MyCAT里面和MySQL对应的逻辑表。

配置位置：mycat/conf/schemas/{库名}.schema.json

{

"customTables": {},

"globalTables": {},

"normalTables": {},

"schemaName": "test",

"shardingTables": {},

"targetName": "prototype"

}

配置说明

1）customTables：自定义表

2）globalTables：全局表

3）normalTables：默认表

4）schemaName：库名

5）shardingTables：分片表

6）targetName：数据源名，也可以是集群名

MyCAT注释指令：

# 清空mycat所有配置

/*+ mycat:resetConfig{} */;

# 创建用户

/*+ mycat:createUser{

"username":"chen",

"password":"123456",

"ip":null,

"transactionType":"xa"}

*/;

# 查看用户

/*+ mycat:showUsers */;

# 删除用户

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

MyCAT2的一主一从配置⭐⭐⭐（在mycat操作）

前提，先搭建好MySQL的主从配置【如果恢复了先前配置好的主从复制，就在主服务器输入show master status;查看端口号和地址，再在从服务器上停止slave，重新同步】，登录MyCAT 2在MyCAT2里面操作，也就是连接8066这个端口。

# 重置配置

/*+ mycat:resetConfig{} */;

# 添加主库读写的数据源

/*+ mycat:createDataSource

{

"dbType": "mysql",

"idleTimeout": 60000,

"initSqls": [],

"initSqlsGetConnection": true,

"instanceType": "READ_WRITE",

"maxCon": 1000,

"maxConnectTimeout": 3000,

"maxRetryCount": 5,

"minCon": 1,

"name": "m1",

"password": "root123",

"type": "JDBC",

"url": "jdbc:mysql://192.168.221.20:3306?useUnicode=true&serverTimezone=UTC&characterEncoding=UTF-8",

"user": "root",

"weight": 0

}

*/;

# 添加从库读的数据源

/*+ mycat:createDataSource

{

"dbType": "mysql",

"idleTimeout": 60000,

"initSqls": [],

"initSqlsGetConnection": true,

"instanceType": "READ",

"maxCon": 1000,

"maxConnectTimeout": 3000,

"maxRetryCount": 5,

"minCon": 1,

"name": "m1s1",

"password": "root123",

"type": "JDBC",

"url": "jdbc:mysql://192.168.221.30:3306?useUnicode=true&serverTimezone=UTC&characterEncoding=UTF-8",

"user": "root",

"weight": 0

}

*/;

# 查询数据源

/*+ mycat:showDataSources{} */\G;

# 创建集群

/*! mycat:createCluster{

"clusterType":"MASTER_SLAVE",

"heartbeat":{

"heartbeatTimeout":1000,

"maxRetry":3,

"minSwitchTimeInterval":300,

"slaveThreshold":0

"masters":[

"m1"

"maxCon":2000,

"name":"prototype",

"readBalanceType":"BALANCE_ALL",

"replicas":[

"m1s1"

"switchType":"SWITCH"

} */;

# 查询集群

/*! mycat:showClusters{} */\G;

由于后面无法在db1里添加表，所以后面又删了重新创建库和表

## 创建逻辑库

CREATE DATABASE db1 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

## 退出mycat

exit;

## 在conf/schemas/db1.schema.json里修改逻辑库的数据源

vim /usr/local/mycat/conf/schemas/db1.schema.json

## 在里面添加集群的名称，作用是让该集群生成并管理这个库的物理库

"targetName":"prototype",

# 重启mycat

cd /usr/local/mycat/bin

./mycat restart

mysql -uroot -p123456 -P8066 -h127.0.0.1

# 查看数据库

show databases;

# 创建db2数据库，进入db2，创建表，添加数据

create database db2;

use db2;

create table sys_user(id bigint primary key, username varchar(200) not null, address varchar(500));

insert into sys_user(id,username,address) values(1,"xiaoming","wuhan");

# 主库、从库重复查看库和表的操作，同步就说明步骤正确