[MYCAT] the core developer of MYCAT will take you to easily master MYCAT routing and forwarding!!

Time:2021-12-9

Write in front

Friends who are familiar with MYCAT know that a very important function of MYCAT is routing and forwarding. Then, this article will take you to see how MYCAT performs routing and forwarding. Well, not much more, let’s go directly to the topic.

Environmental preparation

Software version

Operating system: centos-6.8

JDK version: JDK1.8

MYCAT version: mycat-server-1.6

MySQL:5.7

Note: here, I install MYCAT and MySQL on the same virtual machine (IP: 192.168.209.140, host name: binghe140). You can also install MYCAT and MySQL on different hosts. The test effect is the same.

Create physical library

mysql -uroot -proot -h192.168.209.140 -P3306

drop database if exists db1;
create database db1;
drop database if exists db2;
create database db2;
drop database if exists db3;
create database db3;

Configure MYCAT

Schema.xml configuration

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://org.opencloudb/" >
 
    <schema name="binghe" checkSQLschema="false" sqlMaxLimit="100">
        <table name="travelrecord" dataNode="dn1,dn2,dn3" rule="auto-sharding-long"></table>
    </schema>
    <!-- <dataNode name="dn1$0-743" dataHost="localhost1" database="db$0-743" 
        /> -->
    <dataNode name="dn1" dataHost="localhost1" database="db1" />
    <dataNode name="dn2" dataHost="localhost1" database="db2" />
    <dataNode name="dn3" dataHost="localhost1" database="db3" />
    <!--<dataNode name="dn4" dataHost="sequoiadb1" database="SAMPLE" />
     <dataNode name="jdbc_dn1" dataHost="jdbchost" database="db1" /> 
    <dataNode    name="jdbc_dn2" dataHost="jdbchost" database="db2" /> 
    <dataNode name="jdbc_dn3"     dataHost="jdbchost" database="db3" /> -->
    <dataHost name="localhost1" maxCon="1000" minCon="10" balance="0"
        writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
        <heartbeat>select user()</heartbeat>
        <!-- can have multi write hosts -->
        <writeHost host="hostM1" url="127.0.0.1:3306" user="root" password="root"></writeHost>
        
        <writeHost host="hostM2" url="127.0.0.1:3306" user="root" password="root"></writeHost>
        <!--<writeHost host="hostS1" url="localhost:3316" user="root"-->
            <!--password="123456" />-->
        <!-- <writeHost host="hostM2" url="localhost:3316" user="root" password="123456"/> -->
    </dataHost>
</mycat:schema>

Server.xml configuration

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mycat:server SYSTEM "server.dtd">
<mycat:server xmlns:mycat="http://org.opencloudb/">
    <system>
    <property name="defaultSqlParser">druidparser</property>
    </system>
    <user name="binghe">
        <property name="password">binghe.123</property>
        <property name="schemas">binghe</property>
    </user>
    <user name="test">
        <property name="password">test</property>
        <property name="schemas">binghe</property>
        <property name="readOnly">true</property>
    </user>
</mycat:server>

