在这一期的小窍门里, 分享一个简单好用的分库小中间件, mycat
rules.xml (请增量式的修改, 不建议随意删除自带算法)
这里声明了算法的监控字段, 注意这个sBeginDate, 要和后面配置文件的相对应, 不能早于或者晚于
<?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>gtDate</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-10-01</property>
<!-- <property name="sEndDate">2022-4-30</property> -->
</function>
</mycat:rule>
schema.xml
这里定义了 表/库 是如何路由的.
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<schema name="saas_logical_db" checkSQLschema="false" sqlMaxLimit="100">
<table name="gpsmessage" subTables="gpsmessage_2021$10-12,gpsmessage_2022$01-04" dataNode="dn1" rule="sharding-by-month" />
</schema>
<dataNode name="dn1" dataHost="dataHost01" database="testdb"/>
<dataHost name="dataHost01" maxCon="1000" minCon="10" balance="0"
writeType="0" dbType="mysql" dbDriver="native">
<heartbeat>select 1</heartbeat>
<writeHost host="hostM1" url="127.0.0.1:3306" user="root" password="123456"/>
</dataHost>
</mycat:schema>
表创建语句, 大家可以换成自己的表
create table testdb.gpsmessage_2021+(月份, 单位数月份不要加0前缀 例如1而不是01)
(
id bigint(18) not null,
deviceNo varchar(26) null,
longitude decimal(20, 8) null,
latitude decimal(20, 8) null,
speed int(5) null,
direction int(5) null,
gtDate datetime null,
createDate datetime null,
uploadDate datetime null,
provider varchar(20) null,
rawLongitude decimal(20, 8) null,
rawLatitude decimal(20, 8) null,
gpsId varchar(50) null,
inputDate datetime null,
constraint GPSMessage_id_uindex
unique (id)
);
alter table testdb.gpsmessage_202113
add primary key (id);