MyCat怎么分库分表

2025-03-22 技术教程

本篇内容介绍了“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;

node2

createdatabasetestdb13;createdatabasetestdb14;createdatabasetestdb15;

node3

createdatabasetestdb25;createdatabasetestdb26;createdatabasetestdb27;

二、配置schema.xml

<?xmlversion="1.0"?><!DOCTYPEmycat:schemaSYSTEM"schema.dtd"><mycat:schemaxmlns:mycat="http://io.mycat/"><schemaname="mycatdb"checkSQLschema="false"sqlMaxLimit="100"><tablename="user03"dataNode="dn$1-3,dn$13-15,dn$25-27"rule="auto-sharding-long"></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"/><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="auto-sharding-long"><rule><columns>id</columns><algorithm>autopartition-long-user03</algorithm></rule></tableRule><functionname="autopartition-long-user03"class="io.mycat.route.function.AutoPartitionByLong"><propertyname="mapFile">autopartition-long-user03.txt</property><propertyname="defaultNode">0</property></function></mycat:rule>

四、配置autopartition-long-user03.txt

1-10=011-20=121-30=231-40=341-50=451-60=561-70=671-80=781-10000=8

五、配置server.xml

<username="root"defaultAccount="true"><propertyname="password">mysql</property><propertyname="schemas">mycatdb</property></user>

六、启动mycat

/usr/local/mycat/bin/mycatstart

查看mycat日志

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

七、登录MySQL并查看逻辑表

mysql>usemycatdb;ReadingtableinformationforcompletionoftableandcolumnnamesYoucanturnoffthisfeaturetogetaquickerstartupwith-ADatabasechangedmysql>showtables;+-------------------+|Tablesinmycatdb|+-------------------+|order01||orderdetail01||user01||user02||user03|+-------------------+5rowsinset(0.00sec)mysql>mysql>mysql>droptableifexistsuser03;QueryOK,0rowsaffected,1warning(0.23sec)mysql>createtableuser03(->idintnotnullauto_increment,->namevarchar(64),->primarykey(id)->);QueryOK,0rowsaffected(0.43sec)

八、插入测试数据

共插入50条数据

insertintouser03(id,name)values(1,'steven');insertintouser03(id,name)values(2,'steven');insertintouser03(id,name)values(3,'steven');insertintouser03(id,name)values(4,'steven');insertintouser03(id,name)values(5,'steven');insertintouser03(id,name)values(11,'steven');insertintouser03(id,name)values(12,'steven');insertintouser03(id,name)values(13,'steven');insertintouser03(id,name)values(14,'steven');insertintouser03(id,name)values(15,'steven');insertintouser03(id,name)values(21,'steven');insertintouser03(id,name)values(22,'steven');insertintouser03(id,name)values(23,'steven');insertintouser03(id,name)values(24,'steven');insertintouser03(id,name)values(25,'steven');insertintouser03(id,name)values(31,'steven');insertintouser03(id,name)values(32,'steven');insertintouser03(id,name)values(33,'steven');insertintouser03(id,name)values(34,'steven');insertintouser03(id,name)values(35,'steven');insertintouser03(id,name)values(41,'steven');insertintouser03(id,name)values(42,'steven');insertintouser03(id,name)values(43,'steven');insertintouser03(id,name)values(44,'steven');insertintouser03(id,name)values(45,'steven');insertintouser03(id,name)values(51,'steven');insertintouser03(id,name)values(52,'steven');insertintouser03(id,name)values(53,'steven');insertintouser03(id,name)values(54,'steven');insertintouser03(id,name)values(55,'steven');insertintouser03(id,name)values(61,'steven');insertintouser03(id,name)values(62,'steven');insertintouser03(id,name)values(63,'steven');insertintouser03(id,name)values(64,'steven');insertintouser03(id,name)values(65,'steven');insertintouser03(id,name)values(71,'steven');insertintouser03(id,name)values(72,'steven');insertintouser03(id,name)values(73,'steven');insertintouser03(id,name)values(74,'steven');insertintouser03(id,name)values(75,'steven');insertintouser03(id,name)values(81,'steven');insertintouser03(id,name)values(82,'steven');insertintouser03(id,name)values(83,'steven');insertintouser03(id,name)values(84,'steven');insertintouser03(id,name)values(85,'steven');insertintouser03(id,name)values(91,'steven');insertintouser03(id,name)values(92,'steven');insertintouser03(id,name)values(93,'steven');insertintouser03(id,name)values(94,'steven');insertintouser03(id,name)values(95,'steven');

九、数据验证

三个node只在testdb01-03,testdb13-15，testdb25-27，所以除了这9个物理库之外，其他库查不到分片。

下面在三个node分别验证出分片信息：

node1

mysql>selectcount(*)fromtestdb01.user03;+----------+|count(*)|+----------+|5|+----------+1rowinset(0.00sec)mysql>selectcount(*)fromtestdb02.user03;+----------+|count(*)|+----------+|5|+----------+1rowinset(0.00sec)mysql>selectcount(*)fromtestdb03.user03;+----------+|count(*)|+----------+|5|+----------+1rowinset(0.00sec)

node2

mysql>selectcount(*)fromtestdb13.user03;+----------+|count(*)|+----------+|5|+----------+1rowinset(0.00sec)mysql>selectcount(*)fromtestdb14.user03;+----------+|count(*)|+----------+|5|+----------+1rowinset(0.00sec)mysql>selectcount(*)fromtestdb15.user03;+----------+|count(*)|+----------+|5|+----------+1rowinset(0.00sec)

node3

mysql>selectcount(*)fromtestdb25.user03;+----------+|count(*)|+----------+|5|+----------+1rowinset(0.00sec)mysql>selectcount(*)fromtestdb26.user03;+----------+|count(*)|+----------+|5|+----------+1rowinset(0.00sec)mysql>selectcount(*)fromtestdb27.user03;+----------+|count(*)|+----------+|10|+----------+1rowinset(0.00sec)

“MyCat怎么分库分表”的内容就介绍到这里了，感谢大家的阅读。如果想了解更多行业相关的知识可以关注亿速云网站，小编将为大家输出更多高质量的实用文章！