MyCat sub-database sub-table-actual combat 09 muri-by single month hour
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;create database testdb04;create database testdb05;create database testdb06;create database testdb07;create database testdb08;create database testdb09;create database testdb10;create database testdb11;create database testdb12
Node2
Create database testdb13;create database testdb14;create database testdb15;create database testdb16;create database testdb17;create database testdb18;create database testdb19;create database testdb20;create database testdb21;create database testdb22;create database testdb23;create database testdb24
Node3
Create database testdb25;create database testdb26;create database testdb27;create database testdb28;create database testdb29;create database testdb30;create database testdb31;create database testdb32;create database testdb33;create database testdb34;create database testdb35;create database testdb36
2. Configure schema.xml
Select user () select user () select user ()
3. Configure rule.xml
Adddate sharding-by-hour 24
24 24 copies per day
4. Start mycat
/ usr/local/mycat/bin/mycat start
View mycat Log
STATUS | wrapper | 12:54:18 on 2018-11-23 |-- > Wrapper Started as DaemonSTATUS | wrapper | 12:54:19 on 2018-11-23 | Launching a JVM...INFO | jvm 1 | 12:54:19 on 2018-11-23 | OpenJDK 64-Bit Server VM warning: ignoring option MaxPermSize=64M Support was removed in 8.0INFO | jvm 1 | 12:54:23 on 2018-11-23 | Wrapper (Version 3.2.3) http://wrapper.tanukisoftware.orgINFO | jvm 1 | 12:54:23 on 2018-11-23 | Copyright 1999-2006 Tanuki Software, Inc. All Rights Reserved.INFO | jvm 1 | 12:54:23 on 2018-11-23 | INFO | jvm 1 | 12:54:28 on 2018-11-23 | 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.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 | +-+ | user04 | | user05 | | user06 | | user07 | +-+ 4 rows in set (0.01sec) mysql > drop table if exists user07 Query OK, 0 rows affected, 1 warning (0.38 sec) mysql > create table user07 (- > id int not null,-> name varchar (64),-> adddate varchar (10)->)
Insert test data
Insert into user07 (id,name,adddate) values; insert into user07 (id,name,adddate) values Insert into user07 (id,name,adddate) values; insert into user07 (id,name,adddate) values Insert into user07 (id,name,adddate) values; insert into user07 (id,name,adddate) values Insert into user07 (id,name,adddate) values; insert into user07 (id,name,adddate) values (2018110119') Insert into user07 (id,name,adddate) values, 2018110120'; insert into user07 (id,name,adddate) values, 2018110121'); insert into user07 (id,name,adddate) values, 2018110122'; insert into user07 (id,name,adddate) values, 2018110123'; insert into user07 (id,name,adddate) values Insert into user07 (id,name,adddate) values; insert into user07 (id,name,adddate) values Insert into user07 (id,name,adddate) values; insert into user07 (id,name,adddate) values Insert into user07 (id,name,adddate) values (2018110211')
Execute twice, insert two copies of each piece of data
7. Validate data
Node1
Mysql > select * from testdb01.user07;+----+ | id | name | adddate | +-- + | 1 | steven | 2018110100 | 1 | steven | 2018110100 | +-+ 2 rows in set (0.00 sec) mysql > select * from testdb02.user07 +-id | name | adddate | 1 | steven | 2018110101 | 1 | steven | 2018110101 | +-+ 2 rows in set (sec) mysql > select * from testdb03.user07 +-id | name | adddate | 1 | steven | 2018110102 | 1 | steven | 2018110102 | +-+ 2 rows in set (sec) mysql > select * from testdb04.user07 +-id | name | adddate | 1 | steven | 2018110103 | 1 | steven | 2018110103 | +-+ 2 rows in set (sec) mysql > select * from testdb05.user07 +-id | name | adddate | 1 | steven | 2018110104 | 1 | steven | 2018110104 | +-+ 2 rows in set (sec) mysql > select * from testdb06.user07 +-id | name | adddate | 1 | steven | 2018110105 | 1 | steven | 2018110105 | +-+ 2 rows in set (sec) mysql > select * from testdb07.user07 +-id | name | adddate | 1 | steven | 2018110106 | 1 | steven | 2018110106 | +-+ 2 rows in set (sec) mysql > select * from testdb08.user07 +-id | name | adddate | 1 | steven | 2018110107 | 1 | steven | 2018110107 | +-+ 2 rows in set (sec) mysql > select * from testdb09.user07 +-id | name | adddate | 1 | steven | 2018110108 | 1 | steven | 2018110108 | +-+ 2 rows in set (sec) mysql > select * from testdb10.user07 +-+ | id | name | adddate | +-+ | 1 | steven | 2018110109 | 1 | steven | 2018110109 | +-+ 2 rows in set (0.01sec) mysql > select * from testdb11.user07 +-+ | id | name | adddate | +-+ | 1 | steven | 2018110110 | 1 | steven | 2018110110 | +-+ 2 rows in set (0.02 sec) mysql > select * from testdb12.user07 +-+ | id | name | adddate | +-+ | 1 | steven | 2018110111 | 1 | steven | 2018110111 | +-+ 2 rows in set (sec)
Node2
Mysql > select * from testdb13.user07;+----+ | id | name | adddate | +-- + | 1 | steven | 2018110112 | 1 | steven | 2018110112 | +-+ 2 rows in set (0.00 sec) mysql > select * from testdb14.user07 +-id | name | adddate | 1 | steven | 2018110113 | 1 | steven | 2018110113 | +-+ 2 rows in set (sec) mysql > select * from testdb15.user07 +-id | name | adddate | 1 | steven | 2018110114 | 1 | steven | 2018110114 | +-+ 2 rows in set (sec) mysql > select * from testdb16.user07 +-id | name | adddate | 1 | steven | 2018110115 | 1 | steven | 2018110115 | +-+ 2 rows in set (sec) mysql > select * from testdb17.user07 +-id | name | adddate | 1 | steven | 2018110116 | 1 | steven | 2018110116 | +-+ 2 rows in set (sec) mysql > select * from testdb18.user07 +-id | name | adddate | 1 | steven | 2018110117 | 1 | steven | 2018110117 | +-+ 2 rows in set (sec) mysql > select * from testdb19.user07 +-id | name | adddate | 1 | steven | 2018110118 | 1 | steven | 2018110118 | +-+ 2 rows in set (sec) mysql > select * from testdb20.user07 +-id | name | adddate | 1 | steven | 2018110119 | 1 | steven | 2018110119 | +-+ 2 rows in set (sec) mysql > select * from testdb21.user07 +-id | name | adddate | 1 | steven | 2018110120 | 1 | steven | 2018110120 | +-+ 2 rows in set (sec) mysql > select * from testdb22.user07 +-id | name | adddate | 1 | steven | 2018110121 | 1 | steven | 2018110121 | +-+ 2 rows in set (sec) mysql > select * from testdb23.user07 +-id | name | adddate | 1 | steven | 2018110122 | 1 | steven | 2018110122 | +-+ 2 rows in set (sec) mysql > select * from testdb24.user07 +-+ | id | name | adddate | +-+ | 1 | steven | 2018110123 | 1 | steven | 2018110123 | +-+ 2 rows in set (0.00 sec)
Node3
Mysql > select * from testdb25.user07;+----+ | id | name | adddate | +-- + | 1 | steven | 2018110200 | 1 | steven | 2018110200 | +-+ 2 rows in set (0.00 sec) mysql > select * from testdb26.user07 +-id | name | adddate | 1 | steven | 2018110201 | 1 | steven | 2018110201 | +-+ 2 rows in set (sec) mysql > select * from testdb27.user07 +-id | name | adddate | 1 | steven | 2018110202 | 1 | steven | 2018110202 | +-+ 2 rows in set (sec) mysql > select * from testdb28.user07 +-+ | id | name | adddate | +-+ | 1 | steven | 2018110203 | 1 | steven | 2018110203 | +-+ 2 rows in set (0.02 sec) mysql > select * from testdb29.user07 +-id | name | adddate | 1 | steven | 2018110204 | 1 | steven | 2018110204 | +-+ 2 rows in set (sec) mysql > select * from testdb30.user07 +-id | name | adddate | 1 | steven | 2018110205 | 1 | steven | 2018110205 | +-+ 2 rows in set (sec) mysql > select * from testdb31.user07 +-id | name | adddate | 1 | steven | 2018110206 | 1 | steven | 2018110206 | +-+ 2 rows in set (sec) mysql > select * from testdb32.user07 +-id | name | adddate | 1 | steven | 2018110207 | 1 | steven | 2018110207 | +-+ 2 rows in set (sec) mysql > select * from testdb33.user07 +-id | name | adddate | 1 | steven | 2018110208 | 1 | steven | 2018110208 | +-+ 2 rows in set (sec) mysql > select * from testdb34.user07 +-id | name | adddate | 1 | steven | 2018110209 | 1 | steven | 2018110209 | +-+ 2 rows in set (sec) mysql > select * from testdb35.user07 +-id | name | adddate | 1 | steven | 2018110210 | 1 | steven | 2018110210 | +-+ 2 rows in set (sec) mysql > select * from testdb36.user07 +-+ | id | name | adddate | +-+ | 1 | steven | 2018110211 | 1 | steven | 2018110211 | +-+ 2 rows in set (0.00 sec)
72 records were distributed in 36 slices in chronological order, and the verification was completed.
Note: the day configured in rule.xml is divided into 24 parts, one is one hour, and we have a total of 36 shards, so the data inserted can only be limited to 36 hours at the beginning of each month, such as 2018100100-2018100211.
