mysql er分片_mycat高可用ER分片的2种场景详细分析

最新推荐文章于 2022-09-13 19:00:00 发布
最新推荐文章于 2022-09-13 19:00:00 发布
阅读量249 收藏
点赞数
文章标签： mysql er分片
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_32307987/article/details/113907053
版权

1，ER分片关系简介

有一类业务，例如订单(ORDER)跟订单明细表(ORDER_DETAIL),明细表会依赖二订单，就是该会存在表的主从关系，这类似业务的切分可以抽象出合适的切分规则，比如根据用户ID切分,其它相关的表都依赖于用户ID，再或者根据订单ID进行切分，总之部分业务总会可以抽象出父子关系的表。这类表适用于ER分片表，子表的记录与所关联的父表记录存放在同一个数据分片上，避免数据Join跨库操作，以order与order_detail例子为例，schema.xml中定义合适的分片配置,order,order_detail 根据order_id迕行数据切分，保证相同order_id的数据分到同一个分片上，在进行数据插入操作时，Mycat会获取order所在的分片，然后将order_detail也插入到order所在的分片。

2， 父表按照主键ID分片，字表的分片字段与主表ID关联，配置为ＥＲ分片

2.1 在schema.xml添加如下配置配置文件修改

在rule.xml里面设定分片规则

id

mod-long

然后重启mycat或者重新加载mycat

本blog源地址：http://blog.csdn.net/mchdba/article/details/50655304，不经过原作者mchdba(黄杉)允许，谢绝转载

2.2 先建表， ORDER 和 ORDER_DETAIL 表，有主外键关系

CREATE TABLE ORDER1(ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,SN VARCHAR(64),CREATE_TIME DATETIME);

CREATE TABLE ORDER_DETAIL(ID INT AUTO_INCREMENT PRIMARY KEY, ORDER_ID INT,ORD_STATUS CHAR(1),ADDRESS VARCHAR(128),CREATE_TIME DATETIME,CONSTRAINT FK_ORDID11 FOREIGN KEY (ORDER_ID) REFERENCES ORDER1 (ID));

mysql> CREATE TABLE ORDER1(ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,SN VARCHAR(64),CREATE_TIME DATETIME);

Query OK, 0 rows affected (0.06 sec)

mysql>

mysql> CREATE TABLE ORDER_DETAIL(ID INT AUTO_INCREMENT PRIMARY KEY, ORDER_ID INT,ORD_STATUS CHAR(1),ADDRESS VARCHAR(128),CREATE_TIME DATETIME,CONSTRAINT FK_ORDID FOREIGN KEY (ORDER_ID) REFERENCES ORDER1 (ID));

Query OK, 0 rows affected (0.02 sec)

mysql>

3.3 录入数据：

mysql>

mysql> explain INSERT INTO ORDER1(SN,CREATE_TIME) VALUES('BJ0001',NOW());

