MyCat分库分表--实战09--按单月小时

2025-04-10 技术教程

项目环境：

192.168.8.30 mycat

192.168.8.31 node1

192.168.8.32 node2

192.168.8.33 node3

三个节点MySQL均为单实例

一、创建测试库

node1

createdatabasetestdb01;createdatabasetestdb02;createdatabasetestdb03;createdatabasetestdb04;createdatabasetestdb05;createdatabasetestdb06;createdatabasetestdb07;createdatabasetestdb08;createdatabasetestdb09;createdatabasetestdb10;createdatabasetestdb11;createdatabasetestdb12;

node2

createdatabasetestdb13;createdatabasetestdb14;createdatabasetestdb15;createdatabasetestdb16;createdatabasetestdb17;createdatabasetestdb18;createdatabasetestdb19;createdatabasetestdb20;createdatabasetestdb21;createdatabasetestdb22;createdatabasetestdb23;createdatabasetestdb24;

node3

createdatabasetestdb25;createdatabasetestdb26;createdatabasetestdb27;createdatabasetestdb28;createdatabasetestdb29;createdatabasetestdb30;createdatabasetestdb31;createdatabasetestdb32;createdatabasetestdb33;createdatabasetestdb34;createdatabasetestdb35;createdatabasetestdb36;

二、配置schema.xml

<?xmlversion="1.0"?><!DOCTYPEmycat:schemaSYSTEM"schema.dtd"><mycat:schemaxmlns:mycat="http://io.mycat/"><schemaname="mycatdb"checkSQLschema="false"sqlMaxLimit="100"><tablename="user07"dataNode="dn$1-36"rule="sharding-by-hour-adddate"></table></schema><dataNodename="dn1"dataHost="node1"database="testdb01"/><dataNodename="dn2"dataHost="node1"database="testdb02"/><dataNodename="dn3"dataHost="node1"database="testdb03"/><dataNodename="dn4"dataHost="node1"database="testdb04"/><dataNodename="dn5"dataHost="node1"database="testdb05"/><dataNodename="dn6"dataHost="node1"database="testdb06"/><dataNodename="dn7"dataHost="node1"database="testdb07"/><dataNodename="dn8"dataHost="node1"database="testdb08"/><dataNodename="dn9"dataHost="node1"database="testdb09"/><dataNodename="dn10"dataHost="node1"database="testdb10"/><dataNodename="dn11"dataHost="node1"database="testdb11"/><dataNodename="dn12"dataHost="node1"database="testdb12"/><dataNodename="dn13"dataHost="node2"database="testdb13"/><dataNodename="dn14"dataHost="node2"database="testdb14"/><dataNodename="dn15"dataHost="node2"database="testdb15"/><dataNodename="dn16"dataHost="node2"database="testdb16"/><dataNodename="dn17"dataHost="node2"database="testdb17"/><dataNodename="dn18"dataHost="node2"database="testdb18"/><dataNodename="dn19"dataHost="node2"database="testdb19"/><dataNodename="dn20"dataHost="node2"database="testdb20"/><dataNodename="dn21"dataHost="node2"database="testdb21"/><dataNodename="dn22"dataHost="node2"database="testdb22"/><dataNodename="dn23"dataHost="node2"database="testdb23"/><dataNodename="dn24"dataHost="node2"database="testdb24"/><dataNodename="dn25"dataHost="node3"database="testdb25"/><dataNodename="dn26"dataHost="node3"database="testdb26"/><dataNodename="dn27"dataHost="node3"database="testdb27"/><dataNodename="dn28"dataHost="node3"database="testdb28"/><dataNodename="dn29"dataHost="node3"database="testdb29"/><dataNodename="dn30"dataHost="node3"database="testdb30"/><dataNodename="dn31"dataHost="node3"database="testdb31"/><dataNodename="dn32"dataHost="node3"database="testdb32"/><dataNodename="dn33"dataHost="node3"database="testdb33"/><dataNodename="dn34"dataHost="node3"database="testdb34"/><dataNodename="dn35"dataHost="node3"database="testdb35"/><dataNodename="dn36"dataHost="node3"database="testdb36"/><dataHostname="node1"maxCon="1000"minCon="10"balance="1"writeType="0"dbType="mysql"dbDriver="native"switchType="1"slaveThreshold="100"><heartbeat>selectuser()</heartbeat><writeHosthost="192.168.8.31"url="192.168.8.31:3306"user="root"password="mysql"></writeHost></dataHost><dataHostname="node2"maxCon="1000"minCon="10"balance="1"writeType="0"dbType="mysql"dbDriver="native"switchType="1"slaveThreshold="100"><heartbeat>selectuser()</heartbeat><writeHosthost="192.168.8.32"url="192.168.8.32:3306"user="root"password="mysql"></writeHost></dataHost><dataHostname="node3"maxCon="1000"minCon="10"balance="1"writeType="0"dbType="mysql"dbDriver="native"switchType="1"slaveThreshold="100"><heartbeat>selectuser()</heartbeat><writeHosthost="192.168.8.33"url="192.168.8.33:3306"user="root"password="mysql"></writeHost></dataHost></mycat:schema>

