mysql shell简介

安装

mysql shell是一个mysql的管理工具,提供了很多方便的功能

mysql shell下载地址
https://dev.mysql.com/downloads/shell/

我选择的是windows的操作系统

并且下载的是ZIP的包,解压缩即可使用｡

解压后,windows的mysqlsh的执行文件在以下路径,直接双击调用即可,也可以把该路径写入环境变量,方便调用mysqlsh.exe

./mysql-shell-8.0.28-windows-x86-64bit/bin/mysqlsh.exe

MySQL Shell 8.0.28

Copyright (c) 2016, 2022, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates.
Other names may be trademarks of their respective owners.

Type '\help' or '\?' for help; '\quit' to exit.
WARNING: Found errors loading plugins, for more details look at the log at: C:\Users\xxx\MySQL\mysqlsh\mysqlsh.log
 MySQL  JS >
复制

简单使用mysqlsh

初次接触，多用help

mysqlsh中的帮助命令两种方式

\help'

'\?'

 MySQL  JS > \?

SHELL COMMANDS

The shell commands allow executing specific operations including updating the
shell configuration.

The following shell commands are available:

 - \                   Start multi-line input when in SQL mode.
 - \connect    (\c)    Connects the shell to a MySQL server and assigns the
                       global session.
 - \disconnect         Disconnects the global session.
 - \edit       (\e)    Launch a system editor to edit a command to be executed.
 - \exit               Exits the MySQL Shell, same as \quit.
 - \help       (\?,\h) Prints help information about a specific topic.
 - \history            View and edit command line history.
 - \js                 Switches to JavaScript processing mode.
 - \nopager            Disables the current pager.
 - \nowarnings (\w)    Don't show warnings after every statement.
 - \option             Allows working with the available shell options.
 - \pager      (\P)    Sets the current pager.
 - \py                 Switches to Python processing mode.
 - \quit       (\q)    Exits the MySQL Shell.
 - \reconnect          Reconnects the global session.
 - \rehash             Refresh the autocompletion cache.
 - \show               Executes the given report with provided options and arguments.
 - \source     (\.)    Loads and executes a script from a file.
 - \sql                Executes SQL statement or switches to SQL processing
                       mode when no statement is given.
 - \status     (\s)    Print information about the current global session.
 - \system     (\!)    Execute a system shell command.
 - \use        (\u)    Sets the active schema.
 - \warnings   (\W)    Show warnings after every statement.
 - \watch              Executes the given report with provided options and
                       arguments in a loop.

GLOBAL OBJECTS

The following modules and objects are ready for use when the shell starts:

 - dba    Used for InnoDB Cluster, ReplicaSet, and ClusterSet administration.
 - mysql  Support for connecting to MySQL servers using the classic MySQL
          protocol.
 - mysqlx Used to work with X Protocol sessions using the MySQL X DevAPI.
 - os     Gives access to functions which allow to interact with the operating
          system.
 - shell  Gives access to general purpose functions and properties.
 - sys    Gives access to system specific parameters.
 - util   Global object that groups miscellaneous tools like upgrade checker
          and JSON import.

For additional information on these global objects use: <object>.help()

复制

mysqlsh 中,有 命令,和 对象 两种

命令

例如:

对命令获取帮助

帮助命令: \? \命令

例: \? \connect

 MySQL  JS > \? \connect
NAME
      \connect - Connects the shell to a MySQL server and assigns the global
      session.

SYNTAX
      \connect [<TYPE>] <URI>
      \c [<TYPE>] <URI>

DESCRIPTION
      TYPE is an optional parameter to specify the session type. Accepts the
      following values:

      - --mc, --mysql: create a classic MySQL protocol session (default port
        3306)
      - --mx, --mysqlx: create an X protocol session (default port 33060)
      - --ssh <SSHURI>: create an SSH tunnel to use as a gateway for db
        connection. This requires that db port is specified in advance.

      If TYPE is omitted, automatic protocol detection is done, unless the
      protocol is given in the URI.

      URI and SSHURI format is: [user[:password]@]hostname[:port]

EXAMPLE
      \connect --mx root@localhost
            Creates a global session using the X protocol to the indicated URI.
复制

对象(python模式)

需注意的是，python模式，js模式，对象有差别

js模式对象多了os和sys

而且命令书写也有细微差别

js大小写，

python是带下划线的，例如

 MySQL  172.30.01.01:3306 ssl  opr  JS > dba.createCluster()
 MySQL  172.30.01.01:3306 ssl  opr  JS > \py
