MyCat分库分表--实战03--全局表

2025-04-14 技术教程

项目环境：

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="user02"primaryKey="id"type="global"dataNode="dn$1-35"/><tablename="user01"dataNode="dn$1-35"rule="sharding-by-intfile-mycatdb-kk_user"/></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>

二、配置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/2210:58:17|-->WrapperStartedasDaemonSTATUS|wrapper|2018/11/2210:58:18|LaunchingaJVM...INFO|jvm1|2018/11/2210:58:18|OpenJDK64-BitServerVMwarning:ignoringoptionMaxPermSize=64M;supportwasremovedin8.0INFO|jvm1|2018/11/2210:58:20|Wrapper(Version3.2.3)http://wrapper.tanukisoftware.orgINFO|jvm1|2018/11/2210:58:20|Copyright1999-2006TanukiSoftware,Inc.AllRightsReserved.INFO|jvm1|2018/11/2210:58:20|INFO|jvm1|2018/11/2210:58:24|MyCATServerstartupsuccessfully.seelogsinlogs/mycat.log

五、登录MySQL查看全局表

mysql-uroot-pmysql-P8066-h292.168.8.30

mysql>usemycatdbReadingtableinformationforcompletionoftableandcolumnnamesYoucanturnoffthisfeaturetogetaquickerstartupwith-ADatabasechangedmysql>showtables;+-------------------+|Tablesinmycatdb|+-------------------+|user01||user02|+-------------------+2rowsinset(0.01sec)mysql>droptableuser02;ERROR1105(HY000):Unknowntable'testdb11.user02'mysql>select*fromuser02;ERROR1146(HY000):Table'testdb01.user02'doesn'texistmysql>droptableifexistsuser02;QueryOK,0rowsaffected,1warning(0.40sec

建表user02并插入三条记录

mysql>createtableuser02(->idINT(10)NOTNULLAUTO_INCREMENT,->namevarchar(30),->PRIMARYKEY(id)->);QueryOK,0rowsaffected(1.74sec)mysql>insertintouser02(name)values('steven');QueryOK,1rowaffected(0.29sec)mysql>insertintouser02(name)values('steven');QueryOK,1rowaffected(0.33sec)mysql>insertintouser02(name)values('steven');QueryOK,1rowaffected(0.31sec)

六、数据验证

node1

mysql>select*fromtestdb01.user02;+----+--------+|id|name|+----+--------+|1|steven||2|steven||3|steven|+----+--------+3rowsinset(0.00sec)mysql>select*fromtestdb05.user02;+----+--------+|id|name|+----+--------+|1|steven||2|steven||3|steven|+----+--------+3rowsinset(0.01sec)mysql>select*fromtestdb12.user02;+----+--------+|id|name|+----+--------+|1|steven||2|steven||3|steven|+----+--------+3rowsinset(0.00sec)

node2

mysql>select*fromtestdb16.user02;+----+--------+|id|name|+----+--------+|1|steven||2|steven||3|steven|+----+--------+3rowsinset(0.00sec)mysql>select*fromtestdb19.user02;+----+--------+|id|name|+----+--------+|1|steven||2|steven||3|steven|+----+--------+3rowsinset(0.00sec)mysql>select*fromtestdb22.user02;+----+--------+|id|name|+----+--------+|1|steven||2|steven||3|steven|+----+--------+3rowsinset(0.00sec)

node3

mysql>select*fromtestdb25.user02;+----+--------+|id|name|+----+--------+|1|steven||2|steven||3|steven|+----+--------+3rowsinset(0.00sec)mysql>select*fromtestdb30.user02;+----+--------+|id|name|+----+--------+|1|steven||2|steven||3|steven|+----+--------+3rowsinset(0.00sec)mysql>select*fromtestdb33.user02;+----+--------+|id|name|+----+--------+|1|steven||2|steven||3|steven|+----+--------+

可以看到每个node的每个database存的数据都一模一样，全局表验证完毕。