相關網站說明
部署mysql1.6 :https://www.toutiao.com/item/7237731828113818146/
網官：http://www.mycat.org.cn/
1、連接配接mycat 9066管理端口
指令：mysql -uroot -proot -P9066 -h127.0.0.1
-u:使用者名 -p:密碼 -P:端口
-h:ip位址例：linux路徑切換到mycat安裝路徑的bin目錄中 [root@zwf_192_168_5_40 ~]# mysql -uroot -p'123456789' -P9066 -h192.168.5.2
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 13419
Server version: 5.6.29-mycat-1.6.7.6-release-20211210225857 MyCat Server (monitor)
Copyright (c) 2009-2021 Percona LLC and/or its affiliates
Copyright (c) 2000, 2021, Oracle and/or its affiliates.
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.
[email protected] 22:44: [(none)]> 2、顯示後端實體庫連接配接資訊，包括目前連接配接數，端口等資訊。
[email protected] 13:46: [(none)]>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 | 131 | 3019285 | 192.168.5.44 | 3306 | 58604 | 12419 | 2846 | 11323 | false | false | 0 | aaaa | utf8:45 | 3 | true | false |
| Processor0 | 259 | 359186 | 192.168.5.46 | 3306 | 50638 | 179 | 95 | 1123 | false | false | 0 | bbbbbb | utf8:45 | 3 | true | false |
| Processor0 | 68 | 3042925 | 192.168.5.39 | 3306 | 49682 | 12259 | 2807 | 12823 | false | false | 0 | cccc | utf8:45 | 3 | true | false |
| Processor0 | 135 | 3019434 | 192.168.5.45 | 3306 | 6508 | 12099 | 2774 | 11323 | false | false | 0 | ddddd | utf8:45 | 3 | true | false |
| Processor0 | 202 | 352689 | 192.168.5.46 | 3306 | 29084 | 1139 | 311 | 7423 | false | false | 0 | eeeeeee | utf8:45 | 3 | true | false |
| Processor0 | 266 | 359810 | 192.168.5.46 | 3306 | 58622 | 2179 | 542 | 523 | false | false | 0 | fffffff | utf8:45 | 3 | true | false |
| Processor0 | 139 | 2832780 | 192.168.5.40 | 3306 | 38622 | 9801 | 2337 | 11323 | false | false | 0 | aaaaaddd | utf8:45 | 3 | true | false | 3、顯示目前前端用戶端連接配接情況.
[email protected] 13:47: [(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 |
+------------+-------+--------------+------+------------+----------+--------+---------+--------+---------+---------------+-------------+------------+---------+------------+
| Processor3 | 13419 | 192.168.5.30 | 9066 | 9540 | root | NULL | utf8:33 | 695 | 28467 | 234 | 4096 | 0 | | |
+------------+-------+--------------+------+------------+----------+--------+---------+--------+---------+---------------+-------------+------------+---------+------------+
1 row in set (0.00 sec) 4、目前線程池的執行情況，是否有積壓(active_count)以及task_queue_size，後者為積壓的待處理的SQL，若積壓數目一直保值，則說明後端實體連接配接可能不夠或者SQL執行比較緩慢
[email protected] 13:47: [(none)]>show @@threadpool;
+------------------+-----------+--------------+-----------------+----------------+------------+
| NAME | POOL_SIZE | ACTIVE_COUNT | TASK_QUEUE_SIZE | COMPLETED_TASK | TOTAL_TASK |
+------------------+-----------+--------------+-----------------+----------------+------------+
| Timer | 2 | 0 | 0 | 42862 | 42862 |
| BusinessExecutor | 8 | 0 | 0 | 385 | 385 |
+------------------+-----------+--------------+-----------------+----------------+------------+
2 rows in set (0.00 sec) 5、目前後端實體庫的心跳檢測情況,RS_CODE為1表示心跳正常
[email protected] 13:49: [(none)]>show @@heartbeat;
+--------+-------+--------------+------+---------+-------+--------+---------+--------------+---------------------+-------+
| NAME | TYPE | HOST | PORT | RS_CODE | RETRY | STATUS | TIMEOUT | EXECUTE_TIME | LAST_ACTIVE_TIME | STOP |
+--------+-------+--------------+------+---------+-------+--------+---------+--------------+---------------------+-------+
| hostM1 | mysql | 192.168.5.143 | 3306 | 1 | 0 | idle | 30000 | 1,1,1 | 2022-08-15 13:49:29 | false |
| hostM2 | mysql | 192.168.5.144 | 3306 | 1 | 0 | idle | 30000 | 1,1,1 | 2022-08-15 13:49:29 | false |
| hostS1 | mysql | 192.168.5.144 | 3306 | 1 | 0 | idle | 30000 | 1,1,1 | 2022-08-15 13:49:29 | false |
| hostS2 | mysql | 192.168.5.145 | 3306 | 1 | 0 | idle | 30000 | 0,0,0 | 2022-08-15 13:49:29 | false |
+--------+-------+--------------+------+---------+-------+--------+---------+--------------+---------------------+-------+
16 rows in set (0.00 sec) 6、顯示資料節點的通路情況，包括每個資料節點目前活動連接配接數(active),空閑連接配接數(idle)以及最大連接配接數(maxCon) size，EXECUTE參數表示從該節點擷取連接配接的次數，次數越多，說明通路該節點越多
[email protected] 13:49: [(none)]>show @@datanode;
+------+------------------------------+-------+-------+--------+------+------+---------+------------+----------+---------+---------------+
| NAME | DATHOST | INDEX | TYPE | ACTIVE | IDLE | SIZE | EXECUTE | TOTAL_TIME | MAX_TIME | MAX_SQL | RECOVERY_TIME |
+------+------------------------------+-------+-------+--------+------+------+---------+------------+----------+---------+---------------+
| dn1 | localhost1/aaaa | 0 | mysql | 0 | 3 | 1000 | 1333 | 0 | 0 | 0 | -1 |
| dn10 | localhost4/bbb | 0 | mysql | 0 | 0 | 1000 | 1 | 0 | 0 | 0 | -1 |
| dn11 | localhost4/cccc | 0 | mysql | 0 | 0 | 1000 | 2 | 0 | 0 | 0 | -1 |
+------+------------------------------+-------+-------+--------+------+------+---------+------------+----------+---------+---------------+
13 rows in set (0.01 sec) 6、顯示目前processors的處理情況，包括每個processor的IO吞吐量(NET_IN/NET_OUT)、IO隊列的積壓情況(R_QUEY/W_QUEUE)，Socket Buffer Pool的使用情況 BU_PERCENT為已使用的百分比、BU_WARNS為Socket Buffer Pool不夠時，臨時建立的新的BUFFER的次數，若百分比經常超過90%并且BU_WARNS>0，則表明BUFFER不夠，需要增大，參見性能調優手冊。
[email protected] 13:50: [(none)]>show @@processor;
+------------+---------+---------+-------------+---------+---------+--------------+--------------+------------+----------+----------+----------+
| NAME | NET_IN | NET_OUT | REACT_COUNT | R_QUEUE | W_QUEUE | FREE_BUFFER | TOTAL_BUFFER | BU_PERCENT | BU_WARNS | FC_COUNT | BC_COUNT |
+------------+---------+---------+-------------+---------+---------+--------------+--------------+------------+----------+----------+----------+
| Processor0 | 575442 | 496447 | 0 | 0 | 0 | 687194767351 | 687194767360 | 0 | 0 | 0 | 36 |
| Processor1 | 1716315 | 2177984 | 0 | 0 | 0 | 687194767351 | 687194767360 | 0 | 0 | 0 | 26 |
| Processor2 | 934892 | 985739 | 0 | 0 | 0 | 687194767351 | 687194767360 | 0 | 0 | 0 | 32 |
| Processor3 | 704152 | 589371 | 0 | 0 | 0 | 687194767351 | 687194767360 | 0 | 0 | 1 | 29 |
+------------+---------+---------+-------------+---------+---------+--------------+--------------+------------+----------+----------+----------+
4 rows in set (0.01 sec) 7、顯示緩存的使用情況，對于性能監控和調優很有價值
[email protected] 13:51: [(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 | 82 | 0 | 0 | 1660534419173 | 0 |
| TableID2DataNodeCache.TESTDB_ORDERS | 50000 | 0 | 0 | 0 | 0 | 0 | 0 |
+-------------------------------------+-------+------+--------+------+------+---------------+----------+
3 rows in set (0.01 sec) MAX為緩存的最大值(記錄個數)，CUR為目前已經在緩存中的數量，ACESS為緩存讀次數，HIT為緩存命中次數，PUT 為寫緩存次數，LAST_XX為最後操作時間戳，比較重要的幾個參數：CUR：若CUR接近MAX，而PUT大于MAX很多，則表明MAX需要增大，HIT/ACCESS為緩存命中率，這個值越高越好。
8、殺掉用戶端的連接配接，參數為連接配接的ID值，通過show @@connection，可以展示目前連接配接到MyCAT的所有用戶端程序，若某個程序異常，則可以通過該指令殺掉連接配接，如 KILL @@CONNECTION 26907;
[email protected] 13:54: [(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 |
+------------+-------+--------------+------+------------+----------+--------+---------+--------+---------+---------------+-------------+------------+---------+------------+
| Processor3 | 13419 | 192.168.5.40 | 9066 | 9540 | root | NULL | utf8:33 | 966 | 34215 | 655 | 4096 | 0 | | |
+------------+-------+--------------+------+------------+----------+--------+---------+--------+---------+---------------+-------------+------------+---------+------------+
1 row in set (0.00 sec) 9、重新加載配置檔案schema.xml
如果我們在原有的schema.xml中進行了修改，需要重新加載該檔案使其生效。
[email protected] 13:54: [(none)]>reload @@config;
Query OK, 1 row affected (0.06 sec)
Reload config success ![LINUX檔案管理類指令-基礎[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)