[转帖]强大到没朋友的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

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