(Mycat入门篇)按照月份分布数据

最新推荐文章于 2022-05-30 16:25:16 发布

请给我的爱人一杯mojito

最新推荐文章于 2022-05-30 16:25:16 发布

阅读量406

点赞数

分类专栏： Mysql 文章标签： mycat

本文链接：https://blog.csdn.net/baidu_34007305/article/details/114674113

版权

Mysql 专栏收录该内容

86 篇文章 1 订阅

订阅专栏

背景

按自然月分片的规则

写在前面
原文链接http://www.fullstackyang.com/mycatxi-lie-zhi-yi-shu-ju-fen-pian-ru-men-shi-zhan/
link

和参考文档不同之处

源文档：

现在假设系统的数据库为messagedb，里面只有2张表，一张表为消息表：message，一张表示消息来源的字典表：source 一共3个mysql实例各自需要创建4个数据库

在这里插入图片描述

本文:一共2个mysql实例各自1个库。

mysql版本5.7 mycat1.6.5 逻辑库名为messagedb

实例1：主库 M1 地址 66.190 库名 db1 从库S1 地址 66.191
实例2：主库 M2 地址 66.180 库名 db2
最终数据按照月份分布到db1和db2中

表结构

source是字典表（全局表），所以要在每个实例上创建，message表是数据记录表，也要在每个实例上创建

在2个主库上执行：
create table source (
        id int(11) not null auto_increment primary key comment 'pk',
        name varchar(10) default '' comment 'source name'
);
create table message (
        id int(11) not null auto_increment primary key comment 'pk',
        content varchar(255) default '' comment 'message content',
        create_time date default null,
        source_id int(11) not null,
        foreign key(source_id) references source(id)
);
insert into `source`(`id`,`name`) values(1,'weibo');
insert into `source`(`id`,`name`) values(2,'weixin');
insert into `source`(`id`,`name`) values(3,'qq');
insert into `source`(`id`,`name`) values(4,'email');
insert into `source`(`id`,`name`) values(5,'sms');

在message表中，总共有4个字段：

id：主键
content：消息的内容
create_time：创建时间，这也是mycat进行分片时的参考字段
source_id：source表的外键
另外，我们在source表插入了5条记录，用于测试。到这里，后端数据库的环境就搭建完成了。

以下配置文件的注释方式

<!-- 注释 -->

1. server.xml

该配置文件是用于配置mycat的系统信息，主要有两个标签：system和user。这里的user就是上述访问mycat服务的用户，不是后端数据库的用户。如果我们使用默认的配置，server.xml大概是这样的：

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mycat:server SYSTEM "server.dtd">
<mycat:server xmlns:mycat="http://io.mycat/">
        <system>
                <property name="useSqlStat">0</property>  
                <property name="useGlobleTableCheck">0</property> 
                <property name="sequnceHandlerType">2</property>
                <property name="processorBufferPoolType">0</property>
                <property name="useOffHeapForMerge">1</property>
                <property name="memoryPageSize">1m</property>
                <property name="spillsFileBufferSize">1k</property>
                <property name="useStreamOutput">0</property>
                <property name="systemReserveMemorySize">384m</property>
        </system>
        <user name="root">
                <property name="password">123456</property>
                <property name="schemas">messagedb</property>

                <!-- 表级 DML 权限设置 -->
                <!--            
                <privileges check="false">
                        <schema name="TESTDB" dml="0110" >
                                <table name="tb01" dml="0000"></table>
                                <table name="tb02" dml="1111"></table>
                        </schema>
                </privileges>           
                 -->
        </user>
</mycat:server>

messagedb就是Mycat中的逻辑库名

user标签下schemas属性表示该用户可以访问的数据库，可以定义多个数据库，用英文逗号隔开。
schemas定义的数据库，一定要配置在后面的schema.xml文件对应的逻辑库，否则会提示无法访问。

2.schema.xml

schema配置文件比较复杂，也是最关键的一个配置文件，定义了mycat中的逻辑库、逻辑表，和分片的相关信息。配置如下：

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">

        <schema name="messagedb" checkSQLschema="false" sqlMaxLimit="100">
                <!-- auto sharding by id (long) -->
                <table name="message" dataNode="dn1,dn2,dn3,dn4,dn5,dn6,dn7,dn8,dn9,dn10,dn11,dn12" rule="sh
