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

news/2024/5/19 15:21:46 标签: tidb, 运维, adb

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

http://www.niftyadmin.cn/n/4929807.html

相关文章

Vue CLI 2.x搭建vue(目录最全分析)

Vue CLI 2.x搭建vue(目录最全分析)

一、vue-cli介绍 vue-cli是一个用于快速搭建vue项目的 脚手架。 二、vue-cli安装、更新 安装过nodeJs 、cnpm 后，全局安装vue-cli（以后其他项目可直接使用）： cnpm install -g vue-cli 查看安装成功否（有版本号就是…
阅读更多...
【JavaScript】jquery的导入方式有两种：本地导入和线上导入

【JavaScript】jquery的导入方式有两种：本地导入和线上导入

前言 jQuery是一个用来代替JavaScript来快捷书写前端脚本语言的库，jQuery可以大大的简化复杂的js代码，使开发人员专注于实现页面的效果。 导入方式有两种 jQuery的导入方式有两种，一种是本地导入，一种是利用超链接导入。 方法…
阅读更多...
go struct 的常见问题

go struct 的常见问题

go struct 的常见问题 1. 什么是struct？2. 如何声明、定义和创建一个struct？3. struct和其他数据类型（如数组、切片、map等）有什么区别？4. 如何访问struct字段？5. struct是否支持继承，是否支持重…
阅读更多...
vue2.7如何使用vue-i18n

vue2.7如何使用vue-i18n

版本： vue：2.7.0 vue-i18n：8.28.2 一、下载 npm i vue-i18n8.28.2二、新建 新建一个文件，例如：lang，项目结构如下： index.js： import Vue from vue import VueI18n from vue-i18n…
阅读更多...
如何预防ssl中间人攻击?

如何预防ssl中间人攻击?

当我们连上公共WiFi打开网页或邮箱时，殊不知此时可能有人正在监视着我们的各种网络活动。打开账户网页那一瞬间，不法分子可能已经盗取了我们的银行凭证、家庭住址、电子邮件和联系人信息，而这一切我们却毫不知情。这是一种网络上常见的“中间…
阅读更多...
css常用样式和不常用样式

css常用样式和不常用样式

文章目录 1、hover鼠标变小手2、ul去除点3、文字溢出显示省略号（1）一行文字溢出显示省略号（2）多行文字溢出显示省略号 4、文字单词超出（1）文字单词超出换行（word-wrap）（2…
阅读更多...
UNET 架构综合指南 | 掌握图像分割--附源码

UNET 架构综合指南 | 掌握图像分割--附源码

介绍 在令人兴奋的计算机视觉主题中，图像包含许多秘密和信息，区分和突出显示项目至关重要。图像分割是将图像分割成有意义的区域或对象的过程，在从医学成像到自动驾驶和对象识别等各种应用中至关重要。准确和自动的分割长期以来一直面临挑战，传统方法经常在准确性和效率方…
阅读更多...
(el-switch)操作(不使用 ts)：Element-plus 中 Switch 将默认值修改为 “true“ 与 “false“(字符串)来控制开关

(el-switch)操作(不使用 ts)：Element-plus 中 Switch 将默认值修改为 “true“ 与 “false“(字符串)来控制开关

Ⅰ、Element-plus 提供的 Switch 开关组件与想要目标情况的对比： 1、Element-plus 提供 Switch 组件情况： 其一、Element-ui 自提供的 Switch 代码情况为(示例的代码)： // Element-plus 自提供的代码： // 此时是使用了 ts 语言环…
阅读更多...
最新文章

Copyright @ 2022~2023