超炫之家

Mycat

database

# 一、Mycat简介

# 概述

Mycat 是什么？从定义和分类来看，它是一个开源的分布式数据库系统，是一个实现了 MySQL 协议的 Server，前端用户可以把它看作是一个数据库代理，用 MySQL 客户端工具和命令行访问，而其后端可以用 MySQL 原生（Native） 协议与多个 MySQL 服务器通信，也可以用 JDBC 协议与大多数主流数据库服务器通信，其核心功能是分表分库，即将一个大表水平分割为 N 个小表，存储在后端 MySQL 服务器里或者其他数据库里。

MyCat 是一个强大的数据库中间件，不仅仅可以用作读写分离、以及分表分库、容灾备份，而且可以用于多租户应用开发、云平台基础设施、让你的架构具备很强的适应性和灵活性，借助于即将发布的 MyCat 智能优化模块，系统的数据访问瓶颈和热点一目了然，根据这些统计分析数据，你可以自动或手工调整后端存储，将不同的表映射到不同存储引擎上，而整个应用的代码一行也不用改变。

MyCat的本质是在用户和MySQL之间做了一个中间桥梁。

# 应用场景

  • 单纯的读写分离，此时配置最为简单，支持读写分离，主从切换
  • 分表分库，对于超过 1000 万的表进行分片，最大支持 1000 亿的单表分片
  • 多租户应用，每个应用一个库，但应用程序只连接 MyCat，从而不改造程序本身，实现多租户化
  • 报表系统，借助于 MyCat 的分表能力，处理大规模报表的统计
  • 替代 Hbase，分析大数据
  • 作为海量数据实时查询的一种简单有效方案，比如 100 亿条频繁查询的记录需要在 3 秒内查询出来结果，除了基于主键的查询，还可能存在范围查询或其他属性查询，此时 MyCat 可能是最简单有效的选择

# Mycat端口

  • 管理端口 9066 （查看Mycat运行状态）

  • 数据端口 8066 （进行数据的CRUD）

1、连接Mycat管理操作端口：

mysql -uroot -p123456 -h 192.168.2.153 -P9066 -DTESTDB
1
  • 查看所有的可用命令：show @@help;
  • 查看所有的逻辑数据库：show @@database;
  • 查看所有的数据节点：show @@datanode;
  • 查看所有的数据源：show @@datasource;

2、连接Mycat数据操作端口：

mysql -uroot -p123456 -h 192.168.2.153 -P8066 -DTESTDB
1
  • 查看逻辑数据库：show databases;
  • 使用指定的逻辑数据库：use TESTDB;
  • 查看所有的数据表：show tables;
  • 查看某一张表的数据：select * from table_name;

# 二、主从复制，读写分离

# 概述

随着应用业务数据不断的增大，应用的响应速度不断下降，在检测过程中我们不难发现大多数的请求都是 查询操作。此时，我们可以将数据库扩展成 主从复制模式，将 读操作 和 写操作 分离开来，多台数据库 分摊请求，从而 减少单库 的 访问压力，进而应用得到优化。

# 主从复制的前提条件

  • 主从服务器 操作系统版本 和 位数 一致。
  • 主数据库和从数据库的 版本 要一致。
  • 主数据库和从数据库中的 数据 要一致。
  • 主数据库 开启 二进制 日志，主数据库和从数据库的 server_id 在局域网内必须 一致。

# 主从复制的方式

MySQL 5.6 开始主从复制有两种方式：

  • 基于日志（binlog）；
  • 基于 GTID（全局事务标示符）。

本文只涉及基于日志 binlog 的 主从配置。

# 主从复制的流程

MySQL 同步操作通过3个线程实现，其基本步骤如下：

  1. 主服务器 将数据的更新记录到二进制日志（Binary log）中，用于记录二进制日志事件，这一步由主库线程完成；
  2. 从库将主库的二进制日志复制到本地的中继日志（Relay log），这一步由 从库 I/O 线程 完成；
  3. 从库读取中继日志中的事件，将其重放到数据中，这一步由 从库 SQL 线程 完成。

