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

项目环境：

192.168.8.30 mycat

192.168.8.31 node1

192.168.8.32 node2

192.168.8.33 node3

三个节点MySQL均为单实例

一、配置schema.xml

<?xmlversion="1.0"?><!DOCTYPEmycat:schemaSYSTEM"schema.dtd"><mycat:schemaxmlns:mycat="http://io.mycat/"><schemaname="mycatdb"checkSQLschema="false"sqlMaxLimit="100"><tablename="order01"dataNode="dn$1-3,dn$13-15,dn$25-27"rule="mod-long"><childTablename="orderdetail01"primaryKey="orderdetail_id"joinKey="order_id"parentKey="order_id"/></table></schema><!--<dataNodename="dn1$0-743"dataHost="localhost1"database="db$0-743"/>--><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="dn4"dataHost="sequoiadb1"database="SAMPLE"/><dataNodename="jdbc_dn1"dataHost="jdbchost"database="db1"/><dataNodename="jdbc_dn2"dataHost="jdbchost"database="db2"/><dataNodename="jdbc_dn3"dataHost="jdbchost"database="db3"/>--><dataHostname="node1"maxCon="1000"minCon="10"balance="1"writeType="0"dbType="mysql"dbDriver="native"switchType="1"slaveThreshold="100"><heartbeat>selectuser()</heartbeat><!--canhavemultiwritehosts--><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><!--canhavemultiwritehosts--><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><!--canhavemultiwritehosts--><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="mod-long"><rule><columns>order_id</columns><algorithm>mod-long</algorithm></rule></tableRule><functionname="mod-long"class="io.mycat.route.function.PartitionByMod"><propertyname="count">9</property></function></mycat:rule>

三、配置server.xml

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

四、创建测试库

node1

createdatabasetestdb01;createdatabasetestdb02;createdatabasetestdb03;

node2

createdatabasetestdb13;createdatabasetestdb14;createdatabasetestdb15;

node3

createdatabasetestdb25;createdatabasetestdb26;createdatabasetestdb27;

五、启动mycat

/usr/local/mycat/bin/mycatstart

查看mycat日志

STATUS|wrapper|2018/11/2214:12:55|-->WrapperStartedasDaemonSTATUS|wrapper|2018/11/2214:12:55|LaunchingaJVM...INFO|jvm1|2018/11/2214:12:55|OpenJDK64-BitServerVMwarning:ignoringoptionMaxPermSize=64M;supportwasremovedin8.0INFO|jvm1|2018/11/2214:12:58|Wrapper(Version3.2.3)http://wrapper.tanukisoftware.orgINFO|jvm1|2018/11/2214:12:58|Copyright1999-2006TanukiSoftware,Inc.AllRightsReserved.INFO|jvm1|2018/11/2214:12:58|INFO|jvm1|2018/11/2214:13:04|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|+-------------------+|order01||orderdetail01||user01||user02|+-------------------+4rowsinset(0.00sec)mysql>select*fromorder01;ERROR1105(HY000):Table'testdb03.order01'doesn'texistmysql>mysql>droptableifexistsorder01;QueryOK,0rowsaffected,1warning(0.46sec)mysql>droptableifexistsorderdetail01;QueryOK,0rowsaffected,1warning(0.16sec)mysql>CREATETABLEorder01(->order_idINTNOTNULLAUTO_INCREMENTPRIMARYKEY,->numberVARCHAR(64),->createtimeDATETIME);QueryOK,0rowsaffected(0.58sec)mysql>CREATETABLEorderdetail01(->orderdetail_idINTAUTO_INCREMENTPRIMARYKEY,->order_idINT,->order_statusCHAR(1),->addresssVARCHAR(128),->createtimeDATETIME,->CONSTRAINTfk_iorder01FOREIGNKEY(order_id)REFERENCESorder01->(order_id));QueryOK,0rowsaffected(0.63sec)

七、插入测试数据

父表和子表各插入9条记录

