MyCat分库分表实例教程

2025-03-12 技术教程

这篇文章主要介绍“MyCat分库分表实例教程”，在日常操作中，相信很多人在MyCat分库分表实例教程问题上存在疑惑，小编查阅了各式资料，整理出简单好用的操作方法，希望对大家解答”MyCat分库分表实例教程”的疑惑有所帮助！接下来，请跟着小编一起来学习吧！

项目环境：

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="user06"dataNode="dn$1-36"rule="sharding-by-month-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-month-adddate"><rule><columns>adddate</columns><algorithm>partbymonth</algorithm></rule></tableRule><functionname="partbymonth"class="io.mycat.route.function.PartitionByMonth"><propertyname="dateFormat">yyyy-MM-dd</property><propertyname="sBeginDate">2016-01-01</property></function>

四、启动mycat

/usr/local/mycat/bin/mycatstart

查看mycat日志

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

五、登录MySQL查看逻辑表

mysql-uroot-pmysql-P8066-h292.168.8.30

mysql>showdatabases;+----------+|DATABASE|+----------+|mycatdb|+----------+1rowinset(0.03sec)mysql>usemycatdb;ReadingtableinformationforcompletionoftableandcolumnnamesYoucanturnoffthisfeaturetogetaquickerstartupwith-ADatabasechangedmysql>showtables;+-------------------+|Tablesinmycatdb|+-------------------+|user04||user05||user06|+-------------------+3rowsinset(0.01sec)mysql>select*fromuser06;ERROR1105(HY000):Table'testdb36.user06'doesn'texistmysql>droptableifexistsuser06;QueryOK,0rowsaffected,1warning(0.38sec)mysql>createtableuser06(->idintnotnullauto_increment,->namevarchar(64),->adddatedatetime,->primarykey(id)->);QueryOK,0rowsaffected(1.73sec)

六、插入测试数据

insertintouser06(id,name,adddate)values(1,'steven','2016-05-0912:32:12');insertintouser06(id,name,adddate)values(2,'steven','2016-09-0912:32:12');insertintouser06(id,name,adddate)values(3,'steven','2016-12-0912:32:12');insertintouser06(id,name,adddate)values(4,'steven','2017-05-0912:32:12');insertintouser06(id,name,adddate)values(5,'steven','2017-06-0912:32:12');insertintouser06(id,name,adddate)values(6,'steven','2017-07-0912:32:12');insertintouser06(id,name,adddate)values(7,'steven','2018-05-0912:32:12');insertintouser06(id,name,adddate)values(8,'steven','2018-05-0912:32:12');insertintouser06(id,name,adddate)values(9,'steven','2018-08-0912:32:12');insertintouser06(id,name,adddate)values(10,'steven','2018-12-0912:32:12');

七、验证数据

node1

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

node2

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

node3

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

到此，关于“MyCat分库分表实例教程”的学习就结束了，希望能够解决大家的疑惑。理论与实践的搭配能更好的帮助大家学习，快去试试吧！若想继续学习更多相关知识，请继续关注亿速云网站，小编会继续努力为大家带来更多实用的文章！