shell了解MySQL_MySQLShell：01从入门到蒙圈

最新推荐文章于 2024-08-09 18:13:29 发布

weixin_42413455

最新推荐文章于 2024-08-09 18:13:29 发布

阅读量795

点赞数 1

文章标签： shell了解MySQL

本文链接：https://blog.csdn.net/weixin_42413455/article/details/113348182

版权

以下文章来源于万能修实验室，作者公先生

什么是MySQL Shell

MySQL Shell 是Oracle官方提供的一个交互式工具，用于开发和管理MySQL的服务器。支持JavaScript、SQL、Python...不，是木兰接口。

可使用它来执行数据查询、更新以及各种管理操作。

作为Oracle官方未来核心产品：MySQL-Innodb-Cluster 3个亲儿子的中的一个，更可见它受重视的程度。

文档地址：

开始使用MySQL Shell

下载地址：

这里我们下载配套MySQL8的最新版本

format,png

直接就可以安装，基本不需要依赖包。

format,png

只要mysql命令改成mysqlsh 就可以用了，其他参数不变。

format,png

自定义提示符

安装好后，首先做个蛋疼的测试热下身。

和MongoDB一样，MySQL Shell可以用通过prompt来定义自己的提示符。

不同操作系统下，默认命令行提示符配置文件位置不同：

Oracle Linux 7 RPM：

/usr/share/mysqlsh/prompt/

微软Windows：

C:\Program Files\MySQL\MySQL Shell 8.0\share\mysqlsh\prompt

在Linux 7安装中默认使用这个模板

/usr/share/mysqlsh/prompt/prompt_256.json

官方贴心的提供了很多模板可供使用：

[root@caihao ~]# cd /usr/share/mysqlsh/prompt/[root@caihao prompt]# ls -ltotal 48-rw-r--r-- 1 root root 1245 Dec 17 03:13 prompt_16.json-rw-r--r-- 1 root root 1622 Dec 17 03:13 prompt_256inv.json-rw-r--r-- 1 root root 2137 Dec 17 03:13 prompt_256.json-rw-r--r-- 1 root root 2179 Dec 17 03:13 prompt_256pl+aw.json-rw-r--r-- 1 root root 1921 Dec 17 03:13 prompt_256pl.json-rw-r--r-- 1 root root 183 Dec 17 03:13 prompt_classic.json-rw-r--r-- 1 root root 2172 Dec 17 03:13 prompt_dbl_256.json-rw-r--r-- 1 root root 2250 Dec 17 03:13 prompt_dbl_256pl+aw.json-rw-r--r-- 1 root root 1992 Dec 17 03:13 prompt_dbl_256pl.json-rw-r--r-- 1 root root 1205 Dec 17 03:13 prompt_nocolor.json-rw-r--r-- 1 root root 6197 Dec 17 03:13 README.prompt

有2种方式可以切换到其他模板：

export MYSQLSH_PROMPT_THEME=/usr/share/mysqlsh/prompt/prompt_256pl+aw.json

或者

cp prompt_256pl+aw.json ~/.mysqlsh/prompt.json

这里我们做个自定义配置，默认提示符是这样的：

format,png

下面通过修改配置，在提示符中加入连接数据库的版本信息：

vim /usr/share/mysqlsh/prompt/prompt_256.json

添加如下代码：

{ "separator": "", "text": "%sysvar:version%", "bg": 250, "fg": 166 },

检验下效果，已经可以看到这个数据库是5.7.29版本。

我之前已经在容器中搞了4个版本的数据库，分别对应3305 3306 3307 3308 这4个端口。

( 具体方法可以参考我之前的文章：在Docker中玩转MySQL )

[root@caihao ~]# docker psCONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMESd8d14c9ddecb mysql/mysql-server:8.0 "/entrypoint.sh my..." 2 hours ago Up About an hour (healthy) 0.0.0.0:3308->3306/tcp, 0.0.0.0:33080->33060/tcp mysql-8.0b48d63358964 mysql/mysql-server:5.7 "/entrypoint.sh my..." 2 hours ago Up About an hour (healthy) 0.0.0.0:3307->3306/tcp, 0.0.0.0:33070->33060/tcp mysql-5.79cf7b7541ad0 mysql/mysql-server:5.6 "/entrypoint.sh my..." 2 hours ago Up About an hour (healthy) 0.0.0.0:3306->3306/tcp, 0.0.0.0:33060->33060/tcp mysql-5.6c082d308bb96 mysql/mysql-server:5.5 "/entrypoint.sh my..." 2 hours ago Up About an hour (healthy) 0.0.0.0:3305->3306/tcp, 0.0.0.0:33050->33060/tcp mysql-5.5[root@caihao ~]#

对应的版本、主机名、端口都可以显示。注意5.7以上还使用了SSL

还可以自定义字体，比如做成这样的。

测试3种功能模式

