使用 MySQL Shell 进行 MySQL 升级检查

如果有一种工具可以查看我们现有的数据库，并检查升级到新版本是否存在任何问题，那岂不是很有帮助？MySQL Shell 可以帮你解决这个问题。

MySQL Shell中的全局util对象有一个名为checkForServerUpgrade()的方法，可以检查当前数据库，找出升级到新版本MySQL的任何潜在问题。

 

检查升级兼容性问题的最直接语法是以下命令：

util.checkForServerUpgrade()

 

运行该命令时，它将根据用于运行检查的 MySQL Shell 版本（在我的例子中，我使用的是 8.0.37 版），完成对 MySQL Shell 所连接的数据库实例的升级检查。在我的例子中，我正在检查运行 MySQL 8.0.32 的数据库升级到 MySQL 8.0.37 的兼容性问题。以下是检查结果：

The MySQL server at /data%2Fmysql_data%2Fmysql.sock, version 8.0.32 - MySQL
Community Server - GPL, will now be checked for compatibility issues for
upgrade to MySQL 8.0.37...

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

Errors:   0
Warnings: 0
Notices:  0

No known compatibility errors or issues were found.

 

如果 MySQL Shell 的版本是 8.4，而 MySQL Server 的版本是 8.0.37，则会报以下升级到 8.4 存在的兼容性问题。

The MySQL server at 127.0.0.1:3306, version 8.0.37 - MySQL Community Server -
GPL, will now be checked for compatibility issues for upgrade to MySQL 8.4.0.
To check for a different target server version, use the targetVersion option.1) Removed system variables (removedSysVars)
  No issues found

2) System variables with new default values (sysVarsNewDefaults)
  Warning: Following system variables that are not defined in your
    configuration file will have new default values. Please review if you rely on
    their current values and if so define them before performing upgrade.
  More information:
    https://dev.mysql.com/blog-archive/new-defaults-in-mysql-8-0/

  binlog_transaction_dependency_tracking - default value will change from
    COMMIT_ORDER to WRITESET.
  group_replication_consistency - default value will change from EVENTUAL to
    BEFORE_ON_PRIMARY_FAILOVER.
  group_replication_exit_state_action - default value will change from
    READ_ONLY to OFFLINE_MODE.
  innodb_adaptive_hash_index - default value will change from ON to OFF.
  innodb_buffer_pool_in_core_file - default value will change from ON to OFF.
  innodb_buffer_pool_instances - default value will change from 8 (or 1 if
    innodb_buffer_pool_size < 1GB) to MAX(1, #vcpu/4).
  innodb_change_buffering - default value will change from all to none.
  innodb_doublewrite_files - default value will change from
    innodb_buffer_pool_instances * 2 to 2.
  innodb_doublewrite_pages - default value will change from
    innodb_write_io_threads to 128.
  innodb_flush_method - default value will change from fsynch (unix) or
    unbuffered (windows) to O_DIRECT.
  innodb_io_capacity - default value will change from 200 to 10000.
  innodb_io_capacity_max - default value will change from 200 to 2 x
    innodb_io_capacity.
  innodb_log_buffer_size - default value will change from 16777216 (16MB) to
    67108864 (64MB).
  innodb_log_writer_threads - default value will change from ON to OFF ( if
    #vcpu <= 32 ).
  innodb_numa_interleave - default value will change from OFF to ON.
  innodb_page_cleaners - default value will change from 4 to
    innodb_buffer_pool_instances.
  innodb_parallel_read_threads - default value will change from 4 to
    MAX(#vcpu/8, 4).
  innodb_purge_threads - default value will change from 4 to 1 ( if #vcpu <= 16
    ).
  innodb_read_io_threads - default value will change from 4 to MAX(#vcpu/2, 4).
  innodb_redo_log_capacity - default value will change from 104857600 (100MB)
    to MIN ( #vcpu/2, 16 )GB.

3) Issues reported by 'check table x for upgrade' command (checkTableCommand)
  No issues found

4) Check for deprecated or invalid user authentication methods.
(authMethodUsage)
  Warning: The following users are using the 'mysql_native_password'
  authentication method which is deprecated as of MySQL 8.0.0 and will be
  removed in a future release.
  Consider switching the users to a different authentication method (i.e.
  caching_sha2_password).

  - someDBUser@%

  More information:
    https://dev.mysql.com/doc/refman/8.0/en/caching-sha2-pluggable-authentication.html

5) Check for deprecated or removed plugin usage. (pluginUsage)
  No issues found

6) Check for deprecated or invalid default authentication methods in system
variables. (deprecatedDefaultAuth)
  No issues found

7) Check for deprecated or invalid authentication methods in use by MySQL
Router internal accounts. (deprecatedRouterAuthMethod)
  No issues found

8) Checks for errors in column definitions (columnDefinition)
  No issues found

9) Check for allowed values in System Variables. (sysvarAllowedValues)
  No issues found

10) Checks for user privileges that will be removed (invalidPrivileges)
  Verifies for users containing grants to be removed, since privileges are
    removed as part of the upgrade, raises a NOTICE to inform the user about
    users that will be losing invalid privileges

  'someDBUser'@'%' - The user 'someDBUser'@'%' has the following
    privileges that will be removed as part of the upgrade process: SET_USER_ID
  'root'@'localhost' - The user 'root'@'localhost' has the following privileges
    that will be removed as part of the upgrade process: SET_USER_ID

11) Checks for partitions by key using columns with prefix key indexes
(partitionsWithPrefixKeys)
  No issues found

Errors:   0
Warnings: 21
Notices:  2

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.

这些结果没有显示出令人震惊的兼容性问题，大部分涉及系统变量更新的详细信息。鉴于这些结果，升级到 MySQL 8.4 应该是可行的，无需更改现有数据库。

 

有关运行 util.checkForServerUpgrade() 时检查内容的更多信息，请查看文档。

 

检查其他系统

使用 MySQL Shell，我们可以在运行 util.checkForServerUpgrade() 时检查其他系统。连接到其他系统以运行检查的语法类似下面的命令：

util.checkForServerUpgrade('user@example.com:3306', {"password":"password"})

 

参数选项

·targetVersion - 要检查升级的 MySQl 目标版本。

·include - 以逗号分隔的要运行的检查列表。

·exclude - 以逗号分隔的要排除的检查列表。

 

查看更多的帮助信息

\? util.checkForServerUpgrade

 

总结

在升级到较新版本的 MySQL 时，确定兼容性问题可能会令人望而生畏，而且非常耗时。解决这些问题可能会更加困难和耗时。通过使用 MySQL Shell 中的 util.checkForServerUpgrade()，我们可以快速识别任何问题，并验证这些问题是否已在系统升级之前得到解决。