-
MyCat 分表分库分片项目实战水平分库-按日期天分片
规划计算:要分几个库
表里面是2010-2016的数据,7年 ,保留3年到2020年底,3个月一分片,11*12/3 = 44 ,所以分45个库
<function name="sharding-by-date" class="io.mycat.route.function.PartitionByDate">
<property name="dateFormat">yyyy-MM-dd</property> <!-- 日期格式-->
<property name="sBeginDate">2010-01-01</property> <!-- 开始日期-->
<property name="sEndDate">2020-12-31</property> <!-- 结束日期-->
<property name="sPartionDay">90</property> <!-- 每分片天数-->
</function>
create database itpuxdb01 default character set utf8;
create database itpuxdb02 default character set utf8;
create database itpuxdb03 default character set utf8;
create database itpuxdb04 default character set utf8;
create database itpuxdb05 default character set utf8;
create database itpuxdb06 default character set utf8;
create database itpuxdb07 default character set utf8;
create database itpuxdb08 default character set utf8;
create database itpuxdb09 default character set utf8;
create database itpuxdb10 default character set utf8;
create database itpuxdb11 default character set utf8;
create database itpuxdb12 default character set utf8;
create database itpuxdb13 default character set utf8;
create database itpuxdb14 default character set utf8;
create database itpuxdb15 default character set utf8;
create database itpuxdb16 default character set utf8;
create database itpuxdb17 default character set utf8;
create database itpuxdb18 default character set utf8;
create database itpuxdb19 default character set utf8;
create database itpuxdb20 default character set utf8;
create database itpuxdb21 default character set utf8;
create database itpuxdb22 default character set utf8;
create database itpuxdb23 default character set utf8;
create database itpuxdb24 default character set utf8;
create database itpuxdb25 default character set utf8;
create database itpuxdb26 default character set utf8;
create database itpuxdb27 default character set utf8;
create database itpuxdb28 default character set utf8;
create database itpuxdb29 default character set utf8;
create database itpuxdb30 default character set utf8;
create database itpuxdb31 default character set utf8;
create database itpuxdb32 default character set utf8;
create database itpuxdb33 default character set utf8;
create database itpuxdb34 default character set utf8;
create database itpuxdb35 default character set utf8;
create database itpuxdb36 default character set utf8;
create database itpuxdb37 default character set utf8;
create database itpuxdb38 default character set utf8;
create database itpuxdb39 default character set utf8;
create database itpuxdb40 default character set utf8;
create database itpuxdb41 default character set utf8;
create database itpuxdb42 default character set utf8;
create database itpuxdb43 default character set utf8;
create database itpuxdb44 default character set utf8;
create database itpuxdb45 default character set utf8;
vi schema.xml
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<!-- sqlMaxLimit="10000" 显示的多一点 -->
<schema name="mycatdb" checkSQLschema="false" sqlMaxLimit="10000">
<table name="itpux_member" dataNode="dnitpuxdb$1-45" rule="sharding-by-date-joindate"></table>
</schema>
<!-- 设定数据结点 dnitpuxdb1-dnitpuxdb15 对应的 192.168.0.51 服务 以及对应的物理 schema -->
<dataNode name="dnitpuxdb1" dataHost="192.168.0.51" database="itpuxdb01"
/>
<dataNode name="dnitpuxdb2" dataHost="192.168.0.51" database="itpuxdb02"
/>
<dataNode name="dnitpuxdb3" dataHost="192.168.0.51" database="itpuxdb03"
/>
<dataNode name="dnitpuxdb4" dataHost="192.168.0.51" database="itpuxdb04"
/>
<dataNode name="dnitpuxdb5" dataHost="192.168.0.51" database="itpuxdb05"
/>
<dataNode name="dnitpuxdb6" dataHost="192.168.0.51" database="itpuxdb06"
/>
<dataNode name="dnitpuxdb7" dataHost="192.168.0.51" database="itpuxdb07"
/>
<dataNode name="dnitpuxdb8" dataHost="192.168.0.51" database="itpuxdb08"
/>
<dataNode name="dnitpuxdb9" dataHost="192.168.0.51" database="itpuxdb09"/>
<dataNode name="dnitpuxdb10" dataHost="192.168.0.51" database="itpuxdb10" />
<dataNode name="dnitpuxdb11" dataHost="192.168.0.51" database="itpuxdb11" />
<dataNode name="dnitpuxdb12" dataHost="192.168.0.51" database="itpuxdb12" />
<dataNode name="dnitpuxdb13" dataHost="192.168.0.51" database="itpuxdb13" />
<dataNode name="dnitpuxdb14" dataHost="192.168.0.51" database="itpuxdb14" />
<dataNode name="dnitpuxdb15" dataHost="192.168.0.51" database="itpuxdb15" />
<!-- 设定数据结点 dnitpuxdb16-dnitpuxdb30 对应的 192.168.0.52 服务 以及对应的物理 schema -->
<dataNode name="dnitpuxdb16" dataHost="192.168.0.52" database="itpuxdb16" />
<dataNode name="dnitpuxdb17" dataHost="192.168.0.52" database="itpuxdb17" />
<dataNode name="dnitpuxdb18" dataHost="192.168.0.52" database="itpuxdb18" />
<dataNode name="dnitpuxdb19" dataHost="192.168.0.52" database="itpuxdb19" />
<dataNode name="dnitpuxdb20" dataHost="192.168.0.52" database="itpuxdb20" />
<dataNode name="dnitpuxdb21" dataHost="192.168.0.52" database="itpuxdb21" />
<dataNode name="dnitpuxdb22" dataHost="192.168.0.52" database="itpuxdb22" />
<dataNode name="dnitpuxdb23" dataHost="192.168.0.52" database="itpuxdb23" />
<dataNode name="dnitpuxdb24" dataHost="192.168.0.52" database="itpuxdb24" />
<dataNode name="dnitpuxdb25" dataHost="192.168.0.52" database="itpuxdb25" />
<dataNode name="dnitpuxdb26" dataHost="192.168.0.52" database="itpuxdb26" />
<dataNode name="dnitpuxdb27" dataHost="192.168.0.52" database="itpuxdb27" />
<dataNode name="dnitpuxdb28" dataHost="192.168.0.52" database="itpuxdb28" />
<dataNode name="dnitpuxdb29" dataHost="192.168.0.52" database="itpuxdb29" />
<dataNode name="dnitpuxdb30" dataHost="192.168.0.52" database="itpuxdb30" />
<!-- 设定数据结点 dnitpuxdb31-dnitpuxdb45 对应的 192.168.0.53 服务 以及对应的物理 schema -->
<dataNode name="dnitpuxdb31" dataHost="192.168.0.53" database="itpuxdb31" />
<dataNode name="dnitpuxdb32" dataHost="192.168.0.53" database="itpuxdb32" />
<dataNode name="dnitpuxdb33" dataHost="192.168.0.53" database="itpuxdb33" />
<dataNode name="dnitpuxdb34" dataHost="192.168.0.53" database="itpuxdb34" />
<dataNode name="dnitpuxdb35" dataHost="192.168.0.53" database="itpuxdb35" />
<dataNode name="dnitpuxdb36" dataHost="192.168.0.53" database="itpuxdb36" />
<dataNode name="dnitpuxdb37" dataHost="192.168.0.53" database="itpuxdb37" />
<dataNode name="dnitpuxdb38" dataHost="192.168.0.53" database="itpuxdb38" />
<dataNode name="dnitpuxdb39" dataHost="192.168.0.53" database="itpuxdb39" />
<dataNode name="dnitpuxdb40" dataHost="192.168.0.53" database="itpuxdb40" />
<dataNode name="dnitpuxdb41" dataHost="192.168.0.53" database="itpuxdb41" />
<dataNode name="dnitpuxdb42" dataHost="192.168.0.53" database="itpuxdb42" />
<dataNode name="dnitpuxdb43" dataHost="192.168.0.53" database="itpuxdb43" />
<dataNode name="dnitpuxdb44" dataHost="192.168.0.53" database="itpuxdb44" />
<dataNode name="dnitpuxdb45" dataHost="192.168.0.53" database="itpuxdb45" />
<dataHost name="192.168.0.51" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native">
<heartbeat>select user()</heartbeat>
<!-- can haVe multi write hosts -->
<writeHost host="192.168.0.51" url="192.168.0.51:3306" user="root" password="root" />
</dataHost>
<dataHost name="192.168.0.52" maxCon="1000" minCon="10" balance="1" writeType