# 主从模式的优点

  1. 负载均衡

    通常情况下，会使用主服务器对数据进行 更新、删除 和新建等操作，而将查询 工作落到从库头上。

  2. 异地容灾备份

    可以将主服务器上的数据同步到异地从服务器 上，极大地提高了数据安全性。

  3. 高可用

    数据库的复制功能实现了主服务器与从服务器间的数据同步，一旦主服务器出了故障，从服务器立即担当起主服务器的角色，保障系统持续稳定运作。

  4. 高扩展性

    主从复制模式支持 2 种扩展方式:

    • scale-up

    向上扩展或者纵向扩展，主要是提供比现在服务器 性能更好 的服务器，比如 增加 CPU 和 内存 以及磁盘阵列等，因为有多台服务器，所以可扩展性比单台更大。

    • scale-out

    向外扩展或者横向扩展，是指增加服务器数量的扩展，这样主要能分散各个服务器的压力。

# 主从模式的缺点

  1. 成本增加

    搭建主从肯定会增加成本，毕竟一台服务器和两台服务器的成本完全不同，另外由于主从必须要开启 二进制日志，所以也会造成额外的 性能消耗。

  2. 数据延迟

    从库 从 主库复制数据肯定是会有一定的数据延迟的。所以当刚插入就出现查询的情况，可能查询不出来。当然如果是插入者自己查询，那么可以直接从主库中查询出来，当然这个也是需要用代码来控制的。

  3. 写入更慢

    主从复制主要是针对读远大于写或者对数据备份实时性要求较高的系统中。因为主服务器在写中需要更多操作，而且只有一台可以写入的主库，所以写入的压力并不能被分散。

# 三、MyCat实现主从复制

# 环境准备

主机配置说明

IP:端口 容器网络
192.168.106.211:3311 mysql master 172.22.0.11
192.168.106.211:3321 mysql slave1 172.22.0.21
192.168.106.211:3322 mysql slave2 172.22.0.22

# 部署 3 台 MySQL 容器

# 配置mysql集群

mkdir -p /usr/local/docker/mysql-cluster
cd /usr/local/docker/mysql-cluster
vi docker-compose.yml
1
2
3
  • docker-compose.yml
version: '3.3'
services:
    mysql-master:
        container_name: mysql-master
        image: mysql:8.0
        restart: unless-stopped
        environment:
            MYSQL_ROOT_PASSWORD: root
            MYSQL_DATABASE: kim_test
        command:
            --default-authentication-plugin=mysql_native_password
            --character-set-server=utf8mb4
            --collation-server=utf8mb4_general_ci
            --explicit_defaults_for_timestamp=true
            --lower_case_table_names=1
        ports:
            - 3311:3306
        volumes:
            - ./master/conf/my.cnf:/etc/mysql/my.cnf:ro
            - ./master/data:/var/lib/mysql
        networks:
          mysql-ms-network:
            ipv4_address: 172.22.0.11
    mysql-slave1:
        container_name: mysql-slave1
        image: mysql:8.0
        restart: unless-stopped
        environment:
            MYSQL_ROOT_PASSWORD: root
            MYSQL_DATABASE: kim_test
        command:
            --default-authentication-plugin=mysql_native_password
            --character-set-server=utf8mb4
            --collation-server=utf8mb4_general_ci
            --explicit_defaults_for_timestamp=true
            --lower_case_table_names=1
        ports:
            - 3321:3306
        depends_on:
            - mysql-master
        volumes:
            - ./slave1/conf/my.cnf:/etc/mysql/my.cnf:ro
            - ./slave1/data:/var/lib/mysql
        networks:
            mysql-ms-network:
                ipv4_address: 172.22.0.21
    mysql-slave2:
        container_name: mysql-slave2
        image: mysql:8.0
        restart: unless-stopped
        environment:
            MYSQL_ROOT_PASSWORD: root
            MYSQL_DATABASE: kim_test
        command:
            --default-authentication-plugin=mysql_native_password
            --character-set-server=utf8mb4
            --collation-server=utf8mb4_general_ci
            --explicit_defaults_for_timestamp=true
            --lower_case_table_names=1
        ports:
            - 3322:3306
        depends_on:
            - mysql-master
        volumes:
            - ./slave2/conf/my.cnf:/etc/mysql/my.cnf:ro
            - ./slave2/data:/var/lib/mysql
        networks:
            mysql-ms-network:
                ipv4_address: 172.22.0.22
