MySQL Shell：01 从入门到蒙圈

春节马上到了，小伙伴们感受到氛围已经纷纷开溜。（没氛围？华仔的恭喜发财可以让你马上感受下）

中国人过年要有仪式感，包饺子，贴春联，放鞭炮、换红裤衩、烫头等。

今天测试的对象是很早就听过但没用过的MySQL Shell   那么就看看到底好不好用吧。

 

可别像这样

 

过节了对技术不感兴趣没关系，我也准备好了一些出行小知识：

火车上的屎真抛铁轨上了吗？这是我春运路上的终极困惑

 

---

 

什么是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 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 -l
total 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版本。

 

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

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

[root@caihao ~]# docker ps
CONTAINER ID        IMAGE                    COMMAND                  CREATED             STATUS                       PORTS                                              NAMES
d8d14c9ddecb        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.0
b48d63358964        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.7
9cf7b7541ad0        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.6
c082d308bb96        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点：

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

2. 仅支持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 MySQL
8.0.19...

1) Usage of old temporal type
  No issues found

2) Usage of db objects with names conflicting with new reserved keywords
  No issues found

3) Usage of utf8mb3 charset
  No issues found

4) Table names in the mysql schema conflicting with new tables in 8.0
  No issues found

5) Partitioned tables using engines with non native partitioning
  No issues found

6) Foreign key constraint names longer than 64 characters
  No issues found

7) Usage of obsolete MAXDB sql_mode flag
  No issues found

8) Usage of obsolete sql_mode flags
  No issues found

9) ENUM/SET column definitions containing elements longer than 255 characters
  No issues found

10) Usage of partitioned tables in shared tablespaces
  No issues found

11) Circular directory references in tablespace data file paths
  No issues found

12) Usage of removed functions
  No issues found

13) Usage of removed GROUP BY ASC/DESC syntax
  No issues found

14) 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-logging

15) 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-removed

16) 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 found

18) Schema inconsistencies resulting from file removal or corruption
  No issues found

19) Tables recognized by InnoDB that belong to a different engine
  No issues found

20) Issues reported by 'check table x for upgrade' command
  No issues found

21) 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-replication

Errors:   0
Warnings: 1
Notices:  0

No 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