The sixth article of the MYCAT study note. Data shard by Month data shard

1 Application Scenarios

Mycat has a lot of data library rules, the next few on the relevant feel common rules to try and summarize.

In general, the rules for data fragmentation by natural months are applied to marketplace order queries, similar to those in the last 1 weeks, 2 weeks, and 3 months. or report class applications.

Such data is placed in a single area, eliminating the time to merge data.

Of course, the amount of data per month should not be too big OK.

2 Environmental Description

Windows 7

Native Multi-database Mysql 5.5.2

3306 ports are hung with 4 libraries: Range_db_4, Range_db_5, Range_db_6, range_db_7

3310 ports are hung with 4 libraries: Range_db_8, Range_db_9, Range_db_10, Range_db_11

3 parameter configuration

3.1 Database Configuration

The MySQL client enters the 3306 and 3310 services respectively and begins to build the physical schema.

CREATE SCHEMA ' range_db_4 ' DEFAULT CHARACTER SET UTF8;
CREATE SCHEMA ' range_db_5 ' DEFAULT CHARACTER SET UTF8;
CREATE SCHEMA ' range_db_6 ' DEFAULT CHARACTER SET UTF8;
CREATE SCHEMA ' range_db_7 ' DEFAULT CHARACTER SET UTF8;

...

3306 on

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| Information_schema |
| Mycat_sync_test |
| MySQL |
| Performance_schema |
| Range_db_4 |
| Range_db_5 |
| Range_db_6 |
| range_db_7 |
+--------------------+
8 rows in Set (0.00 sec)

3310 on

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| Information_schema |
| Mycat_sync_test |
| MySQL |
| Performance_schema |
| range_db_10 |
| Range_db_11 |
| Range_db_8 |
| Range_db_9 |
| traveldata_db_1 |
| traveldata_db_2 |
+--------------------+
Rows in Set (0.00 sec)

3.2 Server.xml Configuration

<!--open Test user access Rangedb access rights Rangedb is a virtual schema--

<user name= "Test" >
<property name= "Password" >test</property>
<property name= "Schemas" >TRDB,RANGEDB</property>
</user>

3.3 Schema.xml Configuration

<!--set the virtual schema rangedb information--

<schema name= "Rangedb" checksqlschema= "false" sqlmaxlimit= ">"

<!--set Virtual table t_range_date corresponds to data node dn4:dn11 altogether 8 data shards, using Sharding-by-date shard rules--
<table name= "T_range_date" datanode= "Dn4,dn5,dn6,dn7,dn8,dn9,dn10,dn11" rule= "Sharding-by-date"/>
</schema>

<!--set data node dn4:dn7 corresponding host is 3306 service and corresponding physical schema--

<datanode name= "Dn4" datahost= "localhost3306" database= "Range_db_4"/>
<datanode name= "Dn5" datahost= "localhost3306" database= "Range_db_5"/>
<datanode name= "dn6" datahost= "localhost3306" database= "Range_db_6"/>
<datanode name= "dn7" datahost= "localhost3306" database= "range_db_7"/>

<!--set data node dn8:dn11 corresponding host is 3310 service and corresponding physical schema--

<datanode name= "Dn8" datahost= "localhost3310" database= "Range_db_8"/>
<datanode name= "Dn9" datahost= "localhost3310" database= "Range_db_9"/>
<datanode name= "Dn10" datahost= "localhost3310" database= "range_db_10"/>
<datanode name= "Dn11" datahost= "localhost3310" database= "Range_db_11"/>

<!--settings Datahost 3306 is currently only equipped with a physical machine, to do read and write separation can refer to the opening 1th, 2 content to adjust--

<datahost name= "localhost3306" maxcon= "+" mincon= "balance=" 1 "
Writetype= "0" dbtype= "MySQL" dbdriver= "native" switchtype= "2" slavethreshold= ">"
<writehost host= "hostM3306" url= "localhost:3306" user= "root" password= "root123" ></writeHost>
</dataHost>

<!--settings Datahost 3306--
<datahost name= "localhost3310" maxcon= "+" mincon= "balance=" 1 "
Writetype= "0" dbtype= "MySQL" dbdriver= "native" switchtype= "2" slavethreshold= ">"
<writehost host= "hostM3310" url= "localhost:3310" user= "root" password= "root123" ></writeHost>
</dataHost>

3.4 Rule.xml Configuration

<!--shard field corresponds to date_str shard rule partbymonth--

<tablerule name= "Sharding-by-date" >
<rule>
<columns>date_str</columns>
<algorithm>partbymonth</algorithm>
</rule>
</tableRule>

<!--shard Rule partbymonth configuration starting from 2015-01-01--

<function name= "Partbymonth" class= "Org.opencloudb.route.function.PartitionByMonth" >

<property name= "DateFormat" >yyyy-MM-dd</property>
<property name= "Sbegindate" >2015-01-01</property>
</function>

3.5 Mycat Reload configuration information

Access the Mycat 9066 management port and reload all parameter configurations.

d:\bin\mysql\mysql_3307\bin>mysql-utest-ptest-p 9066
Welcome to the MySQL Monitor. Commands End With; or \g.
Your MySQL Connection ID is 2
Server version:5.5.8-mycat-1.5-beta-20160111170158 Mycat Server (monitor)

Copyright (c), +, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of the Oracle Corporation and/or its
Affiliates. Other names trademarks of their respective
Owners.

Type ' help ', ' or ' \h ' for help. Type ' \c ' to clear the current input statement.

mysql> reload @ @config_all;
Query OK, 1 row affected (0.36 sec)
Reload Config Success