networks:
  mysql-ms-network:
    driver: bridge
    ipam:
      config:
      - subnet: 172.22.0.0/24
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75

创建主库的自定义my.cnf

mkdir -p /usr/local/docker/mysql-cluster/master/conf
cd /usr/local/docker/mysql-cluster/master/conf
vi my.cnf
1
2
3
[mysqld]
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
datadir         = /var/lib/mysql
secure-file-priv= NULL
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

## 设置server_id，一般设置为IP，注意要唯一
server_id=11
## 复制过滤：也就是指定哪个数据库不用同步（mysql库一般不同步）
binlog-ignore-db=mysql
## 开启二进制日志功能，可以随便取，最好有含义（关键就是这里了）
log-bin=replicas-mysql-bin
## 为每个session分配的内存，在事务过程中用来存储二进制日志的缓存
binlog_cache_size=1M
## 主从复制的格式（mixed,statement,row，默认格式是statement）
binlog_format=mixed
## 二进制日志自动删除/过期的天数。默认值为0，表示不自动删除。
expire_logs_days=7
## 跳过主从复制中遇到的所有错误或指定类型的错误，避免slave端复制中断。
## 如：1062错误是指一些主键重复，1032错误是因为主从数据库数据不一致
slave_skip_errors=1062
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

创建从库1的自定义my.cnf

mkdir -p /usr/local/docker/mysql-cluster/slave1/conf
cd /usr/local/docker/mysql-cluster/slave1/conf
vi my.cnf
1
2
3
[mysqld]
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
datadir         = /var/lib/mysql
secure-file-priv= NULL
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

## 设置server_id，一般设置为IP，注意要唯一
server_id=21
## 复制过滤：也就是指定哪个数据库不用同步（mysql库一般不同步）
binlog-ignore-db=mysql
## 开启二进制日志功能，以备Slave作为其它Slave的Master时使用
log-bin=replicas-mysql-bin
## 为每个session 分配的内存，在事务过程中用来存储二进制日志的缓存
binlog_cache_size=1M  
## 主从复制的格式（mixed,statement,row，默认格式是statement）
binlog_format=mixed
## 二进制日志自动删除/过期的天数。默认值为0，表示不自动删除。
expire_logs_days=7
## 跳过主从复制中遇到的所有错误或指定类型的错误，避免slave端复制中断。
## 如：1062错误是指一些主键重复，1032错误是因为主从数据库数据不一致
slave_skip_errors=1062
## relay_log配置中继日志
relay_log=replicas-mysql-relay-bin
## log_slave_updates表示slave将复制事件写进自己的二进制日志
log_slave_updates=1
## 防止改变数据(除了特殊的线程)
read_only=1
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

创建从库2的自定义my.cnf

mkdir -p /usr/local/docker/mysql-cluster/slave2/conf
cd /usr/local/docker/mysql-cluster/slave2/conf
vi my.cnf
1
2
3
[mysqld]
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
datadir         = /var/lib/mysql
secure-file-priv= NULL
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

