How to realize sub-database and sub-table in MyCat
This article introduces the relevant knowledge of "how to achieve sub-database and sub-table in MyCat". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!
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.
First, 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
2. Configure schema.xml
Select user () select user () select user ()
3. Configure rule.xml
Id murmur-id 0 0 9 160
4. Configure server.xml
Mysql mycatdb
5. Start mycat
/ usr/local/mycat/bin/mycat start
View mycat Log
STATUS | wrapper | 16:48:27 on 2018-11-22 |-- > Wrapper Started as DaemonSTATUS | wrapper | 16:48:27 on 2018-11-22 | Launching a JVM...INFO | jvm 1 | 16:48:27 on 2018-11-22 | OpenJDK 64-Bit Server VM warning: ignoring option MaxPermSize=64M Support was removed in 8.0INFO | jvm 1 | 16:48:29 on 2018-11-22 | Wrapper (Version 3.2.3) http://wrapper.tanukisoftware.orgINFO | jvm 1 | 16:48:29 on 2018-11-22 | Copyright 1999-2006 Tanuki Software, Inc. All Rights Reserved.INFO | jvm 1 | 16:48:29 on 2018-11-22 | INFO | jvm 1 | 16:48:34 on 2018-11-22 | MyCAT Server startup successfully. See logs in logs/mycat.log
Log in to MySQL to view the logic table
Mysql-uroot-pmysql-P8066-h292.168.8.30mysql > show databases;+-+ | DATABASE | +-+ | mycatdb | +-+ 1 row in set (0.00 sec) mysql > use mycatdb;Reading 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 | | user03 | | user04 | +-+ 6 rows in set (0.01sec) mysql > select * from user04 ERROR 1105 (HY000): Table 'testdb03.user04' doesn't existmysql > drop table if exists user04;Query OK, 0 rows affected, 1 warning (0.15 sec) mysql > create table user04 (- > id int not null auto_increment,-> name varchar (64),-> primary key (id)->); Query OK, 0 rows affected (0.61 sec)
7. Insert test data
Insert into user04 (id,name) values, insert into user04 (id,name) values Insert into user04 (id,name) values; insert into user04 (id,name) values (11); insert into user04 (id,name) values (12); insert into user04 (id,name) values (13); insert into user04 (id,name) values (14) Insert into user04 (id,name) values (15); insert into user04 (id,name) values (16); insert into user04 (id,name) values (17); insert into user04 (id,name) values (18); insert into user04 (id,name) values (19); insert into user04 (id,name) values (20); insert into user04 (id,name) values (21) Insert into user04 (id,name) values (22 miner steven`s); insert into user04 (id,name) values (23 recordssteven`); insert into user04 (id,name) values (24 pencils steven`); insert into user04 (id,name) values (25 pencils steven`); insert into user04 (id,name) values (26 pencils steven`); insert into user04 (id,name) values (27 mindssteven`); insert into user04 (id,name) values (28 pencils steven`) Insert into user04 (id,name) values (29memore steven`s); insert into user04 (id,name) values (30lemagensteven`); insert into user04 (id,name) values (31memorialsteven`); insert into user04 (id,name) values (32memores steven`); insert into user04 (id,name) values (33remiere steven`s); insert into user04 (id,name) values (34lemagery steven`s); insert into user04 (id,name) values (35paramel steven`) Insert into user04 (id,name) values (36 lemons steven`); insert into user04 (id,name) values (37 recordssteven`); insert into user04 (id,name) values (38 memorials steven`); insert into user04 (id,name) values (39 pencils steven`); insert into user04 (id,name) values (40 pencils steven`); insert into user04 (id,name) values (41 pencils steven`); insert into user04 (id,name) values (42 minions steven`) Insert into user04 (id,name) values (43); insert into user04 (id,name) values (44); insert into user04 (id,name) values (45); insert into user04 (id,name) values (46); insert into user04 (id,name) values (47); insert into user04 (id,name) values (48); insert into user04 (id,name) values (49) Insert into user04 (id,name) values (50)
VIII. Validate data
The three node are only in testdb01-03MagneTestDB 13-15 testdb25-27, so except for these nine physical libraries, there are no fragments found in other libraries.
The following validates the sharding information in the three node:
Node1
Mysql > select * from testdb01.user04;+----+-+ | id | name | +-- +-+ | 8 | steven | | 14 | steven | 16 | steven | 17 | steven | 34 | steven | | 49 | steven | +-+ 6 rows in set (0.00 sec) mysql > select * from testdb02.user04 +-mysql > select * from testdb03.user04 +-+ | id | name | +-+-+ | 11 | steven | | 24 | steven | | 33 | steven | | 35 | steven | | 40 | steven | +-+-- + 5 rows in set (0.00 sec)
Node2
Mysql > select * from testdb13.user04;+----+-+ | id | name | +-+ | 20 | steven | | 25 | steven | | 38 | steven | | 39 | steven | +-+ 4 rows in set (0.00 sec) mysql > select * from testdb14.user04 +-+ | id | name | +-+-+ | 1 | steven | | 41 | steven | | 50 | steven | +-+-- + 3 rows in set (0.01sec) mysql > select * from testdb15.user04 +-+-- +-+ | id | name | +-+-+ | 12 | steven | | 18 | steven | | 32 | steven | | 36 | steven | +-+-+ 4 rows in set (0.00 sec)
Node3
Mysql > select * from testdb25.user04;+----+-+ | id | name | +-- +-+ | 6 | steven | | 13 | steven | 19 | steven | 23 | steven | 27 | steven | | 28 | steven | 29 | steven | 31 | steven | 37 | steven | +-+-+ 9 rows in set (0.00 sec) mysql > select * from testdb26.user04 +-+-- +-+ | id | name | +-+-+ | 4 | steven | 5 | steven | | 15 | steven | | 22 | steven | | 42 | steven | +-+ + 5 rows in set (0.00 sec) mysql > select * from testdb27.user04 + If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!