Switching to Python mode...
 MySQL  172.30.01.01:3306 ssl  opr  Py > dba.create_cluster()
复制

对对象使用帮助

命令: \? 对象名

例: \? dba

 MySQL  JS > \? sys
NAME
      sys - Gives access to system specific parameters.

DESCRIPTION
      Gives access to system specific parameters.

PROPERTIES
      argv
            Contains the list of arguments available during a script
            processing.

      path
            Lists the search paths to load the JavaScript modules.

FUNCTIONS
      help([member])
            Provides help about this object and it's members
复制

常用命令

\connect 连接mysql数据库

\connect [<TYPE>] <URI>
复制

• type: [–mc, --mx, --ssh]
  –mc, --mysql
  –mx, --mysqlx
  –ssh
• uri: [user[:password]@]hostname[:port]

例,使用root用户连接opr数据库

\connect -mc root@172.30.01.01:3306/opr
复制

M MySQL  JS > \connect root@172.30.01.01:3306/opr
Creating a session to 'root@172.30.01.01:3306/opr'
Fetching schema names for autocompletion... Press ^C to stop.
Your MySQL connection id is 92
Server version: 8.0.26 MySQL Community Server - GPL
Default schema set to `opr`.
 MySQL  172.30.01.01:3306 ssl  opr  JS >
复制

\sql \py \js模式切换

切换sql模式相当于进入了mysql客户端,可以使用sql查询数据

\sql
复制

MySQL  172.30.01.01:3306 ssl  opr  SQL > select * from mysql.user limit 1 \G
*************************** 1. row ***************************
                    Host: %
                    User: employee_proxy
             Select_priv: N
...
复制

其他两种模式,熟悉python或者js的自己选择对应的可以

\py
\js
复制

\status 状态查询

MySQL  172.30.01.01:3306 ssl  opr  JS > \status
MySQL Shell version 8.0.28

Connection Id:                97
Current schema:               opr
Current user:                 root@xx.test.com
SSL:                          Cipher in use: TLS_AES_256_GCM_SHA384 TLSv1.3
Using delimiter:              ;
Server version:               8.0.26 MySQL Community Server - GPL
Protocol version:             Classic 10
Client library:               8.0.28
Connection:                   172.30.01.01 via TCP/IP
TCP port:                     3306
Server characterset:          utf8mb4
Schema characterset:          utf8mb4
Client characterset:          utf8mb4
Conn. characterset:           utf8mb4
Result characterset:          utf8mb4
Compression:                  Disabled
Uptime:                       1 day 1 hour 4 min 41.0000 sec

Threads: 3  Questions: 2381  Slow queries: 1  Opens: 1103  Flush tables: 21  Open tables: 217  Queries per second avg: 0
复制

\show 提供一些报告模块

\show <report_name> [options] [arguments]
复制

• report_name: [query, thread, threads]
• options: [–vertical, --help]

 MySQL  172.30.01.01:3306 ssl  opr  JS > \show query --vertical show processlist
*************************** 1. row ***************************
     Id: 5
   User: event_scheduler
   Host: localhost
     db: NULL
Command: Daemon
   Time: 90929
  State: Waiting on empty queue
   Info: NULL
*************************** 2. row ***************************
     Id: 62
   User: root
   Host: localhost
     db: NULL
Command: Sleep
   Time: 9903
  State:
   Info: NULL
复制

\watch show的升级版,不断刷新report

\watch <report_name> [options] [arguments]
复制

• report_name: [query, thread, threads]
• options: [–vertical, --help, --interval=float, --nocls]

–vertical: 竖排打印

–interval: 刷新时间

–nocls: 不请空之前的记录

MySQL  172.30.01.01:3306 ssl  opr  Py > \watch query --vertical --interval=1 show session status like 'Uptime%'

*************************** 1. row ***************************
Variable_name: Uptime
        Value: 92586
*************************** 2. row ***************************
Variable_name: Uptime_since_flush_status
        Value: 92586

 MySQL  172.30.01.01:3306 ssl  opr  Py > \watch query --nocls show session status like 'Uptime%'
+---------------------------+-------+
| Variable_name             | Value |
+---------------------------+-------+
| Uptime                    | 92541 |
| Uptime_since_flush_status | 92541 |
+---------------------------+-------+
+---------------------------+-------+
| Variable_name             | Value |
+---------------------------+-------+
| Uptime                    | 92543 |
| Uptime_since_flush_status | 92543 |
+---------------------------+-------+
复制