## 设置server_id，一般设置为IP，注意要唯一
server_id=22
## 复制过滤：也就是指定哪个数据库不用同步（mysql库一般不同步）
binlog-ignore-db=mysql
## 开启二进制日志功能，以备Slave作为其它Slave的Master时使用
log-bin=replicas-mysql-bin
## 为每个session 分配的内存，在事务过程中用来存储二进制日志的缓存
binlog_cache_size=1M  
## 主从复制的格式（mixed,statement,row，默认格式是statement）
binlog_format=mixed
## 二进制日志自动删除/过期的天数。默认值为0，表示不自动删除。
expire_logs_days=7
## 跳过主从复制中遇到的所有错误或指定类型的错误，避免slave端复制中断。
## 如：1062错误是指一些主键重复，1032错误是因为主从数据库数据不一致
slave_skip_errors=1062
## relay_log配置中继日志
relay_log=replicas-mysql-relay-bin
## log_slave_updates表示slave将复制事件写进自己的二进制日志
log_slave_updates=1
## 防止改变数据(除了特殊的线程)
read_only=1
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

启动mysql集群

cd /usr/local/docker/mysql-cluster
docker-compose up -d
1
2

# 配置mysq主从复制

使用Navicat连接（root用户）

在主库进行如下操作

## 创建复制用户
create user 'repl'@'%' identified by 'repl';
## 为repl用户赋予 REPLICATION SLAVE，replication client 权限。
grant replication slave, replication client on *.* to 'repl'@'%';
## 刷新权限
FLUSH PRIVILEGES ;
## 查看 master 状态
show master status;
1
2
3
4
5
6
7
8

在从库1、从库2分别进行如下操作

##重置同步
RESET SLAVE;

##将从节点挂载到主节点上，  需注意：只有主备库在同一network下，才可以使用该内部IP进行主备复制。
change master to
master_host='172.22.0.11',
master_user='repl',
master_password='repl',
master_log_file='replicas-mysql-bin.000003',
master_log_pos=0;

## 启动同步
START SLAVE;
## 查看同步状态
show slave status ;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

# 测试主从复制

在主库执行如下SQL

CREATE TABLE kim_test.test(id int(4), ip varchar(100), name varchar(200));
1

如果在2个从库也创建了数据库，表示主从复制搭建成功。

# 部署Mycat

# Mycat配置

Mycat配置读写分离有2种方式：

  • Mysql-master负责写入，不负责读取
  • Mysql-master即负责写入，也与mysql-slave一起分摊读取的压力。

本次配置是第一种，读写彻底分离。

# 克隆MyCat

mkdir -p /usr/local/docker
cd /usr/local/docker
git clone https://github.com/dekuan/docker.mycat.git
mv docker.mycat mycat
cd mycat
1
2
3
4
5

# 配置 server.xml

  • 服务端用户名密码配置：vi config/mycat/server.xml，找到第 90 行，参考如下内容配置
<mycat:server xmlns:mycat="http://io.mycat/">
    <!-- Mycat 数据库用户名 -->
    <user name="root">
        <!-- Mycat 数据库密码 -->
        <property name="password">root</property>
        <!-- Mycat 数据库名 -->
        <property name="schemas">kim_test</property>      
        <!-- 是否使用加密的密码，0 表示不使用加密的密码 -->
        <property name="usingDecrypt">0</property>
    </user>
</mycat:server>
1
2
3
4
5
6
7
8
9
10
11

# 配置 schema.xml

  • 配置前先将该配置文件备份。
cp config/mycat/schema.xml config/mycat/schema_back.xml
1
  • 所有的库表分离核心配置文件：vi config/mycat/schema.xml，参考如下内容配置
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">

    <schema name="kim_test" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1" />

    <dataNode name="dn1" dataHost="localhost1" database="kim_test" />

    <dataHost name="localhost1" maxCon="1000" minCon="10" balance="3" writeType="0" 
         dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
        <heartbeat>select user()</heartbeat>
        <writeHost host="hostM1" url="192.168.106.211:3311" user="root" password="root" >
            <readHost host="hostS1" url="192.168.106.211:3321" user="root" password="root" />
            <readHost host="hostS2" url="192.168.106.211:3322" user="root" password="root" />
        </writeHost>
    </dataHost>

</mycat:schema>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

