MyCat分库分表--实战07--按日期天

2025-04-11 技术教程

项目环境：

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="user05"dataNode="dn$1-36"rule="sharding-by-date-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

<mycat:rulexmlns:mycat="http://io.mycat/"><tableRulename="sharding-by-date-adddate"><rule><columns>adddate</columns><algorithm>sharding-by-date</algorithm></rule></tableRule><functionname="sharding-by-date"class="io.mycat.route.function.PartitionByDate"><propertyname="dateFormat">yyyy-MM-dd</property><propertyname="sBeginDate">2010-01-01</property><propertyname="sEndDate">2020-12-31</property><propertyname="sPartionDay">120</property></function>

四、启动mycat

/usr/local/mycat/bin/mycatstart

查看mycat日志

STATUS|wrapper|2018/11/2219:03:02|-->WrapperStartedasDaemonSTATUS|wrapper|2018/11/2219:03:02|LaunchingaJVM...INFO|jvm1|2018/11/2219:03:02|OpenJDK64-BitServerVMwarning:ignoringoptionMaxPermSize=64M;supportwasremovedin8.0INFO|jvm1|2018/11/2219:03:04|Wrapper(Version3.2.3)http://wrapper.tanukisoftware.orgINFO|jvm1|2018/11/2219:03:04|Copyright1999-2006TanukiSoftware,Inc.AllRightsReserved.INFO|jvm1|2018/11/2219:03:04|INFO|jvm1|2018/11/2219:03:08|MyCATServerstartupsuccessfully.seelogsinlogs/mycat.log

五、登录MySQL查看逻辑表

mysql-uroot-pmysql-P8066-h292.168.8.30

mysql>showdatabases;+----------+|DATABASE|+----------+|mycatdb|+----------+1rowinset(0.02sec)mysql>usemycatdb;ReadingtableinformationforcompletionoftableandcolumnnamesYoucanturnoffthisfeaturetogetaquickerstartupwith-ADatabasechangedmysql>showtables;+-------------------+|Tablesinmycatdb|+-------------------+|user04||user05|+-------------------+2rowsinset(0.00sec)mysql>droptableifexistsuser05;QueryOK,0rowsaffected,1warning(0.57sec)mysql>createtableuser05(->idintnotnullauto_increment,->namevarchar(64),->adddatedatetime,->primarykey(id)->);QueryOK,0rowsaffected(1.56sec)

六、插入测试数据

插入10条记录

insertintouser05(id,name,adddate)values(1,'steven','2011-05-0912:32:12');insertintouser05(id,name,adddate)values(2,'steven','2012-05-0912:32:12');insertintouser05(id,name,adddate)values(3,'steven','2013-05-0912:32:12');insertintouser05(id,name,adddate)values(4,'steven','2014-05-0912:32:12');insertintouser05(id,name,adddate)values(5,'steven','2015-05-0912:32:12');insertintouser05(id,name,adddate)values(6,'steven','2016-05-0912:32:12');insertintouser05(id,name,adddate)values(7,'steven','2017-05-0912:32:12');insertintouser05(id,name,adddate)values(8,'steven','2018-05-0912:32:12');insertintouser05(id,name,adddate)values(9,'steven','2019-05-0912:32:12');insertintouser05(id,name,adddate)values(10,'steven','2020-05-0912:32:12');

七、验证数据

node1

mysql>select*fromtestdb01.user05;Emptyset(0.00sec)mysql>select*fromtestdb02.user05;Emptyset(0.00sec)mysql>select*fromtestdb04.user05;Emptyset(0.00sec)mysql>select*fromtestdb05.user05;+----+--------+---------------------+|id|name|adddate|+----+--------+---------------------+|1|steven|2011-05-0912:32:12|+----+--------+---------------------+1rowinset(0.00sec)mysql>select*fromtestdb06.user05;Emptyset(0.01sec)mysql>select*fromtestdb01.user05;Emptyset(0.00sec)mysql>select*fromtestdb02.user05;Emptyset(0.00sec)mysql>select*fromtestdb03.user05;Emptyset(0.00sec)mysql>select*fromtestdb04.user05;Emptyset(0.00sec)mysql>select*fromtestdb05.user05;+----+--------+---------------------+|id|name|adddate|+----+--------+---------------------+|1|steven|2011-05-0912:32:12|+----+--------+---------------------+1rowinset(0.00sec)mysql>select*fromtestdb06.user05;Emptyset(0.01sec)mysql>select*fromtestdb07.user05;Emptyset(0.00sec)mysql>select*fromtestdb08.user05;+----+--------+---------------------+|id|name|adddate|+----+--------+---------------------+|2|steven|2012-05-0912:32:12|+----+--------+---------------------+1rowinset(0.00sec)mysql>select*fromtestdb09.user05;Emptyset(0.01sec)mysql>select*fromtestdb10.user05;Emptyset(0.00sec)mysql>select*fromtestdb11.user05;+----+--------+---------------------+|id|name|adddate|+----+--------+---------------------+|3|steven|2013-05-0912:32:12|+----+--------+---------------------+1rowinset(0.00sec)mysql>select*fromtestdb12.user05;Emptyset(0.00sec)