4 Data validation

4.2 Mycat Building Table

Enter Mycat 8066 service port, choose Rangedb Library, synchronize create table.

d:\bin\mysql\mysql_3310\bin>mysql-utest-ptest-p 8066
Welcome to the MySQL Monitor. Commands End With; or \g.
Your MySQL Connection ID is 1
Server version:5.5.8-mycat-1.5-beta-20160111170158 mycat Server (OPENCLOUNDDB)

Copyright (c), +, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of the Oracle Corporation and/or its
Affiliates. Other names trademarks of their respective
Owners.

Type ' help ', ' or ' \h ' for help. Type ' \c ' to clear the current input statement.

mysql> use rangedb;
Database changed
mysql> CREATE TABLE ' t_range_date ' (' id ' INT not NULL, ' date ' date null, ' date_str ' VARCHAR ') NULL, ' Co ntext ' VARCHAR NULL, PRIMARY KEY (' id '));

Query OK, 0 rows affected (0.09 sec)

4.5 Data Insertion and query

Since only 8 shards have been created, the out-of-section throws an array out-of-bounds exception.

mysql> INSERT INTO t_range_date (Id,date_str,context) VALUES (1, ' 2015-01-01 ', ' month-1-str ');

Insert into T_range_date (Id,date_str,context) VALUES (2, ' 2015-02-01 ', ' month-2-str ');

Insert into T_range_date (Id,date_str,context) VALUES (3, ' 2015-03-01 ', ' month-3-str ');

INSERT INTO t_range_date (Id,date_str,context) VALUES (4, '-04-01 ', ' month-4-str ');

INSERT INTO t_range_date (Id,date_str,context) VALUES (5, '2015-05-01 ', ' Month-5-str ');

INSERT INTO t_range_date (id,date_str,context) Values (6, ' 2015-06-01 ', ' month-6-str ');

< EM id= "__mcedel" >insert into T_range_date (id,date_str,context) VA lues (7, ' 2015-07-01 ', ' month-7-str ');

< EM id= "__mcedel" >insert into T_range_date (id,date_str,context ) VALUES ( 8, ' 2015-08-01 ', ' month-8-str ');

< EM id= "__mcedel" > Insert INTO T_range_date (Id,date_str,con text) VALUES (9, ' 2015-09-01 ', ' month-9-str ');

< EM id= "__mcedel" >insert into T_range_date (id,date_str ,context) VALUES (10, ' 2015-10-01 ', ' Month-10-str ')

< EM id= "__mcedel" >insert into T_range_date (id,da te_str,context) VALUES (one, ' 2015-11-01 ', ' month-11-str ');

Query OK, 1 row affected (0.01 sec)

Query OK, 1 row affected (0.01 sec)

Query OK, 1 row Affected (0.00 sec)

Query OK, 1 row Affected (0.00 sec)

Query OK, 1 row affected (0.01 sec)

Query OK, 1 row Affected (0.00 sec)

Query OK, 1 row affected (0.01 sec)

Query OK, 1 row Affected (0.00 sec)

ERROR 1064 (HY000): Index:8, Size:8
ERROR 1064 (HY000): Index:9, Size:8
ERROR 1064 (HY000): Index:10, Size:8

Mysql> select * from T_range_date;
+----+------+------------+-------------+
| ID | Date | Date_str | Context |
+----+------+------------+-------------+
| 2 | NULL | 2015-02-01 | Month-2-str |
| 4 | NULL | 2015-04-01 | Month-4-str |
| 5 | NULL | 2015-05-01 | Month-5-str |
| 1 | NULL | 2015-01-01 | Month-1-str |
| 3 | NULL | 2015-03-01 | Month-3-str |
| 6 | NULL | 2015-06-01 | Month-6-str |
| 7 | NULL | 2015-07-01 | Month-7-str |
| 8 | NULL | 2015-08-01 | Month-8-str |
+----+------+------------+-------------+
8 rows in Set (0.01 sec)

4.6 Physical Library queries

Data from the first 4 months into the 3306 service physical Library

Mysql> select * from Range_db_4.t_range_date;

SELECT * from Range_db_5.t_range_da te;

SELECT * from Range_db_6.t_range_date;

SELECT * from Range_db_7.t_range_date;

+----+------+------------+-------------+
| ID | Date | Date_str | Context |
+----+------+------------+-------------+
| 1 | NULL | 2015-01-01 | Month-1-str |
+----+------+------------+-------------+
1 row in Set (0.00 sec)

+----+------+------------+-------------+
| ID | Date | Date_str | Context |
+----+------+------------+-------------+
| 2 | NULL | 2015-02-01 | Month-2-str |
+----+------+------------+-------------+
1 row in Set (0.00 sec)

+----+------+------------+-------------+
| ID | Date | Date_str | Context |
+----+------+------------+-------------+
| 3 | NULL | 2015-03-01 | Month-3-str |
+----+------+------------+-------------+
1 row in Set (0.00 sec)

+----+------+------------+-------------+
| ID | Date | Date_str | Context |
+----+------+------------+-------------+
| 4 | NULL | 2015-04-01 | Month-4-str |
+----+------+------------+-------------+
1 row in Set (0.00 sec)

5 Advantages and Disadvantages analysis

1. You can do a simple monthly shard, if you really want to get up, you can configure a quarter of the data into the same datanode.

2. Cannot be configured on a yearly basis, if the data node is insufficient, you need to join early and manually clean up historical data.

The end of this article

The sixth article of the MYCAT study note. Data shard by Month data shard