云社区 博客 博客详情

【Mycat】Mycat核心开发者带你轻松掌握Mycat路由转发!!

SegmentFault思否 发表于 2020-11-15 23:46:05 2020-11-15
0
0

【摘要】 写在前面熟悉Mycat的小伙伴都知道,Mycat一个很重要的功能就是路由转发,那么,这篇文章就带着大家一起来看看Mycat是如何进行路由转发的,好了,不多说了,我们直接进入主题。环境准备软件版本操作系统:CentOS-6.8JDK版本:jdk1.8Mycat版本:Mycat-server-1.6MySQL:5.7注意:这里,我将Mycat和MySQL安装在同一台虚拟机(IP:...

写在前面

熟悉Mycat的小伙伴都知道,Mycat一个很重要的功能就是路由转发,那么,这篇文章就带着大家一起来看看Mycat是如何进行路由转发的,好了,不多说了,我们直接进入主题。

环境准备

软件版本

操作系统:CentOS-6.8

JDK版本:jdk1.8

Mycat版本:Mycat-server-1.6

MySQL:5.7

注意:这里,我将Mycat和MySQL安装在同一台虚拟机(IP:192.168.209.140 主机名为:binghe140),大家也可以将Mycat和MySQL安装到不同的主机上,测试效果是一样的。

创建物理库

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;

配置Mycat

schema.xml配置


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" dataHost="localhost1" database="db1" /> <dataNode name="dn2" dataHost="localhost1" database="db2" /> <dataNode name="dn3" dataHost="localhost1" 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>  <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>    dataHost>
mycat:schema>

server.xml配置


mycat:server SYSTEM "server.dtd">
<mycat:server xmlns:mycat="http://org.opencloudb/"> <system> <property name="defaultSqlParser">druidparserproperty> system> <user name="binghe"> <property name="password">binghe.123property> <property name="schemas">bingheproperty> user> <user name="test"> <property name="password">testproperty> <property name="schemas">bingheproperty> <property name="readOnly">trueproperty> user>
mycat:server>

rule.xml配置


mycat:rule SYSTEM "rule.dtd">
<mycat:rule xmlns:mycat="http://org.opencloudb/"> <tableRule name="rule1"> <rule> <columns>idcolumns> <algorithm>func1algorithm> rule> tableRule> <tableRule name="rule2"> <rule> <columns>user_idcolumns> <algorithm>func1algorithm> rule> tableRule> <tableRule name="sharding-by-intfile"> <rule> <columns>sharding_idcolumns> <algorithm>hash-intalgorithm> rule> tableRule> <tableRule name="auto-sharding-long"> <rule> <columns>idcolumns> <algorithm>rang-longalgorithm> rule> tableRule> <tableRule name="mod-long"> <rule> <columns>idcolumns> <algorithm>mod-longalgorithm> rule> tableRule> <tableRule name="sharding-by-murmur"> <rule> <columns>idcolumns> <algorithm>murmuralgorithm> rule> tableRule> <tableRule name="sharding-by-month"> <rule> <columns>create_datecolumns> <algorithm>partbymonthalgorithm> rule> tableRule> <tableRule name="latest-month-calldate"> <rule> <columns>calldatecolumns> <algorithm>latestMonthalgorithm> rule> tableRule> <tableRule name="auto-sharding-rang-mod"> <rule> <columns>idcolumns> <algorithm>rang-modalgorithm> rule> tableRule> <tableRule name="jch"> <rule> <columns>idcolumns> <algorithm>jump-consistent-hashalgorithm> rule> tableRule> <function name="murmur" class="org.opencloudb.route.function.PartitionByMurmurHash"> <property name="seed">0property> <property name="count">2property> <property name="virtualBucketTimes">160property> function> <function name="hash-int" class="org.opencloudb.route.function.PartitionByFileMap"> <property name="mapFile">partition-hash-int.txtproperty> function> <function name="rang-long" class="org.opencloudb.route.function.AutoPartitionByLong"> <property name="mapFile">autopartition-long.txtproperty> function> <function name="mod-long" class="org.opencloudb.route.function.PartitionByMod">  <property name="count">3property> function> <function name="func1" class="org.opencloudb.route.function.PartitionByLong"> <property name="partitionCount">8property> <property name="partitionLength">128property> function> <function name="latestMonth" class="org.opencloudb.route.function.LatestMonthPartion"> <property name="splitOneDay">24property> function> <function name="partbymonth" class="org.opencloudb.route.function.PartitionByMonth"> <property name="dateFormat">yyyy-MM-ddproperty> <property name="sBeginDate">2020-01-01property> function> <function name="rang-mod" class="org.opencloudb.route.function.PartitionByRangeMod"> <property name="mapFile">partition-range-mod.txtproperty> function> <function name="jump-consistent-hash" class="org.opencloudb.route.function.PartitionByJumpConsistentHash"> <property name="totalBuckets">3property> function>
mycat:rule>

登录Mycat

登录Mycat

命令行输入以下命令登录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 travelrecord (id bigint not null primary key,user_id varchar(100),traveldate DATE, fee decimal,days int); 

结果如下:

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>

说明创建表的SQL语句被Mycat路由到dn1,dn2,dn3三个节点上,也就是说在3个节点上都执行了创建表的SQL。

我们输入建表语句:

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)

