Shulou(Shulou.com)05/31 Report--

This article mainly introduces "how to achieve ER fragmentation in MyCat sub-database sub-table". In the daily operation, I believe that many people have doubts about how to achieve ER fragmentation in MyCat sub-library sub-table. The editor consulted all kinds of materials and sorted out simple and easy-to-use operation methods. I hope it will be helpful to answer the doubts of "how to achieve ER fragmentation in MyCat sub-library sub-table". Next, please follow the editor to study!

Project environment:

192.168.8.30 mycat

192.168.8.31 node1

192.168.8.32 node2

192.168.8.33 node3

The MySQL of three nodes is a single instance.

1. Configure schema.xml

Select user () select user () select user ()

2. Configure rule.xml

Order_id mod-long 9

3. Configure server.xml

Mysql mycatdb

Fourth, create a test library

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

5. Start mycat

/ usr/local/mycat/bin/mycat start

View mycat Log

STATUS | wrapper | 14:12:55 on 2018-11-22 |-- > Wrapper Started as DaemonSTATUS | wrapper | 14:12:55 on 2018-11-22 | Launching a JVM...INFO | jvm 1 | 14:12:55 on 2018-11-22 | OpenJDK 64-Bit Server VM warning: ignoring option MaxPermSize=64M Support was removed in 8.0INFO | jvm 1 | 14:12:58 on 2018-11-22 | Wrapper (Version 3.2.3) http://wrapper.tanukisoftware.orgINFO | jvm 1 | 14:12:58 on 2018-11-22 | Copyright 1999-2006 Tanuki Software, Inc. All Rights Reserved.INFO | jvm 1 | 14:12:58 on 2018-11-22 | INFO | jvm 1 | 14:13:04 on 2018-11-22 | MyCAT Server startup successfully. See logs in logs/mycat.log

Log in to MySQL and view the logic table

Mysql-uroot-pmysql-P8066-h292.168.8.30mysql > show databases;+-+ | DATABASE | +-+ | mycatdb | +-+ 1 row in set (0.02 sec) mysql > use mycatdbReading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with-ADatabase changedmysql > 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 existmysql > 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)

7. Insert test data

Nine records are inserted in each of the parent and child tables

INSERT INTO order01 (order_id,number,createtime) VALUES (1); INSERT INTO orderdetail01 (orderdetail_id,order_id,order_status,addresss,createtime) VALUES (1); INSERT INTO order01 (order_id,number,createtime) VALUES (2) INSERT INTO orderdetail01 (orderdetail_id,order_id,order_status,addresss,createtime) VALUES (2) order_id,number,createtime (ID=2,steven201)', NOW (); INSERT INTO order01 (order_id,number,createtime) VALUES (3)); INSERT INTO orderdetail01 (orderdetail_id,order_id,order_status,addresss,createtime) VALUES (3) INSERT INTO order01 (order_id,number,createtime) VALUES (4 meme steven401) now (); INSERT INTO orderdetail01 (orderdetail_id,order_id,order_status,addresss,createtime) VALUES (4)); INSERT INTO order01 (order_id,number,createtime) VALUES (5) INSERT INTO orderdetail01 (orderdetail_id,order_id,order_status,addresss,createtime) VALUES (5, order_id,number,createtime, 5); INSERT INTO order01 (order_id,number,createtime) VALUES, (6); INSERT INTO orderdetail01 (orderdetail_id,order_id,order_status,addresss,createtime) VALUES (6, ID=6,steven601), NOW ()) INSERT INTO order01 (order_id,number,createtime) VALUES, INSERT INTO orderdetail01 (orderdetail_id,order_id,order_status,addresss,createtime) VALUES, INSERT INTO order01 (order_id,number,createtime) VALUES, INSERT INTO order01 (order_id,number,createtime) VALUES. INSERT INTO orderdetail01 (orderdetail_id,order_id,order_status,addresss,createtime) VALUES (8 beacons), NOW (); INSERT INTO order01 (order_id,number,createtime) VALUES (9); INSERT INTO orderdetail01 (orderdetail_id,order_id,order_status,addresss,createtime) VALUES (9) (ID=9,steven901), NOW ())

VIII. Validate data

In schema.xml, order01 and orderdetail01 shards are only configured with testdb01-03 TestDB 13-15 and TestDB 25-27, so no shards can be found except these 9 physical libraries.

The following validates the sharding information in the three 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) | 0-11-22 14:21:18 | +-- +-+ 1 row in set (2018 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) | 0-11-22 14:21:17 | +-- +-+ 1 row in set (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) | 0-11-22 14:21:18 | +-- +-+ 1 row in set (2018 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) | 0-11-22 14:21:20 | +-- +-+ 1 row in set (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) | 0-11-22 14:21:20 | +-- +-+ 1 row in set (sec) mysql > select * from testdb27.order01 +-+ | order_id | number | createtime | +-+ | 8 | steven801 | 2018-11-22 14:21: 20 | +-+ 1 row in set (0.01sec) mysql > select * from testdb27.orderdetail01 +-- +-+ | orderdetail_id | order_id | order_status | addresss | createtime | | +-+ + | 8 | 8 | 1 | steven801 (ID=8) | Steven801) | 0-11-22 14:21:20 | +-- +-+ 1 row in set (0.00 sec) so far On the "MyCat sub-database sub-table how to achieve ER fragmentation" study is over, I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.