强大到没朋友的mysql

您所在的位置：网站首页 › mysqlshell怎么用 › 强大到没朋友的mysql

强大到没朋友的mysql

2023-10-15 01:06| 来源: 网络整理| 查看: 265

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 [email protected] 登录进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 >

# 安全相关

# 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

强大到没朋友的mysql

今日新闻

推荐新闻