Rule.xml configuration

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mycat:rule SYSTEM "rule.dtd">
<mycat:rule xmlns:mycat="http://org.opencloudb/">
    <tableRule name="rule1">
        <rule>
            <columns>id</columns>
            <algorithm>func1</algorithm>
        </rule>
    </tableRule>
 
    <tableRule name="rule2">
        <rule>
            <columns>user_id</columns>
            <algorithm>func1</algorithm>
        </rule>
    </tableRule>
 
    <tableRule name="sharding-by-intfile">
        <rule>
            <columns>sharding_id</columns>
            <algorithm>hash-int</algorithm>
        </rule>
    </tableRule>
    <tableRule name="auto-sharding-long">
        <rule>
            <columns>id</columns>
            <algorithm>rang-long</algorithm>
        </rule>
    </tableRule>
    <tableRule name="mod-long">
        <rule>
            <columns>id</columns>
            <algorithm>mod-long</algorithm>
        </rule>
    </tableRule>
    <tableRule name="sharding-by-murmur">
        <rule>
            <columns>id</columns>
            <algorithm>murmur</algorithm>
        </rule>
    </tableRule>
    <tableRule name="sharding-by-month">
        <rule>
            <columns>create_date</columns>
            <algorithm>partbymonth</algorithm>
        </rule>
    </tableRule>
    <tableRule name="latest-month-calldate">
        <rule>
            <columns>calldate</columns>
            <algorithm>latestMonth</algorithm>
        </rule>
    </tableRule>
    
    <tableRule name="auto-sharding-rang-mod">
        <rule>
            <columns>id</columns>
            <algorithm>rang-mod</algorithm>
        </rule>
    </tableRule>
    
    <tableRule name="jch">
        <rule>
            <columns>id</columns>
            <algorithm>jump-consistent-hash</algorithm>
        </rule>
    </tableRule>
 
    <function name="murmur"
        class="org.opencloudb.route.function.PartitionByMurmurHash">
        <property name="seed">0</property>
        <property name="count">2</property>
        <property name="virtualBucketTimes">160</property>
    </function>
    <function name="hash-int"
        class="org.opencloudb.route.function.PartitionByFileMap">
        <property name="mapFile">partition-hash-int.txt</property>
    </function>
    <function name="rang-long"
        class="org.opencloudb.route.function.AutoPartitionByLong">
        <property name="mapFile">autopartition-long.txt</property>
    </function>
    <function name="mod-long" class="org.opencloudb.route.function.PartitionByMod">
        <!-- how many data nodes -->
        <property name="count">3</property>
    </function>
 
    <function name="func1" class="org.opencloudb.route.function.PartitionByLong">
        <property name="partitionCount">8</property>
        <property name="partitionLength">128</property>
    </function>
    <function name="latestMonth"
        class="org.opencloudb.route.function.LatestMonthPartion">
        <property name="splitOneDay">24</property>
    </function>
    <function name="partbymonth"
        class="org.opencloudb.route.function.PartitionByMonth">
        <property name="dateFormat">yyyy-MM-dd</property>
        <property name="sBeginDate">2020-01-01</property>
    </function>
    
    <function name="rang-mod" class="org.opencloudb.route.function.PartitionByRangeMod">
            <property name="mapFile">partition-range-mod.txt</property>
    </function>
    
    <function name="jump-consistent-hash" class="org.opencloudb.route.function.PartitionByJumpConsistentHash">
        <property name="totalBuckets">3</property>
    </function>
</mycat:rule>

Log in to MYCAT

Log in to MYCAT

From the command line, enter the following command to log in to MYCAT

D:\>mysql -ubinghe -pbinghe.123 -h192.168.209.140 -P8066
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.8-mycat-1.6.1-RELEASE-20170807215126 MyCat Server (OpenCloundDB)
 
Copyright (c) 2000, 2016, 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>

Create table test

Enter the following command to view the route to create the table

create table travelrecord (id bigint not null primary key,user_id varchar(100),traveldate DATE, fee decimal,days int); 

The results are as follows:

mysql> explain create table travelrecord (id bigint not null primary key,user_id varchar(100),traveldate DATE, fee decimal,days int);
+-----------+-----------------------------------------------------------------------------------------------------------------------+
| DATA_NODE | SQL                                                                                                                   |
+-----------+-----------------------------------------------------------------------------------------------------------------------+
| dn1       | create table travelrecord (id bigint not null primary key,user_id varchar(100),traveldate DATE, fee decimal,days int) |
| dn2       | create table travelrecord (id bigint not null primary key,user_id varchar(100),traveldate DATE, fee decimal,days int) |
| dn3       | create table travelrecord (id bigint not null primary key,user_id varchar(100),traveldate DATE, fee decimal,days int) |
+-----------+-----------------------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)
 