此时,将会在dn1,dn2,dn3三个节点上创建travelrecord表。

录入数据测试

录入到dn1节点

我们在命令行输入如下SQL语句

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

结果如下:

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)

说明Mycat将SQL路由到了dn1节点。

我们执行插入语句:

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>

录入到dn2节点

我们在命令行输入如下语句:

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

结果如下:

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)

说明Mycat将SQL路由到了dn2节点,我们执行插入语句:

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)

路由到dn3节点

我们在命令行输入如下语句

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

结果为:

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)

说明Mycat将SQL路由到了dn3节点,我们同样执行插入语句的操作

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)

查询测试

查询所有数据

在命令行执行如下语句:

explain select * from travelrecord;

结果为:

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)

说明查询所有的数据,Mycat是将SQL语句路由到了所有的数据分片,即dn1,dn2,dn3节点上。

根据id查询指定数据

我们分别在命令行中执行如下SQL:

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

得到的结果依次如下:

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)

说明:按照分片字段查询,Mycat只会将SQL路由到指定的数据分片。

删表测试

在命令行输入如下SQL:

explain drop table travelrecord;

结果如下

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)

有结果可知,删表操作和创建表操作一样,Mycat在本实例中都会将SQL路由到所有的数据分片。

注意:本文的Mycat路由结果针对本文的配置实例,其他配置下,Mycat的路由结果可能会有不同。

好了,我们今天就到这儿吧,我是冰河,我们下期见~~

重磅福利

微信搜一搜【冰河技术】微信公众号,关注这个有深度的程序员,每天阅读超硬核技术干货,公众号内回复【PDF】有我准备的一线大厂面试资料和我原创的超硬核PDF技术文档,以及我为大家精心准备的多套简历模板(不断更新中),希望大家都能找到心仪的工作,学习是一条时而郁郁寡欢,时而开怀大笑的路,加油。如果你通过努力成功进入到了心仪的公司,一定不要懈怠放松,职场成长和新技术学习一样,不进则退。如果有幸我们江湖再见!

另外,我开源的各个PDF,后续我都会持续更新和维护,感谢大家长期以来对冰河的支持!!

写在最后

如果你觉得冰河写的还不错,请微信搜索并关注「 冰河技术 」微信公众号,跟冰河学习高并发、分布式、微服务、大数据、互联网和云原生技术,「 冰河技术 」微信公众号更新了大量技术专题,每一篇技术文章干货满满!不少读者已经通过阅读「 冰河技术 」微信公众号文章,吊打面试官,成功跳槽到大厂;也有不少读者实现了技术上的飞跃,成为公司的技术骨干!如果你也想像他们一样提升自己的能力,实现技术能力的飞跃,进大厂,升职加薪,那就关注「 冰河技术 」微信公众号吧,每天更新超硬核技术干货,让你对如何提升技术能力不再迷茫!

文章来源: segmentfault.com,作者:冰河,版权归原作者所有,如需转载,请联系作者。

原文链接:segmentfault.com/a/1190000038170871

登录后可下载附件,请登录或者注册

【版权声明】本文为华为云社区用户转载文章,如果您发现本社区中有涉嫌抄袭的内容,欢迎发送邮件至:huaweicloud.bbs@huawei.com进行举报,并提供相关证据,一经查实,本社区将立刻删除涉嫌侵权内容。
评论文章 //点赞 收藏 0
点赞
分享文章到微博
分享文章到朋友圈

上一篇:备受争议的PHP前景究竟如何?我们该何去何从?

下一篇:Raft算法之日志篇

评论 (0)


登录后可评论,请 登录注册

评论