什么是MySQL Shell
MySQL Shell 是Oracle官方提供的一个交互式工具，用于开发和管理MySQL的服务器。支持JavaScript、SQL、Python...不，是木兰接口。
可使用它来执行数据查询、更新以及各种管理操作。
作为Oracle官方未来核心产品：MySQL-Innodb-Cluster 3个亲儿子的中的一个，更可见它受重视的程度。
文档地址：
https://dev.mysql.com/doc/mysql-shell/8.0/en/
开始使用MySQL Shell
下载地址：
https://dev.mysql.com/downloads/shell/
这里我们下载配套MySQL8的最新版本
直接就可以安装，基本不需要依赖包。
只要mysql命令改成mysqlsh 就可以用了，其他参数不变。
自定义提示符
安装好后，首先做个蛋疼的测试热下身。
和MongoDB一样，MySQL Shell可以用通过prompt来定义自己的提示符。
不同操作系统下，默认命令行提示符配置文件位置不同：
- Oracle Linux 7 RPM：
/usr/share/mysqlsh/prompt/
- 微软Windows：
C:Program FilesMySQLMySQL Shell 8.0sharemysqlshprompt
在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
这里我们做个自定义配置，默认提示符是这样的：
下面通过修改配置，在提示符中加入连接数据库的版本信息：
vim /usr/share/mysqlsh/prompt/prompt_256.json
添加如下代码：
{ "separator": "", "text": "%sysvar:version%", "bg": 250, "fg": 166 },
检验下效果，已经可以看到这个数据库是5.7.29版本。
(https://mmbiz.qpic.cn/mmbiz_png/swk7QdFvoiasIqhicE1FUxEzk9EmPl0GmvpybnHrKx8mU85I29P1ohyEDcmQcsEcNKfiaicqz1mFPz8bevWFcHqibQA/640?wx_fmt=png&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1)
我之前已经在容器中搞了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中插入数据
查询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升级
具体文档：
https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-utilities-upgrade.html
我们在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版本，就会提示不支持
年前基本就不做更新了，路上无聊，走过路过的同学可以看看下面的历史文章。
祝你有个愉快放松的春节。
# 历史文章归档
- GitHub都在用的高可用工具Orch：
Orchestrator：01 基础篇
Orchestrator：02 高可用方案VIP篇
Orchestrator：03 高可用方案ProxySQL篇
Orchestrator：04 高可用方式部署
- Percona 全力打造的监控平台 PMM：
监控利器 PMM2.0X GA 版本发布！
PMM监控的告警配置
PMM的Ansible部署与重点指标
在PMM中添加Redis和ES
