签到成功

知道了

CNDBA社区CNDBA社区

MySQL Shell 安装 说明

2023-12-20 11:47 1135 0 原创 MySQL
作者： dave
本文链接：http://www.cndba.cn/cndba/dave/article/131486

注：本文根据网络资料整理

1 MySQL Shell 概述

MySQL 从8.0开始提供的MySQL Shell功能，MySQL Shell 是第二代 MySQL 客户端，第一代 MySQL 客户端即是我们常用的 mysql。 虽然是 8.0 开始提供的功能，但在MySQL 5.7之后的版本都可以使用该工具。

官方文档：

https://dev.mysql.com/doc/mysql-shell/8.0/en/
https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-utilities.html

相比于 mysql，MySQL Shell 不仅支持 SQL，还具有以下关键特性：http://www.cndba.cn/cndba/dave/article/131486

  1. 支持 Python 和 JavaScript 两种语言模式。
  2. 支持 AdminAPI。可以用来管理 InnoDB Cluster、InnoDB ClusterSet 和 InnoDB ReplicaSet。
  3. 支持 X DevAPI。可以对文档（ Document ）和表（ Table ）进行 CRUD（Create，Read，Update，Delete）操作。

除此之外，MySQL Shell 还内置了很多实用工具，包括：

http://www.cndba.cn/cndba/dave/article/131486

  1. checkForServerUpgrade：检测目标实例能否升级到指定版本。
  2. dumpInstance：备份实例。
  3. dumpSchemas：备份指定库。
  4. dumpTables：备份指定表。
  5. loadDump：恢复通过上面三个工具生成的备份。
  6. exportTable：将指定的表导出到文本文件中。只是支持单表，效果同 SELECT INTO OUTFILE 一样。
  7. importTable：将指定文本的数据导入到表中。生产环境进行大文件导入时，建议使用这个工具。它会将单个文件进行拆分，然后多线程并行执行 LOAD DATA LOCAL INFILE 操作。不仅提升了导入速度，还规避了大事务的问题。
  8. importJson：将 JSON 格式的数据导入到 MySQL 中，比如将 MongoDB 中通过 mongoexport 导出的数据导入到 MySQL 中。

在使用时注意：

  1. 通过 dumpInstance，dumpSchemas，dumpTables 生成的备份只能通过 loadDump 来恢复。
  2. 通过 exportTable 生成的备份只能通过 importTable 来恢复。

更多 MySQL Shell 的特性参考官方手册：

https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-features.htmlhttp://www.cndba.cn/cndba/dave/article/131486

2 安装 MySQL Shell

安装手册：

https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-install-linux-quick.html

说明：

From version 8.0.18, MySQL Shell uses Python 3, rather than Python 2.7. For platforms that include a system supported installation of Python 3, MySQL Shell uses the most recent version available, with a minimum supported version of Python 3.6. For platforms where Python 3 is not included or is not at the minimum supported version, MySQL Shell bundles Python 3.7.7 up to MySQL Shell 8.0.25, and Python 3.9.5 from MySQL Shell 8.0.26. MySQL Shell maintains code compatibility with Python 2.6 and Python 2.7, so if you require one of these older versions, you can build MySQL Shell from source using the appropriate Python version.http://www.cndba.cn/cndba/dave/article/131486

在官方手册里对版本的说明如上，从MySQL Shell 8.0.18之后的版本使用的是 Python 3， 但通过测试，在python 2.7.5 下，使用MySQL Shell version 8.2.1也没发现异常。 

[dave@www.cndba.cn:~]# python -V
Python 2.7.5

2.1 下载软件

下载时注意glibc的版本：

http://www.cndba.cn/cndba/dave/article/131486 

[dave@www.cndba.cn:~]# ldd --version
ldd (GNU libc) 2.17

下载地址：http://www.cndba.cn/cndba/dave/article/131486http://www.cndba.cn/cndba/dave/article/131486

https://dev.mysql.com/downloads/shell/
https://cdn.mysql.com//Downloads/MySQL-Shell/mysql-shell-8.2.1-linux-glibc2.17-x86-64bit.tar.gz

2.2 安装软件

解压软件：http://www.cndba.cn/cndba/dave/article/131486

