Mycat database sharding Rules auto-sharding-long
1. Define rule. xml
- <TableRule name = "auto-sharding-long-userid">
- <Rule>
- <Columns> userid </columns>
- <Algorithm> rang-long-userid </algorithm>
- </Rule>
- </TableRule>
- <Function name = "rang-long-userid"
- Class = "io. mycat. route. function. AutoPartitionByLong">
- <Property name = "mapFile"> autopartition-long-userid.txt </property>
- </Function>
[Root @ host01 conf] # more autopartition-long-userid.txt
# Range start-end, data node index
# K = 1000, M = 10000.
0-1000 = 0
1001-2000 = 1
2001-3000 = 2
3001-4000 = 3
4001-5000 = 4
5001-6000 = 5
2. schema. xml configuration file
- <? Xml version = "1.0"?>
- <! DOCTYPE mycat: schema SYSTEM "schema. dtd">
- <Mycat: schema xmlns: mycat = "http://io.mycat/">
- <Schema name = "logdb" checkSQLschema = "false" sqlMaxLimit = "100">
- <Table name = "tb_log_t" primaryKey = "ID" autoIncrement = "true" dataNode = "dn1, dn2, dn3, dn4, dn5, dn6 "rule =" mod-long "/>
- <Table name = "tb_user_t" dataNode = "dn1, dn2, dn3, dn4, dn5, dn6" rule = "sharding-by-intfile-provcode"/>
- <Table name = "tb_user_detail_t" dataNode = "dn1, dn2, dn3, dn4, dn5, dn6" rule = "auto-sharding-long-userid"/>
- </Schema>
- <Schema name = "coss03" checkSQLschema = "false" sqlMaxLimit = "100" dataNode = "dnoss03"/>
- <DataNode name = "dn1" dataHost = "localhost1" database = "log01"/>
- <DataNode name = "dn2" dataHost = "localhost1" database = "log02"/>
- <DataNode name = "dn3" dataHost = "localhost1" database = "log03"/>
- <DataNode name = "dn4" dataHost = "localhost1" database = "log04"/>
- <DataNode name = "dn5" dataHost = "localhost1" database = "log05"/>
- <DataNode name = "dn6" dataHost = "localhost1" database = "log06"/>
- <DataNode name = "dnoss03" dataHost = "localhost1" database = "oss03"/>
- <DataHost name = "localhost1" maxCon = "1000" minCon = "10" balance = "0"
- WriteType = "0" dbType = "mysql" dbDriver = "native" switchType = "1" slaveThreshold = "100">
- <Heartbeat> select user ()
- <! -- Can have multi write hosts -->
- <WriteHost host = "hostM1" url = "192.168.56.141: 3306" user = "root"
- Password = "mysql">
- <! -- Can have multi read hosts -->
- <ReadHost host = "hostS2" url = "192.168.56.142: 3306" user = "root" password = "mysql"/>
- </WriteHost>
- </DataHost>
- </Mycat: schema>
3. Table creation statement
Create table tb_user_detail_t (
Userid bigint not null primary key,
Name varchar (64) default null,
Createtime datetime DEFAULT CURRENT_TIMESTAMP,
Moditytime datetime DEFAULT CURRENT_TIMESTAMP
) ENGINE = InnoDB default charset = utf8;
Write Data
Insert into tb_user_detail_t (userid, name) values (999, 'name999 ');
Insert into tb_user_detail_t (userid, name) values (1999, 'name999 ');
Insert into tb_user_detail_t (userid, name) values (2999, 'name999 ');
Insert into tb_user_detail_t (userid, name) values (3999, 'name999 ');
Insert into tb_user_detail_t (userid, name) values (4999, 'name999 ');
Insert into tb_user_detail_t (userid, name) values (5999, 'name999 ');
-- The End --