说明：

  1. balance

    ​ 0, 不开启读写分离机制，所有读操作都发送到当前可用的writeHost上。

    ​ 1，全部的readHost与stand by writeHost参与select语句的负载均衡，简单的说，当双主双从模式(M1->S1，M2->S2，并且M1与M2互为主备)，正常情况下，M2,S1,S2都参与select语句的负载均衡。

    ​ 2，所有读操作都随机的在writeHost、readhost上分发。

    ​ 3，所有读请求随机的分发到wiriterHost对应的readhost执行，writerHost不负担读压力

  2. writeType 表示写模式

    ​ 0，所有的操作发送到配置的第一个writehost

    ​ 1，随机发送到配置的所有writehost

    ​ 2，不执行写操作

  3. switchType 指的是切换的模式，目前的取值也有4种：

    ​ -1，表示不自动切换

    ​ 1，默认值，表示自动切换

    ​ 2，基于MySQL主从同步的状态决定是否切换,心跳语句为show slave status

    ​ 3，基于MySQL galary cluster的切换机制（适合集群）（1.4.1），心跳语句为show status like ‘wsrep%‘。

  4. host属性

    用于标识不同实例，一般writeHost我们使用M1，readHost我们用S1。

  5. url属性

    后端实例连接地址，如果是使用native的dbDriver，则一般为address:port这种形式。

    用JDBC或其他的dbDriver，则需要特殊指定。当使用JDBC时则可以这么写：jdbc:mysql://localhost:3306/。

# 启动mycat

docker-compose up -d --build
docker-compose logs -f
1
2

若日志无错误，且显示successfully，说明配置没问题。

# 测试读写分离

随意找到一台mysql服务器进行测试。

  • 测试添加数据
insert into test values(6, @@hostname, '');
1

  • ·测试查询数据

    为了测试方便，手动将slave、slave2服务器中的数据进行修改：分别加入name字段用于区分是那台服务器。

Select * from test;
1

# 主库宕机解决方案

# 扩展备库解决方案

# 四、分库分表（分片）

# 什么是分库分表（数据切分）

简单来说，就是指通过某种特定的条件，将我们存放在同一个数据库中的数据分散存放到多个数据库（主机）上面，以达到分散单台设备负载的效果

数据的切分（Sharding）根据其切分规则的类型，可以分为两种切分模式。一种是按照不同的表（或者 Schema）来切分到不同的数据库（主机）之上，这种切可以称之为数据的垂直（纵向）切分；另外一种则是根据表中的数据的逻辑关系，将同一个表中的数据按照某种条件拆分到多台数据库（主机）上面，这种切分称之为数据的水平（横向）切分

垂直切分的最大特点就是规则简单，实施也更为方便，尤其适合各业务之间的耦合度非常低，相互影响很小，业务逻辑非常清晰的系统。在这种系统中，可以很容易做到将不同业务模块所使用的表分拆到不同的数据库中。根据不同的表来进行拆分，对应用程序的影响也更小，拆分规则也会比较简单清晰

水平切分于垂直切分相比，相对来说稍微复杂一些。因为要将同一个表中的不同数据拆分到不同的数据库中，对于应用程序来说，拆分规则本身就较根据表名来拆分更为复杂，后期的数据维护也会更为复杂一些

# 垂直切分

一个数据库由很多表的构成，每个表对应着不同的业务，垂直切分是指按照业务将表进行分类，分布到不同的数据库上面，这样也就将数据或者说压力分担到不同的库上面，如下图：

系统被切分成了，用户，订单交易，支付几个模块。一个架构设计较好的应用系统，其总体功能肯定是由很多个功能模块所组成的，而每一个功能模块所需要的数据对应到数据库中就是一个或者多个表。而在架构设计中，各个功能模块相互之间的交互点越统一越少，系统的耦合度就越低，系统各个模块的维护性以及扩展性也就越好。这样的系统，实现数据的垂直切分也就越容易

