in this paper, we will introduce the operation of MyCat's sub database and sub table.

Sub library table

I. Introduction to segmentation rules

      various segmentation rules supported by myCat are defined in rule.xml.

1. Mod long


2. Sharding by month


3. Sharding by date


4. Splitting by hour


5. Range agreement: plan in advance which partition a range belongs to, auto sharding long


6. Range Division


7. Sharding by pattern


8. Sharding by IntFile


9. Fixed slice hash algorithm


10. Intercepting digital hash and parsing sharding by string hash


11. Consistency hash


12. Date range hash fragment rangeDateHash


13. Cutting off numbers for hash range constraint sharding by prefixpattern


14. The application specifies that in the running phase, the application decides which partition to route to. Sharding by substring


15. Sharding by date of hot and cold data


16. Stateful slicing algorithm


17. crc32slot segmentation algorithm

Be careful:

1. Recommended configuration of primary key column in id


2. All tablerules can only be used once. If you need to configure the same sharding rule for multiple tables, you need to redefine the rule here.


3. Once the number of segments in the crc32Slot algorithm is given, MyCat will save the number of segments and the value range of slot to a file. It will not take effect when the number of slices is modified for the next time. You need to delete the file. The file location is in the ruledata directory in the conf directory.

II. Sub warehouse configuration

2.1 create 3 databases

Create three databases Demo1, demo2 and demo3 in the master, because the master-slave relationship will be synchronized to the slave database.

create database demo1 default character set utf8;
create database demo2 default character set utf8;
create database demo3 default character set utf8;

2.2 schema.xml configuration

Modify the information in the schema.xml file as follows:

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100">
<table name="t_user" dataNode="dn1,dn2,dn3" rule="crc32slot" />
</schema>
<dataNode name="dn1" dataHost="localhost1" database="demo1" />
<dataNode name="dn2" dataHost="localhost1" database="demo2" />
<dataNode name="dn3" dataHost="localhost1" database="demo3" />
<dataHost name="localhost1" maxCon="1000" minCon="10" balance="1"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<!-- can have multi write hosts -->
<writeHost host="hostM1" url="192.168.88.180:3306" user="root"
password="123456">
<!-- can have multi read hosts -->
<readHost host="hostS2" url="192.168.88.181:3306" user="root" password="123456" />
</writeHost>
</dataHost>
</mycat:schema>

Be careful:

1. Because there are three libraries, three datanodes are added


2. The allocation rule used is crc32slot


3. The master-slave and read-write separation settings did not change, so the configuration of writeHost and readHost did not change.

2.3 modify rule.xml file

   since the crc32slot algorithm is used and there are three databases, the configuration in rule.xml needs to be modified.

At the same time, we need to delete the rule file in the ruledata directory, otherwise the modified 3 will not work.

Restart mycat service

View assignment rules

2.3 create t ﹣ user table in mycat

  first delete the original created t   user table, then create a t   user representation through mycat, which will create an additional   slot field.

CREATE TABLE t_user (
`id` INT,
`name` VARCHAR (30),
`age` INT,PRIMARY KEY (`id`)
) ENGINE = INNODB DEFAULT CHARSET = utf8;

III. sub library test

                    

insert into t_user(id,name,age)values(1,'HG-93',14)

Note: the syntax of the insert statement should be complete. Don't be lazy to omit the fields, especially the id self growth!

The data is stored in the table structure according to the rules we set.

Then let's take a look at the query operation and see if we can query all the data through mycat.