共计 14896 个字符，预计需要花费 38 分钟才能阅读完成。
这篇文章主要介绍“MyCat 分库分表中怎么实现 ER 分片”，在日常操作中，相信很多人在 MyCat 分库分表中怎么实现 ER 分片问题上存在疑惑，丸趣 TV 小编查阅了各式资料，整理出简单好用的操作方法，希望对大家解答”MyCat 分库分表中怎么实现 ER 分片”的疑惑有所帮助！接下来，请跟着丸趣 TV 小编一起来学习吧！
项目环境：
192.168.8.30 mycat
192.168.8.31 node1
192.168.8.32 node2
192.168.8.33 node3
三个节点 MySQL 均为单实例
一、配置 schema.xml
?xml version= 1.0 ?
!DOCTYPE mycat:schema SYSTEM schema.dtd
mycat:schema xmlns:mycat= http://io.mycat/
schema name= mycatdb checkSQLschema= false sqlMaxLimit= 100
table name= order01 dataNode= dn$1-3,dn$13-15,dn$25-27 rule= mod-long
childTable name= orderdetail01 primaryKey= orderdetail_id joinKey= order_id parentKey= order_id / /table
/schema
!-- dataNode name= dn1$0-743 dataHost= localhost1 database= db$0-743
/ --
dataNode name= dn1 dataHost= node1 database= testdb01 /
dataNode name= dn2 dataHost= node1 database= testdb02 /
dataNode name= dn3 dataHost= node1 database= testdb03 /
dataNode name= dn4 dataHost= node1 database= testdb04 /
dataNode name= dn5 dataHost= node1 database= testdb05 /
dataNode name= dn6 dataHost= node1 database= testdb06 /
dataNode name= dn7 dataHost= node1 database= testdb07 /
dataNode name= dn8 dataHost= node1 database= testdb08 /
dataNode name= dn9 dataHost= node1 database= testdb09 /
dataNode name= dn10 dataHost= node1 database= testdb10 /
dataNode name= dn11 dataHost= node1 database= testdb11 /
dataNode name= dn12 dataHost= node1 database= testdb12 /
dataNode name= dn13 dataHost= node2 database= testdb13 /
dataNode name= dn14 dataHost= node2 database= testdb14 /
dataNode name= dn15 dataHost= node2 database= testdb15 /
dataNode name= dn16 dataHost= node2 database= testdb16 /
dataNode name= dn17 dataHost= node2 database= testdb17 /
dataNode name= dn18 dataHost= node2 database= testdb18 /
dataNode name= dn19 dataHost= node2 database= testdb19 /
dataNode name= dn20 dataHost= node2 database= testdb20 /
dataNode name= dn21 dataHost= node2 database= testdb21 /
dataNode name= dn22 dataHost= node2 database= testdb22 /
dataNode name= dn23 dataHost= node2 database= testdb23 /
dataNode name= dn24 dataHost= node2 database= testdb24 /
dataNode name= dn25 dataHost= node3 database= testdb25 /
dataNode name= dn26 dataHost= node3 database= testdb26 /
dataNode name= dn27 dataHost= node3 database= testdb27 /
dataNode name= dn28 dataHost= node3 database= testdb28 /
dataNode name= dn29 dataHost= node3 database= testdb29 /
dataNode name= dn30 dataHost= node3 database= testdb30 /
dataNode name= dn31 dataHost= node3 database= testdb31 /
dataNode name= dn32 dataHost= node3 database= testdb32 /
dataNode name= dn33 dataHost= node3 database= testdb33 /
dataNode name= dn34 dataHost= node3 database= testdb34 /
dataNode name= dn35 dataHost= node3 database= testdb35 /
!-- dataNode name= dn4 dataHost= sequoiadb1 database= SAMPLE /
dataNode name= jdbc_dn1 dataHost= jdbchost database= db1 /
dataNode name= jdbc_dn2 dataHost= jdbchost database= db2 /
dataNode name= jdbc_dn3 dataHost= jdbchost database= db3 / --
dataHost name= node1 maxCon= 1000 minCon= 10 balance= 1
writeType= 0 dbType= mysql dbDriver= native switchType= 1 slaveThreshold= 100
heartbeat select user() /heartbeat
!-- can have multi write hosts --
writeHost host= 192.168.8.31 url= 192.168.8.31:3306 user= root password= mysql /writeHost
/dataHost
dataHost name= node2 maxCon= 1000 minCon= 10 balance= 1
writeType= 0 dbType= mysql dbDriver= native switchType= 1 slaveThreshold= 100
heartbeat select user() /heartbeat
!-- can have multi write hosts --
writeHost host= 192.168.8.32 url= 192.168.8.32:3306 user= root password= mysql /writeHost
/dataHost
dataHost name= node3 maxCon= 1000 minCon= 10 balance= 1
writeType= 0 dbType= mysql dbDriver= native switchType= 1 slaveThreshold= 100
heartbeat select user() /heartbeat
!-- can have multi write hosts --
writeHost host= 192.168.8.33 url= 192.168.8.33:3306 user= root password= mysql /writeHost
/dataHost
/mycat:schema二、配置 rule.xml
mycat:rule xmlns:mycat= http://io.mycat/
tableRule name= mod-long
rule
columns order_id /columns
algorithm mod-long /algorithm
/rule
/tableRule
function name= mod-long > 三、配置 server.xml
user name= root defaultAccount= true
property name= password mysql /property
property name= schemas mycatdb /property
/user
四、创建测试库
node1
create database testdb01;
create database testdb02;
create database testdb03;node2
create database testdb13;
create database testdb14;
create database testdb15;node3
create database testdb25;
create database testdb26;
create database testdb27;五、启动 mycat
/usr/local/mycat/bin/mycat start查看 mycat 日志
STATUS | wrapper | 2018/11/22 14:12:55 | -- Wrapper Started as Daemon
STATUS | wrapper | 2018/11/22 14:12:55 | Launching a JVM...
INFO | jvm 1 | 2018/11/22 14:12:55 | OpenJDK 64-Bit Server VM warning: ignoring option MaxPermSize=64M; support was removed in 8.0
INFO | jvm 1 | 2018/11/22 14:12:58 | Wrapper (Version 3.2.3) http://wrapper.tanukisoftware.org
INFO | jvm 1 | 2018/11/22 14:12:58 | Copyright 1999-2006 Tanuki Software, Inc. All Rights Reserved.
INFO | jvm 1 | 2018/11/22 14:12:58 |
INFO | jvm 1 | 2018/11/22 14:13:04 | MyCAT Server startup successfully. see logs in logs/mycat.log六、登录 MySQL，查看逻辑表
mysql -uroot -pmysql -P8066 -h292.168.8.30mysql show databases;
+----------+
| DATABASE |
+----------+
| mycatdb |
+----------+
1 row in set (0.02 sec)
mysql use mycatdb
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql show tables;
+-------------------+
| Tables in mycatdb |
+-------------------+
| order01 |
| orderdetail01 |
| user01 |
| user02 |
+-------------------+
4 rows in set (0.00 sec)
mysql select * from order01;
ERROR 1105 (HY000): Table testdb03.order01 doesn t exist
mysql
mysql drop table if exists order01;
Query OK, 0 rows affected, 1 warning (0.46 sec)
mysql drop table if exists orderdetail01;
Query OK, 0 rows affected, 1 warning (0.16 sec)
mysql CREATE TABLE order01(
- order_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
- number VARCHAR(64),
- createtime DATETIME);
Query OK, 0 rows affected (0.58 sec)
mysql CREATE TABLE orderdetail01(
- orderdetail_id INT AUTO_INCREMENT PRIMARY KEY,
- order_id INT,
- order_status CHAR(1),
- addresss VARCHAR(128),
- createtime DATETIME,
- CONSTRAINT fk_iorder01 FOREIGN KEY (order_id) REFERENCES order01
- (order_id));
Query OK, 0 rows affected (0.63 sec)七、插入测试数据
父表和子表各插入 9 条记录
INSERT INTO order01(order_id,number,createtime) VALUES(1, steven101 ,NOW());
INSERT INTO orderdetail01(orderdetail_id,order_id,order_status,addresss,createtime) VALUES (1,1, 1 , steven101(ID=1,steven101) ,NOW());
INSERT INTO order01(order_id,number,createtime) VALUES(2, steven201 ,NOW());
INSERT INTO orderdetail01(orderdetail_id,order_id,order_status,addresss,createtime) VALUES (2,2, 1 , steven201(ID=2,steven201) ,NOW());
INSERT INTO order01(order_id,number,createtime) VALUES(3, steven301 ,NOW());
INSERT INTO orderdetail01(orderdetail_id,order_id,order_status,addresss,createtime) VALUES (3,3, 1 , steven301(ID=3,steven301) ,NOW());
INSERT INTO order01(order_id,number,createtime) VALUES(4, steven401 ,NOW());
INSERT INTO orderdetail01(orderdetail_id,order_id,order_status,addresss,createtime) VALUES (4,4, 1 , steven401(ID=4,steven401) ,NOW());
INSERT INTO order01(order_id,number,createtime) VALUES(5, steven501 ,NOW());
INSERT INTO orderdetail01(orderdetail_id,order_id,order_status,addresss,createtime) VALUES (5,5, 1 , steven501(ID=5,steven501) ,NOW());
INSERT INTO order01(order_id,number,createtime) VALUES(6, steven601 ,NOW());
INSERT INTO orderdetail01(orderdetail_id,order_id,order_status,addresss,createtime) VALUES (6,6, 1 , steven601(ID=6,steven601) ,NOW());
INSERT INTO order01(order_id,number,createtime) VALUES(7, steven701 ,NOW());
INSERT INTO orderdetail01(orderdetail_id,order_id,order_status,addresss,createtime) VALUES (7,7, 1 , steven701(ID=7,steven701) ,NOW());
INSERT INTO order01(order_id,number,createtime) VALUES(8, steven801 ,NOW());
INSERT INTO orderdetail01(orderdetail_id,order_id,order_status,addresss,createtime) VALUES (8,8, 1 , steven801(ID=8,steven801) ,NOW());
INSERT INTO order01(order_id,number,createtime) VALUES(9, steven901 ,NOW());
INSERT INTO orderdetail01(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 * from testdb01.order01;
+----------+-----------+---------------------+
| order_id | number | createtime |
+----------+-----------+---------------------+
| 9 | steven901 | 2018-11-22 14:21:20 |
+----------+-----------+---------------------+
1 row in set (0.00 sec)
mysql select * from testdb01.orderdetail01;
+----------------+----------+--------------+---------------------------+---------------------+
| orderdetail_id | order_id | order_status | addresss | createtime |
+----------------+----------+--------------+---------------------------+---------------------+
| 9 | 9 | 1 | steven901(ID=9,steven901) | 2018-11-22 14:21:23 |
+----------------+----------+--------------+---------------------------+---------------------+
1 row in set (0.01 sec)
mysql select * from testdb02.order01;
+----------+-----------+---------------------+
| order_id | number | createtime |
+----------+-----------+---------------------+
| 1 | steven101 | 2018-11-22 14:21:18 |
+----------+-----------+---------------------+
1 row in set (0.00 sec)
mysql select * from testdb02.orderdetail01;
+----------------+----------+--------------+---------------------------+---------------------+
| orderdetail_id | order_id | order_status | addresss | createtime |
+----------------+----------+--------------+---------------------------+---------------------+
| 1 | 1 | 1 | steven101(ID=1,steven101) | 2018-11-22 14:21:18 |
+----------------+----------+--------------+---------------------------+---------------------+
1 row in set (0.01 sec)
mysql select * from testdb03.order01;
+----------+-----------+---------------------+
| order_id | number | createtime |
+----------+-----------+---------------------+
| 2 | steven201 | 2018-11-22 14:21:18 |
+----------+-----------+---------------------+
1 row in set (0.00 sec)
mysql select * from testdb03.orderdetail01;
+----------------+----------+--------------+---------------------------+---------------------+
| orderdetail_id | order_id | order_status | addresss | createtime |
+----------------+----------+--------------+---------------------------+---------------------+
| 2 | 2 | 1 | steven201(ID=2,steven201) | 2018-11-22 14:21:18 |
+----------------+----------+--------------+---------------------------+---------------------+
1 row in set (0.00 sec)node2
mysql select * from testdb13.order01;
+----------+-----------+---------------------+
| order_id | number | createtime |
+----------+-----------+---------------------+
| 3 | steven301 | 2018-11-22 14:21:17 |
+----------+-----------+---------------------+
1 row in set (0.00 sec)
mysql select * from testdb13.orderdetail01;
+----------------+----------+--------------+---------------------------+---------------------+
| orderdetail_id | order_id | order_status | addresss | createtime |
+----------------+----------+--------------+---------------------------+---------------------+
| 3 | 3 | 1 | steven301(ID=3,steven301) | 2018-11-22 14:21:17 |
+----------------+----------+--------------+---------------------------+---------------------+
1 row in set (0.01 sec)
mysql select * from testdb14.order01;
+----------+-----------+---------------------+
| order_id | number | createtime |
+----------+-----------+---------------------+
| 4 | steven401 | 2018-11-22 14:21:17 |
+----------+-----------+---------------------+
1 row in set (0.00 sec)
mysql select * from testdb14.orderdetail01;
+----------------+----------+--------------+---------------------------+---------------------+
| orderdetail_id | order_id | order_status | addresss | createtime |
+----------------+----------+--------------+---------------------------+---------------------+
| 4 | 4 | 1 | steven401(ID=4,steven401) | 2018-11-22 14:21:17 |
+----------------+----------+--------------+---------------------------+---------------------+
1 row in set (0.00 sec)
mysql select * from testdb15.order01;
+----------+-----------+---------------------+
| order_id | number | createtime |
+----------+-----------+---------------------+
| 5 | steven501 | 2018-11-22 14:21:18 |
+----------+-----------+---------------------+
1 row in set (0.00 sec)
mysql select * from testdb15.orderdetail01;
+----------------+----------+--------------+---------------------------+---------------------+
| orderdetail_id | order_id | order_status | addresss | createtime |
+----------------+----------+--------------+---------------------------+---------------------+
| 5 | 5 | 1 | steven501(ID=5,steven501) | 2018-11-22 14:21:18 |
+----------------+----------+--------------+---------------------------+---------------------+
1 row in set (0.00 sec)node3
mysql select * from testdb25.order01;
+----------+-----------+---------------------+
| order_id | number | createtime |
+----------+-----------+---------------------+
| 6 | steven601 | 2018-11-22 14:21:20 |
+----------+-----------+---------------------+
1 row in set (0.00 sec)
mysql select * from testdb25.orderdetail01;
+----------------+----------+--------------+---------------------------+---------------------+
| orderdetail_id | order_id | order_status | addresss | createtime |
+----------------+----------+--------------+---------------------------+---------------------+
| 6 | 6 | 1 | steven601(ID=6,steven601) | 2018-11-22 14:21:20 |
+----------------+----------+--------------+---------------------------+---------------------+
1 row in set (0.00 sec)
mysql select * from testdb26.order01;
+----------+-----------+---------------------+
| order_id | number | createtime |
+----------+-----------+---------------------+
| 7 | steven701 | 2018-11-22 14:21:20 |
+----------+-----------+---------------------+
1 row in set (0.00 sec)
mysql select * from testdb26.orderdetail01;
+----------------+----------+--------------+---------------------------+---------------------+
| orderdetail_id | order_id | order_status | addresss | createtime |
+----------------+----------+--------------+---------------------------+---------------------+
| 7 | 7 | 1 | steven701(ID=7,steven701) | 2018-11-22 14:21:20 |
+----------------+----------+--------------+---------------------------+---------------------+
1 row in set (0.00 sec)
mysql select * from testdb27.order01;
+----------+-----------+---------------------+
| order_id | number | createtime |
+----------+-----------+---------------------+
| 8 | steven801 | 2018-11-22 14:21:20 |
+----------+-----------+---------------------+
1 row in set (0.01 sec)
mysql select * from testdb27.orderdetail01;
+----------------+----------+--------------+---------------------------+---------------------+
| orderdetail_id | order_id | order_status | addresss | createtime |
+----------------+----------+--------------+---------------------------+---------------------+
| 8 | 8 | 1 | steven801(ID=8,steven801) | 2018-11-22 14:21:20 |
+----------------+----------+--------------+---------------------------+---------------------+
1 row in set (0.00 sec)到此，关于“MyCat 分库分表中怎么实现 ER 分片”的学习就结束了，希望能够解决大家的疑惑。理论与实践的搭配能更好的帮助大家学习，快去试试吧！若想继续学习更多相关知识，请继续关注丸趣 TV 网站，丸趣 TV 小编会继续努力为大家带来更多实用的文章！
正文完