但是往往系统之间有些表难以做到完全的独立，存在着跨库 join 的情况，对于这类的表，就需要去做平衡，是数据库让步业务，共用一个数据源，还是分成多个库，业务之间通过接口来做调用。在系统初期，数据量比较少，或者资源有限的情况下，会选择共用数据源，但是当数据发展到了一定的规模，负载很大的情况，就需要必须去做分割

一般来讲业务存在着复杂 join 的场景是难以切分的，往往业务独立的易于切分。 如何切分，切分到何种程度是考验技术架构的一个难题

优点

  1. 拆分后业务清晰，拆分规则明确
  2. 系统之间整合或扩展容易
  3. 数据维护简单

缺点

  1. 部分业务表无法 join，只能通过接口方式解决，提高了系统复杂度
  2. 受每种业务不同的限制存在单库性能瓶颈，不易数据扩展跟性能提高
  3. 事务处理复杂

由于垂直切分是按照业务的分类将表分散到不同的库，所以有些业务表会过于庞大，存在单库读写与存储瓶颈，所以就需要水平拆分来做解决

# 水平切分

相对于垂直拆分，水平拆分不是将表做分类，而是按照某个字段的某种规则来分散到多个库之中，每个表中包含一部分数据。简单来说，我们可以将数据的水平切分理解为是按照数据行的切分，就是将表中的某些行切分到一个数据库，而另外的某些行又切分到其他的数据库中，如图：

拆分数据就需要定义分片规则。关系型数据库是行列的二维模型，拆分的第一原则是找到拆分维度。 比如：从会员的角度来分析，商户订单交易类系统中查询会员某天某月某个订单，那么就需要按照会员结合日期来拆分，不同的数据按照会员ID 做分组，这样所有的数据查询 join 都会在单库内解决；如果从商户的角度来讲，要查询某个商家某天所有的订单数，就需要按照商户 ID做拆分；但是如果系统既想按会员拆分，又想按商家数据，则会有一定的困难。如何找到合适的分片规则需要综合考虑衡量。

几种典型的分片规则：

  1. 按照用户ID求模，将数据分散到不同的数据库，具有相同数据用户的数据都被分散到一个库中
  2. 按照日期，将不同月甚至日的数据分散到不同的库中
  3. 按照某个特定的字段求摸，或者根据特定范围段分散到不同的库中

如图，切分原则都是根据业务找到适合的切分规则分散到不同的库，下面用用户 ID求模举例

优点

  1. 拆分规则抽象好， join 操作基本可以数据库做
  2. 不存在单库大数据，高并发的性能瓶颈
  3. 应用端改造较少
  4. 提高了系统的稳定性跟负载能力

缺点

  1. 拆分规则难以抽象
  2. 分片事务一致性难以解决
  3. 数据多次扩展难度跟维护量极大
  4. 跨库 join 性能较差

垂直与水平拆分的共同缺点

  1. 引入分布式事务的问题
  2. 跨节点 join 的问题
  3. 跨节点合并排序分页问题
  4. 多数据源管理问题

# 数据源管理方案

  • 客户端模式，在每个应用程序模块中配置管理自己需要的一个（或者多个）数据源，直接访问各个数据库，在模块内完成数据的整合
  • 通过中间代理层来统一管理所有的数据源，后端数据库集群对前端应用程序透明

绝大部分人在面对上面这两种解决思路的时候都会倾向于选择第二种，尤其是系统不断变得庞大复杂的时候。确实，这是一个非常正确的选择，虽然短期内需要付出的成本可能会相对更大一些，但是对整个系统的扩展性来说，是非常有帮助的。

# 五、MyCat 实现分库分表

# 环境准备

主机配置说明

IP:端口
192.168.106.211:3316 mysql master
192.168.106.211:3317 mysql slave1
192.168.106.211:3318 mysql slave2
192.168.106.211:8066 mycat

# 部署 3 台 MySQL 容器

案例分片方案按照自定义数字范围分片方式（auto-sharding-long）进行数据库分片，其规则要求需要 3 台 MySQL

mkdir -p /usr/local/docker/mysql3
cd /usr/local/docker/mysql3
vi docker-compose.yml
1
2
3
  • docker-compose.yml
