Shulou(Shulou.com)06/01 Report--

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-date yyyy-MM-dd 2010-01-01 2020-12-31 120

4. Start mycat

/ usr/local/mycat/bin/mycat start

View mycat Log

STATUS | wrapper | 19:03:02 on 2018-11-22 |-- > Wrapper Started as DaemonSTATUS | wrapper | 19:03:02 on 2018-11-22 | Launching a JVM...INFO | jvm 1 | 19:03:02 on 2018-11-22 | OpenJDK 64-Bit Server VM warning: ignoring option MaxPermSize=64M Support was removed in 8.0INFO | jvm 1 | 19:03:04 on 2018-11-22 | Wrapper (Version 3.2.3) http://wrapper.tanukisoftware.orgINFO | jvm 1 | 19:03:04 on 2018-11-22 | Copyright 1999-2006 Tanuki Software, Inc. All Rights Reserved.INFO | jvm 1 | 19:03:04 on 2018-11-22 | INFO | jvm 1 | 19:03:08 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.02 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 | +-+ | user04 | | user05 | +-+ 2 rows in set (0.00 sec) mysql > drop table if exists user05 Query OK, 0 rows affected, 1 warning (0.57 sec) mysql > create table user05 (- > id int not null auto_increment,-> name varchar (64),-> adddate datetime,-> primary key (id)->); Query OK, 0 rows affected (1.56 sec)

Insert test data

Insert 10 records

Insert into user05 (id,name,adddate) values; insert into user05 (id,name,adddate) values, 2014-05-09, 2012-05-09, 2012-05-09, 2012-05-09, 2012-05-09, 2012-05-09, 2012-05-09, 2012-05-09, 2012-05-09, 2012-05-09, 2012-05-09, 2012-05-09, 2012-05-09. Insert into user05 (id,name,adddate) values; insert into user05 (id,name,adddate) values (2018-05-09, 2018-05-09). Insert into user05 (id,name,adddate) values (2009-05-09, 2019-05-09, 2012); insert into user05 (id,name,adddate) values (10).

7. Validate data

Node1

Mysql > select * from testdb01.user05;Empty set (0.00 sec) mysql > select * from testdb02.user05;Empty set (0.00 sec) mysql > select * from testdb04.user05;Empty set (0.00 sec) mysql > select * from testdb05.user05 +-+ | id | name | adddate | +-+ | 1 | steven | 2011-05-09 12:32:12 | +-+-- -+ 1 row in set (0.00 sec) mysql > select * from testdb06.user05 Empty set (0.01 sec) mysql > select * from testdb01.user05;Empty set (0.00 sec) mysql > select * from testdb02.user05;Empty set (0.00 sec) mysql > select * from testdb03.user05;Empty set (0.00 sec) mysql > select * from testdb04.user05;Empty set (0.00 sec) mysql > select * from testdb05.user05 +-+ | id | name | adddate | +-+ | 1 | steven | 2011-05-09 12:32:12 | +-+-- -+ 1 row in set (0.00 sec) mysql > select * from testdb06.user05 Empty set (0.01 sec) mysql > select * from testdb07.user05;Empty set (0.00 sec) mysql > select * from testdb08.user05 +-+ | id | name | adddate | +-+ | 2 | steven | 2012-05-09 12:32:12 | +-+-- -+ 1 row in set (0.00 sec) mysql > select * from testdb09.user05 Empty set (0.01 sec) mysql > select * from testdb10.user05;Empty set (0.00 sec) mysql > select * from testdb11.user05 +-+ | id | name | adddate | +-+ | 3 | steven | 2013-05-09 12:32:12 | +-+-- -+ 1 row in set (0.00 sec) mysql > select * from testdb12.user05 Empty set (0.00 sec)

Node2

Mysql > select * from testdb13.user05;Empty set (0.00 sec) mysql > select * from testdb14.user05 +-+ | id | name | adddate | +-+ | 4 | steven | 2014-05-09 12:32:12 | +-+-- -+ 1 row in set (0.00 sec) mysql > select * from testdb15.user05 Empty set (0.00 sec) mysql > select * from testdb16.user05;Empty set (0.00 sec) mysql > select * from testdb17.user05 +-+ | id | name | adddate | +-+ | 5 | steven | 2015-05-09 12:32:12 | +-+-- -+ 1 row in set (0.00 sec) mysql > select * from testdb18.user05 Empty set (0.00 sec) mysql > select * from testdb19.user05;Empty set (0.00 sec) mysql > select * from testdb20.user05 +-+ | id | name | adddate | +-+ | 6 | steven | 2016-05-09 12:32:12 | +-+-- -+ 1 row in set (0.00 sec) mysql > select * from testdb21.user05 Empty set (0.00 sec) mysql > select * from testdb22.user05;Empty set (0.00 sec) mysql > select * from testdb23.user05 +-+ | id | name | adddate | +-+ | 7 | steven | 2017-05-09 12:32:12 | +-+-- -+ 1 row in set (0.01sec) mysql > select * from testdb24.user05 Empty set (0.00 sec)

Node3

Mysql > select * from testdb25.user05;Empty set (0.00 sec) mysql > select * from testdb26.user05 +-+ | id | name | adddate | +-+ | 8 | steven | 2018-05-09 12:32:12 | +-+-- -+ 1 row in set (0.00 sec) mysql > select * from testdb27.user05 Empty set (0.00 sec) mysql > select * from testdb28.user05;Empty set (0.00 sec) mysql > select * from testdb29.user05 +-+ | id | name | adddate | +-+ | 9 | steven | 2019-05-09 12:32:12 | +-+-- -+ 1 row in set (0.00 sec) mysql > select * from testdb30.user05 Empty set (0.00 sec) mysql > select * from testdb31.user05;Empty set (0.01sec) mysql > select * from testdb32.user05 +-+ | id | name | adddate | +-+ | 10 | steven | 2020-05-09 12:32:12 | +-+-- -+ 1 row in set (0.00 sec) mysql > select * from testdb33.user05 Empty set (0.01 sec) mysql > select * from testdb34.user05;Empty set (0.00 sec) mysql > select * from testdb35.user05;Empty set (0.00 sec) mysql > select * from testdb36.user05;Empty set (0.01 sec)

You can see that 10 test data are divided into pieces according to 120 days, distributed sequentially in each database, and the verification is completed.

Problems encountered:

When configuring for the first time, a shard of 90 days is configured in rule.xml, so an error is reported:

Caused by: io.mycat.config.util.ConfigException: Illegal table conf: table [USER05] rule function [sharding-by-date] partition size: 45 > table datanode size: 36, please make sure table datanode size = function partition size

The investigation found that about 4015 days from 2011-01-01 to 2020-12-31, there were 44.61111111111111 fragments, exceeding the preset 36 libraries.

After adjustment: 4015 Universe 120 fragments 33.45833333333333, within the range of 36 libraries.

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.