用于 MySQL 升级测试的两个非常有用的工具（pt-upgrade 和 checkForServerUpgrade）

我的上一篇博客，使主要 MySQL 版本升级更容易的 Percona 实用程序，详细介绍了 Percona 工具包中可用的工具，这些工具可帮助我们进行主要的 MySQL 版本升级。 pt-upgrade 工具有助于测试应用程序查询并生成有关每个问题在运行各种 MySQL 版本的服务器上的执行情况的报告。

MySQL Shell Upgrade Checker 是一个实用程序，可帮助在 MySQL 5.7 实例和 MySQL 8.0 升级之间进行兼容性测试，它是 mysql-shell-utilities 的一部分。 util.checkForServerUpgrade() 函数检查 MySQL 5.7 实例是否已为 MySQL 8.0 升级做好准备，并生成包含警告、错误和通知的报告，以准备当前 MySQL 5.7 设置以升级到 MySQL 8.0。

我们可以在当前 MySQL 5.7 环境中运行这个 Upgrade Checker Utility 来生成报告；我建议在与生产具有相同配置的任何副本实例上运行它。

用于执行升级检查工具的用户帐户必须拥有 MySQL Shell 8.0.20 之前的所有权限。从 MySQL Shell 8.0.21 开始，用户帐户需要 RELOAD、PROCESS 和 SELECT 功能。

如何使用升级检查器实用程序生成报告

要使用 Upgrade Checker Utility 生成报告，我们可以登录到 shell 提示符或直接从命令提示符处执行。

mysqlsh -- util checkForServerUpgrade 'root@localhost:3306' --target-version=8.0.29 --config-path=/etc/my.cnf > CheckForServerUpgrade_Report.txt
Please provide the password for 'mysqluser@localhost:3306':

$ mysqlsh
MySQL  JS > util.checkForServerUpgrade('root@localhost:3306', { "targetVersion":"8.0.29", "configPath":"/etc/my.cnf"})
Please provide the password for 'mysqluser@localhost:3306': 

要退出 mysqlsh 命令提示符，请键入 \exit。

MySQL  JS > \exit
Bye!

pt-upgrade 和 Upgrade Checker Utility 是否进行相同的测试？不！

不要将 Upgrade Checker Utility 与 pt-upgrade 工具混淆，因为它们用于不同类型的主要版本升级测试。 Upgrade Checker Utility 在选定的 MySQL 服务器上执行各种测试以确定升级是否成功；但是，该工具无法确认升级是否与应用程序查询或例程兼容。

它是否同时检查 my.cnf 文件和 MySQL 服务器变量？

该实用程序可以查找在配置文件 (my.cnf) 中声明但在目标 MySQL 服务器版本中删除的系统变量，以及未在配置文件中定义但在目标 MySQL 服务器版本中具有不同默认值的系统变量。在为这些检查执行 checkForServerUpgrade() 时，您必须提供配置文件的文件路径。但是，该工具无法识别在 my.cnf 文件中已删除但在 MySQL 服务器中设置的变量。

让我们从 /etc/percona-server.conf.d/mysqld.cnf 中删除 query_cache_type 并运行命令。

]# mysql -uroot -p -e "SHOW VARIABLES WHERE Variable_Name IN ('query_cache_type','query_cache_size')"
Enter password:
+------------------+---------+
| Variable_name    | Value   |
+------------------+---------+
| query_cache_size | 1048576 |
| query_cache_type | ON      |
+------------------+---------+

]# cat /etc/my.cnf
#
# The Percona Server 5.7 configuration file.
#
#
# * IMPORTANT: Additional settings that can override those from this file!
#   The files must end with '.cnf', otherwise they'll be ignored.
#   Please make any edits and changes to the appropriate sectional files
#   included below.
#
!includedir /etc/my.cnf.d/
!includedir /etc/percona-server.conf.d/
]#

Remove query_cache_type variable from mysqld.cnf:

]# sed -i '/query_cache_type/d' /etc/percona-server.conf.d/mysqld.cnf
]#

]# grep -i query /etc/my.cnf /etc/percona-server.conf.d/mysqld.cnf
/etc/percona-server.conf.d/mysqld.cnf:query_cache_size=5058320
]#

由于查询缓存类型变量已从 my.cnf 中删除，因此该工具无法检测到它。

#  mysqlsh -- util checkForServerUpgrade 'root@localhost:3306' --target-version=8.0.29 --config-path=/etc/my.cnf | grep  -B 6  -i "query_cache"
15) Removed system variables
  Error: Following system variables that were detected as being used will be
    removed. Please update your system to not rely on them before the upgrade.
  More information:
    https://dev.mysql.com/doc/refman/8.0/en/added-deprecated-removed.html#optvars-removed

  query_cache_size - is set and will be removed
