【Mycat】Mycat core developer with you to easily master Mycat routing and forwarding!!

Writing in the front

Friends familiar with Mycat know that a very important function of Mycat is routing and forwarding, so, this article will take you to see how Mycat is routing and forwarding, well, not to say more, we directly into the topic.

Environment to prepare

Software version

Operating system: centos-6.8

JDK version: JDk1.8

Mycat version: mycat-server-1.6

MySQL: 5.7

Note: here, I installed 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.

Creating a physical library

mysql -uroot -proot -h192168.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;
Copy the code

Configuration Mycat

Schema. The XML configuration

      
<! DOCTYPEmycat: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>
Copy the code

Server.xml configuration

      
<! DOCTYPEmycat: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>
Copy the code

Rule. The XML configuration

      
<! DOCTYPEmycat: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>
Copy the code

Login Mycat

Login Mycat

Command line Enter the following command to log in to Mycat

D:\>mysql -ubinghe -pbinghe123. -h192168.209.140. -P8066
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.58.-mycat1.61.-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>
Copy the code

Create table tests

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); 
Copy the code

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>
Copy the code

The SQL statement for creating a table is routed to dn1,dn2, and dn3 by Mycat. That is, the SQL statement for creating a table is executed on all three nodes.

We enter the construction sentence:

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)
Copy the code

At this point, the TravelRecord table will be created on dn1, DN2, and DN3 nodes.

Data entry test

Input the data to node DN1

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);
Copy the code

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)
Copy the code

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>
Copy the code

Input data to THE DN2 node

On the command line, type the following statement:

explain insert into travelrecord (id,user_id,traveldate,fee,days) values(8000004.'binghe'.'2020-11-10'.510.5.3);
Copy the code

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)
Copy the code

Mycat routes SQL to dn2. 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)
Copy the code

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);
Copy the code

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)
Copy the code

Mycat routes the SQL to the dn3 node. We also insert the statement

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)
Copy the code

Query test

Query all data

Run the following statement on the cli:

explain select * from travelrecord;
Copy the code

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)
Copy the code

To query all data, Mycat routes SQL statements to all data fragments, namely dN1, DN2, and DN3 nodes.

Query 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;
Copy the code

The results 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)
Copy the code

Mycat will only route SQL to the specified data shard.

Delete table test

Enter the following SQL on the command line:

explain drop table travelrecord;
Copy the code

The results are 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)
Copy the code

As it turns out, deleting a table is the same as creating a table. Mycat routes SQL to all data shards in this example.

Note: The Mycat route results in this article are specific to the configuration examples in this article. The Mycat route results in other configurations may be different.

Ok, let’s stop here today, I am Glacier, we will see you next time ~~

Big welfare

WeChat search the ice technology WeChat 】 the public, focus on the depth of programmers, daily reading of hard dry nuclear technology, the public, reply within [PDF] have I prepared a line companies interview data and my original super hardcore PDF technology document, and I prepared for you more than your resume template (update), I hope everyone can find the right job, Learning is a way of unhappy, sometimes laugh, come on. If you’ve worked your way into the company of your choice, don’t slack off. Career growth is like learning new technology. If lucky, we meet again in the river’s lake!

In addition, I open source each PDF, I will continue to update and maintain, thank you for your long-term support to glacier!!

Write in the last

If you think glacier wrote good, please search and pay attention to “glacier Technology” wechat public number, learn with glacier high concurrency, distributed, micro services, big data, Internet and cloud native technology, “glacier technology” wechat public number updated a large number of technical topics, each technical article is full of dry goods! Many readers have read the articles on the wechat public account of “Glacier Technology” and succeeded in job-hopping to big factories. There are also many readers to achieve a technological leap, become the company’s technical backbone! If you also want to like them to improve their ability to achieve a leap in technical ability, into the big factory, promotion and salary, then pay attention to the “Glacier Technology” wechat public account, update the super core technology every day dry goods, so that you no longer confused about how to improve technical ability!