node2

mysql>select*fromtestdb13.user05;Emptyset(0.00sec)mysql>select*fromtestdb14.user05;+----+--------+---------------------+|id|name|adddate|+----+--------+---------------------+|4|steven|2014-05-0912:32:12|+----+--------+---------------------+1rowinset(0.00sec)mysql>select*fromtestdb15.user05;Emptyset(0.00sec)mysql>select*fromtestdb16.user05;Emptyset(0.00sec)mysql>select*fromtestdb17.user05;+----+--------+---------------------+|id|name|adddate|+----+--------+---------------------+|5|steven|2015-05-0912:32:12|+----+--------+---------------------+1rowinset(0.00sec)mysql>select*fromtestdb18.user05;Emptyset(0.00sec)mysql>select*fromtestdb19.user05;Emptyset(0.00sec)mysql>select*fromtestdb20.user05;+----+--------+---------------------+|id|name|adddate|+----+--------+---------------------+|6|steven|2016-05-0912:32:12|+----+--------+---------------------+1rowinset(0.00sec)mysql>select*fromtestdb21.user05;Emptyset(0.00sec)mysql>select*fromtestdb22.user05;Emptyset(0.00sec)mysql>select*fromtestdb23.user05;+----+--------+---------------------+|id|name|adddate|+----+--------+---------------------+|7|steven|2017-05-0912:32:12|+----+--------+---------------------+1rowinset(0.01sec)mysql>select*fromtestdb24.user05;Emptyset(0.00sec)

node3

mysql>select*fromtestdb25.user05;Emptyset(0.00sec)mysql>select*fromtestdb26.user05;+----+--------+---------------------+|id|name|adddate|+----+--------+---------------------+|8|steven|2018-05-0912:32:12|+----+--------+---------------------+1rowinset(0.00sec)mysql>select*fromtestdb27.user05;Emptyset(0.00sec)mysql>select*fromtestdb28.user05;Emptyset(0.00sec)mysql>select*fromtestdb29.user05;+----+--------+---------------------+|id|name|adddate|+----+--------+---------------------+|9|steven|2019-05-0912:32:12|+----+--------+---------------------+1rowinset(0.00sec)mysql>select*fromtestdb30.user05;Emptyset(0.00sec)mysql>select*fromtestdb31.user05;Emptyset(0.01sec)mysql>select*fromtestdb32.user05;+----+--------+---------------------+|id|name|adddate|+----+--------+---------------------+|10|steven|2020-05-0912:32:12|+----+--------+---------------------+1rowinset(0.00sec)mysql>select*fromtestdb33.user05;Emptyset(0.01sec)mysql>select*fromtestdb34.user05;Emptyset(0.00sec)mysql>select*fromtestdb35.user05;Emptyset(0.00sec)mysql>select*fromtestdb36.user05;Emptyset(0.01sec)

可以看到10条测试数据按照120天一个分片，按顺序分布在各个数据库中，验证完毕。

遇到的问题：

第一次配置时候，在rule.xml中配置的90天一个分片，于是报错：

Caused by: io.mycat.config.util.ConfigException: Illegal table conf : table [ USER05 ] rule function [ sharding-by-date ] partition size : 45 > table datanode size : 36, please make sure table datanode size = function partition size

排查发现：从2011-01-01到2020-12-31大约4015天，4015/90=44.61111111111111个分片，超出了预设的36个库。

调整以后：4015/120=33.45833333333333个分片，在36个库范围之内。