三、配置rule.xml

<tableRulename="sharding-by-hour-adddate"><rule><columns>adddate</columns><algorithm>sharding-by-hour</algorithm></rule></tableRule><functionname="sharding-by-hour"class="io.mycat.route.function.LatestMonthPartion"><propertyname="splitOneDay">24</property></function>

<property name="splitOneDay">24</property>每天24份

四、启动mycat

/usr/local/mycat/bin/mycatstart

查看mycat日志

STATUS|wrapper|2018/11/2312:54:18|-->WrapperStartedasDaemonSTATUS|wrapper|2018/11/2312:54:19|LaunchingaJVM...INFO|jvm1|2018/11/2312:54:19|OpenJDK64-BitServerVMwarning:ignoringoptionMaxPermSize=64M;supportwasremovedin8.0INFO|jvm1|2018/11/2312:54:23|Wrapper(Version3.2.3)http://wrapper.tanukisoftware.orgINFO|jvm1|2018/11/2312:54:23|Copyright1999-2006TanukiSoftware,Inc.AllRightsReserved.INFO|jvm1|2018/11/2312:54:23|INFO|jvm1|2018/11/2312:54:28|MyCATServerstartupsuccessfully.seelogsinlogs/mycat.log

五、登录MySQL查看逻辑表

mysql-uroot-pmysql-P8066-h292.168.8.30

mysql>showdatabases;+----------+|DATABASE|+----------+|mycatdb|+----------+1rowinset(0.02sec)mysql>usemycatdbReadingtableinformationforcompletionoftableandcolumnnamesYoucanturnoffthisfeaturetogetaquickerstartupwith-ADatabasechangedmysql>showtables;+-------------------+|Tablesinmycatdb|+-------------------+|user04||user05||user06||user07|+-------------------+4rowsinset(0.01sec)mysql>droptableifexistsuser07;QueryOK,0rowsaffected,1warning(0.38sec)mysql>createtableuser07(->idintnotnull,->namevarchar(64),->adddatevarchar(10)->);

六、插入测试数据

insertintouser07(id,name,adddate)values(1,'steven','2018110100');insertintouser07(id,name,adddate)values(1,'steven','2018110101');insertintouser07(id,name,adddate)values(1,'steven','2018110102');insertintouser07(id,name,adddate)values(1,'steven','2018110103');insertintouser07(id,name,adddate)values(1,'steven','2018110104');insertintouser07(id,name,adddate)values(1,'steven','2018110105');insertintouser07(id,name,adddate)values(1,'steven','2018110106');insertintouser07(id,name,adddate)values(1,'steven','2018110107');insertintouser07(id,name,adddate)values(1,'steven','2018110108');insertintouser07(id,name,adddate)values(1,'steven','2018110109');insertintouser07(id,name,adddate)values(1,'steven','2018110110');insertintouser07(id,name,adddate)values(1,'steven','2018110111');insertintouser07(id,name,adddate)values(1,'steven','2018110112');insertintouser07(id,name,adddate)values(1,'steven','2018110113');insertintouser07(id,name,adddate)values(1,'steven','2018110114');insertintouser07(id,name,adddate)values(1,'steven','2018110115');insertintouser07(id,name,adddate)values(1,'steven','2018110116');insertintouser07(id,name,adddate)values(1,'steven','2018110117');insertintouser07(id,name,adddate)values(1,'steven','2018110118');insertintouser07(id,name,adddate)values(1,'steven','2018110119');insertintouser07(id,name,adddate)values(1,'steven','2018110120');insertintouser07(id,name,adddate)values(1,'steven','2018110121');insertintouser07(id,name,adddate)values(1,'steven','2018110122');insertintouser07(id,name,adddate)values(1,'steven','2018110123');insertintouser07(id,name,adddate)values(1,'steven','2018110200');insertintouser07(id,name,adddate)values(1,'steven','2018110201');insertintouser07(id,name,adddate)values(1,'steven','2018110202');insertintouser07(id,name,adddate)values(1,'steven','2018110203');insertintouser07(id,name,adddate)values(1,'steven','2018110204');insertintouser07(id,name,adddate)values(1,'steven','2018110205');insertintouser07(id,name,adddate)values(1,'steven','2018110206');insertintouser07(id,name,adddate)values(1,'steven','2018110207');insertintouser07(id,name,adddate)values(1,'steven','2018110208');insertintouser07(id,name,adddate)values(1,'steven','2018110209');insertintouser07(id,name,adddate)values(1,'steven','2018110210');insertintouser07(id,name,adddate)values(1,'steven','2018110211');

