Mycat monthly sharding method,

Mycat monthly sharding method,
Overview

This article describes how to split Mycat by month, including configuration methods and precautions.

Mycat version: 1.4

Data nodes: dn1, dn2, dn3

Architecture: Master/Slave

Configuration

Create test table

CREATE TABLE `tdate` (   `id` int(11) NOT NULL,   `createdate` datetime DEFAULT NULL,   PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8

Run the create table statement on the three nodes.

Shard Configuration

<Table> Parameters

Using the rule = "sharding-by-month" sharding function in table parameter configuration, 13 Data nodes are configured with dn1-dn13, of course, there is no limit on the number of data nodes, in the production environment, it is best to set the value range of mycat to more than one year to avoid repeated restart.

<Datanode> Parameters

Because I only have three nodes here, three node loops are used as the data storage nodes. The default value is, and the data of these months is saved on node 1. Generally, 12 nodes are configured in the production of wedding leave, so that node 1 can exist in January each year,

This write mode is not supported. The Node definition in datanode must be the same as that in table <dataNode name = "dn1, dn4, dn7, dn10, dn13 "dataHost =" localhost1 "database =" db1 "/>

 <Datahost> Parameters

Datahost is configured with master-slave read/write splitting. If the first writehost is down, it is automatically switched to the second writehost.

Partition functions

vim rule.xml

<tableRule name="sharding-by-month">                <rule>                        <columns>createdate</columns>                        <algorithm>sharding-by-month</algorithm>                </rule></tableRule><function name="sharding-by-month"                class="org.opencloudb.route.function.PartitionByMonth">                <property name="dateFormat">yyyy-MM-dd HH:mm:ss</property>                <property name="sBeginDate">2015-01-01 00:00:00</property></function>

The default dateFormat format used by the natural month fragment in the authoritative guide is yyyy-MM-dd. Here I tested it on purpose.

Note: If the dateFormat format is yyyy-MM-dd, The sBeginDate format must be; otherwise, this is the corresponding relationship configured above. When I use the dateFormat yyyy-MM-dd, I can use the time format "00:00:00" in the createdate field of the insert statement. Otherwise, I configured the dateFormat TO BE yyyy-MM-dd HH: mm: ss. When the createdate field of my insert statement is in the format of "", an error is reported, it is recommended that the function configure the format when executing the insert statement.

When the dateFormat format is yyyy-MM-dd, the Data Type of the slice field can be date and datetime, And the dateFormat is yyyy-MM-dd HH: mm: ss is the data type of the partition field. It must be datetime.

SBeginDate is the start time.

Test Data

Execute the insert statement in mycat.

insert into tdate(id,createdate) values(1,'2015-01-01 00:00:00');insert into tdate(id,createdate) values(2,'2015-02-01 00:00:00');insert into tdate(id,createdate) values(3,'2015-03-01 00:00:00');insert into tdate(id,createdate) values(4,'2015-10-01 00:00:00');insert into tdate(id,createdate) values(5,'2016-01-01 00:00:00');

View data in the master:

select * from db1.tdate; select * from db2.tdate; select * from db3.tdate; 

The inserted results are also normally distributed to each part, and the test results are correct.

Notes

1. When insert is performed, the slice fields must be explicitly listed behind the table. Other Default fields may not be listed in 1.4 and will not be affected.

2. functions cannot be used in values, such as: now ()

Summary

The overall concept of mycat sharding for a month is clear, but because mycat itself is relatively weak in Fault Tolerance processing, it is recommended to use standard syntax as simple as possible in SQL statements.

 

 

 

Note: Author: pursuer. chen Blog: http://www.cnblogs.com/chenmh All essays on this site are original. You are welcome to repost them. However, you must indicate the source of the article and clearly give the link at the beginning of the article. Welcome to discussion