MySQL Shell初步使用

MySQL Shell介绍

MySQL Shell是兼容 MySQL 传统命令行客户端的超级客户端，使用它可以对MySQL进行管理和操作。它支持多种语言，包括SQL，JavaScript和Python，并且支持编写脚本。MySQL Shell不但提供了操作SQL的功能、还提供了管理InnoDB Cluster的API，并且集成了大量的MySQL工具。包括：数据库升级检查，备份实例，备份指定数据库，备份指定表，导入多种格式数据等。

使用MySQL Shell，DBA管理MySQL数据库将变得更加便捷高效。接下来让我们一起开启MySQL Shell畅游之旅吧！

下载

下载地址：https://downloads.mysql.com/archives/shell/
软件名称：mysql-shell-8.0.36-linux-glibc2.17-x86-64bit.tar.gz
上传到服务器位置：/data/soft/

安装

创建MySQL Shell安装目录

[root@node1 soft]# mkdir -p /data/mysqlshell/


安装MySQL Shell

[root@node1 ~]# cd /data/soft
[root@node1 soft]# tar -zxvf mysql-shell-8.0.36-linux-glibc2.17-x86-64bit.tar.gz -C /data/mysqlshell
[root@node1 soft]# cd /data/mysqlshell/
[root@node1 mysqlshell]# ls
mysql-shell-8.0.36-linux-glibc2.17-x86-64bit
[root@node1 mysqlshell]# mv mysql-shell-8.0.36-linux-glibc2.17-x86-64bit/ mysqlsh


MySQL Shell使用

连接数据库

只要mysql命令改成mysqlsh就可以，如下：

[root@node1 ~]# mysqlsh -uroot -p'root'
MySQL Shell 8.0.36

常用命令

\ 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.
\G Send command to mysql server, display result vertically.
\g Send command to mysql server.
\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.


三种功能模式切换

切换到SQL模式
\sql
切换到JavaScript模式
\js
切换到Python模式
\py


获取状态信息

MySQL localhost JS > \status
MySQL Shell version 8.0.36

Threads: 3 Questions: 437 Slow queries: 4 Opens: 489 Flush tables: 3 Open tables: 418 Queries per second avg: 0.000
MySQL localhost JS >


命令自动补齐

MySQL localhost SQL > de
DEALLOCATE PREPARE DELETE DELIMITER DESC DESCRIBE
MySQL localhost SQL > de


执行历史命令

MySQL localhost JS >\history
1 \?
2 \history
3 \status


执行linux命令

MySQL localhost JS > \system pwd
/data/mysqlshell/mysqlsh/share/mysqlsh/prompt
MySQL localhost JS > \system ls -l
total 48
-rw-r--r-- 1 7161 31415 1248 Dec 14 01:28 prompt_16.json
-rw-r--r-- 1 7161 31415 2463 Dec 14 01:28 prompt_256inv.json
-rw-r--r-- 1 7161 31415 2795 Dec 14 01:28 prompt_256.json
-rw-r--r-- 1 7161 31415 2949 Dec 14 01:28 prompt_256pl+aw.json
-rw-r--r-- 1 7161 31415 2864 Dec 14 01:28 prompt_256pl.json
-rw-r--r-- 1 7161 31415 183 Dec 14 01:28 prompt_classic.json
-rw-r--r-- 1 7161 31415 2830 Dec 14 01:28 prompt_dbl_256.json
-rw-r--r-- 1 7161 31415 3020 Dec 14 01:28 prompt_dbl_256pl+aw.json
-rw-r--r-- 1 7161 31415 2935 Dec 14 01:28 prompt_dbl_256pl.json
-rw-r--r-- 1 7161 31415 1559 Dec 14 01:28 prompt_nocolor.json
-rw-r--r-- 1 7161 31415 6735 Dec 14 01:28 README.prompt


SQL命令四种输出格式

MySQL Shell支持以表格、制表符、JSON或垂直格式输出结果。

--制表符格式
[root@node1 prompt]# mysqlsh -uroot -p'root' --sql -e"select * from testdb.testtab"
WARNING: Using a password on the command line interface can be insecure.
id
1
2
也可以通过设置shell.options.set('resultFormat','tabbed')使用制表符格式。
MySQL localhost JS > shell.options.set('resultFormat','tabbed')
MySQL localhost JS > \sql select * from testdb.testtab;\sql select * from testdb.testtab;
id
1
2
2 rows in set (0.0115 sec)

--json格式
[root@node1 prompt]# mysqlsh -uroot -p'root' --sql -e"select * from testdb.testtab" --json
{
"warning": "Using a password on the command line interface can be insecure.\n"
}
{
"hasData": true,
"rows": [
{
"id": 1
},
{
"id": 2
}
],
"executionTime": "0.0013 sec",
"affectedRowCount": 0,
"affectedItemsCount": 0,
"warningCount": 0,
"warningsCount": 0,
"warnings": [],
"info": "",
"autoIncrementValue": 0
}
也可以通过shell.options.set('resultFormat','json')使用JSON格式。


备份实例

[root@node1 data]# mysqlsh -h192.168.100.10 -utest -p'test' -P3306 -- util dump-instance /data/backup --threads=2
WARNING: Using a password on the command line interface can be insecure.
Acquiring global read lock
Global read lock acquired
Initializing - done
1 out of 5 schemas will be dumped and within them 1 table, 0 views.
4 out of 7 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 2 threads.
NOTE: Progress information uses estimated values and may not be accurate.
Writing schema metadata - done
Writing DDL - done
Writing table metadata - done
Starting data dump
100% (2 rows / ~2 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: 1
Tables dumped: 1
Uncompressed data size: 8 bytes
Compressed data size: 17 bytes
Compression ratio: 0.5
Rows written: 2
Bytes written: 17 bytes
Average uncompressed throughput: 8.00 B/s
Average compressed throughput: 17.00 B/s


数据库升级检查

直到MySQL Shell 8.0.20，用于运行升级检查工具的用户帐户必须具有所有权限。从MySQL Shell 8.0.21开始，用户帐户需要RELOAD, PROCESS和SELECT权限。

MySQL localhost JS > util.checkForServerUpgrade();
The MySQL server at /data%2Fdata%2Fmysql.sock, version 8.0.33 - MySQL Community
Server - GPL, will now be checked for compatibility issues for upgrade to MySQL
8.0.36...

No known compatibility errors or issues were found.
MySQL localhost JS >


总结

MySQL Shell还有很多功能，需要在工作中不断地去探索~~~