\history 查看历史命令

\history [options]

• options: [del, clear, save]

del, clear, save分别对应删除, 清空, 保存历史命令记录

例:

 MySQL  172.30.01.01:3306 ssl  opr  SQL > \history
    1  select * from xxx;
    2  select * from mysql.user \G
    ...
--删除1-5的记录
MySQL  172.30.01.01:3306 ssl  opr  SQL > \history del 1-5
MySQL  172.30.01.01:3306 ssl  opr  SQL > \history
    6  \? \history
    7  \history
复制

\system 执行本地命令

MySQL  172.30.01.01:3306 ssl  opr  SQL > \! ipconfig
复制

常用对象

使用对象的时候，可以使用tab自动补全命令，方便使用
对象的选项非常多，使用 \? 会很有帮助
以下为这些对象的一些方法

使用方式大概就是
对象.方法(一些参数)
比如：

--查看mysqlshell版本号
MySQL  172.30.01.01:3306 ssl  opr  Py > shell.version
Ver 8.0.28 for Win64 on x86_64 - for MySQL 8.0.28 (MySQL Community Server (GPL))
MySQL  172.30.01.01:3306 ssl  opr  Py > shell.version
Ver 8.0.28 for Win64 on x86_64 - for MySQL 8.0.28 (MySQL Community Server (GPL))
--导出实例数据
 MySQL  172.30.01.01:3306 ssl  opr  Py > util.dump_instance("e:/dump", {"compression": "gzip"})
Acquiring global read lock
Global read lock acquired
Initializing - done
5 out of 9 schemas will be dumped and within them 61 tables, 0 views.
10 out of 13 users will be dumped.
Gathering information - done
All transactions have been started
Locking instance for backup
Global read lock has been released
Writing global DDL files
Writing users DDL
Running data dump using 4 threads.
NOTE: Progress information uses estimated values and may not be accurate.
NOTE: Table statistics not available for `orchestrator`.`orchestrator_db_deployments`, chunking operation may be not optimal. Please consider running 'ANALYZE TABLE `orchestrator`.`orchestrator_db_deployments`;' first.
Writing schema metadata - done
Writing DDL - done
Writing table metadata - done
Starting data dump
100% (4.96K rows / ~4.96K rows), 0.00 rows/s, 0.00 B/s uncompressed, 0.00 B/s compressed
Dump duration: 00:00:00s
Total duration: 00:00:00s
Schemas dumped: 5
Tables dumped: 61
Uncompressed data size: 229.72 KB
Compressed data size: 80.79 KB
Compression ratio: 2.8
Rows written: 4957
Bytes written: 80.79 KB
Average uncompressed throughput: 229.72 KB/s
Average compressed throughput: 80.79 KB/s
复制

dba

MySQL  172.30.01.01:3306 ssl  opr  Py > dba.
check_instance_configuration()         deploy_sandbox_instance()              reboot_cluster_from_complete_outage()
configure_instance()                   drop_metadata_schema()                 session
configure_local_instance()             get_cluster()                          start_sandbox_instance()
configure_replica_set_instance()       get_cluster_set()                      stop_sandbox_instance()
create_cluster()                       get_replica_set()                      upgrade_metadata()
create_replica_set()                   help()                                 verbose
delete_sandbox_instance()              kill_sandbox_instance()
复制

shell

 MySQL  172.30.01.01:3306 ssl  opr  Py > shell.
add_extension_object_member()  disconnect()                   log()                          reports
connect()                      dump_rows()                    open_session()                 set_current_schema()
connect_to_primary()           enable_pager()                 options                        set_session()
create_context()               get_session()                  parse_uri()                    status()
create_extension_object()      help()                         prompt()                       store_credential()
delete_all_credentials()       list_credential_helpers()      reconnect()                    unparse_uri()
delete_credential()            list_credentials()             register_global()              version
disable_pager()                list_ssh_connections()         register_report()
复制

util

 MySQL  172.30.01.01:3306 ssl  opr  Py > util.
check_for_server_upgrade()  dump_schemas()              export_table()              import_json()               load_dump()
dump_instance()             dump_tables()               help()                      import_table()
复制

session

 MySQL  172.30.01.01:3306 ssl  opr  Py > session.
_get_socket_fd()     commit()             get_uri()            is_open()            rollback()           ssh_uri              uri
close()              get_ssh_uri()        help()               query()              run_sql()            start_transaction()
复制