[dave@www.cndba.cn:/data/software]# tar xzvf mysql-shell-8.2.1-linux-glibc2.17-x86-64bit.tar.gz
[dave@www.cndba.cn:/data/software]# cd mysql-shell-8.2.1-linux-glibc2.17-x86-64bit/

配置环境变量：

# echo "export PATH=$PATH:/data/software/mysql-shell-8.2.1-linux-glibc2.17-x86-64bit/bin" >> ~/.bash_profile
# source ~/.bash_profile

3 常用命令

3.1 连接数据库

如下几种方式都可以连接：http://www.cndba.cn/cndba/dave/article/131486

[dave@www.cndba.cn:~]# mysqlsh mysql://root@192.168.56.109:3308
[dave@www.cndba.cn:~]# mysqlsh --uri root@192.168.56.109:3308
[dave@www.cndba.cn:~]# mysqlsh --uri mysql://root@192.168.56.109:3308

Please provide the password for 'root@192.168.56.109:3308': ****************
Save password for 'root@192.168.56.109:3308'? [Y]es/[N]o/Ne[v]er (default No): Y
MySQL Shell 8.2.1

Copyright (c) 2016, 2023, 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.
Creating a Classic session to 'root@192.168.56.109:3308'
Fetching schema names for auto-completion... Press ^C to stop.
Your MySQL connection id is 13669
Server version: 5.7.25-log MySQL Community Server (GPL)
No default schema selected; type /use <schema> to set one.
 MySQL  192.168.56.109:3308  JS >

3.2 查看命令帮助

MySQL  192.168.56.109:3308  JS > /h
The Shell Help is organized in categories and topics. To get help for a
specific category or topic use: /? <pattern>

The <pattern> argument should be the name of a category or a topic.

The pattern is a filter to identify topics for which help is required, it can
use the following wildcards:

- ? matches any single character.
- * matches any character sequence.

The following are the main help categories:

 - AdminAPI       The AdminAPI is an API that enables configuring and managing
                  InnoDB Clusters, ReplicaSets, ClusterSets, among other
                  things.
 - Shell Commands Provides details about the available built-in shell commands.
 - ShellAPI       Contains information about the shell and util global objects
                  as well as the mysql module that enables executing SQL on
                  MySQL Servers.
 - SQL Syntax     Entry point to retrieve syntax help on SQL statements.
 - X DevAPI       Details the mysqlx module as well as the capabilities of the
                  X DevAPI which enable working with MySQL as a Document Store

The available topics include:

- The dba global object and the classes available at the AdminAPI.
- The mysqlx module and the classes available at the X DevAPI.
- The mysql module and the global objects and classes available at the
  ShellAPI.
- The functions and properties of the classes exposed by the APIs.
- The available shell commands.
- Any word that is part of an SQL statement.
- Command Line - invoking built-in shell functions without entering interactive
  mode.

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.
 - plugins Plugin to manage MySQL Shell plugins
 - session Represents the currently open MySQL session.
 - 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()

EXAMPLES
/? AdminAPI
      Displays information about the AdminAPI.

/? /connect
      Displays usage details for the /connect command.

/? checkInstanceConfiguration
      Displays usage details for the dba.checkInstanceConfiguration function.

/? sql syntax
      Displays the main SQL help categories.
 MySQL  192.168.56.109:3308  JS >
命令 别名/快捷方式 描述
/help /h or ? 打印有关MySQL Shell的帮助，或搜索联机帮助。
/quit /q or /exit 退出MySQL Shell。
/ 在SQL模式下，开始多行模式。输入空行时缓存并执行代码。
/status /s 显示当前的MySQL Shell状态。
/js 将执行模式切换为JavaScript。
/py 将执行模式切换为Python。
/sql 将执行模式切换为SQL。
/connect /c 连接到MySQL服务器。
/reconnect 重新连接到同一个MySQL服务器。
/use /u 指定要使用的架构。
/source . 使用活动语言执行脚本文件。
/warnings /W 显示语句生成的任何警告。
/nowarnings /w 不要显示语句生成的任何警告。
/history 查看和编辑命令行历史记录。
/rehash 手动更新自动完成名称缓存。
/option 查询和更改MySQL Shell配置选项。
/show 使用提供的选项和参数运行指定的报告。
/watch 使用提供的选项和参数运行指定的报告，并定期刷新结果。

