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/sh

MYSQL_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%';"
  else
    echo "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';"
  else
    echo "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';"
  else
    echo "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%';"
  else
    echo "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;"
  else
    echo "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';"
  else
    echo "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;"
  else
    echo "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';"
  else
    echo "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';"
  else
    echo "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';"
  else
    echo "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;
"
  ;;
  *)
   echo
   echo "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 compatible

Copyright (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 :10080
      ID: 820997
    USER: root
    HOST: 127.0.0.1
      DB: NULL
 COMMAND: Query
    TIME: 0
   STATE: 2
    INFO: select * from information_schema.cluster_processlist where info is not null order by time desc
  DIGEST: 6140c4093c70debc9c729807414f36c32fad049daecf2d5655afd10a2374fae9
     MEM: 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:00
  SUMMARY_END_TIME: 2023-08-10 16:00:00
         STMT_TYPE: Select
       SCHEMA_NAME: NULL
            DIGEST: 76069b6abc74fc855272108f656d4d1132948a55a3b12f66eaa4373648380d81
       DIGEST_TEXT: select * from `information_schema` . `cluster_processlist` where `info` is not ? order by time desc
       TABLE_NAMES: information_schema.cluster_processlist
       INDEX_NAMES: NULL
 QUERY_SAMPLE_TEXT: select * from information_schema.cluster_processlist where info is not null order by time desc
       PLAN_DIGEST: deabf06ff5a37585b637b6bc4ddf0bb2affc0aa92148953d10f678359c7cda69
              PLAN:     id                      task            estRows operator info                                                   actRows execution info                                                                                                                                                                      memory          disk
        Sort_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/A


7. 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 database
  info                        -- show cluster info
  user                        -- list all user
  var                         -- show variables like 
  config                      -- show config where name like 
  list                        -- show  processlist
  listt                       -- processlist order by time
  active                       -- processlist order by time limit 10
  kill                        -- kill all session 
  kstat                       -- kill session state
  ksql                        -- kill sql session;
  kid                         -- kill session id
  klike                       -- kill info like 
  plan                        -- list sql plan
  size                        -- get table size 
  index                       -- get  on table index
  stats_meta                  -- getstats_meta on table
  stats_histograms            -- get cloumn statistics on table
  ctl                         -- print tiup ctl command