mysql>

Note the SQL statement for creating the table is routed by MYCAT to three nodes DN1, DN2 and DN3, that is, the SQL statement for creating the table is executed on all three nodes.

We enter the table creation statement:

mysql> create table travelrecord (id bigint not null primary key,user_id varchar(100),traveldate DATE, fee decimal,days int);
Query OK, 0 rows affected (0.18 sec)

At this time, the travelrecord table will be created on three nodes: DN1, DN2 and DN3.

Data entry test

Enter to DN1 node

We enter the following SQL statement on the command line

explain insert into travelrecord (id,user_id,traveldate,fee,days) values(100001,'binghe','2020-11-10',510.5,3);

The results are as follows:

mysql> explain insert into travelrecord (id,user_id,traveldate,fee,days) values(100001,'binghe','2020-11-10',510.5,3);
+-----------+-------------------------------------------------------------------------------------------------------------+
| DATA_NODE | SQL                                                                                                         |
+-----------+-------------------------------------------------------------------------------------------------------------+
| dn1       | insert into travelrecord (id,user_id,traveldate,fee,days) values(100001,'binghe','2020-11-10',510.5,3) |
+-----------+-------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Description MYCAT routes SQL to the DN1 node.

We execute the insert statement:

mysql> insert into travelrecord (id,user_id,traveldate,fee,days) values(100001,'binghe','2020-11-10',510.5,3);
Query OK, 1 row affected, 1 warning (0.01 sec)
 
mysql>

Enter to DN2 node

We enter the following statement on the command line:

explain insert into travelrecord (id,user_id,traveldate,fee,days) values(8000004,'binghe','2020-11-10',510.5,3);

The results are as follows:

mysql> explain insert into travelrecord (id,user_id,traveldate,fee,days) values(8000004,'binghe','2020-11-10',510.5,3);
+-----------+--------------------------------------------------------------------------------------------------------------+
| DATA_NODE | SQL                                                                                                          |
+-----------+--------------------------------------------------------------------------------------------------------------+
| dn2       | insert into travelrecord (id,user_id,traveldate,fee,days) values(8000004,'binghe','2020-11-10',510.5,3) |
+-----------+--------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Description MYCAT routes SQL to the DN2 node. We execute the insert statement:

mysql> insert into travelrecord (id,user_id,traveldate,fee,days) values(8000004,'binghe','2020-11-10',510.5,3);
Query OK, 1 row affected, 1 warning (0.06 sec)

Route to DN3 node

We enter the following statement on the command line

explain insert into travelrecord (id,user_id,traveldate,fee,days) values(10000004,'binghe','2020-11-10',510.5,3);

The result is:

mysql> explain insert into travelrecord (id,user_id,traveldate,fee,days) values(10000004,'binghe','2020-11-10',510.5,3);
+-----------+---------------------------------------------------------------------------------------------------------------+
| DATA_NODE | SQL                                                                                                           |
+-----------+---------------------------------------------------------------------------------------------------------------+
| dn3       | insert into travelrecord (id,user_id,traveldate,fee,days) values(10000004,'binghe','2020-11-10',510.5,3) |
+-----------+---------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

It shows that MYCAT routes SQL to the DN3 node. We also execute the operation of inserting statements

mysql>  insert into travelrecord (id,user_id,traveldate,fee,days) values(10000004,'binghe','2020-11-10',510.5,3);
Query OK, 1 row affected, 1 warning (0.01 sec)

Query test

Query all data

Execute the following statement on the command line:

explain select * from travelrecord;

The result is:

mysql> explain select * from travelrecord;
+-----------+--------------------------------------+
| DATA_NODE | SQL                                  |
+-----------+--------------------------------------+
| dn1       | SELECT * FROM travelrecord LIMIT 100 |
| dn2       | SELECT * FROM travelrecord LIMIT 100 |
| dn3       | SELECT * FROM travelrecord LIMIT 100 |
+-----------+--------------------------------------+
3 rows in set (0.01 sec)

