测试环境
两台虚拟机:192.169.10.241(node1)、192.169.10.242(node2)
操作系统:RHEL6.8
Mysql版本:5.7.19
其中:node1的3306、3307、node2的3306 为一主两从的结构
node2的3307为单实例
MyCat安装
下载地址:
http://dl.mycat.io/1.6.7.3/
Mycat这里我也安装在node1上
解压安装包:
tar -zxvf Mycat-server-1.6.7.3-release-20190828135747-linux.tar.gz
MyCat配置、运行
配置文件都在./conf里面
我首先配置的是server.xml和schema.xml文件,针对MyCat的逻辑用户、逻辑db、逻辑节点、物理节点这几个属性
server.xml里面配置MyCat的一些系统参数:
user里面配置的是MyCat逻辑用户的属性,schemas参数对应的是逻辑db
另外在system里面配置了Mycat的管理端口和业务端口
其他的参数暂时都是默认的,xml里面对于各参数的注释交待的也比较清楚
schema.xml里面配置的是MyCat的逻辑db、逻辑节点、物理节点这些属性:
schema里面配置的是逻辑db的信息,包括逻辑db、逻辑db下面的表、逻辑db对应的逻辑节点
dataNode里面配置的是逻辑节点的信息,以及逻辑节点对应的物理节点
dataHost里面配置的是物理节点的信息,包括读写节点、负载均衡、心跳检测等
基本信息配置完成后,就可以启动MyCat了,../bin下面mycat start即可,启动日志打印在./log/wrapper.log
启动遇到的问题:
wrapper.log日志如下
STATUS | wrapper | 2019/09/20 04:23:32 | --> Wrapper Started as Daemon
STATUS | wrapper | 2019/09/20 04:23:33 | Launching a JVM...
INFO | jvm 1 | 2019/09/20 04:23:35 | Wrapper (Version 3.2.3) http://wrapper.tanukisoftware.org
INFO | jvm 1 | 2019/09/20 04:23:35 | Copyright 1999-2006 Tanuki Software, Inc. All Rights Reserved.
INFO | jvm 1 | 2019/09/20 04:23:35 |
INFO | jvm 1 | 2019/09/20 04:23:35 | WrapperSimpleApp: Unable to locate the class io.mycat.MycatStartup: java.lang.UnsupportedClassVersionError: io/mycat/MycatStartup : Unsupported major.minor version 52.
0
INFO | jvm 1 | 2019/09/20 04:23:35 |
INFO | jvm 1 | 2019/09/20 04:23:35 | WrapperSimpleApp Usage:
INFO | jvm 1 | 2019/09/20 04:23:35 | java org.tanukisoftware.wrapper.WrapperSimpleApp {app_class} [app_arguments]
INFO | jvm 1 | 2019/09/20 04:23:35 |
INFO | jvm 1 | 2019/09/20 04:23:35 | Where:
INFO | jvm 1 | 2019/09/20 04:23:35 | app_class: The fully qualified class name of the application to run.
INFO | jvm 1 | 2019/09/20 04:23:35 | app_arguments: The arguments that would normally be passed to the
INFO | jvm 1 | 2019/09/20 04:23:35 | application.
STATUS | wrapper | 2019/09/20 04:23:37 | <-- Wrapper Stopped
从网上查得知,Mycat的运行依赖于jdk,于是从网上下载jdk-8u221-linux-x64.tar.gz,解压,在/usr/bin下面设置软链接,然后也修改了mycat的./conf下的wrapper.conf
修改完成后再启动,启动成功
MyCat读写分离
读写分离的话,主要是配置逻辑节点和物理节点这部分
dataHost相关参数:
balance:
负载均衡类型,目前的取值有 3 种:
1. balance="0", 不开启读写分离机制,所有读操作都发送到当前可用的 writeHost 上。
2. balance="1",全部的 readHost 与 stand by writeHost 参与 select 语句的负载均衡,简单的说,当双主双从模式(M1->S1,M2->S2,并且 M1 与 M2 互为主备),正常情况下,M2,S1,S2 都参与 select 语句的负载均衡。
3. balance="2",所有读操作都随机的在 writeHost、readhost 上分发。
4. balance="3",所有读请求随机的分发到 wiriterHost 对应的 readhost 执行,writerHost 不负担读压力,
注意 balance=3 只在 1.4 及其以后版本有,1.3 没有。
writeType
负载均衡类型,目前的取值有 2 种:
1. writeType="0", 所有写操作发送到配置的第一个 writeHost,第一个挂了切到还生存的第二个 writeHost,
重新启动后已切换后的为准,切换记录在配置文件中:dnindex.properties .
2. writeType="1",所有写操作都随机的发送到配置的 writeHost,1.5 以后废弃不推荐
来测试一下:
在这里可以修改一下./conf/log4j2.xml,将日志输出级别修改为debug,日志为./log/mycat.log
然后通过业务端口登录Mycat,然后查询表
[root@rhel6 bin]# mysql -uroot -p -P8066 -h192.169.10.241
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.6.29-mycat-1.6.7.3-release-20190828215749 MyCat Server (OpenCloudDB)
Copyright (c) 2000, 2017, 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> use testdb;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select * from test1008;
+----+------+
| id | comm |
+----+------+
| 2 | 2 |
| 4 | 4 |
| 1 | 1 |
| 3 | 3 |
+----+------+
4 rows in set (0.01 sec)
mysql> select * from test1008;
+----+------+
| id | comm |
+----+------+
| 2 | 2 |
| 4 | 4 |
| 1 | 1 |
| 3 | 3 |
+----+------+
4 rows in set (0.00 sec)
然后我们去mycat.log里面看日志打印
2019-09-20 08:30:19.571 DEBUG [$_NIOREACTOR-0-RW] (io.mycat.net.FrontendConnection.query(FrontendConnection.java:340)) - ServerConnection [id=1, schema=testdb, host=192.169.10.241, user=root,txIsolation=3, autocommit=true, schema=testdb, executeSql=show tables] select * from test1008
2019-09-20 08:30:19.571 DEBUG [$_NIOREACTOR-0-RW] (io.mycat.server.ServerQueryHandler.query(ServerQueryHandler.java:57)) - ServerConnection [id=1, schema=testdb, host=192.169.10.241, user=root,txIsolation=3, autocommit=true, schema=testdb, executeSql=select * from test1008]select * from test1008
2019-09-20 08:30:19.637 DEBUG [$_NIOREACTOR-0-RW] (io.mycat.cache.impl.EnchachePool.get(EnchachePool.java:77)) - SQLRouteCache miss cache ,key:testdbselect * from test1008
2019-09-20 08:30:19.718 DEBUG [$_NIOREACTOR-0-RW] (io.mycat.cache.impl.EnchachePool.putIfAbsent(EnchachePool.java:60)) - SQLRouteCache add cache ,key:testdbselect * from test1008 value:select * from test1008, route={
1 -> dn1{SELECT *
FROM test1008
LIMIT 100}
2 -> dn2{SELECT *
FROM test1008
LIMIT 100}
}
2019-09-20 08:30:19.718 DEBUG [$_NIOREACTOR-0-RW] (io.mycat.server.NonBlockingSession.execute(NonBlockingSession.java:126)) - ServerConnection [id=1, schema=testdb, host=192.169.10.241, user=root,txIsolation=3, autocommit=true, schema=testdb, executeSql=select * from test1008]select * from test1008, route={
1 -> dn1{SELECT *
FROM test1008
LIMIT 100}
2 -> dn2{SELECT *
FROM test1008
LIMIT 100}
} rrs
2019-09-20 08:30:19.724 DEBUG [$_NIOREACTOR-0-RW] (io.mycat.backend.mysql.nio.handler.MultiNodeQueryHandler.<init>(MultiNodeQueryHandler.java:128)) - execute mutinode query select * from test1008
2019-09-20 08:30:19.729 DEBUG [$_NIOREACTOR-0-RW] (io.mycat.backend.mysql.nio.handler.MultiNodeQueryHandler.<init>(MultiNodeQueryHandler.java:164)) - has data merge logic
2019-09-20 08:30:19.729 DEBUG [$_NIOREACTOR-0-RW] (io.mycat.backend.mysql.nio.handler.MultiNodeQueryHandler.execute(MultiNodeQueryHandler.java:197)) - rrs.getRunOnSlave()-default
2019-09-20 08:30:19.729 DEBUG [$_NIOREACTOR-0-RW] (io.mycat.backend.datasource.PhysicalDBNode.getConnection(PhysicalDBNode.java:96)) - rrs.getRunOnSlave() default
2019-09-20 08:30:19.732 DEBUG [$_NIOREACTOR-0-RW] (io.mycat.backend.datasource.PhysicalDBNode.getConnection(PhysicalDBNode.java:127)) - rrs.getRunOnSlave() default
2019-09-20 08:30:19.733 DEBUG [$_NIOREACTOR-0-RW] (io.mycat.backend.datasource.PhysicalDBPool.getRWBanlanceCon(PhysicalDBPool.java:551)) - select read source hostS2 for dataHost:datahost1
2019-09-20 08:30:19.736 DEBUG [$_NIOREACTOR-0-RW] (io.mycat.backend.datasource.PhysicalDBNode.getConnection(PhysicalDBNode.java:96)) - rrs.getRunOnSlave() default
2019-09-20 08:30:19.736 DEBUG [$_NIOREACTOR-0-RW] (io.mycat.backend.datasource.PhysicalDBNode.getConnection(PhysicalDBNode.java:127)) - rrs.getRunOnSlave() default
2019-09-20 08:30:19.736 DEBUG [$_NIOREACTOR-0-RW] (io.mycat.backend.datasource.PhysicalDBPool.getRWBanlanceCon(PhysicalDBPool.java:551)) - select read source hostM2 for dataHost:datahost2
2019-09-20 08:30:19.742 DEBUG [$_NIOREACTOR-0-RW] (io.mycat.sqlengine.mpp.DataMergeService.onRowMetaData(DataMergeService.java:79)) - field metadata keys:[ID, COMM]
2019-09-20 08:30:19.742 DEBUG [$_NIOREACTOR-0-RW] (io.mycat.sqlengine.mpp.DataMergeService.onRowMetaData(DataMergeService.java:80)) - field metadata values:[ColMeta [colIndex=0, colType=3], ColMeta [colIndex=1, colType=253]]
2019-09-20 08:30:19.743 DEBUG [$_NIOREACTOR-0-RW] (io.mycat.backend.mysql.nio.handler.MultiNodeQueryHandler.rowEofResponse(MultiNodeQueryHandler.java:392)) - io.mycat.backend.mysql.nio.handler.MultiNodeQueryHandler@597ee8c3on row end reseponse MySQLConnection [id=24, lastTime=1568939419729, user=root, schema=testdb, old shema=testdb, borrowed=true, fromSlaveDB=true, threadId=7828, charset=utf8, txIsolation=3, autocommit=true, attachment=dn1{SELECT *
FROM test1008
LIMIT 100}, respHandler=io.mycat.backend.mysql.nio.handler.MultiNodeQueryHandler@597ee8c3, host=192.169.10.241, port=3307, statusSync=null, writeQueue=0, modifiedSQLExecuted=false] false 2
2019-09-20 08:30:19.743 DEBUG [$_NIOREACTOR-0-RW] (io.mycat.server.NonBlockingSession.releaseConnection(NonBlockingSession.java:386)) - release connection MySQLConnection [id=24, lastTime=1568939419729, user=root, schema=testdb, old shema=testdb, borrowed=true, fromSlaveDB=true, threadId=7828, charset=utf8, txIsolation=3, autocommit=true, attachment=dn1{SELECT *
FROM test1008
LIMIT 100}, respHandler=io.mycat.backend.mysql.nio.handler.MultiNodeQueryHandler@597ee8c3, host=192.169.10.241, port=3307, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
2019-09-20 08:30:19.743 DEBUG [$_NIOREACTOR-0-RW] (io.mycat.backend.datasource.PhysicalDatasource.releaseChannel(PhysicalDatasource.java:633)) - release channel MySQLConnection [id=24, lastTime=1568939419729, user=root, schema=testdb, old shema=testdb, borrowed=true, fromSlaveDB=true, threadId=7828, charset=utf8, txIsolation=3, autocommit=true, attachment=null, respHandler=null, host=192.169.10.241, port=3307, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
2019-09-20 08:30:19.744 DEBUG [$_NIOREACTOR-0-RW] (io.mycat.backend.mysql.nio.handler.MultiNodeQueryHandler.rowEofResponse(MultiNodeQueryHandler.java:392)) - io.mycat.backend.mysql.nio.handler.MultiNodeQueryHandler@597ee8c3on row end reseponse MySQLConnection [id=8, lastTime=1568939419729, user=root, schema=testdb2, old shema=testdb2, borrowed=true, fromSlaveDB=false, threadId=128, charset=utf8, txIsolation=3, autocommit=true, attachment=dn2{SELECT *
FROM test1008
LIMIT 100}, respHandler=io.mycat.backend.mysql.nio.handler.MultiNodeQueryHandler@597ee8c3, host=192.169.10.242, port=3307, statusSync=null, writeQueue=0, modifiedSQLExecuted=false] false 1
2019-09-20 08:30:19.745 DEBUG [$_NIOREACTOR-0-RW] (io.mycat.server.NonBlockingSession.releaseConnection(NonBlockingSession.java:386)) - release connection MySQLConnection [id=8, lastTime=1568939419729, user=root, schema=testdb2, old shema=testdb2, borrowed=true, fromSlaveDB=false, threadId=128, charset=utf8, txIsolation=3, autocommit=true, attachment=dn2{SELECT *
FROM test1008
LIMIT 100}, respHandler=io.mycat.backend.mysql.nio.handler.MultiNodeQueryHandler@597ee8c3, host=192.169.10.242, port=3307, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
2019-09-20 08:30:19.745 DEBUG [$_NIOREACTOR-0-RW] (io.mycat.backend.datasource.PhysicalDatasource.releaseChannel(PhysicalDatasource.java:633)) - release channel MySQLConnection [id=8, lastTime=1568939419729, user=root, schema=testdb2, old shema=testdb2, borrowed=true, fromSlaveDB=false, threadId=128, charset=utf8, txIsolation=3, autocommit=true, attachment=null, respHandler=null, host=192.169.10.242, port=3307, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
2019-09-20 08:30:19.746 DEBUG [BusinessExecutor3] (io.mycat.sqlengine.mpp.DataMergeService.getResults(DataMergeService.java:307)) - prepare mpp merge result for select * from test1008
2019-09-20 08:30:19.746 DEBUG [BusinessExecutor3] (io.mycat.backend.mysql.nio.handler.MultiNodeQueryHandler.outputMergeResult(MultiNodeQueryHandler.java:652)) - last packet id:9
我们可以看到,对于datahost1,Mycat选择了hostS2,然后我们再查询几次
2019-09-20 08:49:42.334 DEBUG [$_NIOREACTOR-0-RW] (io.mycat.backend.datasource.PhysicalDBPool.getRWBanlanceCon(PhysicalDBPool.java:551)) - select read source hostS1 for dataHost:datahost1
我们可以看到,这次对于datahost1,Mycat选择了hostS1
MyCat分片
这里我们基于datahost1和datahost2做一个简单的分片
从这个配置中我们可以看出,test1008使用到了dn1和dn2两个逻辑节点,分片规则为testrule,这个testrule是我们在./conf/rule.xml中自定义的
这里我们用的规则是io.mycat.route.function.PartitionByMod,即对id字段进行取模运算,count的数量按照实际的分片方案确定,这里我配置为逻辑节点的数量。
来我们测试一下,先通过Mycat业务口登录,然后进行insert操作
mysql> insert into test1008 values(1,'1');
ERROR 1064 (HY000): partition table, insert must provide ColumnList
mysql> insert into test1008(id,comm) values(1,'1'),(2,'2'),(3,'3'),(4,'4');
Query OK, 4 rows affected (0.02 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from test1008;
+----+------+
| id | comm |
+----+------+
| 2 | 2 |
| 4 | 4 |
| 1 | 1 |
| 3 | 3 |
+----+------+
4 rows in set (0.01 sec)
然后去两个节点分别查询:
mysql> select * from test1008;
+----+------+
| id | comm |
+----+------+
| 2 | 2 |
| 4 | 4 |
+----+------+
2 rows in set (0.00 sec)
mysql> select * from test1008;
+----+------+
| id | comm |
+----+------+
| 1 | 1 |
| 3 | 3 |
+----+------+
2 rows in set (0.01 sec)
OK ,分片完成
总结:
本次记录了第一次上手测试Mycat的一些步骤实验,从整体上先简单的了解了一下Mycat,后续会继续对Mycat的功能进行更细化的了解与测试。
http://www.mycat.io/document/mycat-definitive-guide.pdf -----Mycat的指南书
我总结的这次实验中的Mycat的逻辑结构层图:
逻辑user(root、mycat) | |||||
逻辑db(testdb) | |||||
逻辑node(dn1、dn2) | |||||
物理node(datahost1) | 物理node(datahost2) | ||||
hostM1 | hostS1 | hostS2 | hostM2 |