arding-by-month" />

                <!-- global table is auto cloned to all defined data nodes ,so can join
                        with any table whose sharding node is in the same data node -->
                <table name="source" primaryKey="id" type="global" dataNode="dn1,dn2,dn3,dn4,dn5,dn6,dn7,dn8
,dn9,dn10,dn11,dn12" />
        </schema>
        <!-- <dataNode name="dn1$0-743" dataHost="localhost1" database="db$0-743"
                /> -->
        <dataNode name="dn1" dataHost="66190" database="db1" />
        <dataNode name="dn2" dataHost="66180" database="db2" />
        <dataNode name="dn3" dataHost="66190" database="db1" />
        <dataNode name="dn4" dataHost="66180" database="db2" />
        <dataNode name="dn5" dataHost="66190" database="db1" />
        <dataNode name="dn6" dataHost="66180" database="db2" />
        <dataNode name="dn7" dataHost="66190" database="db1" />
        <dataNode name="dn8" dataHost="66180" database="db2" />
        <dataNode name="dn9" dataHost="66190" database="db1" />
        <dataNode name="dn10" dataHost="66180" database="db2" />
        <dataNode name="dn11" dataHost="66190" database="db1" />
        <dataNode name="dn12" dataHost="66180" database="db2" />

        <dataHost name="66190" maxCon="1000" minCon="10" balance="0"
                          writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100
">
                <heartbeat>select user()</heartbeat>
                <!-- can have multi write hosts -->
                <writeHost host="hostM1" url="192.168.66.190:3306" user="root"
                                   password="123456">
                        <!-- can have multi read hosts -->
                        <readHost host="hostS1" url="192.168.66.191:3306" user="root" password="123456" />
                </writeHost>
        </dataHost>
        <dataHost name="66180" maxCon="1000" minCon="10" balance="0" 
                          writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100
">
                <heartbeat>select user()</heartbeat>
                 <writeHost host="hostM2" url="192.168.66.180:3306" user="root" password="123456"></writeHos
t>
        </dataHost>

</mycat:schema>

以上dataNode标签就是和原文不同的,原文是(字段值)按照每个月分布在不同库中。本文是分布在db1和db2中

并且根据分片规则dataNode=“dn1,dn2,dn3,dn4,dn5,dn6,dn7,dn8,dn9,dn10,dn11,dn12”
的配置不可缺少，否则>=3月份以上的时间插入，会报无节点可用的错误

几点要说明一下：

schema标签定义逻辑库，其下table子标签定义逻辑表，datanode属性定义该逻辑表需要分布到哪几个分片上，
rule属性表示使用何种分片规则，这里我们选择sharding-by-month，这个规则的名称是自定义的，
只要和后面的rule.xml对应起来即可

source表是一张全局表，这里需要使用type=”global”来定义，
这样mycat就可以帮我们在指定的分片上克隆相同的数据，这对join查询是非常有好处的。

datanode标签定义了分片，datahost是主机名，对应dataHost标签的name属性值，
database定义该主机数据库实例上的具体数据库名。

dataHost标签定义数据库实例，其下heartbeart标签表示心跳检测所使用的方法，
writeHost标签定义写数据的实例，另外还有readHost标签可以定义读数据的实例，
这里不考虑读写分离，仅使用写实例，因此需要把balance属性设置为0 (补充配置文件建议是1)

其他属性可以自行查阅官方权威指南(先让它跑起来哈)

最后，出于规范和安全考虑，最好不使用数据库的root用户，
而是另外再创建一个用于mycat访问的用户。(需要手动在对应的实例上创建)

3.rule.xml

rule.xml中定义了很多分片的规则，具体规则的算法可以参考官方权威指南，这里我们直接使用默认的就可以了，其中按自然月的分片规则配置如下：

