MyCAT的五台主从配置与哈希分片

MyCAT的两主两从配置⭐⭐⭐（五台）

一、分库分表的环境准备

我们可以按第6章的方式，搭建MySQL两组主从复制模型。

1.1 准备四个数据库和一个MyCAT

名称	ip	port
dw0	192.168.221.20	3306
dr0	192.168.221.30	3306
dw1	192.168.221.31	3306
dr1	192.168.221.32	3306
mycat	192.168.221.40	8066

1.2 配置MyCAT数据源

通过上面的配置，我们准备了两组主从，分别为：

1）主服务器dw0、从服务器dr0

2）主服务器dw1、从服务器dr1

接下来，我们要在MyCAT里面配置这四个数据源。

# 添加dw0数据源
/*+ mycat:createDataSource
{ "name":"dw0",
  "password":"root123",

"url":"jdbc:mysql://192.168.221.20:3306?useUnicode=true&serverTimezone=UTC&characterEncoding=UTF-8",
  "user":"root",}
*/;
# 添加dr0数据源
/*+ mycat:createDataSource
{ "name":"dr0",
  "password":"root123",

"url":"jdbc:mysql://192.168.221.30:3306?useUnicode=true&serverTimezone=UTC&characterEncoding=UTF-8",
  "user":"root",}
*/;
# 添加dw1数据源
/*+ mycat:createDataSource
{ "name":"dw1",
  "password":"root123",

"url":"jdbc:mysql://192.168.221.31:3306?useUnicode=true&serverTimezone=UTC&characterEncoding=UTF-8",
  "user":"root",}
*/;
# 添加dr1数据源
/*+ mycat:createDataSource
{ "name":"dr1",
  "password":"root123",

"url":"jdbc:mysql://192.168.221.32:3306?useUnicode=true&serverTimezone=UTC&characterEncoding=UTF-8",
  "user":"root",}
*/;
# 查看数据源
/*+ mycat:showDataSources{} */\G;

会出现五个row

或者我们在MyCAT服务器里面看到如下数据源的配置文件。

1.3 配置MyCAT集群配置

注意：自动分片默认要求集群名字以c为前缀，数字为后缀：

1）c0就是分片表第一个节点；

2）c1就是第二个节点。

一般情况下我们使用默认的就可以了。

/*! mycat:createCluster{ "name":"c0", "masters":[  "dw0" ], "replicas":[  "dr0" ]} */;
/*! mycat:createCluster{ "name":"c1", "masters":[  "dw1" ], "replicas":[  "dr1" ]} */;
# 查看集群
/*+ mycat:showClusters{} */\G;

会出现三个row

或者查看MyCAT配置文件里面内容如下：

或者是下面这样的：

{
    "clusterType": "MASTER_SLAVE",
    "heartbeat": {
        "heartbeatTimeout": 1000,
        "maxRetryCount": 3,
        "minSwitchTimeInterval": 300,
        "showLog": false,
        "slaveThreshold": 0.0
    },
    "masters": [
        "dw0"
    ],
    "maxCon": 2000,
    "name": "c0",
    "readBalanceType": "BALANCE_ALL",
    "replicas": [
        "dr0"
    ],
    "switchType": "SWITCH"
}

二、全局表(广播表)配置

全局表：所有分片库中都有全量数据的表。分库分表的环境准备好之后，接下来我们在MyCAT里面执行相关的命令，就可以帮我们创建全局表。

2.1 创建数据库

create database db1 default character set utf8mb4 collate utf8mb4_general_ci;

2.2 创建表

use db1;
create table `sys_dict` ( `id` bigint not null auto_increment,
                        `dict_type` int ,
                        `dict_name` varchar(100) default null,
                        `dict_value` int , primary key (`id`)
                        ) engine=innodb  default charset=utf8mb4 broadcast;
# 上面的SQL中有一个BROADCAST 这个就是全局表的标识。

2.3 查看结果

在MyCAT的配置文件中查看：

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

或者通过Navicat后端数据库中查看发现所有的表全部出现了。

  

2.4 添加数据查看结果

insert into sys_dict(dict_type,dict_name,dict_value) values(1,"男",1);
insert into sys_dict(dict_type,dict_name,dict_value) values(1,"女",0);
# 上面插入的是所有的库中都能查看的数据

2.5 查询数据查看结果

select * from sys_dict;

会出现

+----+-----------+-----------+------------+

| id | dict_type | dict_name | dict_value |

+----+-----------+-----------+------------+

|  1  |         1   | 男          |          1   |

|  2  |         1   | 女          |          0   |

+----+-----------+-----------+------------+

