强大到没朋友的mysql-shell及插件
原创
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
其它一些用的不是很多，可以自行探索。
补充：
相关博客地址：
https://lefred.be/content/mysql-shell-plugins-innodb/
https://lefred.be/content/mysql-lock-information-in-mysql-shell/
https://mysql.wisborg.dk/2019/04/27/mysql-shell-8-0-16-user-defined-reports/
原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。
如有侵权，请联系 cloudcommunity@tencent.com 删除。
原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。
如有侵权，请联系 cloudcommunity@tencent.com 删除。
评论
登录后参与评论
推荐阅读
相关产品与服务
云数据库 SQL Server
腾讯云数据库 SQL Server （TencentDB for SQL Server）是业界最常用的商用数据库之一，对基于 Windows 架构的应用程序具有完美的支持。TencentDB for SQL Server 拥有微软正版授权，可持续为用户提供最新的功能，避免未授权使用软件的风险。具有即开即用、稳定可靠、安全运行、弹性扩缩等特点。