ERROR 1064 (HY000): bad insert sql (sharding column:ID not provided,INSERT INTO ORDER1 (SN, CREATE_TIME)

VALUES ('BJ0001', NOW())

mysql> explain INSERT INTO ORDER1(SN,CREATE_TIME) VALUES('BJ0001',NOW());

ERROR 1064 (HY000): bad insert sql (sharding column:ID not provided,INSERT INTO ORDER1 (SN, CREATE_TIME)

VALUES ('BJ0001', NOW())

mysql> INSERT INTO ORDER1(ID,SN,CREATE_TIME) VALUES(1,'BJ0001',NOW());

Query OK, 1 row affected (0.03 sec)

mysql>

录入数据，一组组录入，涉及到外键关系：

第一组北京的订单

mysql> INSERT INTO ORDER1(ID,SN,CREATE_TIME) VALUES(1,'BJ0001',NOW());

Query OK, 1 row affected (0.03 sec)

mysql>

mysql> INSERT INTO ORDER_DETAIL(ID,ORDER_ID,ORD_STATUS,ADDRESS,CREATE_TIME) VALUES (1,1,'1','test data of ORDER1(ID=1,BJ001) ',NOW());

Query OK, 1 row affected (0.00 sec)

mysql>

第二组上海的订单：

mysql> INSERT INTO ORDER1(ID,SN,CREATE_TIME) VALUES(3,'SHH001',NOW());

Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO ORDER_DETAIL(ID,ORDER_ID,ORD_STATUS,ADDRESS,CREATE_TIME) VALUES (3,3,'1','test data of ORDER1(ID=3,SHH001) ',NOW());

mysql>

第三组广州的订单：

mysql> INSERT INTO ORDER1(ID,SN,CREATE_TIME) VALUES(4,'GZH004',NOW());

Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO ORDER_DETAIL(ID,ORDER_ID,ORD_STATUS,ADDRESS,CREATE_TIME) VALUES (4,4,'1','test data of ORDER1(ID=4,GZH004) ',NOW());

mysql>

第四组 武汉的订单，这里故意将order_id设置成4，看看效果，是否随id为4的广州的那组分片：

mysql> INSERT INTO ORDER1(ID,SN,CREATE_TIME) VALUES(5,'WUHAN005',NOW());

ERROR 1064 (HY000): Index: 2, Size: 2

mysql> INSERT INTO ORDER1(ID,SN,CREATE_TIME) VALUES(6,'WUHAN006',NOW());

Query OK, 1 row affected (0.02 sec)

mysql> INSERT INTO ORDER_DETAIL(ID,ORDER_ID,ORD_STATUS,ADDRESS,CREATE_TIME) VALUES (6,4,'1','test data of ORDER1(ID=6,WUHAN005) ',NOW());

2.4 验证子表是否随父表分片，果然验证如下，大家看到订单详细表ORDER_DETAIL的数据的分片都是随ORDER_ID所在外键的父表的分片：

[root@test_1_11logs]# /usr/local/mysql56s1/bin/mysql -uroot -p -P3317 --socket=/usr/local/mysql56s1/mysql.sock -e "select @@port;select * from db3.ORDER1; select * from db3.ORDER_DETAIL";

Enter password:

+--------+

| @@port |

+--------+

| 3327 |

+--------+

+----+--------+---------------------+

| ID | SN | CREATE_TIME |

+----+--------+---------------------+

| 1 | BJ0001 | 2016-02-11 22:54:26 |

| 4 | GZH004 | 2016-02-11 22:57:49 |

+----+--------+---------------------+

+----+----------+------------+--------------------------------------+---------------------+

| ID | ORDER_ID | ORD_STATUS | ADDRESS | CREATE_TIME |

+----+----------+------------+--------------------------------------+---------------------+

| 1 | 1 | 1 | test data of ORDER1(ID=1,BJ001) | 2016-02-11 23:07:05 |

| 4 | 4 | 1 | test data of ORDER1(ID=4,GZH004) | 2016-02-11 23:09:48 |

| 6 | 4 | 1 | test data of ORDER1(ID=6,WUHAN005) | 2016-02-11 23:10:18 |

+----+----------+------------+--------------------------------------+---------------------+

[root@test_1_11logs]#

[root@test_1_11logs]#

[root@test_1_11logs]# /usr/local/mysql56s1/bin/mysql -uroot -p -P3327 --socket=/usr/local/mysql56m1/mysql.sock -e "select @@port;select * from db3.ORDER1; select * from db3.ORDER_DETAIL";

Enter password:

+--------+

| @@port |

+--------+

| 3317 |

+--------+

+----+----------+---------------------+

| ID | SN | CREATE_TIME |

+----+----------+---------------------+

| 3 | SHH001 | 2016-02-11 22:57:26 |

| 6 | WUHAN006 | 2016-02-11 23:04:39 |

+----+----------+---------------------+

+----+----------+------------+------------------------------------+---------------------+

| ID | ORDER_ID | ORD_STATUS | ADDRESS | CREATE_TIME |

+----+----------+------------+------------------------------------+---------------------+

| 3 | 3 | 1 | test data of ORDER1(ID=3,SHH001) | 2016-02-11 23:09:02 |

+----+----------+------------+------------------------------------+---------------------+

[root@test_1_11logs]#

2.5 有日志为列，看到SHH001这个订单的数据录入都在端口为3317的分片mysql实例上，如下验证：

[root@test_1_11logs]# more mycat.log|grep SHH001

02/11 22:56:58.477 DEBUG [$_NIOREACTOR-3-RW] (ServerQueryHandler.java:56) -ServerConnection [id=1, schema=TESTDB, host=127.0.0.1, user=test,txIsolation=3, autocommit=true, schema=TESTDB]INSERT INTO ORDER1(ID,SN,CREATE_TIME) VALUES(2,'SHH001',NOW())

02/11 22:56:58.478 WARN [$_NIOREACTOR-3-RW] (ServerConnection.java:209) -ServerConnection [id=1, schema=TESTDB, host=127.0.0.1, user=test,txIsolation=3, autocommit=true, schema=TESTDB]INSERT INTO ORDER1(ID,SN,CREATE_TIME) VALUES(2,'SHH001',NOW()) err:java.lang.IndexOutOfBoundsException: Index: 2, Size: 2

02/11 22:57:22.476 DEBUG [$_NIOREACTOR-3-RW] (ServerQueryHandler.java:56) -ServerConnection [id=1, schema=TESTDB, host=127.0.0.1, user=test,txIsolation=3, autocommit=true, schema=TESTDB]INSERT INTO ORDER1(ID,SN,CREATE_TIME) VALUES(2,'SHH001',NOW())

02/11 22:57:22.477 WARN [$_NIOREACTOR-3-RW] (ServerConnection.java:209) -ServerConnection [id=1, schema=TESTDB, host=127.0.0.1, user=test,txIsolation=3, autocommit=true, schema=TESTDB]INSERT INTO ORDER1(ID,SN,CREATE_TIME) VALUES(2,'SHH001',NOW()) err:java.lang.IndexOutOfBoundsException: Index: 2, Size: 2

02/11 22:57:26.988 DEBUG [$_NIOREACTOR-3-RW] (ServerQueryHandler.java:56) -ServerConnection [id=1, schema=TESTDB, host=127.0.0.1, user=test,txIsolation=3, autocommit=true, schema=TESTDB]INSERT INTO ORDER1(ID,SN,CREATE_TIME) VALUES(3,'SHH001',NOW())

02/11 22:57:26.989 DEBUG [$_NIOREACTOR-3-RW] (NonBlockingSession.java:113) -ServerConnection [id=1, schema=TESTDB, host=127.0.0.1, user=test,txIsolation=3, autocommit=true, schema=TESTDB]INSERT INTO ORDER1(ID,SN,CREATE_TIME) VALUES(3,'SHH001',NOW()), route={

1 -> dn21{INSERT INTO ORDER1(ID,SN,CREATE_TIME) VALUES(3,'SHH001',NOW())}

02/11 22:57:26.999 DEBUG [$_NIOREACTOR-0-RW] (NonBlockingSession.java:229) -release connection MySQLConnection [id=4, lastTime=1455202646984, user=root, schema=db3, old shema=db3, borrowed=true, fromSlaveDB=false, threadId=91, charset=latin1, txIsolation=3, autocommit=true, attachment=dn21{INSERT INTO ORDER1(ID,SN,CREATE_TIME) VALUES(3,'SHH001',NOW())}, respHandler=SingleNodeHandler [node=dn21{INSERT INTO ORDER1(ID,SN,CREATE_TIME) VALUES(3,'SHH001',NOW())}, packetId=0], host=12.23.2.11, port=3317, statusSync=null, writeQueue=0, modifiedSQLExecuted=true]

02/11 23:09:02.690 DEBUG [$_NIOREACTOR-3-RW] (ServerQueryHandler.java:56) -ServerConnection [id=1, schema=TESTDB, host=127.0.0.1, user=test,txIsolation=3, autocommit=true, schema=TESTDB]INSERT INTO ORDER_DETAIL(ID,ORDER_ID,ORD_STATUS,ADDRESS,CREATE_TIME) VALUES (3,3,'1','test data of ORDER1(ID=3,SHH001) ',NOW())

VALUES (3, 3, '1', 'test data of ORDER1(ID=3,SHH001) ', NOW())

02/11 23:09:02.692 DEBUG [$_NIOREACTOR-3-RW] (NonBlockingSession.java:113) -ServerConnection [id=1, schema=TESTDB, host=127.0.0.1, user=test,txIsolation=3, autocommit=true, schema=TESTDB]INSERT INTO ORDER_DETAIL(ID,ORDER_ID,ORD_STATUS,ADDRESS,CREATE_TIME) VALUES (3,3,'1','test data of ORDER1(ID=3,SHH001) ',NOW()), route={

VALUES (3, 3, '1', 'test data of ORDER1(ID=3,SHH001) ', NOW())}

VALUES (3, 3, '1', 'test data of ORDER1(ID=3,SHH001) ', NOW())}, respHandler=SingleNodeHandler [node=dn21{INSERT INTO ORDER_DETAIL (ID, ORDER_ID, ORD_STATUS, ADDRESS, CREATE_TIME)

VALUES (3, 3, '1', 'test data of ORDER1(ID=3,SHH001) ', NOW())}, packetId=0], host=12.23.2.11, port=3317, statusSync=null, writeQueue=0, modifiedSQLExecuted=true]

[root@test_1_11logs]#

【】看到直接22:57:26.988时间点处有数据录入，22:57:26.989处有路由分配 route={dn21了，直接走父表的路由了**

2.6 查询路由

mycat命令行里面执行：

mysql> explain select t1.*,t2.* from ORDER1 t1,ORDER_DETAIL t2 where t2.ORD_STATUS='1' and t2.ID=1 and t1.ID=t2.ORDER_ID;

+-----------+-----------------------------------------------------------------------------------------------------------+

| DATA_NODE | SQL |

+-----------+-----------------------------------------------------------------------------------------------------------+

| dn21 | select t1.*,t2.* from ORDER1 t1,ORDER_DETAIL t2 where t2.ORD_STATUS='1' and t2.ID=1 and t1.ID=t2.ORDER_ID |

| dn22 | select t1.*,t2.* from ORDER1 t1,ORDER_DETAIL t2 where t2.ORD_STATUS='1' and t2.ID=1 and t1.ID=t2.ORDER_ID |

+-----------+-----------------------------------------------------------------------------------------------------------+

2 rows in set (0.00 sec)

mysql> select t1.*,t2.* from ORDER1 t1,ORDER_DETAIL t2 where t2.ORD_STATUS='1' and t2.ID=1 and t1.ID=t2.ORDER_ID;

+----+--------+---------------------+----+----------+------------+-----------------------------------+---------------------+

| ID | SN | CREATE_TIME | ID | ORDER_ID | ORD_STATUS | ADDRESS | CREATE_TIME |

+----+--------+---------------------+----+----------+------------+-----------------------------------+---------------------+

| 1 | BJ0001 | 2016-02-12 17:51:24 | 1 | 1 | 1 | test data of ORDER1(ID=1,BJ001) | 2016-02-12 17:51:30 |

+----+--------+---------------------+----+----------+------------+-----------------------------------+---------------------+

1 row in set (0.01 sec)

mysql>

【】mycat.log后台日志debug信息，看到走的route路由是dn21和dn22，所有的节点路由都走了：

02/12 18:22:51.444 DEBUG [$_NIOREACTOR-2-RW] (ServerQueryHandler.java:56) -ServerConnection [id=3, schema=TESTDB, host=127.0.0.1, user=test,txIsolation=3, autocommit=true, schema=TESTDB]explain select t1.*,t2.* from ORDER1 t1,ORDER_DETAIL t2 where t2.ORD_STATUS='1' and t2.ID=1 and t1.ID=t2.ORDER_ID

02/12 18:22:51.444 DEBUG [$_NIOREACTOR-2-RW] (EnchachePool.java:76) -SQLRouteCache miss cache ,key:TESTDB select t1.*,t2.* from ORDER1 t1,ORDER_DETAIL t2 where t2.ORD_STATUS='1' and t2.ID=1 and t1.ID=t2.ORDER_ID

02/12 18:22:51.448 DEBUG [$_NIOREACTOR-2-RW] (EnchachePool.java:59) -SQLRouteCache add cache ,key:TESTDB select t1.*,t2.* from ORDER1 t1,ORDER_DETAIL t2 where t2.ORD_STATUS='1' and t2.ID=1 and t1.ID=t2.ORDER_ID value:select t1.*,t2.* from ORDER1 t1,ORDER_DETAIL t2 where t2.ORD_STATUS='1' and t2.ID=1 and t1.ID=t2.ORDER_ID, route={

1 -> dn21{select t1.*,t2.* from ORDER1 t1,ORDER_DETAIL t2 where t2.ORD_STATUS='1' and t2.ID=1 and t1.ID=t2.ORDER_ID}

2 -> dn22{select t1.*,t2.* from ORDER1 t1,ORDER_DETAIL t2 where t2.ORD_STATUS='1' and t2.ID=1 and t1.ID=t2.ORDER_ID}

}

02/12 18:23:01.214 DEBUG [Timer0] (ConnectionHeartBeatHandler.java:52) -do heartbeat for con MySQLConnection [id=18, lastTime=1455272581207, user=root, schema=db2, old shema=db2, borrowed=true, fromSlaveDB=false, threadId=18, charset=latin1, txIsolation=0, autocommit=true, attachment=null, respHandler=null, host=12.23.2.11, port=3317, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]

02/12 18:23:01.215 DEBUG [Timer0] (ConnectionHeartBeatHandler.java:52) -do heartbeat for con MySQLConnection [id=22, lastTime=1455272581207, user=root, schema=db2, old shema=db2, borrowed=true, fromSlaveDB=false, threadId=21, charset=latin1, txIsolation=0, autocommit=true, attachment=null, respHandler=null, host=12.23.2.11, port=3317, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]

1feb1a8d33912a4d6a4c5a20c4d6e5fe.png

图片1.jpg喎�"/kf/ware/vc/" target="_blank" class="keylink">vcD4NCjxociAvPg0KPGg0IGlkPQ=="27-总结当子表与父表的关联字段正好是父表的分片字段时子表直接根据父表规则进行分片在数据录入的时候子表直接放在父表的分片上面在进行关联查询join的时候走的是">2.7 总结：当子表与父表的关联字段正好是父表的分片字段时，子表直接根据父表规则进行分片，在数据录入的时候子表直接放在父表的分片上面，在进行关联查询join的时候，走的是

3 .父表的分片字段为其他字段，以PROVINCE字段分片，字表的分片字段与主表ID关联，配置为ＥＲ分片

3.1 各种配置

在schema.xml里面添加表配置

在rule.xml里面设定分片规则

PROVINCE

province-str-split

1

partition-hash-str-pro.txt

0

新添加partition-hash-str-pro.txt

[root@test_1_11conf]# more partition-hash-str-pro.txt

beijing=0

shanghai=1

tianjing=2

[root@test_1_11conf]#

然后重启mycat或者重新加载mycat

3.2 建表

CREATE TABLE ORDER2(ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,PROVINCE VARCHAR(16),SN VARCHAR(64),CREATE_TIME DATETIME);

CREATE TABLE ORDER_DETAIL2(ID INT AUTO_INCREMENT PRIMARY KEY, ORDER_ID INT,ORD_STATUS CHAR(1),ADDRESS VARCHAR(128),CREATE_TIME DATETIME,CONSTRAINT FK_ORDID21 FOREIGN KEY (ORDER_ID) REFERENCES ORDER2 (ID));

mysql> CREATE TABLE ORDER2(ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,SN VARCHAR(64),CREATE_TIME DATETIME);

Query OK, 0 rows affected (0.05 sec)

mysql> CREATE TABLE ORDER_DETAIL2(ID INT AUTO_INCREMENT PRIMARY KEY, ORDER_ID INT,ORD_STATUS CHAR(1),ADDRESS VARCHAR(128),CREATE_TIME DATETIME,CONSTRAINT FK_ORDID21 FOREIGN KEY (ORDER_ID) REFERENCES ORDER2 (ID));

Query OK, 0 rows affected (0.02 sec)

mysql>

3.2 录入数据，一组组录入，涉及到外键关系：

INSERT INTO ORDER2(ID,PROVINCE,SN,CREATE_TIME) VALUES(1,'beijing','2BJ0001',NOW());

INSERT INTO ORDER_DETAIL2(ID,ORDER_ID,ORD_STATUS,ADDRESS,CREATE_TIME) VALUES (1,1,'1','test data of ORDER1(ID=1,2BJ0001) ',NOW());

mysql> INSERT INTO ORDER2(ID,PROVINCE,SN,CREATE_TIME) VALUES(1,'beijing','2BJ0001',NOW());

Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO ORDER_DETAIL2(ID,ORDER_ID,ORD_STATUS,ADDRESS,CREATE_TIME) VALUES (1,1,'1','test data of ORDER1(ID=1,2BJ0001) ',NOW());

Query OK, 1 row affected (0.21 sec)

mysql>

INSERT INTO ORDER2(ID,PROVINCE,SN,CREATE_TIME) VALUES(3,'shanghai','2SHH001',NOW());

INSERT INTO ORDER_DETAIL2(ID,ORDER_ID,ORD_STATUS,ADDRESS,CREATE_TIME) VALUES (3,3,'1','test data of ORDER1(ID=3,2SHH001) ',NOW());

mysql> INSERT INTO ORDER2(ID,PROVINCE,SN,CREATE_TIME) VALUES(3,'shanghai','2SHH001',NOW());

Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO ORDER_DETAIL2(ID,ORDER_ID,ORD_STATUS,ADDRESS,CREATE_TIME) VALUES (3,3,'1','test data of ORDER1(ID=3,2SHH001) ',NOW());

Query OK, 1 row affected (0.41 sec)

mysql>

INSERT INTO ORDER2(ID,PROVINCE,SN,CREATE_TIME) VALUES(4,'beijing','2GZH004',NOW());

INSERT INTO ORDER_DETAIL2(ID,ORDER_ID,ORD_STATUS,ADDRESS,CREATE_TIME) VALUES (4,4,'1','test data of ORDER1(ID=4,2GZH004) ',NOW());

mysql> INSERT INTO ORDER2(ID,PROVINCE,SN,CREATE_TIME) VALUES(4,'beijing','2GZH004',NOW());

Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO ORDER_DETAIL2(ID,ORDER_ID,ORD_STATUS,ADDRESS,CREATE_TIME) VALUES (4,4,'1','test data of ORDER1(ID=4,2GZH004) ',NOW());

Query OK, 1 row affected (0.21 sec)

mysql>

INSERT INTO ORDER2(ID,PROVINCE,SN,CREATE_TIME) VALUES(6,'shanghai','2WUHAN006',NOW());

INSERT INTO ORDER_DETAIL2(ID,ORDER_ID,ORD_STATUS,ADDRESS,CREATE_TIME) VALUES (6,4,'1','test data of ORDER1(ID=6,2WUHAN006) ',NOW());

mysql> INSERT INTO ORDER2(ID,PROVINCE,SN,CREATE_TIME) VALUES(6,'shanghai','2WUHAN006',NOW());

Query OK, 1 row affected (0.02 sec)

mysql> INSERT INTO ORDER_DETAIL2(ID,ORDER_ID,ORD_STATUS,ADDRESS,CREATE_TIME) VALUES (6,4,'1','test data of ORDER1(ID=6,2WUHAN006) ',NOW());

Query OK, 1 row affected (0.22 sec)

mysql>

mysql> INSERT INTO ORDER_DETAIL2(ID,ORDER_ID,ORD_STATUS,ADDRESS,CREATE_TIME) VALUES (6,9,'1','test data of ORDER1(ID=6,2WUHAN006) ',NOW());

ERROR 1064 (HY000): can't find (root) parent sharding node for sql:INSERT INTO ORDER_DETAIL2(ID,ORDER_ID,ORD_STATUS,ADDRESS,CREATE_TIME) VALUES (6,9,'1','test data of ORDER1(ID=6,2WUHAN006) ',NOW())

mysql>

3.3 数据验证

[root@test_1_11~]# /usr/local/mysql56s1/bin/mysql -uroot -p --socket=/usr/local/mysql56s1/mysql.sock -e "select @@port;select * from db3.ORDER2; select * from db3.ORDER_DETAIL2";

Enter password:

+--------+

| @@port |

+--------+

| 3327 |

+--------+

+----+----------+-----------+---------------------+

| ID | PROVINCE | SN | CREATE_TIME |

+----+----------+-----------+---------------------+

| 3 | shanghai | 2SHH001 | 2016-02-12 17:24:00 |

| 6 | shanghai | 2WUHAN006 | 2016-02-12 17:24:14 |

+----+----------+-----------+---------------------+

+----+----------+------------+-------------------------------------+---------------------+

| ID | ORDER_ID | ORD_STATUS | ADDRESS | CREATE_TIME |

+----+----------+------------+-------------------------------------+---------------------+

| 3 | 3 | 1 | test data of ORDER1(ID=3,2SHH001) | 2016-02-12 17:24:04 |

+----+----------+------------+-------------------------------------+---------------------+

[root@test_1_11~]#

[root@test_1_11~]# /usr/local/mysql56s1/bin/mysql -uroot -p --socket=/usr/local/mysql56m1/mysql.sock -e "select @@port;select * from db3.ORDER2; select * from db3.ORDER_DETAIL2";

Enter password:

+--------+

| @@port |

+--------+

| 3317 |

+--------+

+----+----------+---------+---------------------+

| ID | PROVINCE | SN | CREATE_TIME |

+----+----------+---------+---------------------+

| 1 | beijing | 2BJ0001 | 2016-02-12 17:23:46 |

| 4 | beijing | 2GZH004 | 2016-02-12 17:24:07 |

+----+----------+---------+---------------------+

+----+----------+------------+---------------------------------------+---------------------+

| ID | ORDER_ID | ORD_STATUS | ADDRESS | CREATE_TIME |

+----+----------+------------+---------------------------------------+---------------------+

| 1 | 1 | 1 | test data of ORDER1(ID=1,2BJ0001) | 2016-02-12 17:23:50 |

| 4 | 4 | 1 | test data of ORDER1(ID=4,2GZH004) | 2016-02-12 17:24:11 |

| 6 | 4 | 1 | test data of ORDER1(ID=6,2WUHAN006) | 2016-02-12 17:24:17 |

+----+----------+------------+---------------------------------------+---------------------+

[root@test_1_11~]#

3.4 mycat.log中debug日志显示，通过关键字2BJ0001来检索父子数据记录的录入情况

vim mycat.log

02/12 17:23:50.530 DEBUG [$_NIOREACTOR-2-RW] (RouterUtil.java:1188) -find root parent's node sql select order2.id from order2 where order2.id=1

02/12 17:23:50.531 DEBUG [BusinessExecutor3] (EnchachePool.java:76) -ER_SQL2PARENTID miss cache ,key:TESTDB:select order2.id from order2 where order2.id=1

02/12 17:23:50.531 DEBUG [BusinessExecutor3] (FetchStoreNodeOfChildTableHandler.java:73) -find child node with sql:select order2.id from order2 where order2.id=1

02/12 17:23:50.531 DEBUG [BusinessExecutor3] (FetchStoreNodeOfChildTableHandler.java:81) -execute in datanode dn21

02/12 17:23:50.531 DEBUG [BusinessExecutor3] (PhysicalDBPool.java:452) -select read source hostM2 for dataHost:m1

02/12 17:23:50.532 DEBUG [$_NIOREACTOR-0-RW] (FetchStoreNodeOfChildTableHandler.java:154) -received rowResponse response,1 from MySQLConnection [id=8, lastTime=1455269030515, user=root, schema=db3, old shema=db3, borrowed=true, fromSlaveDB=false, threadId=15, charset=latin1, txIsolation=0, autocommit=true, attachment=dn21, respHandler=org.opencloudb.mysql.nio.handler.FetchStoreNodeOfChildTableHandler@dde0e41, host=12.23.2.11, port=3317, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]

02/12 17:23:50.533 DEBUG [$_NIOREACTOR-0-RW] (PhysicalDatasource.java:403) -release channel MySQLConnection [id=8, lastTime=1455269030515, user=root, schema=db3, old shema=db3, borrowed=true, fromSlaveDB=false, threadId=15, charset=latin1, txIsolation=0, autocommit=true, attachment=null, respHandler=null, host=12.23.2.11, port=3317, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]

02/12 17:23:50.732 DEBUG [BusinessExecutor4] (RouterUtil.java:1213) -found partion node for child table to insert dn21 sql :INSERT INTO ORDER_DETAIL2(ID,ORDER_ID,ORD_STATUS,ADDRESS,CREATE_TIME) VALUES (1,1,'1','test data of ORDER1(ID=1,2BJ0001) ',NOW())

02/12 17:23:50.733 DEBUG [BusinessExecutor4] (NonBlockingSession.java:113) -ServerConnection [id=3, schema=TESTDB, host=127.0.0.1, user=test,txIsolation=3, autocommit=true, schema=TESTDB]INSERT INTO ORDER_DETAIL2(ID,ORDER_ID,ORD_STATUS,ADDRESS,CREATE_TIME) VALUES (1,1,'1','test data of ORDER1(ID=1,2BJ0001) ',NOW()), route={

1 -> dn21{INSERT INTO ORDER_DETAIL2(ID,ORDER_ID,ORD_STATUS,ADDRESS,CREATE_TIME) VALUES (1,1,'1','test data of ORDER1(ID=1,2BJ0001) ',NOW())}

} rrs

02/12 17:23:50.735 DEBUG [$_NIOREACTOR-3-RW] (NonBlockingSession.java:229) -release connection MySQLConnection [id=7, lastTime=1455269030715, user=root, schema=db3, old shema=db3, borrowed=true, fromSlaveDB=false, threadId=14, charset=latin1, txIsolation=3, autocommit=true, attachment=dn21{INSERT INTO ORDER_DETAIL2(ID,ORDER_ID,ORD_STATUS,ADDRESS,CREATE_TIME) VALUES (1,1,'1','test data of ORDER1(ID=1,2BJ0001) ',NOW())}, respHandler=SingleNodeHandler [node=dn21{INSERT INTO ORDER_DETAIL2(ID,ORDER_ID,ORD_STATUS,ADDRESS,CREATE_TIME) VALUES (1,1,'1','test data of ORDER1(ID=1,2BJ0001) ',NOW())}, packetId=0], host=12.23.2.11, port=3317, statusSync=null, writeQueue=0, modifiedSQLExecuted=true]

02/12 17:23:50.735 DEBUG [$_NIOREACTOR-3-RW] (PhysicalDatasource.java:403) -release channel MySQLConnection [id=7, lastTime=1455269030715, user=root, schema=db3, old shema=db3, borrowed=true, fromSlaveDB=false, threadId=14, charset=latin1, txIsolation=3, autocommit=true, attachment=null, respHandler=null, host=12.23.2.11, port=3317, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]

【】可以看到在17:23:50.532处，看到find root parent’s node sql select order2.id from order2 where order2.id=1这样的日志记录，就是通过外键锁涉及的父表的关联字段id去找所在分片记录，然后在17:23:50.732看到已经路由出来了分片地址route={dn21。

3.5 如果录入不存在的父表数据的时候路由情况

数据录入：

mysql> INSERT INTO ORDER_DETAIL2(ID,ORDER_ID,ORD_STATUS,ADDRESS,CREATE_TIME) VALUES (6,9,'1','test data of ORDER1(ID=6,2WUHAN006) ',NOW());

ERROR 1064 (HY000): can't find (root) parent sharding node for sql:INSERT INTO ORDER_DETAIL2(ID,ORDER_ID,ORD_STATUS,ADDRESS,CREATE_TIME) VALUES (6,9,'1','test data of ORDER1(ID=6,2WUHAN006) ',NOW())

mysql>

查看日志mycat.log情况：

02/12 17:54:43.351 DEBUG [$_NIOREACTOR-2-RW] (ServerQueryHandler.java:56) -ServerConnection [id=3, schema=TESTDB, host=127.0.0.1, user=test,txIsolation=3, autocommit=true, schema=TESTDB]INSERT INTO ORDER_DETAIL2(ID,ORDER_ID,ORD_STATUS,ADDRESS,CREATE_TIME) VALUES (6,9,'1','test data of ORDER1(ID=6,2WUHAN006) ',NOW())

02/12 17:54:43.352 DEBUG [$_NIOREACTOR-2-RW] (RouterUtil.java:1188) -find root parent's node sql select order2.id from order2 where order2.id=9

02/12 17:54:43.353 DEBUG [BusinessExecutor7] (EnchachePool.java:76) -ER_SQL2PARENTID miss cache ,key:TESTDB:select order2.id from order2 where order2.id=9

02/12 17:54:43.353 DEBUG [BusinessExecutor7] (FetchStoreNodeOfChildTableHandler.java:73) -find child node with sql:select order2.id from order2 where order2.id=9

02/12 17:54:43.353 DEBUG [BusinessExecutor7] (FetchStoreNodeOfChildTableHandler.java:81) -execute in datanode dn21

02/12 17:54:43.353 DEBUG [BusinessExecutor7] (PhysicalDBPool.java:452) -select read source hostM2 for dataHost:m1

02/12 17:54:43.354 DEBUG [$_NIOREACTOR-3-RW] (PhysicalDatasource.java:403) -release channel MySQLConnection [id=7, lastTime=1455270883346, user=root, schema=db3, old shema=db3, borrowed=true, fromSlaveDB=false, threadId=14, charset=latin1, txIsolation=3, autocommit=true, attachment=null, respHandler=null, host=12.23.2.11, port=3317, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]

02/12 17:54:43.554 DEBUG [BusinessExecutor7] (FetchStoreNodeOfChildTableHandler.java:81) -execute in datanode dn22

02/12 17:54:43.554 DEBUG [BusinessExecutor7] (PhysicalDBPool.java:452) -select read source hostM2 for dataHost:m2

02/12 17:54:43.554 DEBUG [$_NIOREACTOR-3-RW] (PhysicalDatasource.java:403) -release channel MySQLConnection [id=15, lastTime=1455270883547, user=root, schema=db3, old shema=db3, borrowed=true, fromSlaveDB=false, threadId=14, charset=latin1, txIsolation=3, autocommit=true, attachment=null, respHandler=null, host=12.23.2.11, port=3327, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]

02/12 17:54:43.805 WARN [BusinessExecutor3] (RouterUtil.java:1206) -org.opencloudb.server.NonBlockingSession@634984c8INSERT INTO ORDER_DETAIL2(ID,ORDER_ID,ORD_STATUS,ADDRESS,CREATE_TIME) VALUES (6,9,'1','test data of ORDER1(ID=6,2WUHAN006) ',NOW()) err:can't find (root) parent sharding node for sql:INSERT INTO ORDER_DETAIL2(ID,ORDER_ID,ORD_STATUS,ADDRESS,CREATE_TIME) VALUES (6,9,'1','test data of ORDER1(ID=6,2WUHAN006) ',NOW())

看到，遍历了所有节点dn21和dn22之后，select order2.id from order2 where order2.id=9执行后没有找到记录，就报错了 can’t find (root) parent sharding node for sql:

3.6 join关联查询涉及到的路由，是全节点遍历查询

mysql> explain select t1.*,t2.* from ORDER2 t1,ORDER_DETAIL2 t2 where t2.ORD_STATUS='1' and t2.ID=6;

+-----------+--------------------------------------------------------------------------------------+

| DATA_NODE | SQL |

+-----------+--------------------------------------------------------------------------------------+

| dn21 | select t1.*,t2.* from ORDER2 t1,ORDER_DETAIL2 t2 where t2.ORD_STATUS='1' and t2.ID=6 |

| dn22 | select t1.*,t2.* from ORDER2 t1,ORDER_DETAIL2 t2 where t2.ORD_STATUS='1' and t2.ID=6 |

+-----------+--------------------------------------------------------------------------------------+

2 rows in set (0.00 sec)

mysql>

mysql> select t1.*,t2.* from ORDER2 t1,ORDER_DETAIL2 t2 where t2.ORD_STATUS='1' and t2.ID=6;

+----+----------+---------+---------------------+----+----------+------------+---------------------------------------+---------------------+

| ID | PROVINCE | SN | CREATE_TIME | ID | ORDER_ID | ORD_STATUS | ADDRESS | CREATE_TIME |

+----+----------+---------+---------------------+----+----------+------------+---------------------------------------+---------------------+

| 1 | beijing | 2BJ0001 | 2016-02-12 17:23:46 | 6 | 4 | 1 | test data of ORDER1(ID=6,2WUHAN006) | 2016-02-12 17:24:17 |

| 4 | beijing | 2GZH004 | 2016-02-12 17:24:07 | 6 | 4 | 1 | test data of ORDER1(ID=6,2WUHAN006) | 2016-02-12 17:24:17 |

+----+----------+---------+---------------------+----+----------+------------+---------------------------------------+---------------------+

2 rows in set (0.01 sec)

mysql>

02/12 18:02:23.370 DEBUG [$_NIOREACTOR-2-RW] (ServerQueryHandler.java:56) -ServerConnection [id=3, schema=TESTDB, host=127.0.0.1, user=test,txIsolation=3, autocommit=true, schema=TESTDB]select t1.*,t2.* from ORDER2 t1,ORDER_DETAIL2 t2 where t2.ORD_STATUS='1' and t2.ID=6

02/12 18:02:23.371 DEBUG [$_NIOREACTOR-2-RW] (EnchachePool.java:76) -SQLRouteCache miss cache ,key:TESTDBselect t1.*,t2.* from ORDER2 t1,ORDER_DETAIL2 t2 where t2.ORD_STATUS='1' and t2.ID=6

02/12 18:02:23.373 DEBUG [$_NIOREACTOR-2-RW] (EnchachePool.java:59) -SQLRouteCache add cache ,key:TESTDBselect t1.*,t2.* from ORDER2 t1,ORDER_DETAIL2 t2 where t2.ORD_STATUS='1' and t2.ID=6 value:select t1.*,t2.* from ORDER2 t1,ORDER_DETAIL2 t2 where t2.ORD_STATUS='1' and t2.ID=6, route={

1 -> dn21{select t1.*,t2.* from ORDER2 t1,ORDER_DETAIL2 t2 where t2.ORD_STATUS='1' and t2.ID=6}

2 -> dn22{select t1.*,t2.* from ORDER2 t1,ORDER_DETAIL2 t2 where t2.ORD_STATUS='1' and t2.ID=6}

}

02/12 18:02:23.373 DEBUG [$_NIOREACTOR-2-RW] (NonBlockingSession.java:113) -ServerConnection [id=3, schema=TESTDB, host=127.0.0.1, user=test,txIsolation=3, autocommit=true, schema=TESTDB]select t1.*,t2.* from ORDER2 t1,ORDER_DETAIL2 t2 where t2.ORD_STATUS='1' and t2.ID=6, route={

1 -> dn21{select t1.*,t2.* from ORDER2 t1,ORDER_DETAIL2 t2 where t2.ORD_STATUS='1' and t2.ID=6}

2 -> dn22{select t1.*,t2.* from ORDER2 t1,ORDER_DETAIL2 t2 where t2.ORD_STATUS='1' and t2.ID=6}

} rrs

3.7 【】总结：当子表与父表的关联字段不是父表的分片字段时，必须通过查找对应的父表记录来确认子表所在分片，如果找不到则会抛出错误，在join查询的时候，路由走的是所有分片节点。

4，报错处理总结

4.1 报错1：

mysql> INSERT INTO ORDER2(ID,SN,CREATE_TIME) VALUES(1,'2BJ0001',NOW());

ERROR 1064 (HY000): For input string: "2BJ0001"

mysql>

报错是因为分片字段是str字符串，所以需要修改分片规则1中type从0改成1，0是数字型分片，1是字符串分片。

4.2 报错2：

mysql> INSERT INTO ORDER_DETAIL2(ID,ORDER_ID,ORD_STATUS,ADDRESS,CREATE_TIME) VALUES (1,1,'1','test data of ORDER1(ID=1,2BJ0001) ',NOW());

ERROR 1064 (HY000): can't find (root) parent sharding node for sql:INSERT INTO ORDER_DETAIL2(ID,ORDER_ID,ORD_STATUS,ADDRESS,CREATE_TIME) VALUES (1,1,'1','test data of ORDER1(ID=1,2BJ0001) ',NOW())

mysql>

解决方案1：此类ER表癿揑入操作丌能做为一个亊务迕行数捤提交，如果父子表在一个亊务中迕行提交，显然在亊务没有提交前子表是无法查刡父表癿数捤癿，因此就无法确定sharding node。如果是ER关系癿表在揑入数捤时丌能在同一个亊务中提交数捤，叧能分开提交。

4.3 报错3

解决方案2：上面的方案2个事务搞不定，所以查看后台mycat.log，日志

02/12 14:32:09.378 DEBUG [$_NIOREACTOR-3-RW] (ServerQueryHandler.java:56) -ServerConnection [id=1, schema=TESTDB, host=127.0.0.1, user=test,txIsolation=3, autocommit=true, schema=TESTDB]INSERT INTO ORDER_DETAIL2(ID,ORDER_ID,ORD_STATUS,ADDRESS,CREATE_TIME) VALUES (1,1,'1','test data of ORDER1(ID=1,2BJ0001) ',NOW())

02/12 14:32:09.506 DEBUG [$_NIOREACTOR-3-RW] (RouterUtil.java:1188) -find root parent's node sql select order2.id from order2 where order2.id=1

02/12 14:32:09.513 DEBUG [BusinessExecutor5] (EnchachePool.java:76) -ER_SQL2PARENTID miss cache ,key:TESTDB:select order2.id from order2 where order2.id=1

02/12 14:32:09.513 DEBUG [BusinessExecutor5] (FetchStoreNodeOfChildTableHandler.java:73) -find child node with sql:select order2.id from order2 where order2.id=1

02/12 14:32:09.514 DEBUG [BusinessExecutor5] (FetchStoreNodeOfChildTableHandler.java:81) -execute in datanode dn21

02/12 14:32:09.514 DEBUG [BusinessExecutor5] (PhysicalDBPool.java:452) -select read source hostM2 for dataHost:m1

02/12 14:32:09.535 WARN [$_NIOREACTOR-3-RW] (FetchStoreNodeOfChildTableHandler.java:135) -errorResponse 1146 Table 'db3.order2' doesn't exist

02/12 14:32:09.535 DEBUG [$_NIOREACTOR-3-RW] (PhysicalDatasource.java:403) -release channel MySQLConnection [id=3, lastTime=1455258729497, user=root, schema=db3, old shema=db3, borrowed=true, fromSlaveDB=false, threadId=290, charset=latin1, txIsolation=0, autocommit=true, attachment=null, respHandler=null, host=12.23.2.11, port=3317, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]

02/12 14:32:09.715 DEBUG [BusinessExecutor5] (FetchStoreNodeOfChildTableHandler.java:81) -execute in datanode dn22

02/12 14:32:09.715 DEBUG [BusinessExecutor5] (PhysicalDBPool.java:452) -select read source hostM2 for dataHost:m2

02/12 14:32:09.716 WARN [$_NIOREACTOR-1-RW] (FetchStoreNodeOfChildTableHandler.java:135) -errorResponse 1146 Table 'db3.order2' doesn't exist

后台执行sql确实找不到

mysql> select order2.id from order2 where order2.id=1;

ERROR 1105 (HY000): Table 'db3.order2' doesn't exist

mysql>

原因是2个datanode是互为主从的关系，dn21和dn22是mm结构，然后停止mm结构，2个都是单独的mysql，然后重建ORDER2和ORDER_DETAIL2表，再执行insert操作，ok，问题解决了。

4.4 报错4

解决方案3：表名字大小写的问题导致

mysql> INSERT INTO ORDER_DETAIL2(ID,ORDER_ID,ORD_STATUS,ADDRESS,CREATE_TIME) VALUES (1,1,'1','test data of ORDER1(ID=1,2BJ0001) ',NOW());

ERROR 1064 (HY000): can't find (root) parent sharding node for sql:INSERT INTO ORDER_DETAIL2(ID,ORDER_ID,ORD_STATUS,ADDRESS,CREATE_TIME) VALUES (1,1,'1','test data of ORDER1(ID=1,2BJ0001) ',NOW())

mysql>

后台报错：

02/12 17:04:24.583 DEBUG [$_NIOREACTOR-0-RW] (ServerQueryHandler.java:56) -ServerConnection [id=5, schema=TESTDB, host=127.0.0.1, user=test,txIsolation=3, autocommit=true, schema=TESTDB]INSERT INTO ORDER_DETAIL2(ID,ORDER_ID,ORD_STATUS,ADDRESS,CREATE_TIME) VALUES (1,1,'1','test data of ORDER1(ID=1,2BJ0001) ',NOW())

02/12 17:04:24.584 DEBUG [$_NIOREACTOR-0-RW] (RouterUtil.java:1188) -find root parent's node sql select order2.id from order2 where order2.id=1

02/12 17:04:24.584 DEBUG [BusinessExecutor6] (EnchachePool.java:76) -ER_SQL2PARENTID miss cache ,key:TESTDB:select order2.id from order2 where order2.id=1

02/12 17:04:24.585 DEBUG [BusinessExecutor6] (FetchStoreNodeOfChildTableHandler.java:73) -find child node with sql:select order2.id from order2 where order2.id=1

02/12 17:04:24.585 DEBUG [BusinessExecutor6] (FetchStoreNodeOfChildTableHandler.java:81) -execute in datanode dn21

02/12 17:04:24.585 DEBUG [BusinessExecutor6] (PhysicalDBPool.java:452) -select read source hostM2 for dataHost:m1

02/12 17:04:24.585 WARN [$_NIOREACTOR-3-RW] (FetchStoreNodeOfChildTableHandler.java:135) -errorResponse 1146 Table 'db3.order2' doesn't exist

mysql> select * from db3.order2;

ERROR 1146 (42S02): Table 'db3.order2' doesn't exist

mysql> select * from db3.ORDER2;

Empty set (0.01 sec)

mysql>

mysql>

[mysqld]

lower_case_table_names = 1

然后重启mysql已经mycat服务，再次进行check。

丁丁TINTIN
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
mysql er分片_MyCat 学习笔记 第十篇.数据分片 之 ER分片
weixin_35813719的博客
02-07 290
1 应用场景这篇来说下mycat中自带的er关系分片，所谓er关系分片即可以理解为有关联关系表之间数据分片。类似于订单主表与订单详情表间的分片存储规则。本文所说的er分片分为两种：a. 依据主键进行数据分片，验证发现主表数据保存在第1个datanode中，子表数据根据分片规则存储。b. 依据分片关键字段进行分片，验证发现主表与子表根据分片规则存储，且保存在相同的分片内。接下来，可以下实际配置与数据...
Mycat常见问题与解决方案---宜将剩勇追穷寇，不可沽名学霸王
热门推荐
wangshuang1631的博客
03-17 2万+
1 Mycat目前有哪些功能与特性？答: • 支持 SQL 92标准 • 支持Mysql集群，可以作为Proxy使用 • 支持JDBC连接多数据库 • 支持NoSQL数据库 • 支持galera for mysql集群，percona-cluster或者mariadb cluster，提供高可用性数据分片集群 • 自动故障切换，高可用性 • 支持读写分离，支持Mysql双主多从，以及一
mycat-错误-ERROR 1064 (HY000): bad insert sql (sharding column:ID not provided,INSERT INTO
龙哥(webnum)说的，每天进步一点点，总有一天会实现梦想！
08-07 1万+
mycat在插入数据的时候出现如下错误： ERROR 1064 (HY000): bad insert sql (sharding column:ID not provided,INSERT INTO 一、前提： 1、表是分库表。 2、表自增ID做了db方式的自增配置 二、出现的错误可能问题为： 1、rule规则里的规则配置有误。columns 字段不是指定的分区字段
ER分片表
weixin_43356218的博客
10-29 931
ER分片表 1.ER分片表概念 问题描述： 例如有订单表和订单商品表，两个表都需要分片 如果两个表的关联查询数据分到不同分片，则mycat关联查询时，无法拿到完整数据（不支持分库查） 解决思路：将关联的商品和订单数据放入同一个分片，如何保证？ 可以利用外键字段（两个表的外键和主键字段内容相同）进行hash一致性计算，计算结果一致。 ER分片表逻辑： er分片表是mycat首创 先定义父级表...
《Mycat分布式数据库架构》之ER分片
程序员大佬超的博客
09-13 343
前面的文章由于我们进行了数据的水平切分，对于员工表EMPLOYEES和部门表DEPARTMENTS来说，部门ID是员工表的外键，我们只对EMPLOYEES表进行了切分，所以对于一些数据节点来说是没有部门表的，关联查询就会出问题，所以我们需要将互相依赖的表能够按照同一规则切分到相同的节点上，避免跨库Join关联查询，这里我们就需要采用mycat 的ER分片。
快速学习-Mycat ER 分片子表数据插入报错
逍遥云恋
02-20 1212
Mycat ER 分片子表数据插入报错 答：一般都是插入子表时出现不能找到父节点的报错。报错信息如： [Err] 1064 - can't find (root) parent sharding node for sql:。此类 ER 表的插入操作不能做为一个事务进行数据提交，如果父子表在一个事务中进行提交，显然在事务没有提交前子表是无法查到父表的数据的，因此就无法确定 sharding node。如果是 ER 关系的表在插入数据时不能在同一个事务中提交数据，只能分开提交。 ...
mysql的分库分表（Mycat方案）
最新发布
11-19
Mycat是一款开源的分布式数据库中间件，它支持MySQL协议，能够实现数据的自动分片、读写分离、分布式事务等功能，为大型Web应用提供高性能的数据存储解决方案。 1. **Mycat安装** - 下载：Mycat可以从其GitHub仓库...
Mycat2数据库中间件-其他
06-11
支持通过全局表，ER关系的分片策略，实现了高效的多表join查询。支持多租户方案。支持分布式事务（弱xa）。支持全局序列号，解决分布式下的主键生成问题。分片规则丰富，插件化开发，易于扩展。强大的web，命令行...
Mycat-server-1.6-RELEASE源码
10-16
支持通过全局表，ER关系的分片策略，实现了高效的多表join查询。 支持多租户方案。 支持分布式事务（弱xa）。 支持XA分布式事务（1.6.5）。 支持全局序列号，解决分布式下的主键生成问题。 分片规则丰富，插件化开发...
mycat环境搭建详细
02-22
Mycat 是一个开源的数据库中间件，它可以将多个 MySQL 服务器集群起来，形成一个高性能、可扩展的数据库集群。下面是 Mycat 环境搭建的详细步骤和相关知识点： 解压文件和权限设置 在开始安装 Mycat 之前，需要解...
Mycat数据库中间件技术最佳实践手册-中台
10-12
3.6. **ER表**：ER表（Entity-Relationship Table）是指在Mycat中，当逻辑表需要关联多个分片表时，通过ER表实现跨分片的关联查询。 ### 4. Mycat配置与实践 Mycat的配置主要包括数据源配置、服务器配置、分片规则...
mysql er分片_mycat ER分片的场景详细分析学习
weixin_32392583的博客
02-07 161
mycat高可用 ER分片的场景详细分析1：ER分片关系简介有一类业务，例如订单(ORDER)跟订单明细表(ORDER_DETAIL),明细表会依赖二订单，就是该会存在表的主从关系，这类似业务的切分可以抽象出合适的切分规则，比如根据用户ID切分,其它相关的表都依赖于用户ID，再或者根据订单ID进行切分，总之部分业务总会可以抽象出父子关系的表。这类表适用于ER分片表，子表的记录与所关联的父表记录存放...
Mycat ERROR 1064 (HY000): bad insert sql
lwei_998的专栏
07-29 9246
CREATE TABLE `t` (   `tid` int(10) NOT NULL AUTO_INCREMENT,   `name` varchar(10) DEFAULT NULL,   `sdate` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,   PRIMARY KEY (`tid`) ) ENGINE=In
MyCat使用日记 -- 单库分片
oeSeven的专栏
09-10 591
今天记录一篇MyCat的使用日记。 背景：生产上有一张表的数据量可以轻轻松松上千万，随即想到要做分表。 对于MyCat的介绍这里就不做赘述了，有很多帖子介绍，下面讲一下MyCat从安装到配置再到使用的过程。 一、下载MyCat： 这里是官网，本次使用的是1.6.5版本，也建议使用此版本以上的稳定版本，连接在下方： MyCat官网：http://www.mycat.org.cn/ 下载地址：http://dl.mycat.org.cn/1.6.5/ 下载直接解压，目录...
第一章 记 实际使用mycat管理mysql集群发现的问题
qq_29824979的博客
11-25 1016
前提  已经完成了mysql的两组双机热备搭建   今天准备使用mycat管理 mysql集群时候发现一个问题   完成了mycat的所有配置  准备测试mycat的分片功能  当在mycat中插入数据时候返回 mycat-错误-ERROR 1064 (HY000): bad insert sql (sharding column:ID not provided,INSERT INTO ...
sharding sphere4.0 + mybatis-plus 集成
chenmiwei1262的博客
06-06 5989
demo地址：https://gitee.com/luci-fast/mybatis-plus-sharding 最近进行技术重构，考虑到服务拆分与分表分库，首先想到了mycat，毕竟mycat是代理，对于代码方面来说，能做到零侵入。 但是了解了一下发现mycat社区活跃度，与一些数据分区难点...
mycat 报错：bad insert sql
Z_yu_cc的博客
04-01 1654
导入数据时出现错误： bad insert sql (sharding column:USER_ID not provided,INSERT INTO book008 (bid, bname) 前提操作我进行分片处理，自定义分片规则 解决办法：把自定义里的方法user_id改成实际数据库的字段名bid即可 (因为我连接的实际数据库的字段名不是user_id,) (如上图所示，我连接到实际数据库字段名是bid ,把rule.xml里的user_id改成bid即可） ...
Mycat之——实现ER分片
冰河的专栏
03-04 1280
【前言】引出ER分片 在《Mycat之——设置全局自增id》一文中，我们为水平分片的数据表设置了全局自增的id值，解决了由于分片后各自数据节点的数据表自增，导致Mycat查询数据时，出现的id值重复的问题。 此时，我们继续在Mycat下执行如下SQL语句来关联查询order_master数据表和order_detail数据表的数据。 mysql> select * from order_ma...
Mycat分布式集群实战：高可用、读写分离与分片策略
"Mycat是一款开源的分布式数据库系统，主要功能是实现对MySQL的分布式集群管理，提供数据分片、读写分离、高可用性和故障自动切换等功能。Mycat不仅支持SQL 92标准，还能作为MySQL的Proxy使用，连接其他如Oracle、...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值