version: '3.1'
services:
  mysql-1:
    image: mysql:8.0
    container_name: mysql-1
    environment:
      MYSQL_ROOT_PASSWORD: root
    command:
      --default-authentication-plugin=mysql_native_password
      --character-set-server=utf8mb4
      --collation-server=utf8mb4_general_ci
      --explicit_defaults_for_timestamp=true
      --lower_case_table_names=1
    ports:
      - 3316:3306
    volumes:
      - ./data-1:/var/lib/mysql

  mysql-2:
    image: mysql:8.0
    container_name: mysql-2
    environment:
      MYSQL_ROOT_PASSWORD: root
    command:
      --default-authentication-plugin=mysql_native_password
      --character-set-server=utf8mb4
      --collation-server=utf8mb4_general_ci
      --explicit_defaults_for_timestamp=true
      --lower_case_table_names=1
    ports:
      - 3317:3306
    volumes:
      - ./data-2:/var/lib/mysql

  mysql-3:
    image: mysql:8.0
    container_name: mysql-3
    environment:
      MYSQL_ROOT_PASSWORD: root
    command:
      --default-authentication-plugin=mysql_native_password
      --character-set-server=utf8mb4
      --collation-server=utf8mb4_general_ci
      --explicit_defaults_for_timestamp=true
      --lower_case_table_names=1
    ports:
      - 3318:3306
    volumes:
      - ./data-3:/var/lib/mysql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50

# 部署 MyCat

# 克隆MyCat

mkdir -p /usr/local/docker
cd /usr/local/docker
git clone https://github.com/dekuan/docker.mycat.git
mv docker.mycat mycat
cd mycat
1
2
3
4
5

# 配置 MyCat 数据库分片

  • 服务端用户名密码配置：vi config/mycat/server.xml，找到第 90 行，参考如下内容配置
<mycat:server xmlns:mycat="http://io.mycat/">
    <user name="root">
        <property name="password">root</property>
        <property name="schemas">kim_test</property>      
        <property name="usingDecrypt">0</property>
    </user>
</mycat:server>
1
2
3
4
5
6
7
  • 数据节点、数据库、分库分表配置：vi config/mycat/schema.xml，参考如下内容配置
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">

    <schema name="kim_test" checkSQLschema="true" sqlMaxLimit="100">
    	<table  name="tb_test" dataNode="dataNode1,dataNode2,dataNode3" rule="auto-sharding-long"/>
    </schema>
        
    <dataNode name="dataNode1" dataHost="dataHost1" database="kim_test_1" />
    <dataNode name="dataNode2" dataHost="dataHost2" database="kim_test_2" />
    <dataNode name="dataNode3" dataHost="dataHost3" database="kim_test_3" />
    
    <dataHost name="dataHost1" maxCon="1000" minCon="10" balance="0"
              writeType="0" dbType="mysql" dbDriver="jdbc" switchType="-1" slaveThreshold="100">
        <heartbeat>select user()</heartbeat>
        
        <writeHost
            host="192.168.106.211"
            url="jdbc:mysql://192.168.106.211:3316?useSSL=false&amp;serverTimezone=UTC&amp;characterEncoding=utf8"
            user="root" password="root">  
        </writeHost>
    </dataHost>
    <dataHost name="dataHost2" maxCon="1000" minCon="10" balance="0"
              writeType="0" dbType="mysql" dbDriver="jdbc" switchType="-1" slaveThreshold="100">
        <heartbeat>select user()</heartbeat>
        <writeHost
            host="192.168.106.211"
            url="jdbc:mysql://192.168.106.211:3317?useSSL=false&amp;serverTimezone=UTC&amp;characterEncoding=utf8"
            user="root" password="root">
        </writeHost>
    </dataHost>
    <dataHost name="dataHost3" maxCon="1000" minCon="10" balance="0"
              writeType="0" dbType="mysql" dbDriver="jdbc" switchType="-1" slaveThreshold="100">
        <heartbeat>select user()</heartbeat>
        <writeHost
            host="192.168.106.211"
            url="jdbc:mysql://192.168.106.211:3318?useSSL=false&amp;serverTimezone=UTC&amp;characterEncoding=utf8"
            user="root" password="root">
        </writeHost>
    </dataHost>     
