The various sharding rules supported by myCat are defined in rule-xml.

1. Modulus mod – long
2. Natural month Sharding-by-month
3. Sharding -by-date sharding by date (day)
4. Split sharding-by-hour by monthly hour
5. Scope convention: The sharding field scope is planned in advance. Auto-sharding-long
6. Range modular sharding
7. The sharding-by-pattern is constrained by model circle
8. Enumerating sharding-by-intfile for fragments
9. Fixed fragmentation hash algorithm
10. Intercepting a numeric hash resolves a sharding-by-stringhash
11. Consistency of the hash
12. Date Range Hash Fragment rangeDateHash
13. Intercepting numbers to hash the pattern constraint Sharding-by-Prefixpattern
14. The application specifies that at runtime the application decides which shard to route to. sharding-by-substring
15. Hot and cold data fragments sharding-by-date
16. Stateful sharding algorithm
17. Crc32slot Fragmentation algorithm

Note:

1. You are advised to configure the primary key column in id
2. All tableRule can be used only 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 shards in the crc32Slot algorithm is given, MyCat saves the number of shards and the value range of SLOR to a file. This modification does not take effect. You need to delete the file. The file location is in the RuleData directory in the conf directory.

Create 3 databases demo1, Demo2, and Demo3 in master, because the master/slave relationship will be synchronized to the slave library.

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

     
Copy the code

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

      

      
<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>

     
Copy the code

Note:

1. Since there are three libraries, three have been addeddataNode
2. The allocation rules used arecrc32slot
3. The master-slave and read-write separation Settings are not moving, sowriteHostandreadHostThe configuration of the

Because the CRC32slot algorithm is used and there are three databases, the configuration in rule-xml needs to be modified

In the meantime, we need to deleteruledataDirectory in the rule file, otherwise modified3It won’t work

Restart the MyCat service

Viewing Assignment Rules

Select * from t_user; select * from mycat; select * from t_user; select * from mycat;

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

     
Copy the code

Insert data into mycat client, and then query the corresponding physical library for details

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

     
Copy the code

Note: insert statement syntax should be complete, do not be lazy omit fields, especially id growth!!

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

And then let’s look at the query operation, mycat to see if we can get all the data,

Follow wechat official account [Programmer’s Dream, focusing on Java, SpringBoot, SpringCloud, microservices, Docker, and full-stack technologies such as backend separation.