<?xml version="1.0" encoding="UTF-8"?>
<!-- - - Licensed under the Apache License, Version 2.0 (the "License"); 
        - you may not use this file except in compliance with the License. - You 
        may obtain a copy of the License at - - http://www.apache.org/licenses/LICENSE-2.0 
        - - Unless required by applicable law or agreed to in writing, software - 
        distributed under the License is distributed on an "AS IS" BASIS, - WITHOUT 
        WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. - See the 
        License for the specific language governing permissions and - limitations 
        under the License. -->
<!DOCTYPE mycat:rule SYSTEM "rule.dtd">
<mycat:rule xmlns:mycat="http://io.mycat/">
        <tableRule name="sharding-by-month">
                <rule>
                        <columns>create_time</columns>
                        <algorithm>partbymonth</algorithm>
                </rule>
        </tableRule>

        <function name="partbymonth"
                class="io.mycat.route.function.PartitionByMonth">
                <property name="dateFormat">yyyy-MM-dd</property>
                <property name="sBeginDate">2021-01-01</property>
        </function>

</mycat:rule>

tableRule标签定义分片规则的，其下columns标签表示对数据库表中的哪个字段应用规则，
algorithm指定实现算法的名称，对应的是function标签中的name属性值

function标签定义对应的实现类，以及参数，包括dateFormat（日期格式）和sBeginDate（起始日期）

说明：起始日期是用来计算数据所在的分片位置，例如2021年1月的message就会找到第1个分片，即dn1，2021年12月的message就会找到第12个分片，即dn12，但是如果出现了2022年1月的message，mycat就会去找第13个分片，但是配置文件中又没有对应的配置，那么就会抛出无法找到分片的错误。
也就是说下一年就需要重新设置一次sBeginDate（起始日期）

ERROR 1064 (HY000): Can't find a valid data node for specified node index
:MESSAGE -> CREATE_TIME -> 2022-12-21 -> Index : 23

最后再来总结一下配置文件的关系

在这里插入图片描述

如图所示，server.xml定义了访问mycat服务的用户，以及该用户授权的数据库（逻辑库），schema.xml定义了具体的逻辑库，逻辑表，以及分片和数据库实例的信息，rule.xml分片规则和实现类。

测试

到这里已经完成了mycat的配置文件，我们先访问管理端口9066，看一下运行情况：

show @@datanode;
+------+-----------+-------+-------+--------+------+------+---------+------------+----------+---------+---------------+
| NAME | DATHOST   | INDEX | TYPE  | ACTIVE | IDLE | SIZE | EXECUTE | TOTAL_TIME | MAX_TIME | MAX_SQL | RECOVERY_TIME |
+------+-----------+-------+-------+--------+------+------+---------+------------+----------+---------+---------------+
| dn1  | 66190/db1 |     0 | mysql |      0 |   10 | 1000 |     380 |          0 |        0 |       0 |            -1 |
| dn10 | 66180/db2 |     0 | mysql |      0 |   10 | 1000 |     378 |          0 |        0 |       0 |            -1 |
| dn11 | 66190/db1 |     0 | mysql |      0 |   10 | 1000 |     380 |          0 |        0 |       0 |            -1 |
| dn12 | 66180/db2 |     0 | mysql |      0 |   10 | 1000 |     378 |          0 |        0 |       0 |            -1 |
| dn2  | 66180/db2 |     0 | mysql |      0 |   10 | 1000 |     378 |          0 |        0 |       0 |            -1 |
| dn3  | 66190/db1 |     0 | mysql |      0 |   10 | 1000 |     380 |          0 |        0 |       0 |            -1 |
| dn4  | 66180/db2 |     0 | mysql |      0 |   10 | 1000 |     378 |          0 |        0 |       0 |            -1 |
| dn5  | 66190/db1 |     0 | mysql |      0 |   10 | 1000 |     380 |          0 |        0 |       0 |            -1 |
| dn6  | 66180/db2 |     0 | mysql |      0 |   10 | 1000 |     378 |          0 |        0 |       0 |            -1 |
| dn7  | 66190/db1 |     0 | mysql |      0 |   10 | 1000 |     380 |          0 |        0 |       0 |            -1 |
| dn8  | 66180/db2 |     0 | mysql |      0 |   10 | 1000 |     378 |          0 |        0 |       0 |            -1 |
| dn9  | 66190/db1 |     0 | mysql |      0 |   10 | 1000 |     380 |          0 |        0 |       0 |            -1 |
+------+-----------+-------+-------+--------+------+------+---------+------------+----------+---------+---------------+