</mycat:schema>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41

说明：

  1. balance

    ​ 0, 不开启读写分离机制，所有读操作都发送到当前可用的writeHost上。

    ​ 1，全部的readHost与stand by writeHost参与select语句的负载均衡，简单的说，当双主双从模式(M1->S1，M2->S2，并且M1与M2互为主备)，正常情况下，M2,S1,S2都参与select语句的负载均衡。

    ​ 2，所有读操作都随机的在writeHost、readhost上分发。

    ​ 3，所有读请求随机的分发到wiriterHost对应的readhost执行，writerHost不负担读压力

  2. writeType 表示写模式

    ​ 0，所有的操作发送到配置的第一个writehost

    ​ 1，随机发送到配置的所有writehost

    ​ 2，不执行写操作

  3. switchType 指的是切换的模式，目前的取值也有4种：

    ​ -1，表示不自动切换

    ​ 1，默认值，表示自动切换

    ​ 2，基于MySQL主从同步的状态决定是否切换,心跳语句为show slave status

    ​ 3，基于MySQL galary cluster的切换机制（适合集群）（1.4.1），心跳语句为show status like ‘wsrep%‘。

  • 分片规则配置：vi config/mycat/rule.xml，分别查看第 32 行和第 105 行
<tableRule name="auto-sharding-long">
    <rule>
        <columns>id</columns>
        <algorithm>rang-long</algorithm>
    </rule>
</tableRule>
1
2
3
4
5
6
<function name="rang-long" class="io.mycat.route.function.AutoPartitionByLong">
    <property name="mapFile">autopartition-long.txt</property>
</function>
1
2
3
  • 自定义数字范围分片规则：vi config/mycat/autopartition-long.txt
# range start-end ,data node index
# K=1000,M=10000.
# ID 0-5000000 保存在 dataNode1
0-500M=0
# ID 5000000-10000000 保存在 dataNode2
500M-1000M=1
# ID 10000000-15000000 保存在 dataNode3
1000M-1500M=2
1
2
3
4
5
6
7
8

# 构建并启动mycat

# 注意：配置完成后再启动
docker-compose up -d --build
1
2

# 测试MyCat 分片

  • 通过 MyCat 操作数据库

    使用 Navicat连接 MyCat 数据库，Mycat默认端口号为 8066。

    分别在 3 台 MySQL 数据库上创建 kim_test，按照刚才创建 MySQL 容器的序号创建。

CREATE DATABASE IF NOT EXISTS kim_test DEFAULT CHARACTER SET utf8mb4 DEFAULT COLLATE utf8mb4_general_ci;
1
  • 建表语句
  create table tb_test (id int not null primary key,name varchar(100),sharding_id int not null);
1
  • 测试数据
insert into tb_test(id, name,sharding_id) values(1000000, 'lixiaohong', 0);
insert into tb_test(id, name,sharding_id) values(6000000, 'lixiaolu', 1);
insert into tb_test(id, name,sharding_id) values(7000000, 'pgone', 1);
insert into tb_test(id, name,sharding_id) values(11000000, 'jianailiang', 2);
1
2
3
4
  • 检验测试结果

按照上面的配置规则

  1. id 为 1000000 的数据应该写入 dataNode1， 即 3316服务器的kim_test.tb_test 表中；
  2. id 为 6000000 的数据应该写入 dataNode2， 即 3317服务器的kim_test.tb_test 表中；
  3. id 为 7000000 的数据应该写入 dataNode2， 即 3317服务器的kim_test.tb_test 表中；
  4. id 为 11000000 的数据应该写入 dataNode3， 即 3318服务器的kim_test.tb_test 表中。

至此这说明分片成功了。

MySQL