使用mysql-shell 检查工具检查兼容性

jiawang

1、安装 mysql-shell rpm 软件包
[root@sztech setup]# rpm -Uvh mysql-shell-8.0.36-1.el7.x86_64.rpm --force --nodeps     
warning: mysql-shell-8.0.36-1.el7.x86_64.rpm: Header V4 RSA/SHA256 Signature, key ID a8d3785c: NOKEY
Preparing...                          ################################# [100%]
Updating / installing...
   1:mysql-shell-8.0.36-1.el7         ################################# [100%]
[root@sztech setup]#


2、查看mysql-shell的版本
[root@sztech setup]# mysqlsh --version
mysqlsh   Ver 8.0.36 for Linux on x86_64 - for MySQL 8.0.36 (MySQL Community Server (GPL))
[root@sztech setup]#
检查该版本是否可以升级到MySQL 8.0.36


3、执行检查兼容性
[root@sztech setup]# mysqlsh -uroot -p -S /var/lib/mysql/mysql.sock -e  "util.checkForServerUpgrade()"
Please provide the password for 'root@/var%2Flib%2Fmysql%2Fmysql.sock': ******
Save password for 'root@/var%2Flib%2Fmysql%2Fmysql.sock'? [Y]es/[N]o/Ne[v]er (default No):
The MySQL server at /var%2Flib%2Fmysql%2Fmysql.sock, version 5.7.42 - MySQL
Community Server (GPL), will now be checked for compatibility issues for
upgrade to MySQL 8.0.36...


1) Usage of old temporal type
  No issues found


2) MySQL 8.0 syntax check for routine-like objects
  No issues found


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


4) Usage of utf8mb3 charset
  Warning: The following objects use the utf8mb3 character set. It is
    recommended to convert them to use utf8mb4 instead, for improved Unicode
    support.
  More information:
    https://dev.mysql.com/doc/refman ... nicode-utf8mb3.html


  ‘example_db’ - schema's default character set: utf8
  ‘example_db’.users.name - column's default character set: utf8
  ‘example_db’.users.email - column's default character set: utf8


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


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


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


8) Usage of obsolete MAXDB sql_mode flag
  No issues found


9) Usage of obsolete sql_mode flags
  No issues found


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


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


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


13) Usage of removed functions
  No issues found


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


15) 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/relnot ... sqld-8-0-13-logging


16) 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 ... tml#optvars-removed


17) 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/


18) Zero Date, Datetime, and Timestamp values
  Warning: By default zero date/datetime/timestamp values are no longer allowed
    in MySQL, as of 5.7.8 NO_ZERO_IN_DATE and NO_ZERO_DATE are included in
    SQL_MODE by default. These modes should be used with strict mode as they will
    be merged with strict mode in a future release. If you do not include these
    modes in your SQL_MODE setting, you are able to insert
    date/datetime/timestamp values that contain zeros. It is strongly advised to
    replace zero values with valid ones, as they may not work correctly in the
    future.
  More information:
    https://lefred.be/content/mysql-8-0-and-wrong-dates/


  global.sql_mode - does not contain either NO_ZERO_DATE or NO_ZERO_IN_DATE
    which allows insertion of zero dates
  session.sql_mode -  of 2 session(s) does not contain either NO_ZERO_DATE or
    NO_ZERO_IN_DATE which allows insertion of zero dates


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


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


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


22) 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 ... ompatibility-issues
    https://dev.mysql.com/doc/refman ... assword-replication


23) Columns which cannot have default values
  No issues found


24) Check for invalid table names and schema names used in 5.7
  No issues found


25) Check for orphaned routines in 5.7
  No issues found


26) Check for deprecated usage of single dollar signs in object names
  No issues found


27) Check for indexes that are too large to work on higher versions of MySQL
Server than 5.7
  No issues found


28) Check for deprecated '.<table>' syntax used in routines.
  No issues found


29) Check for columns that have foreign keys pointing to tables from a diffrent
database engine.
  No issues found


Errors:   0
Warnings: 6
Notices:  0


NOTE: 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.
[root@sztech setup]#
检查结果显示：
没有发现使用旧时态类型的问题
没有发现与MySQL 8.0语法相关的问题
没有发现与数据库对象名称与新保留关键字冲突的问题
发现某些对象使用了utf8mb3字符集，建议转换为utf8mb4以获得更好的Unicode支持
没有发现与mysql模式中的表名冲突的问题
没有发现使用具有非本地分区的引擎的分区表的问题
没有发现外键约束名称超过64个字符的问题
没有发现使用已弃用的MAXDB sql_mode标志的问题
发现一些DB对象已经使用了过时的sql_mode选项
没有发现ENUM/SET列定义中包含超过255个字符的元素的问题
没有发现在共享表空间中使用分区表的问题
没有发现表空间数据文件路径中存在循环目录引用的问题
没有发现使用已删除函数的问题
没有发现已删除的GROUP BY ASC/DESC语法的问题
无法运行需要指定完整MySQL服务器配置文件路径的日志系统变量检查
无法运行需要指定完整MySQL服务器配置文件路径的已删除系统变量检查
无法运行需要指定完整MySQL服务器配置文件路径的新默认值系统变量检查
没有发现零日期、日期时间和时间戳值的问题
没有发现由文件删除或损坏导致的模式不一致的问题
没有发现被InnoDB识别为属于不同引擎的表的问题
没有发现通过'check table x for upgrade'命令报告的问题
发现新的默认身份验证插件引入了兼容性问题，建议在升级后重新配置服务器以恢复到以前的默认身份验证插件
没有发现不能有默认值的列的问题
没有发现在5.7中使用的无效表名和模式名的问题
没有发现在5.7中遗留存储过程的问题
没有发现在对象名称中使用单个美元符号的已弃用用法
没有发现在高于MySQL Server 5.7版本上工作的索引过大的问题
没有发现在例程中使用已弃用'.<table>'语法的问题
没有发现具有外键指向不同数据库引擎的表的列的问题
总结：
错误：0
警告：6
注意事项：0