执行两次，每条数据插入两份

七、验证数据

node1

mysql>select*fromtestdb01.user07;+----+--------+------------+|id|name|adddate|+----+--------+------------+|1|steven|2018110100||1|steven|2018110100|+----+--------+------------+2rowsinset(0.00sec)mysql>select*fromtestdb02.user07;+----+--------+------------+|id|name|adddate|+----+--------+------------+|1|steven|2018110101||1|steven|2018110101|+----+--------+------------+2rowsinset(0.00sec)mysql>select*fromtestdb03.user07;+----+--------+------------+|id|name|adddate|+----+--------+------------+|1|steven|2018110102||1|steven|2018110102|+----+--------+------------+2rowsinset(0.00sec)mysql>select*fromtestdb04.user07;+----+--------+------------+|id|name|adddate|+----+--------+------------+|1|steven|2018110103||1|steven|2018110103|+----+--------+------------+2rowsinset(0.00sec)mysql>select*fromtestdb05.user07;+----+--------+------------+|id|name|adddate|+----+--------+------------+|1|steven|2018110104||1|steven|2018110104|+----+--------+------------+2rowsinset(0.00sec)mysql>select*fromtestdb06.user07;+----+--------+------------+|id|name|adddate|+----+--------+------------+|1|steven|2018110105||1|steven|2018110105|+----+--------+------------+2rowsinset(0.00sec)mysql>select*fromtestdb07.user07;+----+--------+------------+|id|name|adddate|+----+--------+------------+|1|steven|2018110106||1|steven|2018110106|+----+--------+------------+2rowsinset(0.00sec)mysql>select*fromtestdb08.user07;+----+--------+------------+|id|name|adddate|+----+--------+------------+|1|steven|2018110107||1|steven|2018110107|+----+--------+------------+2rowsinset(0.00sec)mysql>select*fromtestdb09.user07;+----+--------+------------+|id|name|adddate|+----+--------+------------+|1|steven|2018110108||1|steven|2018110108|+----+--------+------------+2rowsinset(0.00sec)mysql>select*fromtestdb10.user07;+----+--------+------------+|id|name|adddate|+----+--------+------------+|1|steven|2018110109||1|steven|2018110109|+----+--------+------------+2rowsinset(0.01sec)mysql>select*fromtestdb11.user07;+----+--------+------------+|id|name|adddate|+----+--------+------------+|1|steven|2018110110||1|steven|2018110110|+----+--------+------------+2rowsinset(0.02sec)mysql>select*fromtestdb12.user07;+----+--------+------------+|id|name|adddate|+----+--------+------------+|1|steven|2018110111||1|steven|2018110111|+----+--------+------------+2rowsinset(0.01sec)

node2