INSERTINTOorder01(order_id,number,createtime)VALUES(1,'steven101',NOW());INSERTINTOorderdetail01(orderdetail_id,order_id,order_status,addresss,createtime)VALUES(1,1,'1','steven101(ID=1,steven101)',NOW());INSERTINTOorder01(order_id,number,createtime)VALUES(2,'steven201',NOW());INSERTINTOorderdetail01(orderdetail_id,order_id,order_status,addresss,createtime)VALUES(2,2,'1','steven201(ID=2,steven201)',NOW());INSERTINTOorder01(order_id,number,createtime)VALUES(3,'steven301',NOW());INSERTINTOorderdetail01(orderdetail_id,order_id,order_status,addresss,createtime)VALUES(3,3,'1','steven301(ID=3,steven301)',NOW());INSERTINTOorder01(order_id,number,createtime)VALUES(4,'steven401',NOW());INSERTINTOorderdetail01(orderdetail_id,order_id,order_status,addresss,createtime)VALUES(4,4,'1','steven401(ID=4,steven401)',NOW());INSERTINTOorder01(order_id,number,createtime)VALUES(5,'steven501',NOW());INSERTINTOorderdetail01(orderdetail_id,order_id,order_status,addresss,createtime)VALUES(5,5,'1','steven501(ID=5,steven501)',NOW());INSERTINTOorder01(order_id,number,createtime)VALUES(6,'steven601',NOW());INSERTINTOorderdetail01(orderdetail_id,order_id,order_status,addresss,createtime)VALUES(6,6,'1','steven601(ID=6,steven601)',NOW());INSERTINTOorder01(order_id,number,createtime)VALUES(7,'steven701',NOW());INSERTINTOorderdetail01(orderdetail_id,order_id,order_status,addresss,createtime)VALUES(7,7,'1','steven701(ID=7,steven701)',NOW());INSERTINTOorder01(order_id,number,createtime)VALUES(8,'steven801',NOW());INSERTINTOorderdetail01(orderdetail_id,order_id,order_status,addresss,createtime)VALUES(8,8,'1','steven801(ID=8,steven801)',NOW());INSERTINTOorder01(order_id,number,createtime)VALUES(9,'steven901',NOW());INSERTINTOorderdetail01(orderdetail_id,order_id,order_status,addresss,createtime)VALUES(9,9,'1','steven901(ID=9,steven901)',NOW());

八、验证数据

schema.xml中，order01和orderdetail01分片只配置了testdb01-03，testdb13-15，testdb25-27，所以除了这9个物理库之外，其他库查不到分片。

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

node1

mysql>select*fromtestdb01.order01;+----------+-----------+---------------------+|order_id|number|createtime|+----------+-----------+---------------------+|9|steven901|2018-11-2214:21:20|+----------+-----------+---------------------+1rowinset(0.00sec)mysql>select*fromtestdb01.orderdetail01;+----------------+----------+--------------+---------------------------+---------------------+|orderdetail_id|order_id|order_status|addresss|createtime|+----------------+----------+--------------+---------------------------+---------------------+|9|9|1|steven901(ID=9,steven901)|2018-11-2214:21:23|+----------------+----------+--------------+---------------------------+---------------------+1rowinset(0.01sec)mysql>select*fromtestdb02.order01;+----------+-----------+---------------------+|order_id|number|createtime|+----------+-----------+---------------------+|1|steven101|2018-11-2214:21:18|+----------+-----------+---------------------+1rowinset(0.00sec)mysql>select*fromtestdb02.orderdetail01;+----------------+----------+--------------+---------------------------+---------------------+|orderdetail_id|order_id|order_status|addresss|createtime|+----------------+----------+--------------+---------------------------+---------------------+|1|1|1|steven101(ID=1,steven101)|2018-11-2214:21:18|+----------------+----------+--------------+---------------------------+---------------------+1rowinset(0.01sec)mysql>select*fromtestdb03.order01;+----------+-----------+---------------------+|order_id|number|createtime|+----------+-----------+---------------------+|2|steven201|2018-11-2214:21:18|+----------+-----------+---------------------+1rowinset(0.00sec)mysql>select*fromtestdb03.orderdetail01;+----------------+----------+--------------+---------------------------+---------------------+|orderdetail_id|order_id|order_status|addresss|createtime|+----------------+----------+--------------+---------------------------+---------------------+|2|2|1|steven201(ID=2,steven201)|2018-11-2214:21:18|+----------------+----------+--------------+---------------------------+---------------------+1rowinset(0.00sec)