MySQL Shell不仅是一个增强版mysql客户端工具，还提供JavaScript和Python脚本功能。

3种模式的切换：

\py

\sql

\js

SQL操作已经可以做自动补全

Python操作，可以直接执行自己写的py程序：

JS方式可以执行json文档操作，这里先建立一个Collection

然后在Collection中插入数据

format,png

查询json数据，最后删除测试表

JS也可以进行各种数据库管理工作

比如管理Replicaset，这个最近新出的功能配置比较繁琐，以后专门写一篇。

MySQL 8.0.19 localhost:3306+ ssl JS > rs.status();{ "replicaSet": { "name": "testReplicaset", "primary": "test77:3306", "status": "AVAILABLE", "statusText": "All instances available.", "topology": { "test77:3306": { "address": "test77:3306", "instanceRole": "PRIMARY", "mode": "R/W", "status": "ONLINE" }, "test78:3306": { "address": "test78:3306", "instanceRole": "SECONDARY", "mode": "R/O", "replication": { "applierStatus": "APPLIED_ALL", "applierThreadState": "Slave has read all relay log; waiting for more updates", "receiverStatus": "ON", "receiverThreadState": "Waiting for master to send event", "replicationLag": null }, "status": "ONLINE" } }, "type": "ASYNC" }}

升级检查功能

Oracle在MySQL Shell 中偷偷的集成了3个小工具，其中一个是升级检查功能：

util.checkForServerUpgrade();

通过它可以验证MySQL服务器实例是否已准备好进行升级。

但是要注意以下2点：

只支持检查MySQL 5.7之后版本的MySQL

仅支持GA版本之间的升级。不支持从非GA版本的MySQL 5.7或8.0升级

具体文档：

我们在5.7的库上测试下：

MySQL 5.7.29 localhost:3307 ssl JS > util.checkForServerUpgrade();The MySQL server at localhost:3307, version 5.7.29 - MySQL Community Server(GPL), will now be checked for compatibility issues for upgrade to MySQL8.0.19...1) Usage of old temporal type No issues found2) Usage of db objects with names conflicting with new reserved keywords No issues found3) Usage of utf8mb3 charset No issues found4) Table names in the mysql schema conflicting with new tables in 8.0 No issues found5) Partitioned tables using engines with non native partitioning No issues found6) Foreign key constraint names longer than 64 characters No issues found7) Usage of obsolete MAXDB sql_mode flag No issues found8) Usage of obsolete sql_mode flags No issues found9) ENUM/SET column definitions containing elements longer than 255 characters No issues found10) Usage of partitioned tables in shared tablespaces No issues found11) Circular directory references in tablespace data file paths No issues found12) Usage of removed functions No issues found13) Usage of removed GROUP BY ASC/DESC syntax No issues found14) Removed system variables for error logging to the system log configuration To run this check requires full path to MySQL server configuration file to be specified at 'configPath' key of options dictionary More information: https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-13.html#mysqld-8-0-13-logging15) Removed system variables To run this check requires full path to MySQL server configuration file to be specified at 'configPath' key of options dictionary More information: https://dev.mysql.com/doc/refman/8.0/en/added-deprecated-removed.html#optvars-removed16) System variables with new default values To run this check requires full path to MySQL server configuration file to be specified at 'configPath' key of options dictionary More information: https://mysqlserverteam.com/new-defaults-in-mysql-8-0/17) Zero Date, Datetime, and Timestamp values No issues found18) Schema inconsistencies resulting from file removal or corruption No issues found19) Tables recognized by InnoDB that belong to a different engine No issues found20) Issues reported by 'check table x for upgrade' command No issues found21) New default authentication plugin considerations Warning: The new default authentication plugin 'caching_sha2_password' offers more secure password hashing than previously used 'mysql_native_password' (and consequent improved client connection authentication). However, it also has compatibility implications that may affect existing MySQL installations. If your MySQL installation must serve pre-8.0 clients and you encounter compatibility issues after upgrading, the simplest way to address those issues is to reconfigure the server to revert to the previous default authentication plugin (mysql_native_password). For example, use these lines in the server option file: [mysqld] default_authentication_plugin=mysql_native_password However, the setting should be viewed as temporary, not as a long term or permanent solution, because it causes new accounts created with the setting in effect to forego the improved authentication security. If you are using replication please take time to understand how the authentication plugin changes may impact you. More information: https://dev.mysql.com/doc/refman/8.0/en/upgrading-from-previous-series.html#upgrade-caching-sha2-password-compatibility-issues https://dev.mysql.com/doc/refman/8.0/en/upgrading-from-previous-series.html#upgrade-caching-sha2-password-replicationErrors: 0Warnings: 1Notices: 0No fatal errors were found that would prevent an upgrade, but some potential issues were detected. Please ensure that the reported issues are not significant before upgrading. MySQL 5.7.29 localhost:3307 ssl JS >

但是在5.6版本，就会提示不支持

format,png