ERROR: 1 errors were found. Please correct these issues before upgrading to avoid compatibility issues.

在 JSON 格式中，报告如下所示：

注意：为了使博客更具可读性，我缩短了报告。

# mysqlsh -- util checkForServerUpgrade 'root@localhost:3306' --target-version=8.0.29 --config-path=/etc/my.cnf --output-format=JSON
{
    "serverAddress": "localhost:3306",
    "serverVersion": "5.7.39-42 - Percona Server (GPL), Release 42, Revision b0a7dc2da2e",
    "targetVersion": "8.0.29",
    "errorCount": 1,
    "warningCount": 27,
    "noticeCount": 1,
    "summary": "1 errors were found. Please correct these issues before upgrading to avoid compatibility issues.",
    "checksPerformed": [
        {
            "id": "oldTemporalCheck",
            "title": "Usage of old temporal type",
            "status": "OK",
            "detectedProblems": []
        },
        {
            "id": "reservedKeywordsCheck",
            "title": "Usage of db objects with names conflicting with new reserved keywords",
            "status": "OK",
            "detectedProblems": []
        },
…
        {
            "id": "sqlModeFlagCheck",
            "title": "Usage of obsolete sql_mode flags",
            "status": "OK",
            "description": "Notice: The following DB objects have obsolete options persisted for sql_mode, which will be cleared during upgrade to 8.0.",
            "documentationLink": "https://dev.mysql.com/doc/refman/8.0/en/mysql-nutshell.html#mysql-nutshell-removals",
            "detectedProblems": [
                {
                    "level": "Notice",
                    "dbObject": "global system variable sql_mode",
                    "description": "defined using obsolete NO_AUTO_CREATE_USER option"
                }
            ]
        },
        {
            "id": "enumSetElementLenghtCheck",
            "title": "ENUM/SET column definitions containing elements longer than 255 characters",
            "status": "OK",
            "detectedProblems": []
        },
…
        {
            "id": "removedSysVars",
            "title": "Removed system variables",
            "status": "OK",
            "description": "Error: Following system variables that were detected as being used will be removed. Please update your system to not rely on them before the upgrade.",
            "documentationLink": "https://dev.mysql.com/doc/refman/8.0/en/added-deprecated-removed.html#optvars-removed",
            "detectedProblems": [
                {
                    "level": "Error",
                    "dbObject": "query_cache_size",
                    "description": "is set and will be removed"
                }
            ]
        },
        {
            "id": "sysVarsNewDefaults",
            "title": "System variables with new default values",
            "status": "OK",
            "description": "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.",
            "documentationLink": "https://mysqlserverteam.com/new-defaults-in-mysql-8-0/",
            "detectedProblems": [
                {
                    "level": "Warning",
                    "dbObject": "back_log",
                    "description": "default value will change"
                },
                {
                    "level": "Warning",
                    "dbObject": "innodb_max_dirty_pages_pct",
                    "description": "default value will change from 75 (%)  90 (%)"
                }
            ]
        },
        {
            "id": "zeroDatesCheck",
            "title": "Zero Date, Datetime, and Timestamp values",
            "status": "OK",
            "detectedProblems": []
        },
…
    ],
    "manualChecks": [
        {
            "id": "defaultAuthenticationPlugin",
            "title": "New default authentication plugin considerations",
            "description": "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:\n\n[mysqld]\ndefault_authentication_plugin=mysql_native_password\n\nHowever, 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.\nIf you are using replication please take time to understand how the authentication plugin changes may impact you.",
            "documentationLink": "https://dev.mysql.com/doc/refman/8.0/en/upgrading-from-previous-series.html#upgrade-caching-sha2-password-compatibility-issues\nhttps://dev.mysql.com/doc/refman/8.0/en/upgrading-from-previous-series.html#upgrade-caching-sha2-password-replication"
        }
    ]
}

请阅读 Daniel Guzmán Burgos 的博客文章以了解有关升级检查器实用程序的更多信息，并单击链接以了解有关 pt-upgrade 测试的更多信息。

在大版本升级之前，应用程序查询测试和配置检查是不可避免的任务，pt-upgrade 和“升级检查器实用程序”非常有用。

原文标题：Two Extremely Useful Tools (pt-upgrade and checkForServerUpgrade) for MySQL Upgrade Testing
原文作者：Arunjith Aravindan
原文地址：https://www.percona.com/blog/two-extremely-useful-tools-pt-upgrade-and-checkforserverupgrade-for-mysql-upgrade-testing/