YYQ运维技术博客_运维的工作学习之路mycat的管理
mysql -ukk -pcentos -h127.0.0.1 -P9066
查看mycat的管理命令
ySQL [(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 |
所有节点的情况
MySQL [(none)]> show @@datasource;
+----------+---------------+-------+---------------+------+------+--------+------+------+---------+-----------+------------+
| DATANODE | NAME | TYPE | HOST | PORT | W/R | ACTIVE | IDLE | SIZE | EXECUTE | READ_LOAD | WRITE_LOAD |
+----------+---------------+-------+---------------+------+------+--------+------+------+---------+-----------+------------+
| dn1 | 192.168.2.220 | mysql | 192.168.2.220 | 3306 | W | 0 | 2 | 1000 | 2482 | 2 | 11 |
| dn1 | 192.168.2.221 | mysql | 192.168.2.220 | 3306 | W | 0 | 8 | 1000 | 3877 | 9 | 0 |
| dn1 | 192.168.2.221 | mysql | 192.168.2.221 | 3306 | R | 0 | 9 | 1000 | 3891 | 14 | 0 |
| dn3 | 192.168.2.224 | mysql | 192.168.2.224 | 3306 | W | 0 | 8 | 1000 | 3882 | 0 | 9 |
| dn3 | 192.168.2.225 | mysql | 192.168.2.220 | 3306 | W | 0 | 0 | 1000 | 0 | 0 | 0 |
| dn3 | 192.168.2.225 | mysql | 192.168.2.225 | 3306 | R | 0 | 9 | 1000 | 3884 | 7 | 0 |
| dn2 | 192.168.2.222 | mysql | 192.168.2.222 | 3306 | W | 0 | 8 | 1000 | 3882 | 0 | 9 |
| dn2 | 192.168.2.223 | mysql | 192.168.2.220 | 3306 | W | 0 | 0 | 1000 | 0 | 0 | 0 |
| dn2 | 192.168.2.223 | mysql | 192.168.2.223 | 3306 | R | 0 | 9 | 1000 | 3881 | 4 | 0 |
| dn4 | 192.168.2.226 | mysql | 192.168.2.226 | 3306 | W | 0 | 8 | 1000 | 3883 | 0 | 9 |
| dn4 | 192.168.2.227 | mysql | 192.168.2.220 | 3306 | W | 0 | 0 | 1000 | 0 | 0 | 0 |
| dn4 | 192.168.2.227 | mysql | 192.168.2.227 | 3306 | R | 0 | 9 | 1000 | 3884 | 6 | 0 |
+----------+---------------+-------+---------------+------+------+--------+------+------+---------+-----------+------------+
12 rows in set (0.01 sec)
服务器状态
MySQL [(none)]> show @@server ;
+--------------+-------------+--------------+------------+---------------+---------------+---------+--------+
| UPTIME | USED_MEMORY | TOTAL_MEMORY | MAX_MEMORY | RELOAD_TIME | ROLLBACK_TIME | CHARSET | STATUS |
+--------------+-------------+--------------+------------+---------------+---------------+---------+--------+
| 10h 47m 36s | 101930904 | 1037959168 | 4151836672 | 1563721987071 | -1 | utf8 | ON |
+--------------+-------------+--------------+------------+---------------+---------------+---------+--------+
1 row in set (0.01 sec)
查看逻辑库的状态
MySQL [(none)]> show @@databases;
+----------+
| DATABASE |
+----------+
| oss |
+----------+
1 row in set (0.01 sec)
查看连接状态
MySQL [(none)]> show @@backend ;
+------------+-------+---------+---------------+------+--------+--------+---------+-------+--------+----------+------------+----------+---------+---------+------------+
| processor | id | mysqlId | host | port | l_port | net_in | net_out | life | closed | borrowed | SEND_QUEUE | schema | charset | txlevel | autocommit |
+------------+-------+---------+---------------+------+--------+--------+---------+-------+--------+----------+------------+----------+---------+---------+------------+
| Processor0 | 20738 | 57 | 192.168.2.220 | 3306 | 23394 | 24489 | 5847 | 24918 | false | false | 0 | hellodb1 | utf8:45 | 3 | true |
| Processor0 | 21769 | 13 | 192.168.2.224 | 3306 | 27422 | 23121 | 5523 | 24238 | false | false | 0 | hellodb3 | utf8:45 | 3 | true |
| Processor0 | 21770 | 13 | 192.168.2.226 | 3306 | 48450 | 23121 | 5523 | 24238 | false | false | 0 | hellodb4 | utf8:45 | 3 | true |
| Processor0 | 21771 | 13 | 192.168.2.222 | 3306 | 43542 | 23121 | 5523 | 24238 | false | false | 0 | hellodb2 | utf8:45 | 3 | true |
| Processor0 | 21772 | 1080 | 192.168.2.220 | 3306 | 25470 | 23121 | 5523 | 24238 | false | false | 0 | hellodb1 | utf8:45 | 3 | true |
| Processor0 | 41 | 25 | 192.168.2.227 | 3306 | 46085 | 35507 | 8420 | 38938 | false | false | 0 | hellodb4 | utf8:33 | 3 | true |
| Processor0 | 42 | 26 | 192.168.2.227 | 3306 | 46091 | 36186 | 8420 | 38938 | false | false | 0 | hellodb4 | utf8:33 | 3 | true |
| Processor0 | 43 | 45 | 192.168.2.223 | 3306 | 6693 | 35205 | 8385 | 38938 | false | false | 0 | hellodb2 | utf8:45 | 3 | true |
| Processor0 | 44 | 27 | 192.168.2.225 | 3306 | 48415 | 35205 | 8385 | 38938 | false | false | 0 | hellodb3 | utf8:45 | 3 | true |
| Processor0 | 45 | 46 | 192.168.2.223 | 3306 | 6695 | 35864 | 8404 | 38938 | false | false | 0 | hellodb2 | utf8:33 | 3 | true |
| Processor0 | 46 | 26 | 192.168.2.225 | 3306 | 48413 | 35869 | 8404 | 38938 | false | false | 0 | hellodb3 | utf8:33 | 3 | true |
| Processor0 | 47 | 545 | 192.168.2.221 | 3306 | 14827 | 36069 | 8418 | 38938 | false | false | 0 | hellodb1 | utf8:33 | 3 | true |
| Processor0 | 48 | 27 | 192.168.2.227 | 3306 | 46099 | 35871 | 8404 | 38938 | false | false | 0 | hellodb4 | utf8:33 | 3 | true |
| Processor0 | 49 | 546 | 192.168.2.221 | 3306 | 14831 | 35324 | 8431 | 38938 | false | false | 0 | hellodb1 | utf8:33 | 3 | true |
| Processor0 | 50 | 47 | 192.168.2.223 | 3306 | 6705 | 35129 | 8367 | 38938 | false | false | 0 | hellodb2 | utf8:45 | 3 | true |
| Processor0 | 51 | 547 | 192.168.2.221 | 3306 | 14833 | 35281 | 8403 | 38938 | false | false | 0 | hellodb1 | utf8:45 | 3 | true |
| Processor0 | 52 | 28 | 192.168.2.225 | 3306 | 48433 | 35791 | 8386 | 38938 | false | false | 0 | hellodb3 | utf8:33 | 3 | true |
| Processor0 | 21311 | 12 | 192.168.2.226 | 3306 | 47544 | 23501 | 5613 | 24538 | false | false | 0 | hellodb4 | utf8:45 | 3 | true |
| Processor0 | 21312 | 628 | 192.168.2.220 | 3306 | 24554 | 23501 | 5613 | 24538 | false | false | 0 | hellodb1 | utf8:45 | 3 | true |
| Processor0 | 21313 | 629 | 192.168.2.220 | 3306 | 24556 | 23501 | 5613 | 24538 | false | false | 0 | hellodb1 | utf8:45 | 3 | true |
| Processor0 | 21314 | 12 | 192.168.2.222 | 3306 | 42626 | 23501 | 5613 | 24538 | false | false | 0 | hellodb2 | utf8:45 | 3 | true |
| Processor0 | 21315 | 11 | 192.168.2.222 | 3306 | 42624 | 23501 | 5613 | 24538 | false | false | 0 | hellodb2 | utf8:45 | 3 | true |
| Processor0 | 21316 | 11 | 192.168.2.226 | 3306 | 47534 | 23501 | 5613 | 24538 | false | false | 0 | hellodb4 | utf8:45 | 3 | true |
+------------+-------+---------+---------------+------+--------+--------+---------+-------+--------+----------+------------+----------+---------+---------+------------+
服务器管理内容查看
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 |
+------------+------+-----------+------+------------+------+--------+---------+--------+---------+---------------+-------------+------------+---------+------------+
| Processor0 | 8 | 127.0.0.1 | 9066 | 28463 | kk | NULL | utf8:33 | 293 | 13316 | 719 | 4096 | 0 | | |
+------------+------+-----------+------+------------+------+--------+---------+--------+---------+---------------+-------------+------------+---------+------------+
1 row in set (0.00 sec
查看连接状态
MySQL [(none)]> show processlist;
+----+------+---------------------+----------+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+---------------------+----------+---------+------+-------+------------------+
| 45 | kk | 192.168.2.210:6693 | hellodb2 | Sleep | 47 | | NULL |
| 46 | kk | 192.168.2.210:6695 | hellodb2 | Sleep | 17 | | NULL |
| 47 | kk | 192.168.2.210:6705 | hellodb2 | Sleep | 57 | | NULL |
| 48 | kk | 192.168.2.210:7615 | hellodb2 | Sleep | 27 | | NULL |
| 49 | kk | 192.168.2.210:7617 | hellodb2 | Sleep | 67 | | NULL |
| 50 | kk | 192.168.2.210:8533 | hellodb2 | Query | 0 | init | show processlist |
| 51 | kk | 192.168.2.210:9441 | hellodb2 | Sleep | 77 | | NULL |
| 52 | kk | 192.168.2.210:10349 | hellodb2 | Sleep | 37 | | NULL |
| 53 | kk | 192.168.2.210:48083 | hellodb2 | Sleep | 7 | | NULL |
+----+------+---------------------+----------+---------+------+-------+------------------+
9 rows in set (0.01 sec)
MYSQL的管理
MySQL [(none)]> show @@datanode;
+------+---------------------+-------+-------+--------+------+------+---------+------------+----------+---------+---------------+
| NAME | DATHOST | INDEX | TYPE | ACTIVE | IDLE | SIZE | EXECUTE | TOTAL_TIME | MAX_TIME | MAX_SQL | RECOVERY_TIME |
+------+---------------------+-------+-------+--------+------+------+---------+------------+----------+---------+---------------+
| dn1 | localhost1/hellodb1 | 1 | mysql | 0 | 0 | 1000 | 0 | 0 | 0 | 0 | -1 |
| dn2 | localhost2/hellodb2 | 0 | mysql | 0 | 10 | 1000 | 43 | 0 | 0 | 0 | -1 |
| dn3 | localhost3/hellodb3 | 0 | mysql | 0 | 10 | 1000 | 43 | 0 | 0 | 0 | -1 |
| dn4 | localhost4/hellodb4 | 0 | mysql | 0 | 10 | 1000 | 43 | 0 | 0 | 0 | -1 |
+------+---------------------+-------+-------+--------+------+------+---------+------------+----------+---------+---------------+
4 rows in set (0.00 sec)
*************************** 4. row ***************************
NAME: dn4 节点名称
DATHOST: localhost4/hellodb4 节点对应的活动服务器的名称
INDEX: 0
TYPE: mysql
ACTIVE: 0 活动的连接数
IDLE: 10 闲置的连接数
SIZE: 1000 总连接数数量
EXECUTE: 42
TOTAL_TIME: 0
MAX_TIME: 0
MAX_SQL: 0
RECOVERY_TIME: -1
4 rows in set (0.00 s
查看逻辑库的列表
MySQL [(none)]> show @@datanode where schema=oss;
+------+---------------------+-------+-------+--------+------+------+---------+------------+----------+---------+---------------+
| NAME | DATHOST | INDEX | TYPE | ACTIVE | IDLE | SIZE | EXECUTE | TOTAL_TIME | MAX_TIME | MAX_SQL | RECOVERY_TIME |
+------+---------------------+-------+-------+--------+------+------+---------+------------+----------+---------+---------------+
| dn1 | localhost1/hellodb1 | 1 | mysql | 0 | 0 | 1000 | 0 | 0 | 0 | 0 | -1 |
| dn2 | localhost2/hellodb2 | 0 | mysql | 0 | 10 | 1000 | 58 | 0 | 0 | 0 | -1 |
| dn3 | localhost3/hellodb3 | 0 | mysql | 0 | 10 | 1000 | 58 | 0 | 0 | 0 | -1 |
| dn4 | localhost4/hellodb4 | 0 | mysql | 0 | 10 | 1000 | 58 | 0 | 0 | 0 | -1 |
+------+---------------------+-------+-------+--------+------+------+---------+------------+----------+---------+---------------+
4 rows in set (0.00 sec)
查看心跳检测的记录
MySQL [(none)]> show @@heartbeat;
+---------------+-------+---------------+------+---------+-------+--------+---------+--------------+---------------------+-------+
| NAME | TYPE | HOST | PORT | RS_CODE | RETRY | STATUS | TIMEOUT | EXECUTE_TIME | LAST_ACTIVE_TIME | STOP |
+---------------+-------+---------------+------+---------+-------+--------+---------+--------------+---------------------+-------+
| 192.168.2.226 | mysql | 192.168.2.226 | 3306 | 1 | 0 | idle | 0 | 5,4,4 | 2019-07-22 14:32:03 | false |
| 192.168.2.227 | mysql | 192.168.2.220 | 3306 | -1 | 0 | idle | 0 | 1,2,2 | 2019-07-22 14:32:03 | false |
| 192.168.2.227 | mysql | 192.168.2.227 | 3306 | 1 | 0 | idle | 0 | 4,4,4 | 2019-07-22 14:32:03 | false |
| 192.168.2.224 | mysql | 192.168.2.224 | 3306 | 1 | 0 | idle | 0 | 4,3,3 | 2019-07-22 14:32:03 | false |
| 192.168.2.225 | mysql | 192.168.2.220 | 3306 | -1 | 0 | idle | 0 | 1,821,821 | 2019-07-22 14:32:03 | false |
| 192.168.2.225 | mysql | 192.168.2.225 | 3306 | 1 | 0 | idle | 0 | 4,4,4 | 2019-07-22 14:32:03 | false |
| 192.168.2.222 | mysql | 192.168.2.222 | 3306 | 1 | 0 | idle | 0 | 4,3,3 | 2019-07-22 14:32:03 | false |
| 192.168.2.223 | mysql | 192.168.2.220 | 3306 | -1 | 0 | idle | 0 | 1,2,2 | 2019-07-22 14:32:03 | false |
| 192.168.2.223 | mysql | 192.168.2.223 | 3306 | 1 | 0 | idle | 0 | 3,3,3 | 2019-07-22 14:32:03 | false |
| 192.168.2.220 | mysql | 192.168.2.220 | 3306 | -1 | 0 | idle | 0 | 1,492,492 | 2019-07-22 14:32:03 | false |
| 192.168.2.221 | mysql | 192.168.2.220 | 3306 | -1 | 0 | idle | 0 | 1,2,2 | 2019-07-22 14:32:03 | false |
| 192.168.2.221 | mysql | 192.168.2.221 | 3306 | 1 | 0 | idle | 0 | 3,3,3 | 2019-07-22 14:32:03 | false |
+---------------+-------+---------------+------+---------+-------+--------+---------+--------------+---------------------+-------+
12 rows in set (0.01 sec)
RS_CODE如果是1的话mysql节点是可以链接的，如果是-1的话就是数据库节点链接超时，要解决问题了
查看所有节点的主机情况
MySQL [(none)]> show @@datasource;
+----------+---------------+-------+---------------+------+------+--------+------+------+---------+-----------+------------+
| DATANODE | NAME | TYPE | HOST | PORT | W/R | ACTIVE | IDLE | SIZE | EXECUTE | READ_LOAD | WRITE_LOAD |
+----------+---------------+-------+---------------+------+------+--------+------+------+---------+-----------+------------+
| dn1 | 192.168.2.220 | mysql | 192.168.2.220 | 3306 | W | 0 | 1 | 1000 | 10 | 0 | 0 |
| dn1 | 192.168.2.221 | mysql | 192.168.2.220 | 3306 | W | 0 | 4 | 1000 | 13 | 0 | 0 |
| dn1 | 192.168.2.221 | mysql | 192.168.2.221 | 3306 | R | 0 | 8 | 1000 | 91 | 0 | 0 |
| dn3 | 192.168.2.224 | mysql | 192.168.2.224 | 3306 | W | 0 | 10 | 1000 | 95 | 0 | 0 |
| dn3 | 192.168.2.225 | mysql | 192.168.2.220 | 3306 | W | 0 | 0 | 1000 | 0 | 0 | 0 |
| dn3 | 192.168.2.225 | mysql | 192.168.2.225 | 3306 | R | 0 | 8 | 1000 | 94 | 2 | 0 |
| dn2 | 192.168.2.222 | mysql | 192.168.2.222 | 3306 | W | 0 | 10 | 1000 | 95 | 0 | 0 |
| dn2 | 192.168.2.223 | mysql | 192.168.2.220 | 3306 | W | 0 | 0 | 1000 | 0 | 0 | 0 |
| dn2 | 192.168.2.223 | mysql | 192.168.2.223 | 3306 | R | 0 | 8 | 1000 | 92 | 0 | 0 |
| dn4 | 192.168.2.226 | mysql | 192.168.2.226 | 3306 | W | 0 | 10 | 1000 | 95 | 0 | 0 |
| dn4 | 192.168.2.227 | mysql | 192.168.2.220 | 3306 | W | 0 | 0 | 1000 | 0 | 0 | 0 |
| dn4 | 192.168.2.227 | mysql | 192.168.2.227 | 3306 | R | 0 | 8 | 1000 | 93 | 1 | 0 |
+----------+---------------+-------+---------------+------+------+--------+------+------+---------+-----------+------------+
12 rows in set (0.00 sec)
5-mycat的管理
未经允许不得转载：YYQ运维技术博客_运维的工作学习之路 » 5-mycat的管理