show @@heartbeat;
+--------+-------+----------------+------+---------+-------+--------+---------+--------------+---------------------+-------+
| NAME   | TYPE  | HOST           | PORT | RS_CODE | RETRY | STATUS | TIMEOUT | EXECUTE_TIME | LAST_ACTIVE_TIME    | STOP  |
+--------+-------+----------------+------+---------+-------+--------+---------+--------------+---------------------+-------+
| hostM2 | mysql | 192.168.66.180 | 3306 |       1 |     0 | idle   |       0 | 1,1,1        | 2021-03-11 17:56:08 | false |
| hostM1 | mysql | 192.168.66.190 | 3306 |       1 |     0 | idle   |       0 | 1,1,1        | 2021-03-11 17:56:09 | false |
| hostS1 | mysql | 192.168.66.191 | 3306 |      -1 |     0 | idle   |       0 | 0,0,0        | 2021-03-11 17:56:09 | false |
+--------+-------+----------------+------+---------+-------+--------+---------+--------------+---------------------+-------+

如果看到各个节点都已经出现，并且心跳状态RS_CODE=1，则表示后端数据库连接正常。(如果为-1可能是没有用户)

在Mycat端模拟数据插入
mysql -h127.0.0.1 -uroot -p -P 8066

insert into message(`content`, `create_time`, `source_id`) values('aaaa','2021-01-21',1);

insert into message(`content`, `create_time`, `source_id`) values('aaaa','2021-02-01',1);

insert into message(`content`, `create_time`, `source_id`) values('aaaa','2021-03-10',1);

insert into message(`content`, `create_time`, `source_id`) values('aaaa','2021-04-21',4);

insert into message(`content`, `create_time`, `source_id`) values('aaaa','2021-05-21',4);
insert into message(`content`, `create_time`, `source_id`) values('aaaa','2021-05-22',5);

insert into message(`content`, `create_time`, `source_id`) values('aaaa','2021-06-21',4);

验证

66.190 简单看就是奇数月份在db1库的message表中

select * from message;
+----+---------+-------------+-----------+
| id | content | create_time | source_id |
+----+---------+-------------+-----------+
|  1 | aaaa    | 2021-01-21  |         1 |
|  2 | aaaa    | 2021-03-10  |         1 |
|  3 | aaaa    | 2021-05-21  |         4 |
|  4 | aaaa    | 2021-05-22  |         5 |
+----+---------+-------------+-----------+

66.180 偶数月份在db2中

select * from message;
+----+---------+-------------+-----------+
| id | content | create_time | source_id |
+----+---------+-------------+-----------+
|  1 | aaaa    | 2021-02-01  |         1 |
|  2 | aaaa    | 2021-04-21  |         4 |
|  3 | aaaa    | 2021-06-21  |         4 |
+----+---------+-------------+-----------+

分片的结果，message表中的数据根据create_time的值按月进行了分片。小插曲，mysql8.0的客户端连接Mycat时需配置驱动和密码认证方式哟

完结撒花

本文说明，主要技术内容来自互联网技术大佬的分享，还有一些自我的加工(仅仅起到注释说明的作用)。如有相关疑问，请留言，将确认之后，执行侵权必删

请给我的爱人一杯mojito

关注

0
点赞
踩
3

收藏

觉得还不错? 一键收藏
0
评论
(Mycat入门篇)按照月份分布数据

背景按自然月分片的规则写在前面原文链接http://www.fullstackyang.com/mycatxi-lie-zhi-yi-shu-ju-fen-pian-ru-men-shi-zhan/link和参考文档不同之处源文档：现在假设系统的数据库为messagedb，里面只有2张表，一张表为消息表：message，一张表示消息来源的字典表：source 一共3个mysql实例各自需要创建4个数据库本文:一共2个mysql实例各自1个库。mysql版本5.7 mycat
复制链接

扫一扫