Chinaunix首页 | 论坛 | 认证专区 | 博客 登录 | 注册

东南西北风andyhuang.blog.chinaunix.net

脚踏实地,一步一个脚印!

  • 博客访问: 2665891
  • 博文数量: 687
  • 博客积分: 8199
  • 博客等级: 中将
  • 技术积分: 9589
  • 用 户 组: 普通用户
  • 注册时间: 2010-05-25 20:19
个人简介

脚踏实地、勇往直前!

文章分类

全部博文(687)

文章存档

2017年(66)

2016年(48)

2015年(50)

2014年(169)

2013年(52)

2012年(177)

2011年(93)

2010年(30)

微信关注

IT168企业级官微



微信号:IT168qiye



系统架构师大会



微信号:SACC2013

订阅
热词专题
mycat分库规则sharding-by-date 2017-08-10 15:19:39

分类: Mysql/postgreSQL


1.rule.xml

点击(此处)折叠或打开

  1. <tableRule name="sharding-by-date-test">
  2. <rule>
  3.        <columns>createtime</columns>
  4.        <algorithm>sharding-by-date</algorithm>
  5. </rule>
  6. </tableRule>
  7. <function name="sharding-by-date" class="io.mycat.route.function.PartitionByDate">
  8.     <property name="dateFormat">yyyy-MM-dd</property>
  9.     <property name="sBeginDate">2016-01-01</property>
  10.     <property name="sPartionDay">10</property>
  11. </function>
分片日期从2016-01-01开始,每10天一个分片。

2.schema.xml

点击(此处)折叠或打开

  1. <!DOCTYPE mycat:schema SYSTEM "schema.dtd">
  2. <mycat:schema xmlns:mycat="http://io.mycat/">

  3.         <schema name="logdb" checkSQLschema="false" sqlMaxLimit="100">
  4.           <table name="tb_log_t" primaryKey="ID" autoIncrement="true" dataNode="dn1,dn2,dn3,dn4,dn5,dn6" rule="mod-long" />
  5.           <table name="tb_user_t" dataNode="dn1,dn2,dn3,dn4,dn5,dn6" rule="sharding-by-intfile-provcode" />
  6.           <table name="tb_user_detail_t" dataNode="dn1,dn2,dn3,dn4,dn5,dn6" rule="auto-sharding-long-userid" />
  7.           <table name="tb_user_murmur_t" dataNode="dn1,dn2,dn3,dn4,dn5,dn6" rule="sharding-by-murmur" />
  8.           <table name="tb_user_murmur_string_t" dataNode="dn1,dn2,dn3,dn4,dn5,dn6" rule="sharding-by-murmur-userid" />
  9.           <table name="tb_user_crc32slot_t" dataNode="dn1,dn2,dn3,dn4,dn5,dn6" rule="crc32slot" />
  10.           <table name="tb_user_partbymonth_t" dataNode="dn$1-13" rule="sharding-by-month" />
  11.           <table name="tb_user_latestmonth_t" dataNode="dn1,dn2,dn3,dn4,dn5,dn6" rule="latest-month-calldate" />
  12.           <!-- <table name="t_subtable" primaryKey="id" autoIncrement="true" subTables="t_subtable_$(20170730,20170806)" dataNode="d
  13. n1"/> -->
  14.         <table name="tb_user_shardingbydate_t" dataNode="dn1,dn2,dn3,dn4,dn5,dn6" rule="sharding-by-date-test" />
  15.         </schema>
  16.         <schema name="coss03" checkSQLschema="false" sqlMaxLimit="100" dataNode="dnoss03"/>
  17.         <dataNode name="dn1" dataHost="localhost1" database="log01" />
  18.         <dataNode name="dn2" dataHost="localhost1" database="log02" />
  19.         <dataNode name="dn3" dataHost="localhost1" database="log03" />
  20.         <dataNode name="dn4" dataHost="localhost1" database="log04" />
  21.         <dataNode name="dn5" dataHost="localhost1" database="log05" />
  22.         <dataNode name="dn6" dataHost="localhost1" database="log06" />
  23.         <dataNode name="dn7" dataHost="localhost1" database="log01" />
  24.         <dataNode name="dn8" dataHost="localhost1" database="log02" />
  25.         <dataNode name="dn9" dataHost="localhost1" database="log03" />
  26.         <dataNode name="dn10" dataHost="localhost1" database="log04" />
  27.         <dataNode name="dn11" dataHost="localhost1" database="log05" />
  28.         <dataNode name="dn12" dataHost="localhost1" database="log06" />
  29.         <dataNode name="dn13" dataHost="localhost1" database="log01" />
  30.         <dataNode name="dnoss03" dataHost="localhost1" database="oss03" />
  31.         <dataHost name="localhost1" maxCon="1000" minCon="10" balance="0"
  32.                           writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
  33.                 <heartbeat>select user()</heartbeat>
  34.                 <!-- can have multi write hosts -->
  35.                 <writeHost host="hostM1" url="192.168.56.141:3306" user="root"
  36.                                    password="mysql">
  37.                         <!-- can have multi read hosts -->
  38.                         <readHost host="hostS2" url="192.168.56.142:3306" user="root" password="mysql" />
  39.                 </writeHost>
  40.         </dataHost>
  41. </mycat:schema>

