【TiDBer 唠嗑茶话会 48】非正式 TiDB相关 SQL 脚本征集大赛！

[buddyyuan]

我自己封装了一个查看参数和变量的脚本。

#!/bin/bash

case $1 in
-pd)
mysql -uroot -h127.0.0.1 -P4000 -p"" -e "SHOW CONFIG WHERE type ='pd' and name like '%$2%'"
;;
-tidb)
mysql -uroot -h127.0.0.1 -P4000 -p"" -e "SHOW CONFIG WHERE type ='tidb' and name like '%$2%'"
;;
-tikv)
mysql -uroot -h127.0.0.1 -P4000 -p"" -e "SHOW CONFIG WHERE type ='tikv' and name like '%$2%'"
;;
-tiflash)
mysql -uroot -h127.0.0.1 -P4000 -p"" -e "SHOW CONFIG WHERE type ='tiflash' and name like '%$2%'"
;;
-var)
mysql -uroot -h127.0.0.1 -P4000 -p"" -e "show variables like '%$2%';"
;;
-h)
echo "-pd       show pd parameters" 
echo "-tidb     show tidb parameters"
echo "-tikv     show tikv parameters"
echo "-tiflash  show tiflash parameters"
echo "-var      show itidb variables"
;;
esac

使用起来如下：

[root@xxxxx ~]# sh showparammeter.sh -tikv memory-pool-quota
mysql: [Warning] Using a password on the command line interface can be insecure.
+------+----------------------+-------------------------------+----------------------+
| Type | Instance             | Name                          | Value                |
+------+----------------------+-------------------------------+----------------------+
| tikv | 192.16.201.125:29160 | server.grpc-memory-pool-quota | 9223372036854775807B |
| tikv | 192.16.201.125:29161 | server.grpc-memory-pool-quota | 9223372036854775807B |
| tikv | 192.16.201.130:29161 | server.grpc-memory-pool-quota | 9223372036854775807B |
| tikv | 192.16.201.130:29160 | server.grpc-memory-pool-quota | 9223372036854775807B |
| tikv | 192.16.201.210:29161 | server.grpc-memory-pool-quota | 9223372036854775807B |
| tikv | 192.16.201.210:29160 | server.grpc-memory-pool-quota | 9223372036854775807B |
+------+----------------------+-------------------------------+----------------------+
[root@xxxxx ~]# sh showparammeter.sh -pd quota
mysql: [Warning] Using a password on the command line interface can be insecure.
+------+---------------------+---------------------+-------+
| Type | Instance            | Name                | Value |
+------+---------------------+---------------------+-------+
| pd   | 172.16.201.125:2379 | quota-backend-bytes | 8GiB  |
+------+---------------------+---------------------+-------+

关键是你还能用 grep 在过滤一次。

[root@vm172-16-201-125 ~]# sh showparammeter.sh -tikv memory-pool-quota | grep -i "210:29160"
tikv    192.16.201.210:29160    server.grpc-memory-pool-quota   9223372036854775807B

[langpingtan]

create user ‘test_user’ identified by ‘test_user’;
grant select,insert,update,delete on test.* to test_user;
show grants for test_user;

select * from information_schema.cluster_deadlocks;
select * from information_schema.cluster_slow_query;

[十万个不知道]

select VARIABLE_NAME, VARIABLE_VALUE from mysql.tidb where VARIABLE_NAME like “tikv_gc%”;
挺好

[画地为牢]

show stats_healthy​;

[ealam_小羽]

查找重复记录：
select *
from 表
where 重复字段 in
(
select 重复字段
from 表
group by 重复字段
having count(*)>1
)

[coderv]

SHOW INDEX FROM user;

[caiyfc]

查询耗时最高的慢sql

select query sql_text,
       sum_query_time,
       mnt as executions,
       avg_query_time,
       avg_proc_time,
       avg_wait_time,
       max_query_time,
       avg_backoff_time,
       Cop_proc_addr,
       digest,
       (case
         when avg_proc_time = 0 then
          'point_get or commit'
         when (avg_proc_time > avg_wait_time and
              avg_proc_time > avg_backoff_time) then
          'coprocessor_process'
         when (avg_backoff_time > avg_wait_time and
              avg_proc_time < avg_backoff_time) then
          'backoff'
         else
          'coprocessor_wait'
       end) as type
  from (select substr(query, 1, 100) query,
               count(*) mnt,
               avg(query_time) avg_query_time,
               avg(process_time) avg_proc_time,
               avg(wait_time) avg_wait_time,
               max(query_time) max_query_time,
               sum(query_time) sum_query_time,
               digest,
               Cop_proc_addr,
               avg(backoff_time) avg_backoff_time
          from information_schema.cluster_slow_query
         where time >= '2022-07-14 17:00:00'
           and time <= '2022-07-15 17:10:00'
           and DB = 'web'
         group by substr(query, 1, 100)) t
 order by max_query_time desc limit 20;

[gary (feng)]

SELECT table_schema, SUM(data_length)/1024/1024 AS data_length, SUM(index_length)/1024/1024 AS index_length, SUM(data_length+index_length)/1024/1024 AS sum FROM information_schema.tables WHERE table_schema = “${schema_name}” GROUP BY table_schema;

[tracy0984]

日常维护用的最多的SQL
select * from information_schema.cluster_processlist;

– kill id;

[凌云Cloud (Ti D Ber Jum2mr Em)]

作为数据库开发人员，recovery必备语句
FLASHBACK TABLE target_table_name[TO new_table_name]

-恢复数据（适用于drop与truncate）

[Tank001]

我怎么感觉基本都是写业务sql , 基本都是select …
使用一些时间函数

[TiDBer_dog]

来一个批量修改库名
./bat_rename.sh lihongbao/ dev2_kelun dev2_sinodemo 路径./leo_backup

[天蓝色的小九]

show stats_meta where db_name like ‘%sbtest%’;

[边城元元]

show VARIABLES like

[胡杨树旁]

show stats_histograms where table_name like ‘%***%’

[neolithic]

show databases;

[wuxiangdong]

show config where type=‘tikv’ and name like ‘%pool%’;

[Jiawei (渔不是鱼)]

select * from information_schema.processlist where info is not null
高并发的场景下 获取sql。

[草木小白]

FLASHBACK TABLE t1 TO t2;

[Ming]

查看schema下的表都有哪些
show tables in schema;