TiDB 应急运维脚本，更加方便的管理TiDB集群

TiDB 应急运维脚本，更加方便的管理TiDB集群

使用方法

使用方法：[tidb@localhost ~]$ which tiup
~/.tiup/bin/tiup编辑脚本，MYSQL_PASSWD 和 PORT 根据实际替换 
[tidb@localhost ~]$ vi ~/.tiup/bin/ti#version 1.1
#@author guanguanglei
##########################################################
#date 2023/06/14 version 1.0#!/bin/shMYSQL_USER=root
MYSQL_PASSWD=root 
PORT=4000          
TIDB_HOST=127.0.0.1
TIDB_CONNSTR="mysql -u$MYSQL_USER -p$MYSQL_PASSWD -h$TIDB_HOST -P$PORT"
CLUSTER_NAME=gsc-quotation
case $1 in si)${TIDB_CONNSTR};;info)${TIDB_CONNSTR} -e "select * from information_schema.CLUSTER_INFO;";;user)${TIDB_CONNSTR} -e "select user,host from mysql.user;";;tidb)tiup cluster display  $CLUSTER_NAME;;edit)tiup cluster edit-config  $CLUSTER_NAME;;var)if [ "$2"x != x ]; then${TIDB_CONNSTR} -e "show variables like '%$2%';"else echo "please input second paramter with variable name"fi;;config)if [ "$2"x != x ]; then${TIDB_CONNSTR} -e "show config where name  like '%$2%';"elseecho "please input config "fi;;list)${TIDB_CONNSTR} -e "select * from information_schema.cluster_processlist;";;listt)${TIDB_CONNSTR} -e "select * from information_schema.cluster_processlist order by time desc ;";;active)${TIDB_CONNSTR} -e "select * from information_schema.cluster_processlist where info is not null order by time desc \G;";;kill)${TIDB_CONNSTR} -e "select concat(concat('mysql -u$MYSQL_USER -p$MYSQL_PASSWD -h ',substring_index(a.INSTANCE,':',1), ' -P',substring_index(a.INSTANCE,':',-1) ,' -e '  ),concat('\"kill tidb ',b.id,';\"')) from information_schema.CLUSTER_info a join information_schema.cluster_processlist b on a.STATUS_ADDRESS=b.instance;";;kstat)if [ "$2"x != x ]; then${TIDB_CONNSTR} -e "select concat(concat('mysql -u$MYSQL_USER -p$MYSQL_PASSWD -h ',substring_index(a.INSTANCE,':',1), ' -P',substring_index(a.INSTANCE,':',-1) ,' -e '  ),concat('\"kill tidb ',b.id,';\"')) from information_schema.CLUSTER_info a join information_schema.cluster_processlist b on a.STATUS_ADDRESS=b.instance where state='$2';"else echo "please input second paramter with session state"fi;;ksql)if [ "$2"x != x ]; then${TIDB_CONNSTR} -e "select concat(concat('mysql -u$MYSQL_USER -p$MYSQL_PASSWD -h ',substring_index(a.INSTANCE,':',1), ' -P',substring_index(a.INSTANCE,':',-1) ,' -e '  ),concat('\"kill tidb ',b.id,';\"')) from information_schema.CLUSTER_info a join information_schema.cluster_processlist b on a.STATUS_ADDRESS=b.instance where digest='$2';"elseecho "please input second paramter with sql digest"fi;;kid)if [ "$2"x != x ]; then${TIDB_CONNSTR} -e "select concat(concat('mysql -u$MYSQL_USER -p$MYSQL_PASSWD -h ',substring_index(a.INSTANCE,':',1), ' -P',substring_index(a.INSTANCE,':',-1) ,' -e '  ),concat('\"kill tidb ',b.id,';\"')) from information_schema.CLUSTER_info a join information_schema.cluster_processlist b on a.STATUS_ADDRESS=b.instance  where id='$2';"elseecho "please input second paramter with connect id"fi;;klike)if [ "$2"x != x ]; then${TIDB_CONNSTR} -e "select concat(concat('mysql -u$MYSQL_USER -p$MYSQL_PASSWD -h ',substring_index(a.INSTANCE,':',1), ' -P',substring_index(a.INSTANCE,':',-1) ,' -e '  ),concat('\"kill tidb ',b.id,';\"')) from information_schema.CLUSTER_info a join information_schema.cluster_processlist b on a.STATUS_ADDRESS=b.instance  where info like '%$2%';"elseecho "please input info like "fi;;plan)if [ "$2"x != x ]; then${TIDB_CONNSTR} -e  "select SUMMARY_BEGIN_TIME,SUMMARY_END_TIME,STMT_TYPE,SCHEMA_NAME,DIGEST,DIGEST_TEXT,TABLE_NAMES,INDEX_NAMES,QUERY_SAMPLE_TEXT,PLAN_DIGEST,PLAN from information_schema.statements_summary where digest='$2'\G;"elseecho "please input second paramter with sql digest"fi;;size)if [ "$2"x != x ] && [ "$3"x != x ]; then${TIDB_CONNSTR} -e  "SELECT TABLE_NAME,(DATA_LENGTH+INDEX_LENGTH)/1024/1024 mb,TABLE_ROWS FROM information_schema.TABLES WHERE TABLE_SCHEMA='$2' AND TABLE_NAME='$3';"elseecho "please input second paramter with table owner and table_name"fi;;index)if [ "$2"x != x ] && [ "$3"x != x ]; then${TIDB_CONNSTR} -e  "show indexes from $2.$3;"elseecho "please input second paramter table_owner table_name"fi;;  stats_meta)if [ "$2"x != x ] && [ "$3"x != x ]; then${TIDB_CONNSTR} -e  "show stats_meta where db_name='$2' table_name='$3';"elseecho "please input second paramter table_owner table_name"fi;; stats_histograms)if [ "$2"x != x ] && [ "$3"x != x ]; then${TIDB_CONNSTR} -e  "show stats_histograms where  db_name='$2' and db_name='$3';"elseecho "please input second paramter table_owner table_name"fi;;dump)if [ "$2"x != x ] && [ "$3"x != x ]; then${TIDB_CONNSTR} -e  "select concat(' tiup dumpling -uroot -p   -h --filetype csv -t 8 -o /tmp/backup -f   ', '''', table_schema,'.',table_name,'''' ' --sql ''select * from ',table_name,'''') from tables where  table_schema='$2' and table_name='$3';"elseecho "please input second paramter table_owner table_name"fi;;ctl)${TIDB_CONNSTR} -e  "select concat('tiup ctl:',(select substring(version(),13,19)),'  pd  -u  http://',  (select INSTANCE from information_schema.cluster_info where TYPE='pd' limit 1),'  -i') from dual;
";;*)echoecho "Usage: mib keyword [value1] ";echo echo "  -----------------------------------------------------------------";echo "  si                          -- login tidb database";echo "  info                        -- show cluster info";echo "  tidb                        -- tiup cluster display";echo "  edit                        -- show cluster edit";echo "  user                        -- list all user";echo "  var                         -- show variables like ";echo "  config                      -- show config where name like ";echo "  list                        -- show  processlist";echo "  listt                       -- processlist order by time";  echo "  active                       -- processlist order by time limit 10";echo "  kill                        -- kill all session ";echo "  kstat                       -- kill session state";echo "  ksql                        -- kill sql session;";    echo "  kid                         -- kill session id";  echo "  klike                       -- kill info like "; echo "  plan                        -- list sql plan";  echo "  size                        -- get table size ";echo "  index                       -- get  on table index";echo "  stats_meta                  -- getstats_meta on table";echo "  stats_histograms            -- get cloumn statistics on table";echo "  ctl                         -- print tiup ctl command";    
;;
esac

