背景
按自然月分片的规则
写在前面
原文链接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时需配置驱动和密码认证方式哟
完结撒花
本文说明,主要技术内容来自互联网技术大佬的分享,还有一些自我的加工(仅仅起到注释说明的作用)。如有相关疑问,请留言,将确认之后,执行侵权必删