概述
MyCAT 自身有类似其他数据库的管理监控方式，可以通过 Mysql 命令行，登录管理端口（9066） 执行相应的 SQL 进行管理，也可以通过 jdbc 的方式进行远程连接管理。
mycat登录端口配置
目前 mycat 有两个端口， 8066 数据端口， 9066 管理端口，命令行的登陆是通过 9066 管理端口来操作，登录方式类似于 mysql 的服务端登陆。
mysql -umycat -p123456 -P 9066 -h 192.168.201.34
-h 后面是主机，即当前 mycat 按照的主机地址，本地可用 127.0.0.1 远程需要远程 ip
-u Mycat server.xml 中配置的逻辑库用户
-p Mycat server.xml 中配置的逻辑库密码
-P 后面是端口 默认 9066，注意 P 是大写
-d Mycat server.xml 中配置的逻辑库
数据端口与管理端口的配置端口修改：
<system>
<property name="serverPort">8066</property>
<property name="managerPort">9066</property>
</system>
数据端口默认 8066，管理端口默认 9066
命令学习
show
通过 show @@help; 可以查看所有的命令：
MySQL [(none)]> 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)
reload @@config
该命令用于更新配置文件，例如更新 schema.xml 文件后在命令行窗口输入该命令，可不用重启即进行配置文件更新。
MySQL [(none)]> reload @@config;
Query OK, 1 row affected (0.06 sec)
Reload config success
show @@datanode
显示 MyCAT 的数据节点的列表，对应 schema.xml 配置文件的 dataNode 节点
MySQL [(none)]> reload @@datanode;
Empty set (0.00 sec)
MySQL [(none)]> show @@datanode where schema=mycat;
Empty set (0.00 sec)
show @@heartbeat
该命令用于报告心跳状态:
RS_CODE 状态： OK_STATUS = 1;正常状态
ERROR_STATUS = -1; 连接出错
TIMEOUT_STATUS = -2;连接超时
INIT_STATUS = 0; 初始化状态
若节点故障，会连续默认 5 个周期检测，心跳连续失败，就会变成-1 ，节点故障确认，然后可能发生切换
MySQL [(none)]> show @@heartbeat;
+--------+-------+----------------+------+---------+-------+--------+---------+--------------+---------------------+-------+
| NAME | TYPE | HOST | PORT | RS_CODE | RETRY | STATUS | TIMEOUT | EXECUTE_TIME | LAST_ACTIVE_TIME | STOP |
+--------+-------+----------------+------+---------+-------+--------+---------+--------------+---------------------+-------+
| hostM1 | mysql | 192.168.201.33 | 3306 | -1 | 1 | idle | 30000 | 3,3,3 | 2020-08-12 14:59:16 | false |
| hostS1 | mysql | 192.168.201.35 | 3306 | -1 | 1 | idle | 30000 | 3,3,8 | 2020-08-12 14:59:16 | false |
+--------+-------+----------------+------+---------+-------+--------+---------+--------------+---------------------+-------+
2 rows in set (0.00 sec)
show @@version
该命令用于获取 MyCAT 的版本:
MySQL [(none)]> show @@version;
+---------------------------------------------+
| VERSION |
+---------------------------------------------+
| 5.6.29-mycat-1.6.7.1-release-20190627191042 |
+---------------------------------------------+
1 row in set (0.00 sec)
show @@connection
该命令用于获取 Mycat 的前端连接状态，即应用与 mycat 的连接
MySQL [(none)]> show @@connection;
+------------+------+----------------+------+------------+-------+--------+---------+--------+---------+---------------+-------------+------------+---------+------------+
| PROCESSOR | ID | HOST | PORT | LOCAL_PORT | USER | SCHEMA | CHARSET | NET_IN | NET_OUT | ALIVE_TIME(S) | RECV_BUFFER | SEND_QUEUE | txlevel | autocommit |
+------------+------+----------------+------+------------+-------+--------+---------+--------+---------+---------------+-------------+------------+---------+------------+
| Processor2 | 4 | 192.168.201.34 | 9066 | 37364 | mycat | NULL | utf8:33 | 489 | 10715 | 1408 | 4096 | 0 | | |
+------------+------+----------------+------+------------+-------+--------+---------+--------+---------+---------------+-------------+------------+---------+------------+
1 row in set (0.00 sec)
MySQL [(none)]> kill @@connection 4; //杀掉连接
ERROR 2013 (HY000): Lost connection to MySQL server during query
show @@connection
查看后端连接状态
MySQL [(none)]> show @@backend;
Empty set (0.00 sec)
show @@cache
查看 mycat 缓存:
SQLRouteCache： sql 路由缓存。
TableID2DataNodeCache ： 缓存表主键与分片对应关系。
ER_SQL2PARENTID ： 缓存 ER 分片中子表与父表关系。
MySQL [(none)]> 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)
show @@datasource
查看数据源状态，如果配置了主从，或者多主可以切换。
MySQL [(none)]> show @@datasource;
+----------+--------+-------+----------------+------+------+--------+------+------+---------+-----------+------------+
| DATANODE | NAME | TYPE | HOST | PORT | W/R | ACTIVE | IDLE | SIZE | EXECUTE | READ_LOAD | WRITE_LOAD |
+----------+--------+-------+----------------+------+------+--------+------+------+---------+-----------+------------+
| dn1 | hostM1 | mysql | 192.168.201.33 | 3306 | W | 0 | 0 | 1000 | 0 | 0 | 0 |
| dn1 | hostS1 | mysql | 192.168.201.35 | 3306 | R | 0 | 0 | 1000 | 0 | 0 | 0 |
+----------+--------+-------+----------------+------+------+--------+------+------+---------+-----------+------------+
2 rows in set (0.00 sec)
切换数据源： switch @@datasource name:index
MySQL [(none)]> switch @@datasource dn1:1;
Query OK, 0 rows affected (0.00 sec)
name： schema 中配置的 dataHost 中 name；
index： schema 中配置的 dataHost 的 writeHost index 位标，即按照配置顺序从上到下的一次顺 序，从 0开始。
切换数据源时，会将原数据源所有的连接池中连接关闭，并且从新数据源创建新连接，此时 mycat 服务不可用。
show @@syslog limit
该命令工作在 9066 端口，用来在客户端命令窗口显示系统日志信息，
通常用于远程查看 Mycat-Server 的日志信息。
参数： limit= 后接正整数，该数值用来限定每次最多显示的日志条数
MySQL [TESTDB]> show @@syslog limit=1;
+---------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| DATE | LOG |
+---------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 2020-08-12 15:56:10 | .552 INFO [$_NIOREACTOR-3-RW] (io.mycat.net.handler.FrontendAuthenticator.success(FrontendAuthenticator.java:226)) - [thread=$_NIOREACTOR-3-RW,class=ManagerConnection,id=5,host=192.168.201.34,port=9066,schema=TESTDB]'mycat' login success |
+---------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
网友评论