修改权限

[tidb@localhost ~]$ chmod 775 ~/.tiup/bin/ti

使用示例

1. 查看集群状态
ti  tidb 2. 登录数据库
[tidb@localhost ~]$ ti si
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 820992
Server version: 5.7.25-TiDB-v4.0.13-1-g7eeeda700 TiDB Server (Apache License 2.0) Community Edition, MySQL 5.7 compatibleCopyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.MySQL [(none)]> 3.查看活跃会话
[tidb@localhost ~]$ ti active 
*************************** 1. row ***************************
INSTANCE: xxxxxxxx :10080ID: 820997USER: rootHOST: 127.0.0.1DB: NULLCOMMAND: QueryTIME: 0STATE: 2INFO: select * from information_schema.cluster_processlist where info is not null order by time descDIGEST: 6140c4093c70debc9c729807414f36c32fad049daecf2d5655afd10a2374fae9MEM: 6
TxnStart: 08-10 15:22:29.866(443456461174472707)4. 登录pd 
[root@vm10-2-103-65 ~]# ti ctl
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| concat('tiup ctl:',(select substring(version(),13,19)),'  pd  -u  http://',  (select INSTANCE from information_schema.cluster_info where TYPE='pd' limit 1),'  -i') |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tiup ctl:v7.1.0  pd  -u  http://10.2.103.65:2379  -i                                                                                                                |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+
[root@vm10-2-103-65 ~]# tiup ctl:v7.1.0  pd  -u  http://10.2.103.65:2379  -i 
Starting component `ctl`: /root/.tiup/components/ctl/v7.1.0/ctl pd -u http://10.2.103.65:2379 -i
»  5. 查看参数
[root@vm10-2-103-65 ~]# ti var oom
+--------------------------------+--------+
| Variable_name                  | Value  |
+--------------------------------+--------+
| tidb_enable_tmp_storage_on_oom | ON     |
| tidb_mem_oom_action            | CANCEL |
+--------------------------------+--------+查看config参数 
root@vm10-2-103-65 ~]# ti config block-cache
+------+--------------------+-------------------------------------------+----------+
| Type | Instance           | Name                                      | Value    |
+------+--------------------+-------------------------------------------+----------+
| tikv | 10.2.103.199:20160 | raftdb.defaultcf.block-cache-size         | 317MiB   |
| tikv | 10.2.103.199:20160 | raftdb.defaultcf.disable-block-cache      | false    |
| tikv | 10.2.103.199:20160 | raftdb.defaultcf.prepopulate-block-cache  | disabled |
| tikv | 10.2.103.199:20160 | rocksdb.defaultcf.block-cache-size        | 3971MiB  |
| tikv | 10.2.103.199:20160 | rocksdb.defaultcf.disable-block-cache     | false    |
| tikv | 10.2.103.199:20160 | rocksdb.defaultcf.prepopulate-block-cache | disabled |6.查看执行计划
[root@vm10-2-103-65 ~]# ti plan 76069b6abc74fc855272108f656d4d1132948a55a3b12f66eaa4373648380d81
*************************** 1. row ***************************
SUMMARY_BEGIN_TIME: 2023-08-10 15:30:00SUMMARY_END_TIME: 2023-08-10 16:00:00STMT_TYPE: SelectSCHEMA_NAME: NULLDIGEST: 76069b6abc74fc855272108f656d4d1132948a55a3b12f66eaa4373648380d81DIGEST_TEXT: select * from `information_schema` . `cluster_processlist` where `info` is not ? order by time descTABLE_NAMES: information_schema.cluster_processlistINDEX_NAMES: NULLQUERY_SAMPLE_TEXT: select * from information_schema.cluster_processlist where info is not null order by time descPLAN_DIGEST: deabf06ff5a37585b637b6bc4ddf0bb2affc0aa92148953d10f678359c7cda69PLAN:     id                      task            estRows operator info                                                   actRows execution info                                                                                                                                                                      memory          diskSort_5                  root            9990    information_schema.cluster_processlist.time:desc                1       time:41.9ms, loops:2                                                                                                                                                                7.70 KB         0 Bytes└─TableReader_10        root            9990    data:Selection_9                                                1       time:41.9ms, loops:2, cop_task: {num: 2, max: 41.8ms, min: 41.5ms, avg: 41.6ms, p95: 41.8ms, rpc_num: 2, rpc_time: 83.2ms, copr_cache_hit_ratio: 0.00, max_distsql_concurrency: 2}  727 Bytes       N/A└─Selection_9         cop[tidb]       9990    not(isnull(information_schema.cluster_processlist.info))        0                                                                                                                                                                                           N/A             N/A└─TableFullScan_8   cop[tidb]       10000   table:CLUSTER_PROCESSLIST, keep order:false, stats:pseudo       0                                                                                                                                                                                           N/A             N/A7. kill 会话 
[root@vm10-2-103-65 ~]# ti kid 716063544658887111
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| concat(concat('mysql -uroot -pM4Ab_d03%pQ27^1Xa# -h ',substring_index(a.INSTANCE,':',1), ' -P',substring_index(a.INSTANCE,':',-1) ,' -e '  ),concat('"kill tidb ',b.id,';"')) |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| mysql -uroot -pM4Ab_d03%pQ27^1Xa# -h 10.2.103.65 -P4000 -e "kill tidb 716063544658887111;"                                                                                    |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
[root@vm10-2-103-65 ~]#  mysql -uroot -pM4Ab_d03%pQ27^1Xa# -h 10.2.103.65 -P4000 -e "kill tidb 716063544658887111;"  8.常用命令
[root@vm10-2-103-65 ~]# ti Usage: mib keyword [value1] -----------------------------------------------------------------si                          -- login tidb databaseinfo                        -- show cluster infouser                        -- list all uservar                         -- show variables like config                      -- show config where name like list                        -- show  processlistlistt                       -- processlist order by timeactive                       -- processlist order by time limit 10kill                        -- kill all session kstat                       -- kill session stateksql                        -- kill sql session;kid                         -- kill session idklike                       -- kill info like plan                        -- list sql plansize                        -- get table size index                       -- get  on table indexstats_meta                  -- getstats_meta on tablestats_histograms            -- get cloumn statistics on tablectl                         -- print tiup ctl command