MyCat怎么实现分库分表

2025-03-12 技术教程

本篇内容介绍了“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="user04"dataNode="dn$1-3,dn$13-15,dn$25-27"rule="sharding-by-murmur-user04-id"></table></schema><dataNodename="dn1"dataHost="node1"database="testdb01"/><dataNodename="dn2"dataHost="node1"database="testdb02"/><dataNodename="dn3"dataHost="node1"database="testdb03"/><dataNodename="dn13"dataHost="node2"database="testdb13"/><dataNodename="dn14"dataHost="node2"database="testdb14"/><dataNodename="dn15"dataHost="node2"database="testdb15"/><dataNodename="dn25"dataHost="node3"database="testdb25"/><dataNodename="dn26"dataHost="node3"database="testdb26"/><dataNodename="dn27"dataHost="node3"database="testdb27"/><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-murmur-user04-id"><rule><columns>id</columns><algorithm>murmur-id</algorithm></rule></tableRule><functionname="murmur-id"class="io.mycat.route.function.PartitionByMurmurHash"><propertyname="seed">0</property><propertyname="type">0</property><propertyname="count">9</property><propertyname="virtualBucketTimes">160</property></function>

四、配置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/2216:48:27|-->WrapperStartedasDaemonSTATUS|wrapper|2018/11/2216:48:27|LaunchingaJVM...INFO|jvm1|2018/11/2216:48:27|OpenJDK64-BitServerVMwarning:ignoringoptionMaxPermSize=64M;supportwasremovedin8.0INFO|jvm1|2018/11/2216:48:29|Wrapper(Version3.2.3)http://wrapper.tanukisoftware.orgINFO|jvm1|2018/11/2216:48:29|Copyright1999-2006TanukiSoftware,Inc.AllRightsReserved.INFO|jvm1|2018/11/2216:48:29|INFO|jvm1|2018/11/2216:48:34|MyCATServerstartupsuccessfully.seelogsinlogs/mycat.log

六、登录MySQL查看逻辑表

mysql-uroot-pmysql-P8066-h292.168.8.30

mysql>showdatabases;+----------+|DATABASE|+----------+|mycatdb|+----------+1rowinset(0.00sec)mysql>usemycatdb;ReadingtableinformationforcompletionoftableandcolumnnamesYoucanturnoffthisfeaturetogetaquickerstartupwith-ADatabasechangedmysql>showtables;+-------------------+|Tablesinmycatdb|+-------------------+|order01||orderdetail01||user01||user02||user03||user04|+-------------------+6rowsinset(0.01sec)mysql>select*fromuser04;ERROR1105(HY000):Table'testdb03.user04'doesn'texistmysql>droptableifexistsuser04;QueryOK,0rowsaffected,1warning(0.15sec)mysql>createtableuser04(->idintnotnullauto_increment,->namevarchar(64),->primarykey(id)->);QueryOK,0rowsaffected(0.61sec)

七、插入测试数据

insertintouser04(id,name)values(1,'steven');insertintouser04(id,name)values(2,'steven');insertintouser04(id,name)values(3,'steven');insertintouser04(id,name)values(4,'steven');insertintouser04(id,name)values(5,'steven');insertintouser04(id,name)values(6,'steven');insertintouser04(id,name)values(7,'steven');insertintouser04(id,name)values(8,'steven');insertintouser04(id,name)values(9,'steven');insertintouser04(id,name)values(10,'steven');insertintouser04(id,name)values(11,'steven');insertintouser04(id,name)values(12,'steven');insertintouser04(id,name)values(13,'steven');insertintouser04(id,name)values(14,'steven');insertintouser04(id,name)values(15,'steven');insertintouser04(id,name)values(16,'steven');insertintouser04(id,name)values(17,'steven');insertintouser04(id,name)values(18,'steven');insertintouser04(id,name)values(19,'steven');insertintouser04(id,name)values(20,'steven');insertintouser04(id,name)values(21,'steven');insertintouser04(id,name)values(22,'steven');insertintouser04(id,name)values(23,'steven');insertintouser04(id,name)values(24,'steven');insertintouser04(id,name)values(25,'steven');insertintouser04(id,name)values(26,'steven');insertintouser04(id,name)values(27,'steven');insertintouser04(id,name)values(28,'steven');insertintouser04(id,name)values(29,'steven');insertintouser04(id,name)values(30,'steven');insertintouser04(id,name)values(31,'steven');insertintouser04(id,name)values(32,'steven');insertintouser04(id,name)values(33,'steven');insertintouser04(id,name)values(34,'steven');insertintouser04(id,name)values(35,'steven');insertintouser04(id,name)values(36,'steven');insertintouser04(id,name)values(37,'steven');insertintouser04(id,name)values(38,'steven');insertintouser04(id,name)values(39,'steven');insertintouser04(id,name)values(40,'steven');insertintouser04(id,name)values(41,'steven');insertintouser04(id,name)values(42,'steven');insertintouser04(id,name)values(43,'steven');insertintouser04(id,name)values(44,'steven');insertintouser04(id,name)values(45,'steven');insertintouser04(id,name)values(46,'steven');insertintouser04(id,name)values(47,'steven');insertintouser04(id,name)values(48,'steven');insertintouser04(id,name)values(49,'steven');insertintouser04(id,name)values(50,'steven');

八、验证数据

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

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

node1

mysql>select*fromtestdb01.user04;+----+--------+|id|name|+----+--------+|8|steven||14|steven||16|steven||17|steven||34|steven||49|steven|+----+--------+6rowsinset(0.00sec)mysql>select*fromtestdb02.user04;+----+--------+|id|name|+----+--------+|9|steven||10|steven||44|steven||45|steven||46|steven||48|steven|+----+--------+6rowsinset(0.00sec)mysql>select*fromtestdb03.user04;+----+--------+|id|name|+----+--------+|11|steven||24|steven||33|steven||35|steven||40|steven|+----+--------+5rowsinset(0.00sec)

node2

mysql>select*fromtestdb13.user04;+----+--------+|id|name|+----+--------+|20|steven||25|steven||38|steven||39|steven|+----+--------+4rowsinset(0.00sec)mysql>select*fromtestdb14.user04;+----+--------+|id|name|+----+--------+|1|steven||41|steven||50|steven|+----+--------+3rowsinset(0.01sec)mysql>select*fromtestdb15.user04;+----+--------+|id|name|+----+--------+|12|steven||18|steven||32|steven||36|steven|+----+--------+4rowsinset(0.00sec)

node3

mysql>select*fromtestdb25.user04;+----+--------+|id|name|+----+--------+|6|steven||13|steven||19|steven||23|steven||27|steven||28|steven||29|steven||31|steven||37|steven|+----+--------+9rowsinset(0.00sec)mysql>select*fromtestdb26.user04;+----+--------+|id|name|+----+--------+|4|steven||5|steven||15|steven||22|steven||42|steven|+----+--------+5rowsinset(0.00sec)mysql>select*fromtestdb27.user04;+----+--------+|id|name|+----+--------+|2|steven||3|steven||7|steven||21|steven||26|steven||30|steven||43|steven||47|steven|+----+--------+8rowsinset(0.00sec)

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