node2

mysql>select*fromtestdb13.order01;+----------+-----------+---------------------+|order_id|number|createtime|+----------+-----------+---------------------+|3|steven301|2018-11-2214:21:17|+----------+-----------+---------------------+1rowinset(0.00sec)mysql>select*fromtestdb13.orderdetail01;+----------------+----------+--------------+---------------------------+---------------------+|orderdetail_id|order_id|order_status|addresss|createtime|+----------------+----------+--------------+---------------------------+---------------------+|3|3|1|steven301(ID=3,steven301)|2018-11-2214:21:17|+----------------+----------+--------------+---------------------------+---------------------+1rowinset(0.01sec)mysql>select*fromtestdb14.order01;+----------+-----------+---------------------+|order_id|number|createtime|+----------+-----------+---------------------+|4|steven401|2018-11-2214:21:17|+----------+-----------+---------------------+1rowinset(0.00sec)mysql>select*fromtestdb14.orderdetail01;+----------------+----------+--------------+---------------------------+---------------------+|orderdetail_id|order_id|order_status|addresss|createtime|+----------------+----------+--------------+---------------------------+---------------------+|4|4|1|steven401(ID=4,steven401)|2018-11-2214:21:17|+----------------+----------+--------------+---------------------------+---------------------+1rowinset(0.00sec)mysql>select*fromtestdb15.order01;+----------+-----------+---------------------+|order_id|number|createtime|+----------+-----------+---------------------+|5|steven501|2018-11-2214:21:18|+----------+-----------+---------------------+1rowinset(0.00sec)mysql>select*fromtestdb15.orderdetail01;+----------------+----------+--------------+---------------------------+---------------------+|orderdetail_id|order_id|order_status|addresss|createtime|+----------------+----------+--------------+---------------------------+---------------------+|5|5|1|steven501(ID=5,steven501)|2018-11-2214:21:18|+----------------+----------+--------------+---------------------------+---------------------+1rowinset(0.00sec)

node3

mysql>select*fromtestdb25.order01;+----------+-----------+---------------------+|order_id|number|createtime|+----------+-----------+---------------------+|6|steven601|2018-11-2214:21:20|+----------+-----------+---------------------+1rowinset(0.00sec)mysql>select*fromtestdb25.orderdetail01;+----------------+----------+--------------+---------------------------+---------------------+|orderdetail_id|order_id|order_status|addresss|createtime|+----------------+----------+--------------+---------------------------+---------------------+|6|6|1|steven601(ID=6,steven601)|2018-11-2214:21:20|+----------------+----------+--------------+---------------------------+---------------------+1rowinset(0.00sec)mysql>select*fromtestdb26.order01;+----------+-----------+---------------------+|order_id|number|createtime|+----------+-----------+---------------------+|7|steven701|2018-11-2214:21:20|+----------+-----------+---------------------+1rowinset(0.00sec)mysql>select*fromtestdb26.orderdetail01;+----------------+----------+--------------+---------------------------+---------------------+|orderdetail_id|order_id|order_status|addresss|createtime|+----------------+----------+--------------+---------------------------+---------------------+|7|7|1|steven701(ID=7,steven701)|2018-11-2214:21:20|+----------------+----------+--------------+---------------------------+---------------------+1rowinset(0.00sec)mysql>select*fromtestdb27.order01;+----------+-----------+---------------------+|order_id|number|createtime|+----------+-----------+---------------------+|8|steven801|2018-11-2214:21:20|+----------+-----------+---------------------+1rowinset(0.01sec)mysql>select*fromtestdb27.orderdetail01;+----------------+----------+--------------+---------------------------+---------------------+|orderdetail_id|order_id|order_status|addresss|createtime|+----------------+----------+--------------+---------------------------+---------------------+|8|8|1|steven801(ID=8,steven801)|2018-11-2214:21:20|+----------------+----------+--------------+---------------------------+---------------------+1rowinset(0.00sec)

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