MyCat的介绍与安装以及基本使用
站长
· 阅读数 10
MyCat
MyCat概述
Mycat是数据库中间件，连接java应用程序和数据库。
Java程序与数据库紧密关联耦合严重，高访问量高并发对数据库的压力巨大，因此可以引入数据库中间件MyCat解决。
Mycat的官网：http://www.mycat.org.cn/mycat1.html
Mycat学习指南：https://www.yuque.com/books/share/0576de75-ffc4-4c34-8586-952ae4636944
GitHub地址：https://github.com/MyCATApache/Mycat-Server
特性
核心功能
1.读写分离
Java操作MyCat，Mycat作为数据源访问，根据Java读、写请求分发到主从Mysql上，从而实现了读写分离。
2.数据分片
对数据库垂直拆分（分库）、对表水平拆分（分表）、对数据库垂直与表水平拆分（分库分表）
3.多数据源整合
Java操作MyCat，Mycat作为数据源访问，根据不同业务进行数据源划分，MyCat访问不同的数据源（MySql、MongoDB），从而实现多数据源整合。
原理
Mycat的原理中最重要的一个动词是“拦截”，它拦截用户发送的 SQL 语句，首先对 SQL语句做一些特定的分析：如分片分析、路由分析、读写分离分析、缓存分析等，然后将此 SQL 发往真实数据库，并将返回的结果做适当的处理，最终再返回给用户。
MyCat的基本使用
安装MyCat
下载地址：http://dl.mycat.org.cn/
wget http://dl.mycat.org.cn/1.6.7.6/20220524101549/Mycat-server-1.6.7.6-release-20220524173810-linux.tar.gz
解压mycat
tar -zxvf Mycat-server-1.6.7.6-release-20220524173810-linux.tar.gz
核心配置
1.MYCAT_HOME/conf/schema.xml中定义逻辑库，表、分片节点等内容
2.MYCAT_HOME/conf/rule.xml中定义分片规则
3.MYCAT_HOME/conf/server.xml中定义用户以及系统相关变量，如端口等
[root@administrator mycat]# ls conf/
autopartition-long.txt dbseq.sql log4j2.xml partition-range-mod.txt sequence_db_conf.properties server.xml zkdownload
auto-sharding-long.txt dbseq - utf8mb4.sql migrateTables.properties rule.xml sequence_distributed_conf.properties sharding-by-enum.txt
auto-sharding-rang-mod.txt ehcache.xml myid.properties schema.xml sequence_http_conf.properties wrapper.conf
cacheservice.properties index_to_charset.properties partition-hash-int.txt sequence_conf.properties sequence_time_conf.properties zkconf
基本配置
1.修改配置文件server.xml
修改用户信息，由root用户改为mycat用户，与MySQL区分，同时这个账号是连接MyCat的账号
110 <!--<user name="root" defaultAccount="true">-->
110 <user name="mycat" defaultAccount="true">
111 <property name="password">123456</property>
112 <property name="schemas">TESTDB</property>
113 <property name="defaultSchema">TESTDB</property>
114 <!--No MyCAT Database selected 错误前会尝试使用该schema作为schema，不设置则为null,报错 -->
115
116 <!-- 表级 DML 权限设置 -->
117 <!--
118 <privileges check="false">
119 <schema name="TESTDB" dml="0110" >
120 <table name="tb01" dml="0000"></table>
121 <table name="tb02" dml="1111"></table>
122 </schema>
123 </privileges>
124 -->
125 </user>
2.修改配置文件schema.xml
先备份schema.xml为schema.xml.back
cp schema.xml schema.xml.back
配置schema.xml
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<!-- 配置逻辑库 -->
<schema name="TESTDB" checkSQLschema="true" sqlMaxLimit="100" randomDataNode="dn1">
<!-- 配置逻辑库下的表 -->
<table name="mytb" primaryKey="id" dataNode="dn1" ></table>
</schema>
<!-- 数据节点配置-->
<dataNode name="dn1" dataHost="host1" database="mydb" />
<!-- 节点具体配置-->
<dataHost name="host1" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1" slaveThreshold="100">
<!-- 心跳检测 -->
<heartbeat>select user()</heartbeat>
<writeHost host="hostM1" url="jdbc:mysql://localhost:3306" user="mycat" password="123456"></writeHost>
</dataHost>
</mycat:schema>
启动MyCat
./mycat start 启动
./mycat stop 停止
./mycat console 前台运行
./mycat restart 重启服务
./mycat pause 暂停
./mycat status 查看启动状态
[root@administrator mycat]# bin/mycat console
Running Mycat-server...
wrapper | --> Wrapper Started as Console
wrapper | Launching a JVM...
jvm 1 | Wrapper (Version 3.2.3) http://wrapper.tanukisoftware.org
jvm 1 | Copyright 1999-2006 Tanuki Software, Inc. All Rights Reserved.
jvm 1 |
jvm 1 | Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver'. The driver is automatically registered via the SPI and manual loading of the driver class is generally unnecessary.
jvm 1 | MyCAT Server startup successfully. see logs in logs/mycat.log
测试验证
MyCat安装、配置完毕后，就可以让应用程序、客户端连接MyCat，通过Mycat操作MySQL，从而实现程序与数据库的解耦。
1.9066是MyCat管理窗口的端口，主要用于管理维护Mycat
mysql -umycat -p123456 -P 9066 -h IP
2.8066是MyCat数据查询的端口，主要用于通过Mycat查询数据
mysql -umycat -p123456 -P 8066 -h IP
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| TESTDB |
+--------------------+
14 rows in set (0.00 sec)
mysql> use TESTDB;
Database changed
mysql> show tables;
+----------------+
| Tables_in_mydb |
+----------------+
| mytb |
+----------------+
1 row in set (0.01 sec)
mysql> select * from mytb;
+------+-------+
| id | name |
+------+-------+
| 1 | mycat |
+------+-------+
1 row in set (0.00 sec)
mysql>
Mycat的安全设置
标签权限控制
1.user标签
<!-- 应用程序连接中间件逻辑库的用户名 -->
<user name="user">
<!-- 应用程序连接中间件逻辑库的密码 -->
<property name="password">user</property>
<!-- 逻辑库名称 -->
<property name="schemas">TESTDB</property>
<!-- true:只读；默认false：可读可写 -->
<property name="readOnly">true</property>
<!-- 默认逻辑库名称 -->
<property name="defaultSchema">TESTDB</property>
</user>
2.privileges标签
privileges 标签可以对逻辑库（schema）、表（table）进行精细化的DML权限控制
<user name="mycat" defaultAccount="true">
<property name="password">123456</property>
<property name="schemas">TESTDB</property>
<property name="defaultSchema">TESTDB</property>
<!--No MyCAT Database selected 错误前会尝试使用该schema作为schema，不设置则为null,报错 -->
<!-- 表级 DML 权限设置 -->
<!-- 是否开启权限检查,默认false -->
<privileges check="false">
<schema name="TESTDB" dml="0110" >
<table name="tb01" dml="0000"></table>
<table name="tb02" dml="1111"></table>
</schema>
</privileges>
-->
</user>
| DML权限 | 增加insert | 更新update | 查询select | 删除select |
|---|---|---|---|---|
| 0000 | N | N | N | N |
| 0010 | N | N | Y | N |
| 1110 | Y | Y | Y | N |
| 1111 | Y | Y | Y | Y |
SQL拦截
firewall标签用来定义防火墙；firewall下whitehost标签用来定义IP白名单 ，blacklist用来定义SQL黑名单。
1.白名单
设置白名单，实现指定的某主机某用户可以访问Mycat
<!-- 全局SQL防火墙设置 -->
<firewall>
<!--白名单 -->
<whitehost>
<!--白名单可以使用通配符%或着*-->
<!--对于127.0.0.1能以root账户登录-->
<host host="1*7.0.0.*" user="root"/>
</whitehost>
<blacklist check="false">
</blacklist>
</firewall>
2.黑名单
设置黑名单，实现Mycat对具体SQL操作的拦截，如增删改查等操作的拦截。
<!-- 全局SQL防火墙设置 -->
<firewall>
<!--白名单 -->
<whitehost>
<!--白名单可以使用通配符%或着*-->
<!--对于127.0.0.1能以root账户登录-->
<host host="1*7.0.0.*" user="root"/>
</whitehost>
<!-- 黑名单 -->
<blacklist check="true">
<!-- 禁止root用户进行删除操作 -->
<property name="deleteAllow">false</property>
</blacklist>
</firewall>
| 配置项 | 缺省值 | 描述 |
|---|---|---|
| selelctAllow | true | 是否允许执行SELECT语句 |
| deleteAllow | true | 是否允许执行DELETE语句 |
| updateAllow | true | 是否允许执行UPDATE语句 |
| insertAllow | true | 是否允许执行INSERT语句 |
| createTableAllow | true | 是否允许创建表 |
| setAllow | true | 是否允许使用set语法 |
| alterTableAllow | true | 是否允许执行Alter Table语句 |
| dropTableAllow | true | 是否允许修改表 |
| commitAllow | true | 是否允许执行commit操作 |
| rollbackAllow | true | 是否允许执行roll back操作 |
MyCat的常用管理命令
9066是Mycat的管理端口，登录Mycat管理窗口：mysql -umycat -h127.0.0.1 -P9066 -p123456
1.查看所有管理命令
列出59个Mycat的管理命令
mysql> show @@help;
+--------------------------------------------------------------+--------------------------------------------+
| STATEMENT | DESCRIPTION |
+--------------------------------------------------------------+--------------------------------------------+
| show @@time.current | Report current timestamp |
| show @@time.startup | Report startup timestamp |
| show @@version | Report Mycat Server version |
| show @@server | Report server status |
| show @@threadpool | Report threadPool status |
| show @@database | Report databases |
| show @@datanode | Report dataNodes |
| show @@datanode where schema = ? | Report dataNodes |
| show @@datasource | Report dataSources |
| show @@datasource where dataNode = ? | Report dataSources |
| show @@datasource.synstatus | Report datasource data synchronous |
| show @@datasource.syndetail where name=? | Report datasource data synchronous detail |
| show @@datasource.cluster | Report datasource galary cluster variables |
| show @@processor | Report processor status |
| show @@command | Report commands status |
| show @@connection | Report connection status |
| show @@cache | Report system cache usage |
| show @@backend | Report backend connection status |
| show @@session | Report front session details |
| show @@connection.sql | Report connection sql |
| show @@sql.execute | Report execute status |
| show @@sql.detail where id = ? | Report execute detail status |
| show @@sql | Report SQL list |
| show @@sql.high | Report Hight Frequency SQL |
| show @@sql.slow | Report slow SQL |
| show @@sql.resultset | Report BIG RESULTSET SQL |
| show @@sql.sum | Report User RW Stat |
| show @@sql.sum.user | Report User RW Stat |
| show @@sql.sum.table | Report Table RW Stat |
| show @@parser | Report parser status |
| show @@router | Report router status |
| show @@heartbeat | Report heartbeat status |
| show @@heartbeat.detail where name=? | Report heartbeat current detail |
| show @@slow where schema = ? | Report schema slow sql |
| show @@slow where datanode = ? | Report datanode slow sql |
| show @@sysparam | Report system param |
| show @@syslog limit=? | Report system mycat.log |
| show @@white | show mycat white host |
| show @@white.set=?,? | set mycat white host,[ip,user] |
| show @@directmemory=1 or 2 | show mycat direct memory usage |
| show @@check_global -SCHEMA= ? -TABLE=? -retry=? -interval=? | check mycat global table consistency |
| switch @@datasource name:index | Switch dataSource |
| kill @@connection id1,id2,... | Kill the specified connections |
| stop @@heartbeat name:time | Pause dataNode heartbeat |
| reload @@config | Reload basic config from file |
| reload @@config_all | Reload all config from file |
| reload @@route | Reload route config from file |
| reload @@user | Reload user config from file |
| reload @@sqlslow= | Set Slow SQL Time(ms) |
| reload @@user_stat | Reset show @@sql @@sql.sum @@sql.slow |
| rollback @@config | Rollback all config from memory |
| rollback @@route | Rollback route config from memory |
| rollback @@user | Rollback user config from memory |
| reload @@sqlstat=open | Open real-time sql stat analyzer |
| reload @@sqlstat=close | Close real-time sql stat analyzer |
| offline | Change MyCat status to OFF |
| online | Change MyCat status to ON |
| clear @@slow where schema = ? | Clear slow sql by schema |
| clear @@slow where datanode = ? | Clear slow sql by datanode |
+--------------------------------------------------------------+--------------------------------------------+
59 rows in set (0.01 sec)
2.重新加载配置文件
只能重新加载Mycat常用配置文件，如果修改一些特殊的配置文件，需要重启Mycat
mysql>reload @@config;
Query OK, 1 row affected (0.07 sec)
Reload config success
3.查看逻辑库
mysql> show @@databases;
+----------+
| DATABASE |
+----------+
| TESTDB |
+----------+
1 row in set (0.00 sec)
4.查看数据节点
mysql> show @@datanode;
+------+------------+-------+-------+--------+------+------+---------+------------+----------+---------+---------------+
| NAME | DATHOST | INDEX | TYPE | ACTIVE | IDLE | SIZE | EXECUTE | TOTAL_TIME | MAX_TIME | MAX_SQL | RECOVERY_TIME |
+------+------------+-------+-------+--------+------+------+---------+------------+----------+---------+---------------+
| dn1 | host1/mydb | 0 | mysql | 0 | 10 | 1000 | 12 | 0 | 0 | 0 | -1 |
| dn2 | host2/mydb | 0 | mysql | 0 | 10 | 1000 | 10 | 0 | 0 | 0 | -1 |
+------+------------+-------+-------+--------+------+------+---------+------------+----------+---------+---------------+
2 rows in set (0.01 sec)
注意：可以使用\G进行格式化结果输出
mysql> show @@datanode \G
*************************** 1. row ***************************
NAME: dn1
DATHOST: host1/mydb
INDEX: 0
TYPE: mysql
ACTIVE: 0
IDLE: 10
SIZE: 1000
EXECUTE: 12
TOTAL_TIME: 0
MAX_TIME: 0
MAX_SQL: 0
RECOVERY_TIME: -1
*************************** 2. row ***************************
NAME: dn2
DATHOST: host2/mydb
INDEX: 0
TYPE: mysql
ACTIVE: 0
IDLE: 10
SIZE: 1000
EXECUTE: 10
TOTAL_TIME: 0
MAX_TIME: 0
MAX_SQL: 0
RECOVERY_TIME: -1
2 rows in set (0.00 sec)
5.查看逻辑库对应物理库所在的数据节点
查看TESTDB逻辑库对应的物理数据库所在的数据节点
mysql> show @@datanode where schema=TESTDB;
+------+------------+-------+-------+--------+------+------+---------+------------+----------+---------+---------------+
| NAME | DATHOST | INDEX | TYPE | ACTIVE | IDLE | SIZE | EXECUTE | TOTAL_TIME | MAX_TIME | MAX_SQL | RECOVERY_TIME |
+------+------------+-------+-------+--------+------+------+---------+------------+----------+---------+---------------+
| dn1 | host1/mydb | 0 | mysql | 0 | 10 | 1000 | 12 | 0 | 0 | 0 | -1 |
| dn2 | host2/mydb | 0 | mysql | 0 | 10 | 1000 | 10 | 0 | 0 | 0 | -1 |
+------+------------+-------+-------+--------+------+------+---------+------------+----------+---------+---------------+
2 rows in set (0.00 sec)
6.查看可用数据节点
mysql> show @@heartbeat;
+--------+-------+-----------+------+---------+-------+--------+---------+--------------+---------------------+-------+
| NAME | TYPE | HOST | PORT | RS_CODE | RETRY | STATUS | TIMEOUT | EXECUTE_TIME | LAST_ACTIVE_TIME | STOP |
+--------+-------+-----------+------+---------+-------+--------+---------+--------------+---------------------+-------+
| hostM1 | mysql | localhost | 3306 | 1 | 0 | idle | 0 | 14,14,14 | 2022-07-19 17:03:09 | false |
| hostS1 | mysql | localhost | 3308 | 1 | 0 | idle | 0 | 14,14,14 | 2022-07-19 17:03:09 | false |
| hostM2 | mysql | localhost | 3307 | 1 | 0 | idle | 0 | 14,14,14 | 2022-07-19 17:03:09 | false |
| hostS2 | mysql | localhost | 3309 | 1 | 0 | idle | 0 | 14,14,14 | 2022-07-19 17:03:09 | false |
+--------+-------+-----------+------+---------+-------+--------+---------+--------------+---------------------+-------+
4 rows in set (0.00 sec)
RS_CODE值说明：
-1：代表连接出错
0：初始化的状态
1：代表连接后端的MySQL正常
2：代表连接超时
7.查看连接信息
1.查看上层应用连接Mycat的所有连接信息
mysql> show @@connection;
+------------+------+-----------+------+------------+-------+--------+---------+--------+---------+---------------+-------------+------------+---------+------------+
| PROCESSOR | ID | HOST | PORT | LOCAL_PORT | USER | SCHEMA | CHARSET | NET_IN | NET_OUT | ALIVE_TIME(S) | RECV_BUFFER | SEND_QUEUE | txlevel | autocommit |
+------------+------+-----------+------+------------+-------+--------+---------+--------+---------+---------------+-------------+------------+---------+------------+
| Processor0 | 3 | 127.0.0.1 | 9066 | 58110 | mycat | NULL | utf8:33 | 483 | 7788 | 246 | 4096 | 0 | | |
+------------+------+-----------+------+------------+-------+--------+---------+--------+---------+---------------+-------------+------------+---------+------------+
1 row in set (0.00 sec)
可以使用：kill @@connection ID的方式来杀死连接Mycat的进程
mysql> kill @@ connection 3;
2.查看Mycat连接后端数据库的信息
mysql> show @@backend;
+------------+------+---------+-----------+------+--------+--------+---------+------+--------+----------+------------+--------+---------+---------+------------+-------------+
| processor | id | mysqlId | host | port | l_port | net_in | net_out | life | closed | borrowed | SEND_QUEUE | schema | charset | txlevel | autocommit | tx_readonly |
+------------+------+---------+-----------+------+--------+--------+---------+------+--------+----------+------------+--------+---------+---------+------------+-------------+
| Processor0 | 32 | 0 | localhost | 3309 | 0 | 0 | 0 | 812 | false | false | 0 | | | | | |
| Processor0 | 2 | 0 | localhost | 3306 | 0 | 0 | 0 | 1413 | false | false | 0 | | | | | |
| Processor0 | 34 | 0 | localhost | 3308 | 0 | 0 | 0 | 512 | false | false | 0 | | | | | |
| Processor0 | 4 | 0 | localhost | 3306 | 0 | 0 | 0 | 1413 | false | false | 0 | | | | | |
+------------+------+---------+-----------+------+--------+--------+---------+------+--------+----------+------------+--------+---------+---------+------------+-------------+
8.查看Mycat中的缓存状态
mysql> show @@cache;
+-------------------------------------+-------+------+--------+------+------+-------------+----------+
| CACHE | MAX | CUR | ACCESS | HIT | PUT | LAST_ACCESS | LAST_PUT |
+-------------------------------------+-------+------+--------+------+------+-------------+----------+
| ER_SQL2PARENTID | 1000 | 0 | 0 | 0 | 0 | 0 | 0 |
| SQLRouteCache | 10000 | 0 | 0 | 0 | 0 | 0 | 0 |
| TableID2DataNodeCache.TESTDB_ORDERS | 50000 | 0 | 0 | 0 | 0 | 0 | 0 |
+-------------------------------------+-------+------+--------+------+------+-------------+----------+
3 rows in set (0.00 sec)
各缓存的类型说明：
ER_SQL2PARENTID：缓存ER分片中，表与父表之间的关系
SQLRouteCache：缓存SQL的路由信息
TableID2DataNodeCache.TESTDB_ORDERS：缓存表与表的主键与分片的对应关系。如果主键不为分片键时，为了加快查询的速度，可以缓存主键与分片键的对应关系
9.查看数据节点所在主机节点
mysql> show @@datasource;
+----------+--------+-------+-----------+------+------+--------+------+------+---------+-----------+------------+
| DATANODE | NAME | TYPE | HOST | PORT | W/R | ACTIVE | IDLE | SIZE | EXECUTE | READ_LOAD | WRITE_LOAD |
+----------+--------+-------+-----------+------+------+--------+------+------+---------+-----------+------------+
| dn1 | hostM1 | mysql | localhost | 3306 | W | 0 | 10 | 1000 | 12 | 2 | 0 |
| dn1 | hostS1 | mysql | localhost | 3308 | R | 0 | 8 | 1000 | 10 | 0 | 0 |
| dn2 | hostM2 | mysql | localhost | 3307 | W | 0 | 10 | 1000 | 10 | 0 | 0 |
| dn2 | hostS2 | mysql | localhost | 3309 | R | 0 | 8 | 1000 | 8 | 0 | 0 |
+----------+--------+-------+-----------+------+------+--------+------+------+---------+-----------+------------+
4 rows in set (0.00 sec)
10.总结
上述Mycat命令行操作便是常用于管理Mycat服务，更多命令行操作参考
show @@help命令
Mycat命令行管理方式常用于管理单台Mycat服务，如果需要同时管理Mycat集群中的多个Mycat服务，就需要使用Mycat-Web进行管理。
Mycat-Web性能监控
Mycat-Web是Mycat可视化运维的管理和监控平台，帮助Mycat分担统计任务和配置管理任务。
Mycat-Web引入了ZooKeeper作为配置中心，可以管理多个节点。
Mycat-Web主要管理和监控Mycat的流量、连接、活动线程和内存等，具备IP白名单、邮件告警等模块，还可以统计SQL并分析慢SQL和高频SQL等。
转载自:https://juejin.cn/post/7124846632041971749