[转帖]强大到没朋友的mysql-shell及插件

强大,朋友,mysql,shell,插件 · 浏览次数 : 0

小编点评

归纳总结以上内容,生成内容时需要带简单的排版。 **排版方法** * **格式**:使用 `any_value()` 获取数据长度和索引长度 * **拼接**:使用 `concat()` 方法将数据长度和索引长度拼接起来 * **字符串**:使用 `lpad()` 和 `rpad()` 方法对数据进行字符串补全 * **字符串**:使用 `format_pico_time()` 和 `format_pico_time()` 方法对数据进行时间格式化 **示例** * **数据格式**:`any_value(data_length) + any_value(index_length)` * **拼接**:`concat(format_pico_time(data_length), any_value(index_length)), " ")` * **字符串**:`lpad("abc", 10, " ", "right")` * **字符串**:`format_pico_time(data_length, any_value(index_length))` * **字符串**:`format_pico_time(data_length, any_value(index_length))` **其他** * 可以使用 `any_value()` 获取数据长度和索引长度 * 可以使用 `format_pico_time()` 和 `format_pico_time()` 方法对数据进行时间格式化 * 可以使用 `lpad()` 和 `rpad()` 方法对数据进行字符串补全 * 可以使用 `format_pico_time()` 和 `format_pico_time()` 方法对数据进行时间格式化

正文

强大到没朋友的mysql-shell及插件

https://cloud.tencent.com/developer/article/1676131?areaSource=&traceId=

 

mysql-shell 是官方出品的运维相关小工具, 目前已经具备很多功能。如果再配合大佬写的插件,简直强大到没朋友。

这里我简单搬运一些常用功能过来。如果想看全面的欢迎订阅 https://lefred.be/ 这个大佬的博客。

 

大佬写的mysql-shell 插件的地址 https://github.com/lefred/mysqlshell-plugins

 

安装方法:

$ mkdir -p ~/.mysqlsh/plugins
$ git clone https://github.com/lefred/mysqlshell-plugins.git ~/.mysqlsh/plugins/ext

注意: 我自己测试,这个插件能支持到percona版本8.0.20,在社区版8.0.21上实验失败。

 

然后,建议再安装下mysql-shell-udr这个插件:

cd /root/
git clone https://github.com/lefred/mysql-shell-udr.git 
mkdir  /root/.mysqlsh/init.d
mv /root/mysql-shell-udr/* /root/.mysqlsh/init.d

 

完成上面的2个插件的安装后,我们可以简单试验下效果:

$ mysqlsh dts@192.168.2.4 登录进mysqlsh命令行界面

 

# 查看我们上面安装的mysql-shell-udr

 MySQL  192.168.2.4:33060+ ssl  JS > \show 
Available reports: alter_progress, gr_info, gr_recovery_progress, locks_info, query, sessions, thread, threads.


 MySQL  192.168.2.4:33060+ ssl  JS > \show thread
GENERAL
Thread ID:                475282
Connection ID:            198651
Thread type:              FOREGROUND
Program name:             mysqlsh
User:                     dts
Host:                     192.168.2.4
Database:                 NULL
Command:                  Query
Time:                     00:00:00
State:                    executing
Transaction state:        NULL
Prepared statements:      0
Bytes received:           ?
Bytes sent:               ?
Info:                     SELECT json_object('tid',t.THR ... JOIN information_schema.innodb
Previous statement:       NULL


 MySQL  192.168.2.4:33060+ ssl  JS > \show threads
+--------+--------+------+-------------+------+---------+----------+-----------+---------+-------------------------------------------------------------------+-----------+
| tid    | cid    | user | host        | db   | command | time     | state     | txstate | info                                                              | nblocking |
+--------+--------+------+-------------+------+---------+----------+-----------+---------+-------------------------------------------------------------------+-----------+
| 475282 | 198651 | dts  | 192.168.2.4 | NULL | Query   | 00:00:00 | executing | NULL    | SELECT json_object('cid',t.PRO ... READ_ID = io.thread_id WHERE t | 0         |
+--------+--------+------+-------------+------+---------+----------+-----------+---------+-------------------------------------------------------------------+-----------+

 
  MySQL  192.168.2.4:33060+ ssl  JS > \show sessions
+--------+---------+----------------+--------+-------------------------------------------------------------------+-----------+------------+
| thd_id | conn_id | user           | db     | statement                                                         | latency   | memory     |
+--------+---------+----------------+--------+-------------------------------------------------------------------+-----------+------------+
| 475133 | 198155  | root@localhost | sbtest | alter table sbtest4 add column cad int                            | 27.93 s   | 128.24 KiB |
| 475282 | 198651  | mysqlx/worker  | NULL   | SELECT `thd_id`,`conn_id`,`use ... ER BY `statement_latency` DESC | 123.19 ms | 1.24 MiB   |
+--------+---------+----------------+--------+-------------------------------------------------------------------+-----------+------------+
 MySQL  192.168.2.4:33060+ ssl  JS > 


 MySQL  192.168.2.4:33060+ ssl  JS > \show sessions -E
*************************** 1. row ***************************
   thd_id: 475282
  conn_id: 198651
     user: mysqlx/worker
       db: NULL
statement: SELECT `thd_id`,`conn_id`,`use ... ER BY `statement_latency` DESC
  latency: 122.85 ms
   memory: 1.87 MiB


 MySQL  192.168.2.4:33060+ ssl  JS > \show locks_info
+-----------+-----------+----------------+-----------+-----------+-------------+-----------+
| trx_id    | thread_id | table          | lock_type | lock_mode | lock_status | lock_data |
+-----------+-----------+----------------+-----------+-----------+-------------+-----------+
| 777992136 | 475989    | sbtest.sbtest4 | TABLE     | IX        | GRANTED     | NULL      |
+-----------+-----------+----------------+-----------+-----------+-------------+-----------+
 


 MySQL  192.168.2.4:33060+ ssl  JS > \show alter_progress 
+-----------+----------------------------------------+-------------------------------------------+----------------+------------+
| THREAD_ID | SQL_TEXT                               | State                                     | WORK_CartedAgo | Memory     |
+-----------+----------------------------------------+-------------------------------------------+----------------+------------+
| 475133    | alter table sbtest4 add column cad int | stage/sql/Waiting for table metadata lock | NULL  6.98 min | 128.24 KiB |
+-----------+----------------------------------------+-------------------------------------------+----------------+------------+


 MySQL  192.168.2.4:33060+ ssl  JS > \show query select Host,User from mysql.user where user='abc'
+------+------+
| Host | User |
+------+------+
| %    | abc  |
+------+------+

 

 

# check相关命令

 MySQL  192.168.2.4:33060+ ssl  JS > \help
 MySQL  192.168.2.4:33060+ ssl  JS > ext.check.  # 用tab键能列出可用的命令
getAmountDDL()                getLocks()                    getSlowerQuery()
getBinlogs()                  getNonInnoDBTables()          getTrxWithMostRowsAffected()
getBinlogsIO()                getQueryMostRowAffected()     getTrxWithMostStatements()
getCascadingFK()              getQueryTempDisk()            help()
getFullTableScanQuery()       getQueryUpdatingSamePK()      showTrxSize()
getInnoDBTablesWithNoPK()     getRunningStatements()        showTrxSizeSort()

 MySQL  192.168.2.4:33060+ ssl  JS > ext.check.getBinlogs()   # 列出当前节点有哪些binlog
Binary log file(s) present:
mysql-bin.000001
mysql-bin.000002
mysql-bin.000003
mysql-bin.000004
mysql-bin.000005

 MySQL  192.168.2.4:33060+ ssl  JS > ext.check.get   # 用tab键能列出可用的命令
getAmountDDL()                getInnoDBTablesWithNoPK()     getQueryUpdatingSamePK()
getBinlogs()                  getLocks()                    getRunningStatements()
getBinlogsIO()                getNonInnoDBTables()          getSlowerQuery()
getCascadingFK()              getQueryMostRowAffected()     getTrxWithMostRowsAffected()
getFullTableScanQuery()       getQueryTempDisk()            getTrxWithMostStatements()

 MySQL  192.168.2.4:33060+ ssl  JS > ext.check.getInnoDBTablesWithNoPK()  # 找出没有主键的表
+--------------+-------------------+--------+------------+------------+
| TABLE_SCHEMA | TABLE_NAME        | ENGINE | TABLE_ROWS | SIZE       |
+--------------+-------------------+--------+------------+------------+
| bx_cmdb      | resourcesinfo_log | InnoDB |       1506 | 352.00 KiB |
| chaoge_user  | ABC               | InnoDB |          0 | 16.00 KiB  |
| chaoge_user  | ABC2              | InnoDB |          0 | 16.00 KiB  |
| sakila       | tb22              | InnoDB |          2 | 16.00 KiB  |
| test         | tttt              | InnoDB |          0 | 16.00 KiB  |
+--------------+-------------------+--------+------------+------------+

#下面是一个mdl锁等待的案例 (198506 连接没有提交,导致 198155  的ddl会话一直被阻塞中)
 MySQL  192.168.2.4:33060+ ssl  JS > ext.check.getLocks()
+-----------------+--------------+----------------+-------------------+-------------------+-------------------------------------------------------------------+
| mysql_thread_id | trx_duration | row_locks_held | row_locks_pending | tables_with_locks | current_statement                                                 |
+-----------------+--------------+----------------+-------------------+-------------------+-------------------------------------------------------------------+
|          198506 | 5.69 s       |              1 |                 0 | sbtest.sbtest4    | delete from sbtest4 where k='111' limit 20                        |
|          198155 | 2.09 s       |              0 |                 0 | NULL              | alter table sbtest4 add column cad int                            |
|          198026 | 717.79 us    |              0 |                 0 | NULL              | SELECT thr.processlist_id AS m ... IT DESC               LIMIT 10 |
|               6 | 679.17 us    |              0 |                 0 | NULL              | NULL                                                              |
|          197533 | 134.05 us    |              0 |                 0 | NULL              | select * from alert                                               |
|          195132 | 133.39 us    |              0 |                 0 | NULL              | select * from alert                                               |
+-----------------+--------------+----------------+-------------------+-------------------+-------------------------------------------------------------------+
For which thread_id do you want to see locks ? (198506) 
Metadata Locks:
---------------
GRANTED SHARED_WRITE on sbtest.sbtest4

Data Locks:
-----------
GRANTED TABLE (IX) LOCK on sbtest.sbtest4 (None)
 MySQL  192.168.2.4:33060+ ssl  JS > ext.check.getLocks()
+-----------------+--------------+----------------+-------------------+-------------------+-------------------------------------------------------------------+
| mysql_thread_id | trx_duration | row_locks_held | row_locks_pending | tables_with_locks | current_statement                                                 |
+-----------------+--------------+----------------+-------------------+-------------------+-------------------------------------------------------------------+
|          198506 | 13.86 s      |              1 |                 0 | sbtest.sbtest4    | delete from sbtest4 where k='111' limit 20                        |
|          198155 | 10.26 s      |              0 |                 0 | NULL              | alter table sbtest4 add column cad int                            |
|          198026 | 718.28 us    |              0 |                 0 | NULL              | SELECT thr.processlist_id AS m ... IT DESC               LIMIT 10 |
|               6 | 679.17 us    |              0 |                 0 | NULL              | NULL                                                              |
|          197533 | 134.05 us    |              0 |                 0 | NULL              | select * from alert                                               |
|          195132 | 133.39 us    |              0 |                 0 | NULL              | select * from alert                                               |
+-----------------+--------------+----------------+-------------------+-------------------+-------------------------------------------------------------------+
For which thread_id do you want to see locks ? (198506) 198155
Metadata Locks:
---------------
GRANTED EXCLUSIVE on sbtest.#sql-356fdf_3060b
GRANTED SHARED_UPGRADABLE on sbtest.sbtest4
PENDING EXCLUSIVE on sbtest.sbtest4

Data Locks:
-----------
None

 

# innodb相关命令

 MySQL  192.168.2.4:33060+ ssl  JS > ext.innodb.
getAlterProgress()         getFragmentedTables()      getTablesInBP()
getAutoincFill()           getFragmentedTablesDisk()  help()


 MySQL  192.168.2.4:33060+ ssl  JS > ext.innodb.getFragmentedTablesDisk()
+------------------------------------------+---------+-------------+------------+-------------+------------+------------+-------------+--------+
| NAME                                     | ROWS    | DATA_SIZE   | INDEX_SIZE | TOTAL_SIZE  | DATA_FREE  | FILE_SIZE  | WASTED_SIZE | FREE   |
+------------------------------------------+---------+-------------+------------+-------------+------------+------------+-------------+--------+
| sbtest/sbtest1                           | 4931568 | 1.05 GiB    | 74.59 MiB  | 1.12 GiB    | 390.00 MiB | 1.52 GiB   | 413.41 MiB  | 26.50% |
| sbtest/sbtest6                           | 4935097 | 1017.00 MiB |    0 bytes | 1017.00 MiB | 7.00 MiB   | 1.14 GiB   | 155.00 MiB  | 13.23% |
| employees/employees                      |  298034 | 17.78 MiB   |    0 bytes | 17.78 MiB   |    0 bytes | 21.00 MiB  | 3.22 MiB    | 15.33% |
| grafana/migration_log                    |     208 | 96.00 KiB   |    0 bytes | 96.00 KiB   |    0 bytes | 192.00 KiB | 96.00 KiB   | 50.00% |
| sakila/staff                             |       2 | 64.00 KiB   | 32.00 KiB  | 96.00 KiB   |    0 bytes | 160.00 KiB | 64.00 KiB   | 40.00% |
| sakila/film_category                     |    1000 | 64.00 KiB   | 16.00 KiB  | 80.00 KiB   |    0 bytes | 160.00 KiB | 80.00 KiB   | 50.00% |
| sakila/city                              |     600 | 48.00 KiB   | 16.00 KiB  | 64.00 KiB   |    0 bytes | 128.00 KiB | 64.00 KiB   | 50.00% |
+------------------------------------------+---------+-------------+------------+-------------+------------+------------+-------------+--------+
Don't forget to run 'ANALYZE TABLE ...' for a more accurate result.

 MySQL  192.168.2.4:33060+ ssl  JS > ext.innodb.getFragmentedTables()
+---------------------------+--------+-------+-------+-------+------------+---------+-----------+---------------+
| TABLE                     | ENGINE | ROWS  | DATA  | IDX   | TOTAL SIZE | IDXFRAC | data_free | data_free_pct |
+---------------------------+--------+-------+-------+-------+------------+---------+-----------+---------------+
| grafana.dashboard_version | InnoDB | 0.00M | 0.00G | 0.00G | 0.00G      |    0.02 | 8.00MB    | (100%)        |
| grafana.dashboard         | InnoDB | 0.00M | 0.00G | 0.00G | 0.00G      |    0.09 | 5.00MB    | (100%)        |
| sbtest.sbtest1            | InnoDB | 4.93M | 1.05G | 0.07G | 1.12G      |    0.07 | 390.00MB  | (34.01%)      |
+---------------------------+--------+-------+-------+-------+------------+---------+-----------+---------------+
Don't forget to run 'ANALYZE TABLE ...' for a more accurate result.


 MySQL  192.168.2.4:33060+ ssl  JS > ext.innodb.getAlterProgress()
+-----------+----------------------------------------+-------------------------------------------+----------------+----------------+--------------+------------+-----------+
| THREAD_ID | SQL_TEXT                               | State                                     | WORK_COMPLETED | WORK_ESTIMATED | CompletedPct | StartedAgo | Memory    |
+-----------+----------------------------------------+-------------------------------------------+----------------+----------------+--------------+------------+-----------+
|    475133 | alter table sbtest4 add column cad int | stage/sql/Waiting for table metadata lock |           NULL |           NULL | NULL         |     2.11 s | 58.22 KiB |
+-----------+----------------------------------------+-------------------------------------------+----------------+----------------+--------------+------------+-----------+


 MySQL  192.168.2.4:33060+ ssl  JS > ext.innodb.getTablesInBP()   # 这个命令比较慢,生产环境尽量不要使用。
 它实际上在db上执行的sql如下:
 SELECT t1.TABLE_NAME 'Table Name', COUNT(*) AS Pages,   
                   format_bytes(SUM(IF(COMPRESSED_SIZE = 0, 16384, COMPRESSED_SIZE))) 
                      AS 'Total Data in BP', 
                   format_bytes(any_value(data_length)+any_value(index_length)) 
                      'Total Table Size',
                   lpad(concat(round(SUM(IF(COMPRESSED_SIZE = 0, 16384, COMPRESSED_SIZE))
                     /(any_value(data_length)+any_value(index_length)) * 100,2),'%'),"6"," ") 
                       as 'in BP' 
                   FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE t1 
                   JOIN INFORMATION_SCHEMA.TABLES t2 
                     ON concat('`',t2.TABLE_SCHEMA,'`.`',t2.TABLE_NAME,'`') = t1.TABLE_NAME 
                   WHERE t2.TABLE_SCHEMA  NOT IN ('mysql', 'sys')  
                   GROUP BY t1.TABLE_NAME 
                   ORDER BY SUM(IF(COMPRESSED_SIZE = 0, 16384, COMPRESSED_SIZE)) desc, 
                                (any_value(data_length)+any_value(index_length)) desc;
                                
  
#查看自增id使用情况                                                              
 MySQL  192.168.2.4:33060+ ssl  JS > ext.innodb.getAutoincFill()  实际上执行的sql是如下这个:
SELECT table_schema, table_name, column_name, auto_increment,
		  pow(2, case data_type
		    when 'tinyint'   then 7
		    when 'smallint'  then 15
		    when 'mediumint' then 23
		    when 'int'       then 31
		    when 'bigint'    then 63
		    end+(column_type like '% unsigned'))-1 as max_int
		  FROM information_schema.tables t
		  JOIN information_schema.columns c USING (table_schema,table_name)
		  WHERE c.extra = 'auto_increment' AND t.auto_increment IS NOT NULL;
	

 

# 创建账号

 MySQL  192.168.2.4:33060+ ssl  JS > ext.user.create()
Enter the new user's account: abc
Enter the password (leave is blank to generate one): ******
Does the user need to change his password ? (Y,n) n
Do you want to lock the account after 3 failed attempts ? (Y,n) n
 MySQL  192.168.2.4:33060+ ssl  JS > 

 

# 安全相关

 MySQL  192.168.2.4:33060+ ssl  JS > ext.security.showAuthMethods()
Default authentication method is mysql_native_password
+-----------------------+-------+
| method                | users |
+-----------------------+-------+
| mysql_native_password |    20 |
| caching_sha2_password |     2 |
+-----------------------+-------+

 MySQL  192.168.2.4:33060+ ssl  JS > ext.security.showPasswordExpire()
Default password doesn't expire
On expired password disconnect
+-----------------------+-----------------------+---------------+
| user                  | password_last_changed | expires_in    |
+-----------------------+-----------------------+---------------+
| `abc`@`%`             | 2020-08-09 12:56:05   | do not expire |
| `dba`@`%`             | 2018-11-13 19:15:14   | do not expire |
| `grafana`@`%`         | 2018-11-07 16:19:54   | do not expire |
| `root`@`localhost`    | 2018-11-07 16:19:21   | do not expire |
+-----------------------+-----------------------+---------------+


 MySQL  192.168.2.4:33060+ ssl  JS > ext.security.showPasswordExpireSoon()
 MySQL  192.168.2.4:33060+ ssl  JS > 

 

# watch命令

 MySQL  192.168.2.4:33060+ ssl  JS > \watch  query select Host,User from mysql.user where user='abc'

# 每5秒钟运行一次
\watch query --interval=5 SELECT conn_id, sys.format_statement(current_statement) AS statement, format_pico_time(statement_latency) AS latency FROM sys.x$session ORDER BY statement_latency DESC LIMIT 10

 

其它一些用的不是很多,可以自行探索。

与[转帖]强大到没朋友的mysql-shell及插件相似的内容:

[转帖]强大到没朋友的mysql-shell及插件

强大到没朋友的mysql-shell及插件 https://cloud.tencent.com/developer/article/1676131?areaSource=&traceId= mysql-shell 是官方出品的运维相关小工具, 目前已经具备很多功能。如果再配合大佬写的插件,简直强大到

[转帖]聊聊Chat GPT-1到GPT-4的发展历程

http://blog.itpub.net/69925873/viewspace-2935360/ OpenAI的Generative Pre-trained Transformer(GPT)模型通过引入非常强大的语言模型,在自然语言处理(NLP)领域引起了巨大震动。这些模型可以执行各种NLP任务,

[转帖] 原来awk真是神器啊

https://www.cnblogs.com/codelogs/p/16060082.html 简介# 刚开始入门awk时,觉得awk很简单,像是一个玩具,根本无法应用到工作之中,但随着对awk的了解不断加深,就会越发觉得这玩意的强大,大佬们称其为上古神器,绝不是空穴来风。这也可以说明,一些热门的

[转帖]tidb之旅——tidb架构选择

https://zhuanlan.zhihu.com/p/641650168 前言 从4月份开始利用tidb改造了我们公司bi系统。这个过程中,我感觉到了tidb的强大。也打算记录一下整个改造过程。我打算从4个方面来记录这个改造过程。tidb架构选择,dm工具的使用——这两个部分还是tidb6.5.

[转帖]警惕Oracle数据库性能“隐形杀手”——Direct Path Read

一、 简介 Oracle 的11g版本正式发布到今天已经10年有余,最新版本也已经到了20c,但是Direct Path Read(直接路径读)导致性能问题的案例仍时有发生,很多12c的用户还是经常遇到这个问题,所以有必要把这个事情再跟大家讲一遍,通过2个典型案例加深理解。 早在2012年,盖国强大

[转帖]19.awk报告生成器,文本解释器

在本博客中,AWK是一个系列文章,本人会尽量以通俗易懂的方式递进的总结awk命令的相关知识点。 awk系列博文直达链接:AWK命令总结之从放弃到入门 我们先来用专业的术语描述一下awk是什么,如果你看不懂,没关系,我们会再用”大白话”解释一遍。 awk是一个报告生成器,它拥有强大的文本格式化的能力,

[转帖]至强SP简析、FPGA及Fabric版本揭秘

https://zhuanlan.zhihu.com/p/387076957 作者注:在英特尔推出至强可扩展处理器(Skylake)及平台当天到一周之内,我先后发表了图说和架构解析的文章,又在2017年8月9日以此文对新至强产品线构成进行比较详细的介绍。 某冰冰曾经说过:我能承受多大的诋毁,就能承受

[转帖]龙芯总强调单核同频性能,是因为Pentium4要到25+GHz才能比得上11代酷睿单核

https://zhuanlan.zhihu.com/p/522073827 闲话不说,先上图,是SPEC CPU 2006 int(单任务)测试的成绩: 上图中的成绩,大部分是本人自己测试的结果。SPEC CPU 2006 的测试可费劲了,Pentium 4运行一遍完整测试(int base/pe

[转帖]龙芯总强调同频性能,是因奔腾4要到25+GHz才比得上酷睿11代单核

https://baijiahao.baidu.com/s?id=1734320620568707041 闲话不说,先上图,是SPEC CPU 2006 int(单任务)测试的成绩: 上图中的成绩,大部分是本人自己测试的结果。SPEC CPU 2006 的测试可费劲了,Pentium 4运行一遍完整

[转帖]图解第四代英特尔至强可扩展处理器家族

https://aijishu.com/a/1060000000379987 今天,代号Sapphire Rapids(SPR)的第四代英特尔至强(Intel Xeon)可扩展处理器,终于来了。 从三代Lake变到Rapids,有继承,有发展。这里的继承是指2D Mesh(网格)架构,发展是指Chi