管理mycat命令详解
mycat监听两个端口，分别为8066和9066；mycat服务默认的数据端口是8066，而9066端口则是mycat管理端口，用于管理mycat的整个集群状态。监听的端口可以在server.xml配置文件中修改。
连接管理端口的方法和连接8066端口的方法基本一样。
[root@test1 conf]# mysql -uroot -P9066 -h127.0.0.1 -p123456 #大写的P指定管理端口 Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 19 Server version: 5.6.29-mycat-1.6-RELEASE-20161028204710 MyCat Server (monitor) Copyright (c) 2000, 2018, 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> show @@help; #查看使用的命令
下面来介绍管理端口中使用的命令：
- reload命令：
- 重新加载配置文件，在修改完配置文件之后，不用重启mycat，可以使用如下命令重新加载：
mysql> reload @@config; Query OK, 1 row affected (0.08 sec) Reload config success
- 从mycat1.5开始新增开启和关闭sql监控分析的指令，需要在QPS测试时关闭SQL监控分析功能，否则测试结果会很差。
开启SQL监控分析功能： reload @@sqlstat=open. 关闭SQL监控分析功能： reload @@sqlstat=close. 设置慢SQL时间阈值： reload @@sqlslow= 重置SQL监控分析的数据： reload @@user_stat ：这个命令用于清除缓存。改命令工作在9066端口，用来将客户端执行
show @@sql; show @@sql.sum;show@@slow.success;命令之后的缓存信息清除、
- 重新加载配置文件，在修改完配置文件之后，不用重启mycat，可以使用如下命令重新加载：
- show命令：
- 显示mycat数据库列表，显示列表对应schema.xml配置文件中的schema子节点。
mysql> show @@database; +----------+ | DATABASE | +----------+ | TESTDB | +----------+ 1 row in set (0.02 sec)
- 显示mycat数据节点列表，显示列表对应schema.xml配置文件中的dataNode节点。
mysql> show @@dataNode; +------+---------------------+-------+-------+--------+------+------+---------+------------+----------+---------+---------------+ | NAME | DATHOST | INDEX | TYPE | ACTIVE | IDLE | SIZE | EXECUTE | TOTAL_TIME | MAX_TIME | MAX_SQL | RECOVERY_TIME | +------+---------------------+-------+-------+--------+------+------+---------+------------+----------+---------+---------------+ | dn1 | backup-one/mytest | 0 | mysql | 0 | 10 | 1000 | 5675 | 0 | 0 | 0 | -1 | | dn2 | backup-two/mytest | 0 | mysql | 0 | 10 | 1000 | 5669 | 0 | 0 | 0 | -1 | | dn3 | backup-three/mytest | 0 | mysql | 0 | 10 | 1000 | 5672 | 0 | 0 | 0 | -1 | +------+---------------------+-------+-------+--------+------+------+---------+------------+----------+---------+---------------+ 3 rows in set (0.01 sec) #NAME：表示dataNode的名称。
#DATAHOST：表示对应的datahost属性的值，即数据主机。
#ACTIVE:表示活跃的连接数量，IDLE：表示空闲的连接数量。SIZE：表示对应的总连接数量。
这个命令可以和where子句结合，查找对应schema的节点数量。
因为这里只有一个schema，因此显示的结果一样的。
mysql> show @@dataNode where schema=TESTDB;
+------+---------------------+-------+-------+--------+------+------+---------+------------+----------+---------+---------------+
| NAME | DATHOST | INDEX | TYPE | ACTIVE | IDLE | SIZE | EXECUTE | TOTAL_TIME | MAX_TIME | MAX_SQL | RECOVERY_TIME |
+------+---------------------+-------+-------+--------+------+------+---------+------------+----------+---------+---------------+
| dn1 | backup-one/mytest | 0 | mysql | 0 | 10 | 1000 | 5703 | 0 | 0 | 0 | -1 |
| dn2 | backup-two/mytest | 0 | mysql | 0 | 10 | 1000 | 5697 | 0 | 0 | 0 | -1 |
| dn3 | backup-three/mytest | 0 | mysql | 0 | 10 | 1000 | 5700 | 0 | 0 | 0 | -1 |
+------+---------------------+-------+-------+--------+------+------+---------+------------+----------+---------+---------------+
3 rows in set (0.00 sec) - 检查心跳状态。
mysql> show @@heartbeat; +--------+-------+--------------+------+---------+-------+--------+---------+--------------+---------------------+-------+ | NAME | TYPE | HOST | PORT | RS_CODE | RETRY | STATUS | TIMEOUT | EXECUTE_TIME | LAST_ACTIVE_TIME | STOP | +--------+-------+--------------+------+---------+-------+--------+---------+--------------+---------------------+-------+ | hostS3 | mysql | 10.0.102.204 | 3306 | 1 | 0 | idle | 0 | 2,1,1 | 2019-01-18 22:51:16 | false | | hostS2 | mysql | 10.0.102.221 | 3306 | 1 | 0 | idle | 0 | 1,1,1 | 2019-01-18 22:51:16 | false | | hostS1 | mysql | 10.0.102.222 | 3306 | 1 | 0 | idle | 0 | 1,1,1 | 2019-01-18 22:51:16 | false | +--------+-------+--------------+------+---------+-------+--------+---------+--------------+---------------------+-------+ 3 rows in set (0.01 sec) RS_CODE状态如下： OK_STATUS=1代表正常状态。 ERROR_STATUS =-1 代表连接错误 TIMEOUT_STATUS=-2代表连接超时 INIT_STATUS=0代表初始化状态 若节点发生故障，则会连续进行默认的5个周期检测，心跳连接失败后就会变成-1，节点故障确认，然后可能发生切换。
- 显示mycat的版本号
mysql> show @@version; +-----------------------------------------+ | VERSION | +-----------------------------------------+ | 5.6.29-mycat-1.6-RELEASE-20161028204710 | +-----------------------------------------+ 1 row in set (0.01 sec) mysql>
- 该命令用于获取mycat当前连接状态，即应用于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 | +------------+------+--------------+------+------------+------+--------+---------+--------+---------+---------------+-------------+------------+---------+------------+ | Processor2 | 20 | 127.0.0.1 | 9066 | 53951 | root | NULL | utf8:33 | 305 | 3169 | 897 | 4096 | 0 | | | | Processor3 | 21 | 10.0.102.204 | 8066 | 31165 | root | NULL | utf8:33 | 99 | 198 | 3 | 4096 | 0 | 3 | true | +------------+------+--------------+------+------------+------+--------+---------+--------+---------+---------------+-------------+------------+---------+------------+ 2 rows in set (0.00 sec) mysql>
- 用于强制关闭连接，id值可由show @@connection命令查看。
mysql> kill @@connection 21; Query OK, 0 rows affected (0.01 sec)
- 查看后端连接状态：
mysql> show @@backend; +------------+------+---------+--------------+------+--------+--------+---------+-------+--------+----------+------------+--------+----------+---------+------------+ | processor | id | mysqlId | host | port | l_port | net_in | net_out | life | closed | borrowed | SEND_QUEUE | schema | charset | txlevel | autocommit | +------------+------+---------+--------------+------+--------+--------+---------+-------+--------+----------+------------+--------+----------+---------+------------+ | Processor0 | 12 | 121 | 10.0.102.221 | 3306 | 23186 | 45568 | 10848 | 59005 | false | false | 0 | mytest | utf8:33 | 3 | true | | Processor0 | 20 | 126 | 10.0.102.221 | 3306 | 23195 | 45668 | 10797 | 59005 | false | false | 0 | mytest | utf8:33 | 3 | true | | Processor0 | 28 | 126 | 10.0.102.222 | 3306 | 58192 | 45673 | 10907 | 59005 | false | false | 0 | mytest | utf8:33 | 3 | true | | Processor0 | 8 | 86 | 10.0.102.204 | 3306 | 19028 | 43028 | 10814 | 59005 | false | false | 0 | mytest | utf8:33 | 3 | true | | Processor0 | 16 | 128 | 10.0.102.221 | 3306 | 23193 | 45731 | 10750 | 59005 | false | false | 0 | mytest | utf8:33 | 3 | true | | Processor0 | 24 | 121 | 10.0.102.222 | 3306 | 58187 | 48792 | 10856 | 59005 | false | false | 0 | mytest | utf8:33 | 3 | true | | Processor0 | 4 | 79 | 10.0.102.204 | 3306 | 19021 | 41697 | 10810 | 59005 | false | false | 0 | mytest | utf8:33 | 3 | true | | Processor1 | 13 | 123 | 10.0.102.221 | 3306 | 23189 | 45908 | 10756 | 59005 | false | false | 0 | mytest | utf8:33 | 3 | true | | Processor1 | 5 | 82 | 10.0.102.204 | 3306 | 19024 | 42684 | 10797 | 59005 | false | false | 0 | mytest | utf8:33 | 3 | true | | Processor1 | 25 | 128 | 10.0.102.222 | 3306 | 58194 | 45750 | 10743 | 59005 | false | false | 0 | mytest | latin1:5 | 3 | true | | Processor1 | 17 | 125 | 10.0.102.221 | 3306 | 23191 | 46793 | 10757 | 59005 | false | false | 0 | mytest | utf8:33 | 3 | true | | Processor1 | 9 | 87 | 10.0.102.204 | 3306 | 19029 | 42282 | 10808 | 59005 | false | false | 0 | mytest | utf8:33 | 3 | true |
- 用于查看mycat缓存。
mysql> show @@cache; +-------------------------------------+-------+------+--------+------+------+-------------+----------+ | CACHE | MAX | CUR | ACCESS | HIT | PUT | LAST_ACCESS | LAST_PUT | +-------------------------------------+-------+------+--------+------+------+-------------+----------+ | SQLRouteCache | 10000 | 0 | 0 | 0 | 0 | 0 | 0 | | TableID2DataNodeCache.TESTDB_ORDERS | 50000 | 0 | 0 | 0 | 0 | 0 | 0 | | ER_SQL2PARENTID | 1000 | 0 | 0 | 0 | 0 | 0 | 0 | +-------------------------------------+-------+------+--------+------+------+-------------+----------+ 3 rows in set (0.01 sec) SQLRouteCache: SQL语句路由缓存 TableID2DateNodeCache：缓存表主键与分片对应关系。 ER_SQL2PARENTID：缓存ER分片中子表与父表对应关系。
- 查看数据源的状态，如果配置了主从或者多主，则可以切换。
mysql> show @@datasource; +----------+--------+-------+--------------+------+------+--------+------+------+---------+-----------+------------+ | DATANODE | NAME | TYPE | HOST | PORT | W/R | ACTIVE | IDLE | SIZE | EXECUTE | READ_LOAD | WRITE_LOAD | +----------+--------+-------+--------------+------+------+--------+------+------+---------+-----------+------------+ | dn2 | hostS2 | mysql | 10.0.102.221 | 3306 | W | 0 | 10 | 1000 | 5956 | 11 | 17 | | dn3 | hostS3 | mysql | 10.0.102.204 | 3306 | W | 0 | 10 | 1000 | 5959 | 16 | 15 | | dn1 | hostS1 | mysql | 10.0.102.222 | 3306 | W | 0 | 10 | 1000 | 5962 | 12 | 22 | +----------+--------+-------+--------------+------+------+--------+------+------+---------+-----------+------------+ 3 rows in set (0.01 sec)
这个命令在后面读写分离查看状态的时候会用到。
-
switch @@datasource name:index 用于切换数据源
name: schema中配置的dataHost中的name index: schema中配置的dataHost的writeHost index坐标，安装从上到下的配置顺序，从0开始。 切换数据源时会将原数据所有的连接池中的连接关闭，并且从新数据源创建新的连接，此时mycat服务不可用。 注意：reload @@config和switch @@datasource name:index命令在执行过程中mycat服务不可用，应谨慎处理，防止正在提交的事务出错。
- show @@syslog limit： 用于显示系统日志
mysql> show @@syslog limit=3; +---------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | DATE | LOG | +---------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | 2019-01-18 23:26:58 | .687 INFO [$_NIOREACTOR-1-RW] (io.mycat.net.handler.FrontendAuthenticator.success(FrontendAuthenticator.java:194)) - ServerConnection [id=23, schema=null, host=10.0.102.204, user=root,txIsolation=3, autocommit=true, schema=null]'root' login success | | 2019-01-18 23:26:50 | .929 INFO [$_NIOREACTOR-0-RW] (io.mycat.net.handler.FrontendAuthenticator.success(FrontendAuthenticator.java:194)) - [thread=$_NIOREACTOR-0-RW,class=ManagerConnection,id=22,host=127.0.0.1,port=9066,schema=null]'root' login success | | 2019-01-18 23:26:33 | .159 INFO [Timer0] (io.mycat.net.AbstractConnection.close(AbstractConnection.java:508)) - close connection,reason: idle ,[thread=Timer0,class=ManagerConnection,id=20,host=127.0.0.1,port=9066,schema=null] | +---------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 3 rows in set (0.02 sec) 端口号：该命令工作在9066端口，用来在客户端命令窗口中显示系统日志信息，通常用于远程查看mycat server的日志信息。 参数：limit=后接正整数，该数值用来限定每次显示的日志条数的最大数值。
- SQL统计命令
show @@sql：显示在mycat中执行过的sql语句。
show @@sql.slow：显示慢SQL语句。
show @@sql.sum: 显示sql语句的整体执行情况，读写比例等。
- 显示mycat数据库列表，显示列表对应schema.xml配置文件中的schema子节点。
