07、Mycat实战：centos7上使用mycat部署水平分表—按日期分片-程序员快看-教程，程序员编程资料站

接上一篇帖子《MyCat-07之centos7上使用mycat部署水平分表---分片枚举》

一、目标

使用mycat部署按日期水平分片分表。
什么叫按日期分片呢？

按照日期轮询隔几天存放到一个节点服务器，到了天数后再放到另外一台节点服务器，然后一直循环下去，最终实现分片分表。

二、实施mycat按日期分片分表

以下配置均在mycat服务器mycat31上执行

1、修改vim/usr/local/mycat/conf/schema.xml；
添加一行内容

，意为本次试验的按日期分片的表名叫login_log，该表被水平分片到了dn1和dn2两台服务器上，使用的规则叫sharding_by_time。完整的schema.xml代码如下

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

        <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
           <table name="teacher" dataNode="dn2"></table>
           <table name="student" dataNode="dn1,dn2" rule="mod_rule">
               <childTable name="student_record" primaryKey="xid" joinKey="student_id" parentKey="xid" />
           </table>
           <table name="xuser" dataNode="dn1,dn2" type="global"></table>
           <table name="salary" dataNode="dn1,dn2" rule="auto_sharding_long"></table>
           <table name="login_log" dataNode="dn1,dn2" rule="sharding_by_time"></table>
        </schema>
        <dataNode name="dn1" dataHost="host1" database="xkahn" />
        <dataNode name="dn2" dataHost="host2" database="xkahn" />
        <dataHost name="host1" maxCon="1000" minCon="10" balance="0"
                          writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
                <heartbeat>select user()</heartbeat>
                <writeHost host="hostM1" url="10.100.100.31:3306" user="root"
                                   password="123123">
                </writeHost>
        </dataHost>
        <dataHost name="host2" maxCon="1000" minCon="10" balance="0"
                          writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
                <heartbeat>select user()</heartbeat>
                <writeHost host="hostM1" url="10.100.100.32:3306" user="root"
                                   password="123123">
                </writeHost>
        </dataHost>
</mycat:schema>

2、修改/usr/local/mycat/conf/rule.xml；

2-1.添加规则

* * * * <tableRule name="sharding_by_time">
* * * * * * * * <rule>
* * * * * * * * * * * * <columns>logintime</columns>
* * * * * * * * * * * * <algorithm>partitionByTime</algorithm>
* * * * * * * * </rule>
* * * * </tableRule>

注释：算法规则名叫sharding_by_time(与schema.xml中的保持对应)，分片抓取数据的列是logintime(表名在schema.xml中有定义)，算法使用的是partitionByTime(下面会自定义添加，算法名字要保持一致)

2-2.添加算法

        <function name="partitionByTime"
                          class="io.mycat.route.function.PartitionByDate">
                <property name="dateFormat">yyyy-MM-dd</property>
                <property name="sBeginDate">2014-01-01</property>
                <property name="sEndDate">2014-01-04</property>
                <property name="sPartionDay">2</property>
        </function>

注释：
yyyy-MM-dd ---->日期格式年月日
sBeginDate ---->第一条数据如2014-01-01
sPartionDay ---->每隔两天分片一次
sEndDate ---->第二轮循环结束的天是2014-01-04
大概意思是，假定输入是2014-01-01开始，那么1号2号放到第一个节点服务器，3号和4号就放到第二个节点服务器上，这样一轮就结束了，partionDay就是间接的告诉规则，一共两台服务器，走了一轮4条数据了，假定还有5号和6号就轮询的放到第一个节点服务器上，然后就依次类推下去。

3、再打开个mycat31的终端用以启动mycat程序；

cd /usr/local/mycat/bin
./mycat console

4-1.再打开个mycat31的终端用以启动mycat数据管理平台

mysql -umycat -p123456 -h 10.100.100.31 -P 8066
use TESTDB;

4-2.在mycat中创建用于本次试验的表login_log

create table login_log(xid int(10) not null unique primary key,name varchar(20) not null,logintime datetime,issuccess varchar(100),note varchar(500));

4-3.在mycat中插入用于本次的测试数据

insert into login_log(xid,name,logintime,issuccess,note) values (1,"kahn","2014-01-01","success","2014-01-01登录成功了");
insert into login_log(xid,name,logintime,issuccess,note) values (2,"kahn2","2014-01-02","success","2014-01-02登录成功了");
insert into login_log(xid,name,logintime,issuccess,note) values (3,"kahn3","2014-01-03","success","2014-01-03登录成功了");
insert into login_log(xid,name,logintime,issuccess,note) values (4,"kahn4","2014-01-04","success","2014-01-04登录成功了");
insert into login_log(xid,name,logintime,issuccess,note) values (5,"kahn5","2014-01-05","success","2014-01-05登录成功了");
insert into login_log(xid,name,logintime,issuccess,note) values (6,"kahn6","2014-01-06","success","2014-01-06登录成功了");
insert into login_log(xid,name,logintime,issuccess,note) values (7,"kahn7","2020-03-27","success","2020-03-27登录成功了");
insert into login_log(xid,name,logintime,issuccess,note) values (8,"kahn8","2020-03-28","success","2020-03-28登录成功了");
insert into login_log(xid,name,logintime,issuccess,note) values (9,"kahn9","2020-03-29","success","2020-03-29登录成功了");
insert into login_log(xid,name,logintime,issuccess,note) values (10,"kahn10","2020-03-30","success","2020-03-30登录成功了");
insert into login_log(xid,name,logintime,issuccess,note) values (11,"kahn11","2020-03-31","success","2020-03-31登录成功了");
insert into login_log(xid,name,logintime,issuccess,note) values (12,"kahn12","2020-04-01","success","2020-04-01登录成功了");
insert into login_log(xid,name,logintime,issuccess,note) values (13,"kahn13","2014-01-07","success","2014-01-07登录成功了");
insert into login_log(xid,name,logintime,issuccess,note) values (14,"kahn14","2014-01-08","success","2014-01-08登录成功了");
insert into login_log(xid,name,logintime,issuccess,note) values (15,"kahn15","2014-01-09","success","2014-01-09登录成功了");

三、测试效果

1、分别用mycat数据管理平台去select*fromlogin_log;看数据；

2、分别再去两台物理mysql服务器上观察数据是怎么分布的select*fromlogin_log;；

我是分成功了，不知道你怎么样。照例送你一碗鸡汤。

---------------------END---------------2020年3月26日23:06:37---------------------------

---------------------鸡汤：比你优秀的人都比你努力，你有什么资格懒散？---------

07、Mycat实战：centos7上使用mycat部署水平分表---按日期分片

一、目标

二、实施mycat按日期分片分表

三、测试效果