或者通过Navicat查看表格sys_dict

发现数据并没有重复。

三、分片表配置【重点】

关键字：dbpartition、tbpartitition、tbpartitions、dbpartitions。

以上的运行成功必须是c0、c1的数据源配置没有问题才行。

3.1 创建表

create table orders(id bigint not null auto_increment,
               order_type int,customer_id int,
               amount decimal(10,2),  primary key(id)
               ) engine=innodb  default charset=utf8mb4 dbpartition by mod_hash(customer_id) tbpartition by mod_hash(customer_id) tbpartitions 1 dbpartitions 2;
# dbpartition by mod_hash(customer_id)： 指定数据库的分片算法及使用哪一条数据进行分片
# hashtbpartition by mod_hash(customer_id) ：指定表的分片算法及使用哪一条数据进行分片
# hashtbpartitions 1  表的分片数量
# dbpartitions 2  数据库的分片数量

3.2 查看MyCAT生成的配置

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

3.3 添加数据

insert into orders(id,order_type,customer_id,amount) values(1,101,100,100101),(2,101,100,100101),(3,101,100,100101),(4,102,101,101102),(5,102,101,101102),(6,102,101,101102);

3.4 查询后台物理库

从图中我们可以看到数据库生成了。打开数据库后，里面的表也生成了，里面的数据也分开了，并不在一个表里。

3.5 MyCAT中查询(Navicat查看mycat的db1的order内容)

从上图中，我们发现查询的结果也帮我们合并了。

四、ER表配置

说明：在1.6的版本中，我们ER表的配置有关系的数据必须存放在相同的库中，但是在2.0中不用了，MyCAT2自动帮我们优化了。上面我们创建了一张订单表，接下来我们创建一张订单详情表。

4.1 创建表

create table orders_detail( id bigint auto_increment,
                    detail varchar(2000),
                    order_id bigint, primary key(id)
                    ) engine=innodb  default charset=utf8mb4 dbpartition by mod_hash(order_id) tbpartition by mod_hash(order_id) tbpartitions 1 dbpartitions 2;
#dbpartition by mod_hash(order_id)：指定数据库的分片算法及使用哪一条数据进行分片hashtbpartition
#by mod_hash(order_id)：指定表的分片算法及使用哪一条数据进行分片
#hashtbpartitions 1     表的分片数量
#dbpartitions 2         数据库的分片数量

4.2 查看MyCAT生成的配置

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

从上图中我们可以出已经放到分片表里面了。

4.3 添加数据

insert into orders_detail values(1,"详情1",1);
insert into orders_detail values(2,"详情2",2);
insert into orders_detail values(3,"详情3",3);
insert into orders_detail values(4,"详情4",4);
insert into orders_detail values(5,"详情5",5);
insert into orders_detail values(6,"详情6",6);

4.4 查询后台物理库

从上图可知，dw0里面只存了三条。

发现dw1里面也存放了三条。

4.5 MyCAT中关联查询

select * from orders o inner join orders_detail od on(o.id=od.order_id);

如果在mysql输入会出现：

+----+------------+-------------+-----------+------+---------+----------+

| ID | ORDER_TYPE | CUSTOMER_ID | AMOUNT    | id0  | detail  | order_id |

+----+------------+-------------+-----------+------+---------+----------+

|  1 |        101 |         100 | 100101.00 |    1 | 详情1   |        1 |

|  2 |        101 |         100 | 100101.00 |    2 | 详情2   |        2 |

|  3 |        101 |         100 | 100101.00 |    3 | 详情3   |        3 |

|  4 |        102 |         101 | 101102.00 |    4 | 详情4   |        4 |

|  5 |        102 |         101 | 101102.00 |    5 | 详情5   |        5 |

|  6 |        102 |         101 | 101102.00 |    6 | 详情6   |        6 |

+----+------------+-------------+-----------+------+---------+----------+

五、分片算法简介

5.1取模哈希分片 MOD_HASH

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

2）分库键和分表键是同键

3）分表下标=分片值%(分库数量*分表数量)

4）分库下标=分表下标/分表数量

5）分库键和分表键是不同键

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

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

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

5.2 范围哈希分片 RANGE_HASH

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

2）仅支持数值类型，字符串类型

3）当时字符串类型时候，第三个参数生效

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

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

6）根据数值按分片数取余

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

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

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

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

2）分库键和分表键是同键

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

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

5）分库键和分表键是不同键

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

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

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

5.4日期哈希分片 YYYYDD

1）仅用于分库

2）DD是一年之中的天数

3）(YYYY*366+DD)%分库数

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