3.创建表
CREATE TABLE `tb_user_shardingbydate_t` (
  `id` varchar(32) NOT NULL,
  `name` varchar(64) DEFAULT NULL,
  `createtime` varchar(10)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

写入数据:
insert into tb_user_shardingbydate_t(id,name,createtime) values('a0001','name1','2016-01-01');
insert into tb_user_shardingbydate_t(id,name,createtime) values('a0002','name1','2016-01-02');
insert into tb_user_shardingbydate_t(id,name,createtime) values('a0003','name1','2016-01-03');
insert into tb_user_shardingbydate_t(id,name,createtime) values('a0004','name1','2016-01-04');
insert into tb_user_shardingbydate_t(id,name,createtime) values('a0005','name1','2016-01-05');
insert into tb_user_shardingbydate_t(id,name,createtime) values('a0006','name1','2016-01-06');
insert into tb_user_shardingbydate_t(id,name,createtime) values('a0007','name1','2016-01-07');
insert into tb_user_shardingbydate_t(id,name,createtime) values('a0005','name1','2016-01-08');
insert into tb_user_shardingbydate_t(id,name,createtime) values('a0006','name1','2016-01-09');
insert into tb_user_shardingbydate_t(id,name,createtime) values('a0007','name1','2016-01-10');


insert into tb_user_shardingbydate_t(id,name,createtime) values('a0001','name1','2016-01-11');
insert into tb_user_shardingbydate_t(id,name,createtime) values('a0002','name1','2016-01-12');
insert into tb_user_shardingbydate_t(id,name,createtime) values('a0003','name1','2016-01-13');
insert into tb_user_shardingbydate_t(id,name,createtime) values('a0004','name1','2016-01-14');
insert into tb_user_shardingbydate_t(id,name,createtime) values('a0005','name1','2016-01-15');
insert into tb_user_shardingbydate_t(id,name,createtime) values('a0006','name1','2016-01-16');
insert into tb_user_shardingbydate_t(id,name,createtime) values('a0007','name1','2016-01-17');
insert into tb_user_shardingbydate_t(id,name,createtime) values('a0005','name1','2016-01-18');
insert into tb_user_shardingbydate_t(id,name,createtime) values('a0006','name1','2016-01-19');
insert into tb_user_shardingbydate_t(id,name,createtime) values('a0007','name1','2016-01-20');

-- The End --


阅读(2009) | 评论(0) | 转发(1) |
给主人留下些什么吧!~~
评论热议
请登录后评论。

登录 注册