mysql>select*fromtestdb13.user07;+----+--------+------------+|id|name|adddate|+----+--------+------------+|1|steven|2018110112||1|steven|2018110112|+----+--------+------------+2rowsinset(0.00sec)mysql>select*fromtestdb14.user07;+----+--------+------------+|id|name|adddate|+----+--------+------------+|1|steven|2018110113||1|steven|2018110113|+----+--------+------------+2rowsinset(0.00sec)mysql>select*fromtestdb15.user07;+----+--------+------------+|id|name|adddate|+----+--------+------------+|1|steven|2018110114||1|steven|2018110114|+----+--------+------------+2rowsinset(0.00sec)mysql>select*fromtestdb16.user07;+----+--------+------------+|id|name|adddate|+----+--------+------------+|1|steven|2018110115||1|steven|2018110115|+----+--------+------------+2rowsinset(0.00sec)mysql>select*fromtestdb17.user07;+----+--------+------------+|id|name|adddate|+----+--------+------------+|1|steven|2018110116||1|steven|2018110116|+----+--------+------------+2rowsinset(0.00sec)mysql>select*fromtestdb18.user07;+----+--------+------------+|id|name|adddate|+----+--------+------------+|1|steven|2018110117||1|steven|2018110117|+----+--------+------------+2rowsinset(0.00sec)mysql>select*fromtestdb19.user07;+----+--------+------------+|id|name|adddate|+----+--------+------------+|1|steven|2018110118||1|steven|2018110118|+----+--------+------------+2rowsinset(0.00sec)mysql>select*fromtestdb20.user07;+----+--------+------------+|id|name|adddate|+----+--------+------------+|1|steven|2018110119||1|steven|2018110119|+----+--------+------------+2rowsinset(0.00sec)mysql>select*fromtestdb21.user07;+----+--------+------------+|id|name|adddate|+----+--------+------------+|1|steven|2018110120||1|steven|2018110120|+----+--------+------------+2rowsinset(0.00sec)mysql>select*fromtestdb22.user07;+----+--------+------------+|id|name|adddate|+----+--------+------------+|1|steven|2018110121||1|steven|2018110121|+----+--------+------------+2rowsinset(0.00sec)mysql>select*fromtestdb23.user07;+----+--------+------------+|id|name|adddate|+----+--------+------------+|1|steven|2018110122||1|steven|2018110122|+----+--------+------------+2rowsinset(0.00sec)mysql>select*fromtestdb24.user07;+----+--------+------------+|id|name|adddate|+----+--------+------------+|1|steven|2018110123||1|steven|2018110123|+----+--------+------------+2rowsinset(0.00sec)

node3

mysql>select*fromtestdb25.user07;+----+--------+------------+|id|name|adddate|+----+--------+------------+|1|steven|2018110200||1|steven|2018110200|+----+--------+------------+2rowsinset(0.00sec)mysql>select*fromtestdb26.user07;+----+--------+------------+|id|name|adddate|+----+--------+------------+|1|steven|2018110201||1|steven|2018110201|+----+--------+------------+2rowsinset(0.00sec)mysql>select*fromtestdb27.user07;+----+--------+------------+|id|name|adddate|+----+--------+------------+|1|steven|2018110202||1|steven|2018110202|+----+--------+------------+2rowsinset(0.00sec)mysql>select*fromtestdb28.user07;+----+--------+------------+|id|name|adddate|+----+--------+------------+|1|steven|2018110203||1|steven|2018110203|+----+--------+------------+2rowsinset(0.02sec)mysql>select*fromtestdb29.user07;+----+--------+------------+|id|name|adddate|+----+--------+------------+|1|steven|2018110204||1|steven|2018110204|+----+--------+------------+2rowsinset(0.00sec)mysql>select*fromtestdb30.user07;+----+--------+------------+|id|name|adddate|+----+--------+------------+|1|steven|2018110205||1|steven|2018110205|+----+--------+------------+2rowsinset(0.00sec)mysql>select*fromtestdb31.user07;+----+--------+------------+|id|name|adddate|+----+--------+------------+|1|steven|2018110206||1|steven|2018110206|+----+--------+------------+2rowsinset(0.00sec)mysql>select*fromtestdb32.user07;+----+--------+------------+|id|name|adddate|+----+--------+------------+|1|steven|2018110207||1|steven|2018110207|+----+--------+------------+2rowsinset(0.00sec)mysql>select*fromtestdb33.user07;+----+--------+------------+|id|name|adddate|+----+--------+------------+|1|steven|2018110208||1|steven|2018110208|+----+--------+------------+2rowsinset(0.00sec)mysql>select*fromtestdb34.user07;+----+--------+------------+|id|name|adddate|+----+--------+------------+|1|steven|2018110209||1|steven|2018110209|+----+--------+------------+2rowsinset(0.00sec)mysql>select*fromtestdb35.user07;+----+--------+------------+|id|name|adddate|+----+--------+------------+|1|steven|2018110210||1|steven|2018110210|+----+--------+------------+2rowsinset(0.00sec)mysql>select*fromtestdb36.user07;+----+--------+------------+|id|name|adddate|+----+--------+------------+|1|steven|2018110211||1|steven|2018110211|+----+--------+------------+2rowsinset(0.00sec)

72条记录，按照时间先后顺序分布在36个分片中，验证完毕。

注意：rule.xml中配置的一天分成24份，一份是1小时，我们共36个分片，所以插入的数据只能限制在每月月初的36小时之内，如2018100100-2018100211