3.3 操作示例

MySQL Shell 默认是 JS 默认，比如切换到 SQL 模式：http://www.cndba.cn/cndba/dave/article/131486

 MySQL  192.168.56.109:3308  JS > /sql
Switching to SQL mode... Commands end with ;
Fetching global names for auto-completion... Press ^C to stop.
 MySQL  192.168.56.109:3308  SQL > select * from cndba.cndba;
+----+-------+-------+------------+------------+----------+----------+
| id | fname | lname | hired      | separated  | job_code | store_id |
+----+-------+-------+------------+------------+----------+----------+
|  1 | NULL  | NULL  | 1950-01-01 | 9999-12-31 |     1001 |        1 |
|  2 | NULL  | NULL  | 1960-01-01 | 9999-12-31 |     1002 |        2 |
|  3 | NULL  | NULL  | 1980-01-01 | 9999-12-31 |     1003 |        3 |
|  4 | NULL  | NULL  | 1990-01-01 | 9999-12-31 |     1004 |        4 |
+----+-------+-------+------------+------------+----------+----------+
4 rows in set (0.0004 sec)
 MySQL  192.168.56.109:3308  SQL > 

 MySQL  192.168.56.109:3308  SQL > /js
Switching to JavaScript mode...
 MySQL  192.168.56.109:3308  JS > /show thread
GENERAL
Thread ID:                13713
Connection ID:            13688
Thread type:              FOREGROUND
Program name:             mysqlsh
User:                     root
Host:                     192.168.56.109
Database:                 NULL
Command:                  Query
Time:                     00:00:00
State:                    Sending data
Transaction state:        NULL
Prepared statements:      0
Bytes received:           2837
Bytes sent:               22443
Info:                     SELECT json_object('tid',t.THR ... JOIN information_schema.innodb
Previous statement:       NULL

 MySQL  192.168.56.109:3308  JS >

 MySQL  192.168.56.109:3308  JS > /status
MySQL Shell version 8.2.1

Connection Id:                13688
Current schema:               
Current user:                 root@192.168.56.109
SSL:                          Not in use.
Using delimiter:              ;
Server version:               5.7.25-log MySQL Community Server (GPL)
Protocol version:             Classic 10
Client library:               8.2.0
Connection:                   192.168.56.109 via TCP/IP
TCP port:                     3308
Server characterset:          utf8mb4
Schema characterset:          utf8mb4
Client characterset:          utf8mb4
Conn. characterset:           utf8mb4
Result characterset:          utf8mb4
Compression:                  Disabled
Uptime:                       4 days 19 hours 11 min 35.0000 sec

Threads: 16  Questions: 3094760  Slow queries: 0  Opens: 474  Flush tables: 1  Open tables: 362  Queries per second avg: 7.462
 MySQL  192.168.56.109:3308  JS > /q
Bye!
[dave@www.cndba.cn:~]#

版权声明：本文为博主原创文章，未经博主允许不得转载。

用户评论
* 以下用户言论只代表其个人观点，不代表CNDBA社区的观点或立场
dave

dave

关注

人的一生应该是这样度过的：当他回首往事的时候，他不会因为虚度年华而悔恨，也不会因为碌碌无为而羞耻；这样，在临死的时候，他就能够说：“我的整个生命和全部精力，都已经献给世界上最壮丽的事业....."

  • 2283
    原创
  • 3
    翻译
  • 579
    转载
  • 196
    评论
  • 访问：8178317次
  • 积分：4428
  • 等级：核心会员
  • 排名：第1名
精华文章

    达梦数据库运维实战

    Oracle 18c 必须掌握的新特性：管理与实战

    Oracle数据库问题解决方案和故障排除手册
    最新问题
    查看更多+
    热门文章
      热门用户
      推荐用户
        友情链接： CNDBA社区 | openGauss | openEuler | openAnolis | 广告联系: database@ustc.edu
        Copyright © 2016 All Rights Reserved. Powered by CNDBA · 皖ICP备2022006297号-1·

        QQ交流群

        注册联系QQ