The premise of this article is:
mycat is configured correctly and starts normally.
1,schema.xml
<table>label:
DataNode -- Slice node specifies (value: name attribute value in dataNode) Rule ------- Fragmentation rule selection (value: name attribute value in rule tag)
[root@dras-test conf]# vim schema.xml
1 <?xml version="1.0"?>
2 <!DOCTYPE mycat:schema SYSTEM "schema.dtd">
3 <mycat:schema xmlns:mycat="http://io.mycat/">
4 <schema name="mycatdb" checkSQLschema="false" sqlMaxLimit="100">
5 <!-- auto sharding by id (long) -->
6 <table name="t_person" dataNode="dn1,dn2" rule="mod-long" />
7 <table name="t_user" primaryKey="id" dataNode="dn1,dn2" rule="sharding-by-murmur" />
<!-- Global Table -->
8 <!-- table name="province" type="global" dataNode="dn1,dn2,dn3" />
9
10 <table name="student" dataNode="dn1,dn2" rule="auto-sharding-long-sharejoin" />
11 <table name="score" dataNode="dn2,dn3" rule="auto-sharding-long-sharejoin" />
12 <table name="score" dataNode="dn1,dn2" rule="auto-sharding-long-sharejoin" />
13
<!-- ER Slicing -->
14 <table name="customer" dataNode="dn1,dn2,dn3" rule="auto-sharding-long-customer">
15 <childTable name="orders" joinKey="customer_id" parentKey="id"/>
16 </table -->
17
18 <table name="user" primaryKey="id" dataNode="dn1,dn2" rule="mod-long-test">
19 <childTable name="cell" joinKey="user_id" parentKey="id"/>
20 <childTable name="note" joinKey="user_id" parentKey="id"/>
21 <childTable name="lit" joinKey="user_id" parentKey="id"/>
22 <childTable name="lit_usr" joinKey="user_id" parentKey="id"/>
23 </table>
24
25 </schema>
26
27 <dataNode name="dn1" dataHost="localhost1" database="db1" />
28 <dataNode name="dn2" dataHost="localhost1" database="db2" />
29 <dataNode name="dn3" dataHost="localhost1" database="db3" />
30
31 <dataHost name="localhost1" maxCon="500" minCon="100" balance="2"
32 writeType="1" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
33 <heartbeat>select user()</heartbeat>
34
35 <writeHost host="hostM1" url="localhost:3306" user="root"
36 password="" >
37 </writeHost>
38
39 </dataHost>
40 </mycat:schema>
2,rule.xml
<tablerule>label
columns -- Specifies the column name of the fragmented column;
Algorithm - Select the slicing algorithm (name attribute in the function tag)
<function>label
Define the algorithm, class-slicing algorithm class name and path;
<count>number of slices, how many slices should be divided;
Rules used when <mapFile>range fragmentation;
The default value of <type>is 0, meaning that the value of the fragmented column is an integer, and non-zero means that it is a string.
[root@dras-test conf]# vim rule.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mycat:rule SYSTEM "rule.dtd">
<mycat:rule xmlns:mycat="http://io.mycat/">
<tableRule name="mod-long">
<rule>
<columns>person_id</columns>
<algorithm>mod-long</algorithm>
</rule>
</tableRule>
<tableRule name="mod-long-test">
<rule>
<columns>id</columns>
<algorithm>mod-long</algorithm>
</rule>
</tableRule>
<tableRule name="auto-sharding-long-customer">
<rule>
<columns>id</columns>
<algorithm>auto-sharding-long-customer</algorithm>
</rule>
</tableRule>
<tableRule name="auto-sharding-long-sharejoin">
<rule>
<columns>id</columns>
<algorithm>auto-sharding-long-sharejoin</algorithm>
</rule>
</tableRule>
<tableRule name="sharding-by-murmur">
<rule>
<columns>uuid</columns>
<algorithm>murmur</algorithm>
</rule>
</tableRule>
<function name="mod-long" class="io.mycat.route.function.PartitionByMod">
<!-- how many data nodes -->
<property name="count">2</property>
</function>
<function name="auto-sharding-long-customer" class="io.mycat.route.function.AutoPartitionByLong">
<property name="mapFile">autopartition-long.txt</property>
</function>
<function name="auto-sharding-long-sharejoin" class="io.mycat.route.function.AutoPartitionByLong">
<property name="mapFile">autopartition-long-sharejoin.txt</property>
</function>
<function name="murmur" class="io.mycat.route.function.PartitionByMurmurHash">
<property name="seed">0</property><!-- Default is 0 -->
<property name="type">1</property><!-- Default is 0, Express integer, Non-zero representation string-->
<property name="count">2</property><!-- The number of database nodes to be fragmented must be specified, otherwise they cannot be fragmented -->
<property name="virtualBucketTimes">160</property><!-- An actual database node is mapped to so many virtual nodes that the default is 160 times, that is, 160 times the number of virtual nodes than the number of physical nodes. -->
<!-- <property name="weightMapFile">weightMapFile</property> Node weight, node without specified weight defaults to 1.with properties Fill in the file format from 0 to count-1 The integer value, that is, the node index, is key，Value with node weight value.>All weight values must be positive integers or 1 instead -->
<property name="bucketMapPath">/usr/local/mycat/logs/bucketMapPath</property>
<!-- Used for testing to observe the distribution of physical and virtual nodes, if this property is specified, the virtual node's murmur hash The mapping of values to physical nodes is output to this file line by line, with no default value, and nothing will be output if not specified -->
</function>
</mycat:rule>
3. Description
For the above profiles, select one to illustrate, and so on.
For the t_person table:
In sechma.xml:
<table name="t_person" dataNode="dn1,dn2" rule="mod-long" />
Note that they exist on the slice nodes dn1 and dn2 respectively, corresponding to db1 and db2 of the actual MySQL database:
<dataNode name="dn1" dataHost="localhost1" database="db1" />
<dataNode name="dn2" dataHost="localhost1" database="db2" />
Database db1 and db2 are also on the slicing host localhost1, which is the actual MySQL server to which you are connecting.
<writeHost host="hostM1" url="localhost:3306" user="root" password="" >
Therefore, the t_person table is stored in db1 and db2 of the actual MySQL server, respectively, according to rule='mod-long'.
In rule.xml,
The mod-long algorithm specifies that its tiles are id, and the tiling algorithm is mod-long to model the ID column.
count=2, which means to take a modulus of 2,
The modeled value is 0, stored in dn1, modeled value is 1, stored in dn2.
4. Validation
Create a t_person table with an id column in the mycat database and insert five pieces of data:
[root@dras-test ~]# mysql -uroot -p123456 -h127.0.0.1 -P8066
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.29-mycat-1.6-RELEASE-20161028204710 MyCat Server (OpenCloundDB)
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+----------+
| DATABASE |
+----------+
| mycatdb |
+----------+
1 row in set (0.00 sec)
mysql> use mycatdb;
Database changed
mysql>
mysql> create table t_person(id int(11) primary key, name varchar(32));
Query OK, 0 rows affected (0.04 sec)
mysql> desc t_person;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(32) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)
mysql>
mysql> insert into t_person(id,name) values(1,"Moxiao1"),(2,"Moxiao2"),(3,"Moxiao3"),(4,"Moxiao4"),(5,"Moxiao5");
Query OK, 5 rows affected (0.02 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql>
mysql> select * from t_person;
+----+---------+
| id | name |
+----+---------+
| 2 | Moxiao2 |
| 4 | Moxiao4 |
| 1 | Moxiao1 |
| 3 | Moxiao3 |
| 5 | Moxiao5 |
+----+---------+
5 rows in set (0.04 sec)
In the actual physical MySQL server, view:
[root@dras-test conf]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 522063
Server version: 5.1.71-log Source distribution
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| db1 |
| db2 |
| db3 |
| estudy |
| mysql |
| test |
| yundras |
+--------------------+
8 rows in set (0.00 sec)
mysql> select * from db1.t_person;
+----+---------+
| id | name |
+----+---------+
| 2 | Moxiao2 |
| 4 | Moxiao4 |
+----+---------+
2 rows in set (0.00 sec)
mysql> select * from db2.t_person;
+----+---------+
| id | name |
+----+---------+
| 1 | Moxiao1 |
| 3 | Moxiao3 |
| 5 | Moxiao5 |
+----+---------+
3 rows in set (0.00 sec)The t_person table was successfully automatically dispersed by mycat into t_persons of db1 and db2 libraries.
Solve the problem of a large amount of data in a form.When querying with a slice list as a condition, it first finds the slice in which it is located, narrowing down the search scope.
mysql> explain select * from t_person where id=3;
+-----------+-----------------------------------------------+
| DATA_NODE | SQL |
+-----------+-----------------------------------------------+
| dn2 | SELECT * FROM t_person WHERE id = 3 LIMIT 100 |
+-----------+-----------------------------------------------+
1 row in set (0.01 sec)