MyCat分库分表--实战02--分片枚举

2025-04-08 技术教程

项目环境：

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;

二、配置schema.xml

<?xmlversion="1.0"?><!DOCTYPEmycat:schemaSYSTEM"schema.dtd"><mycat:schemaxmlns:mycat="http://io.mycat/"><schemaname="mycatdb"checkSQLschema="false"sqlMaxLimit="100"><tablename="user01"dataNode="dn$1-35"rule="sharding-by-intfile-mycatdb-kk_user"></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="sharding-by-intfile-mycatdb-kk_user"><rule><columns>province</columns><algorithm>hash-int</algorithm></rule></tableRule><functionname="hash-int"class="io.mycat.route.function.PartitionByFileMap"><propertyname="mapFile">partition-hash-int-mycatdb-kk_user.txt</property><propertyname="type">1</property><propertyname="defaultNode">0</property></function></mycat:rule>

四、配置partition-hash-int-mycatdb-kk_user.txt

北京市=0上海市=1云南省=2内蒙古=3贵州省=4重庆市=5台湾省=6吉林省=7四川省=8天津市=9宁夏省=10安徽省=11山东省=12山西省=13广东省=14广西省=15新疆省=16江苏省=17江西省=18河北省=19河南省=20浙江省=21海南省=22湖北省=23湖南省=24澳门=25甘肃省=26福建省=27西藏=28辽宁省=29陕西省=30青海省=31香港=32黑龙江省=33DEFAULT_NODE=34

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

七、登录mysql进行数据验证

mysql-uroot-pmysql-P8066-h292.168.8.30

因为schema.xml里边配置了table的属性，所以登录MySQL会看到这张表，但是查看不到数据，也无法用drop table直接删除。

第一次删除此表需要用命令DROP TABLE IF EXISTS `user01`; 否则会提示找不到此表。

创建表，需要含province字段

mysql>DROPTABLEIFEXISTS`user01`;QueryOK,0rowsaffected(0.84sec)mysql>createtableuser01(provincevarchar(40));QueryOK,0rowsaffected(1.74sec)

向user01表插入测试数据，每个省份插入20条记录

在node1查看部分数据

mysql>selectcount(*),provincefromtestdb01.user01groupbyprovince;+----------+-----------+|count(*)|province|+----------+-----------+|20|北京市|+----------+-----------+1rowinset(0.01sec)mysql>selectcount(*),provincefromtestdb05.user01groupbyprovince;+----------+-----------+|count(*)|province|+----------+-----------+|20|贵州省|+----------+-----------+1rowinset(0.00sec)mysql>selectcount(*),provincefromtestdb12.user01groupbyprovince;+----------+-----------+|count(*)|province|+----------+-----------+|20|安徽省|+----------+-----------+1rowinset(0.00sec)

在node2查看部分数据

mysql>selectcount(*),provincefromtestdb16.user01groupbyprovince;+----------+-----------+|count(*)|province|+----------+-----------+|20|广西省|+----------+-----------+1rowinset(0.00sec)mysql>selectcount(*),provincefromtestdb19.user01groupbyprovince;+----------+-----------+|count(*)|province|+----------+-----------+|20|江西省|+----------+-----------+1rowinset(0.00sec)mysql>selectcount(*),provincefromtestdb22.user01groupbyprovince;+----------+-----------+|count(*)|province|+----------+-----------+|20|浙江省|+----------+-----------+1rowinset(0.00sec)

在node3查看部分数据

mysql>selectcount(*),provincefromtestdb25.user01groupbyprovince;+----------+-----------+|count(*)|province|+----------+-----------+|20|湖南省|+----------+-----------+1rowinset(0.00sec)mysql>selectcount(*),provincefromtestdb30.user01groupbyprovince;+----------+-----------+|count(*)|province|+----------+-----------+|20|辽宁省|+----------+-----------+1rowinset(0.01sec)mysql>selectcount(*),provincefromtestdb33.user01groupbyprovince;+----------+----------+|count(*)|province|+----------+----------+|20|香港|+----------+----------+1rowinset(0.00sec)

八、查看各个分片的大小

node1

mysql>selecttable_schema,table_nameas"Tables",ROUND(((data_length+->index_length)/1024),2)"SizeinKB"->frominformation_schema.TABLES->whereTABLE_NAME="user01"->orderby(data_length+index_length)desc;+--------------+--------+------------+|table_schema|Tables|SizeinKB|+--------------+--------+------------+|testdb09|user01|16.00||testdb08|user01|16.00||testdb07|user01|16.00||testdb06|user01|16.00||testdb05|user01|16.00||testdb04|user01|16.00||testdb03|user01|16.00||testdb02|user01|16.00||testdb12|user01|16.00||testdb01|user01|16.00||testdb11|user01|16.00||testdb10|user01|16.00|+--------------+--------+------------+12rowsinset(0.00sec)

node2

mysql>selecttable_schema,table_nameas"Tables",ROUND(((data_length+->index_length)/1024),2)"SizeinKB"->frominformation_schema.TABLES->whereTABLE_NAME="user01"->orderby(data_length+index_length)desc;+--------------+--------+------------+|table_schema|Tables|SizeinKB|+--------------+--------+------------+|testdb18|user01|16.00||testdb17|user01|16.00||testdb16|user01|16.00||testdb15|user01|16.00||testdb14|user01|16.00||testdb24|user01|16.00||testdb13|user01|16.00||testdb23|user01|16.00||testdb22|user01|16.00||testdb21|user01|16.00||testdb20|user01|16.00||testdb19|user01|16.00|+--------------+--------+------------+12rowsinset(0.00sec)

node3

mysql>selecttable_schema,table_nameas"Tables",ROUND(((data_length+->index_length)/1024),2)"SizeinKB"->frominformation_schema.TABLES->whereTABLE_NAME="user01"->orderby(data_length+index_length)desc;+--------------+--------+------------+|table_schema|Tables|SizeinKB|+--------------+--------+------------+|testdb26|user01|16.00||testdb25|user01|16.00||testdb35|user01|16.00||testdb34|user01|16.00||testdb33|user01|16.00||testdb32|user01|16.00||testdb31|user01|16.00||testdb30|user01|16.00||testdb29|user01|16.00||testdb28|user01|16.00||testdb27|user01|16.00|+--------------+--------+------------+11rowsinset(0.00sec)

到此，分片枚举结束。