Description: MYCAT queries all data. MYCAT routes SQL statements to all data segments, that is, DN1, DN2 and DN3 nodes.

Query the specified data by ID

We execute the following SQL on the command line:

explain select * from travelrecord where id = 1000004;
explain select * from travelrecord where id = 8000004;
explain select * from travelrecord where id = 10000004;

The results obtained are as follows:

mysql> explain select * from travelrecord where id = 1000004;
+-----------+---------------------------------------------------------+
| DATA_NODE | SQL                                                     |
+-----------+---------------------------------------------------------+
| dn1       | SELECT * FROM travelrecord WHERE id = 1000004 LIMIT 100 |
+-----------+---------------------------------------------------------+
1 row in set (0.06 sec)
 
mysql> explain select * from travelrecord where id = 8000004;
+-----------+---------------------------------------------------------+
| DATA_NODE | SQL                                                     |
+-----------+---------------------------------------------------------+
| dn2       | SELECT * FROM travelrecord WHERE id = 8000004 LIMIT 100 |
+-----------+---------------------------------------------------------+
1 row in set (0.00 sec)
 
mysql> explain select * from travelrecord where id = 10000004;
+-----------+----------------------------------------------------------+
| DATA_NODE | SQL                                                      |
+-----------+----------------------------------------------------------+
| dn3       | SELECT * FROM travelrecord WHERE id = 10000004 LIMIT 100 |
+-----------+----------------------------------------------------------+
1 row in set (0.00 sec)

Note: according to the fragment field query, MYCAT will only route SQL to the specified data fragment.

Delete table test

Enter the following SQL on the command line:

explain drop table travelrecord;

give the result as follows

mysql> explain drop table travelrecord;
+-----------+-------------------------+
| DATA_NODE | SQL                     |
+-----------+-------------------------+
| dn1       | drop table travelrecord |
| dn2       | drop table travelrecord |
| dn3       | drop table travelrecord |
+-----------+-------------------------+
3 rows in set (0.00 sec)

The results show that the operation of deleting a table is the same as that of creating a table. In this example, MYCAT will route SQL to all data fragments.

Note: the MYCAT routing results in this article are specific to the configuration examples in this article. Under other configurations, the MYCAT routing results may be different.

Well, let’s stop here today. I’m glacier. I’ll see you next time~~

Heavy benefits

WeChat search official account official, WeChat’s official account, pay attention to this deep programmer, read hard core technology dry cargo every day, reply to PDF, and I have prepared a big factory interview information and my original PDF technology document, and I have prepared many resume templates for you (constantly updated). I hope you can find your favorite job. Learning is a way of being depressed and laughing sometimes. Come on. If you successfully enter the company you want through your efforts, you must not relax. Career growth is the same as new technology learning. If you don’t advance, you will fall back. See you in Jianghu if you are lucky!

In addition, I will continue to update and maintain all my open source PDFs. Thank you for your long-term support for GLACIER!!

Write at the end

If you think binghe is not bad, please search on wechat and follow “Glacier TechnologyThe official account of WeChat is learning high concurrent, distributed, micro services, big data, Internet and cloud native technology with glaciers.Glacier TechnologyThe official account of WeChat has updated a lot of technical topics, and every technical article is full of dry cargo. Many readers have readGlacier TechnologyWeChat’s official account of the public, who has been interviewing the official, has successfully switched to a big factory. Many readers have also achieved a technological leap and become the technical backbone of the company! If you want to improve your ability, achieve a leap in technical ability, enter big factories, get promoted and get a raise like them, pay attention to “Glacier TechnologyWeChat official account, updating the hard core technology dry cargo every day, so that you can’t lose your knowledge of how to improve the technical capability.

[MYCAT] the core developer of MYCAT will take you to easily master MYCAT routing and forwarding!!