Mysql 8.0 MGR 小版本滚动升级

原创大表哥 All China Database Union 2025-01-02

381

Hello, 大家好，今天分享一下 MYSQL 8.0 MGR 的升级案例。

数据库升级背景：公司安全部在生产的机器上扫描到了MYSQL 8.0的漏洞如下：

公司安全小哥建议mysql 版本升级到 8.0.35 之上的版本，升级到哪个版本呢？

我找出了半年前很火的一个mysql 事件： MYSQL 不建议更新到8.0.37之后的版本。
https://www.percona.com/blog/do-not-upgrade-to-any-version-of-mysql-after-8-0-37/

大致问题就是大家耳熟能详的数据库实例中超过1万张表，数据库重启会crash 掉

我司现在数据库版本是：8.0.35-27

root@localhost:mysql_jasonDB.sock [performance_schema]> select @@version;
--------------
select @@version
--------------


+-----------+
| @@version |
+-----------+
| 8.0.35-27 |
+-----------+
1 row in set (0.00 sec)

复制

可供选择的目前只有 8.0.36 和 8.0.37 2个版本喽。

https://docs.percona.com/percona-distribution-for-mysql/8.0/release-notes.html

保守的目的，我们这次只升级一个小版本 8.0.35 升级到 8.0.36 : 这里我要查一下 8.0.36 和周边组件的兼容性，来确定是否周边组件类似 proxysql, mysqlrooter , xrtabackup 等是否也要一起升级。

https://docs.percona.com/percona-distribution-for-mysql/8.0/release-notes-ps-v8.0.36.html#bug-fixes

我司主要用到了下面3各组件： ProxySQL, xrtabackup 和 toolkit

需要和现有环境来比对一下，

ProxySQL: 2.5.5-percona-1.1


admin@127.0.0.1:3732 [(none)]> select @@version;
+-------------------+
| @@version         |
+-------------------+
| 2.5.5-percona-1.1 |
+-------------------+
1 row in set (0.00 sec)
复制

Xtrabackup 版本比对： xtrabackup version 8.0.35-30

INFRA [mysql@dc02psqldbuat04 bin]# xtrabackup --version
2024-12-27T12:14:24.926216+08:00 0 [Note] [MY-011825] [Xtrabackup] recognized server arguments: --datadir=/var/lib/mysql
xtrabackup version 8.0.35-30 based on MySQL server 8.0.35 Linux (x86_64) (revision id: 6beb4b49)

复制

percona-toolkit 目前有2个安装包： percona-toolkit-3.2.1 VS percona-toolkit-3.5.7

INFRA [mysql@dc02psqldbuat04 software]# ls -lhtr | grep percona-toolkit
-rw------- 1 mysql mysql  15M Dec 21 15:24 percona-toolkit-3.2.1_x86_64.tar
-rw------- 1 mysql mysql  70M Dec 21 15:24 percona-toolkit-3.5.7_x86_64.tar

复制

如何查看我们安装的percona-toolkit 是什么版本呢？我们找到percona-toolkit bin目录的一个可执行的命令即可：

INFRA [mysql@dc02psqldbuat04 bin]# ./pt-online-schema-change --version
pt-online-schema-change 3.2.1

复制

我们还需要升级一下 percona-toolkit 工具包，备份老的版本，解压新的版本覆盖即可。

下一步，我们找到 percona mysql 8.0.36 的 tar 包下载地址： https://www.percona.com/downloads

我们查看一下 linux server glib 的版本：ldd (GNU libc) 2.17

INFRA [mysql@dc02psqldbuat04 software]# ldd --version
ldd (GNU libc) 2.17
Copyright (C) 2012 Free Software Foundation, Inc.
This is free software; see the source for copying conditions.  There is NO
warranty; not even for MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
Written by Roland McGrath and Ulrich Drepper.

复制

我们需要下载mysql person 软件的文件为： Percona-Server-8.0.36-28-Linux.x86_64.glibc2.17.tar.gz

关于升级的详细信息，我们需要参考官网文档： https://dev.mysql.com/doc/refman/8.0/en/upgrading.html

作为DBA需要考虑的几个点：

A) MYSQL 的升级路径：（upgrading from MySQL 8.0.x to 8.0.z is supported,对于mysql 8.0以上的版本是支持直接升级的）

mysql 5.7 -> mysql 8.0
mysql 5.6 -> mysql 5.7 -> mysql 8.0
mysql 8.0.x - mysql 8.0.y

如果想从 5.6 直接升级到 8.0 是不行的，是不能够跨越大版本升级的。（或许通过ETL工具直接迁移到高版本的是更好的可行方案）
参考： https://dev.mysql.com/doc/refman/8.0/en/upgrade-paths.html

Upgrade from MySQL 5.7 to 8.0 is supported. However, upgrade is only supported between General Availability (GA) releases. For MySQL 8.0, it is required that you upgrade from a MySQL 5.7 GA release (5.7.9 or higher). Upgrades from non-GA releases of MySQL 5.7 are not supported.

Upgrading to the latest release is recommended before upgrading to the next version. For example, upgrade to the latest MySQL 5.7 release before upgrading to MySQL 8.0.

Upgrade that skips versions is not supported. For example, upgrading directly from MySQL 5.6 to 8.0 is not supported.

Once a release series reaches General Availability (GA) status, upgrade within the release series (from one GA version to another GA version) is supported. For example, upgrading from MySQL 8.0.x to 8.0.y is supported. (Upgrade involving development-status non-GA releases is not supported.) Skipping a release is also supported. For example, upgrading from MySQL 8.0.x to 8.0.z is supported. MySQL 8.0.11 is the first GA status release within the MySQL 8.0 release series.

B）需要分析一下升级到 mysql 8.0 对你的应用的影响(如果老的版本是5.7)

https://dev.mysql.com/doc/refman/8.0/en/upgrading-from-previous-series.html
这里列出比较重要的几点：

默认权限插件的改变： mysql_native_password --》 caching_sha2_password
这个可能会导致应用程序连不上
SQL_MODE 默认值的改变：可能会导致应用的SQL语法解析报错

C）升级前对数据库进行全面的检查https://dev.mysql.com/doc/refman/8.0/en/upgrade-prerequisites.html
官网推荐用： mysql shell 的升级检查工具： mysqlsh> util.checkForServerUpgrade()D）升级前一定要对数据库进行全备份。
建议使用 xtrabackup 或者企业版的备份工具
接下来，我们要对我们MGR 集群进行滚动升级：

MGR集群信息如下：

root@localhost:mysql_jasonDB.sock [performance_schema]> select * from replication_group_members;
--------------
select * from replication_group_members
--------------




*************************** 1. row ***************************
             CHANNEL_NAME: group_replication_applier
                MEMBER_ID: db999999-0010-0025-0014-000000001931
              MEMBER_HOST: 10.29.234.19
              MEMBER_PORT: 3730
             MEMBER_STATE: ONLINE
              MEMBER_ROLE: SECONDARY
           MEMBER_VERSION: 8.0.35
MEMBER_COMMUNICATION_STACK: XCom
*************************** 2. row ***************************
             CHANNEL_NAME: group_replication_applier
                MEMBER_ID: db999999-0010-0025-0014-000000001941
              MEMBER_HOST: 10.29.234.20
              MEMBER_PORT: 3730
             MEMBER_STATE: ONLINE
              MEMBER_ROLE: SECONDARY
           MEMBER_VERSION: 8.0.35
MEMBER_COMMUNICATION_STACK: XCom
*************************** 3. row ***************************
             CHANNEL_NAME: group_replication_applier
                MEMBER_ID: db999999-0010-0025-0014-000000001951
              MEMBER_HOST: 10.29.234.18
              MEMBER_PORT: 3730
             MEMBER_STATE: ONLINE
              MEMBER_ROLE: PRIMARY
           MEMBER_VERSION: 8.0.35
MEMBER_COMMUNICATION_STACK: XCom
3 rows in set (0.00 sec)

复制

我们先升级2个备库：
升级部分我们参考： https://dev.mysql.com/doc/refman/8.0/en/upgrade-binary-package.html
官网给出大致9步的操作流程：
1. Review the information in Section 3.1, “Before You Begin”.
2. Ensure the upgrade readiness of your installation by completing the preliminary checks in Section 3.6, “Preparing Your Installation for Upgrade”.
3. If you use XA transactions with InnoDB, run XA RECOVER before upgrading to check for uncommitted XA transactions. If results are returned, either commit or rollback the XA transactions by issuing an XA COMMIT or XA ROLLBACK statement.
4. If you are upgrading from MySQL 5.7.11 or earlier to MySQL 8.0, and there are encrypted InnoDB tablespaces, rotate the keyring master key by executing this statement:

（以上4步，我们可以忽略，1.没有使用分布式事务 2. MYSQL 升级是 8.0.35 升级到 8.0.36, 不存在 mysql 5.7 到 mysql 8.0的大版本跳跃式升级）
5. If you normally run your MySQL server configured with innodb_fast_shutdown set to 2 (cold shutdown), configure it to perform a fast or slow shutdown by executing either of these statements:
（第5步，简单的说就是不要设置 cold shutdown 既 innodb_fast_shutdown = 2，即 fast shutdown 和 slow shutdown 均可以）
关于 innodb_fast_shutdown 的解释：

简单的说除非在紧急的情况下，实现快速关闭数据库, 设置 innodb_fast_shutdown =2 冷关闭的方式会引起数据损坏的风险。
我们standby 实例上默认是1，无需改动

root@localhost:mysql_jasonDB.sock [performance_schema]> select @@innodb_fast_shutdown;
--------------
select @@innodb_fast_shutdown
--------------


+------------------------+
| @@innodb_fast_shutdown |
+------------------------+
|                      1 |
+------------------------+
1 row in set (0.00 sec)

复制

Shutdown the old MySQL server.
我们关闭standby 数据库：

root@localhost:mysql_jasonDB.sock [performance_schema]> shutdown;
--------------
shutdown
--------------


Query OK, 0 rows affected (0.00 sec)

复制

7.Upgrade the MySQL binaries or packages. If upgrading a binary installation, unpack the new MySQL binary distribution package. See Obtain and Unpack the Distribution. For package-based installations, install the new packages.

我们把下载的percona mysql 8.0.36版本解压到一个全新的路径

sudo tar -xvf /data/software/Percona-Server-8.0.36.tar.gz -C /data
sudo mv /data/Percona-Server-8.0.36-28-Linux.x86_64.glibc2.17 /data/percona8.0.36
sudo chown -R mysql:mysql  /data/percona8.0.36

复制

这里需要值得注意：如果升级前的mysql 实例配置了 TDE 功能的 keyring 组件，需要在解压后的新版本的软件目录添加文件：
拷贝manifest 文件从从老版本的percona 软件 bin目录下： mysqld.my
（ cp /data/percona8.0.35/bin/mysqld.my 到 /data/percona8.0.36/bin/mysqld.my）

INFRA [mysql@dc02psqldbuat05 bin]# cp /data/percona8.0.35/bin/mysqld.my /data/percona8.0.36/bin/

复制

1）如果是 global 模式的：
a)需要从老版本的percona 软件lib\plugin目录下拷贝： component_keyring_file.cnf 和 component_keyring_file 和到新的软件目录下
（ cp /data/percona8.0.35/lib/plugin 到 /data/percona8.0.36/lib/plugin）
2）如果是local 模式的：只需要从老版本的percona 软件目录下拷贝： component_keyring_file.cnf 到新的软件目录下
（ cp /data/percona8.0.35/lib/plugin 到 /data/percona8.0.36/lib/plugin）
我这里是local 实例级别的加密方式:

INFRA [mysql@dc02psqldbuat05 plugin]# cp /data/percona8.0.35/lib/plugin/component_keyring_file.cnf /data/percona8.0.36/lib/plugin/component_keyring_file.cnf
INFRA [mysql@dc02psqldbuat05 plugin]# cat /data/percona8.0.36/lib/plugin/component_keyring_file.cnf
{
 "read_local_config": true
}

复制

Start the MySQL 8.0 server, using the existing data directory
第八步，我们用软件/data/percona8.0.36 启动刚才关闭的standby database


/data/percona8.0.36/bin/mysqld_safe --defaults-file=/data/jasonDB/my_jasonDB.cnf  --user=mysql > /dev/null  2>&1 &
复制

我们观察数据库日志：数据字典的升级信息： Server upgrade from ‘80035’ to ‘80036’ completed.

2024-12-30T17:06:52.086146+08:00 7 [Note] [MY-012486] [InnoDB] DDL log post ddl : end for thread id : 7
2024-12-30T17:06:52.601156+08:00 7 [Note] [MY-013400] [Server] Upgrade of help tables completed.
2024-12-30T17:06:52.601490+08:00 7 [Note] [MY-013394] [Server] Checking 'mysql' schema.
2024-12-30T17:06:52.755902+08:00 7 [Note] [MY-013394] [Server] Checking 'sys' schema.
2024-12-30T17:06:52.826592+08:00 7 [System] [MY-013381] [Server] Server upgrade from '80035' to '80036' completed.

复制

我们观察MGR集群的状态：一个standby 节点已经变成 MEMBER_VERSION: 8.0.36

root@localhost:mysql_jasonDB.sock [performance_schema]> select * from replication_group_members;
--------------
select * from replication_group_members
--------------


*************************** 1. row ***************************
             CHANNEL_NAME: group_replication_applier
                MEMBER_ID: db999999-0010-0025-0014-000000001931
              MEMBER_HOST: 10.29.234.19
              MEMBER_PORT: 3730
             MEMBER_STATE: ONLINE
              MEMBER_ROLE: SECONDARY
           MEMBER_VERSION: 8.0.36
MEMBER_COMMUNICATION_STACK: XCom
*************************** 2. row ***************************
             CHANNEL_NAME: group_replication_applier
                MEMBER_ID: db999999-0010-0025-0014-000000001941
              MEMBER_HOST: 10.29.234.20
              MEMBER_PORT: 3730
             MEMBER_STATE: ONLINE
              MEMBER_ROLE: SECONDARY
           MEMBER_VERSION: 8.0.35
MEMBER_COMMUNICATION_STACK: XCom
*************************** 3. row ***************************
             CHANNEL_NAME: group_replication_applier
                MEMBER_ID: db999999-0010-0025-0014-000000001951
              MEMBER_HOST: 10.29.234.18
              MEMBER_PORT: 3730
             MEMBER_STATE: ONLINE
              MEMBER_ROLE: PRIMARY
           MEMBER_VERSION: 8.0.35
MEMBER_COMMUNICATION_STACK: XCom
3 rows in set (0.00 sec)

复制

9.我们重复步骤6-8步骤，升级另外一个standby DB

root@localhost:mysql_jasonDB.sock [performance_schema]> select @@innodb_fast_shutdown;
--------------
select @@innodb_fast_shutdown
--------------


+------------------------+
| @@innodb_fast_shutdown |
+------------------------+
|                      1 |
+------------------------+
1 row in set (0.00 sec)


root@localhost:mysql_jasonDB.sock [performance_schema]> shutdown;
--------------
shutdown
--------------


Query OK, 0 rows affected (0.00 sec)

sudo tar -xvf /data/software/Percona-Server-8.0.36.tar.gz -C /data
sudo mv /data/Percona-Server-8.0.36-28-Linux.x86_64.glibc2.17 /data/percona8.0.36
sudo chown -R mysql:mysql  /data/percona8.0.36

cp /data/percona8.0.35/lib/plugin/component_keyring_file.cnf /data/percona8.0.36/lib/plugin/component_keyring_file.cnf
cp /data/percona8.0.35/bin/mysqld.my /data/percona8.0.36/bin/
/data/percona8.0.36/bin/mysqld_safe --defaults-file=/data/jasonDB/my_jasonDB.cnf  --user=mysql > /dev/null  2>&1 &

复制

我们验证一下standby 升级后的数据库版本：

root@localhost:mysql_jasonDB.sock [performance_schema]> select @@version;
--------------
select @@version
--------------


+-----------+
| @@version |
+-----------+
| 8.0.36-28 |
+-----------+
1 row in set (0.00 sec)

复制

最后我们升级一下主库，我们需要做一个switchover 切换一下：
我们看到直接切换 group_replication_set_as_primary，会报一个错误： The appointed primary member has a version that is greater than the one of some of the members in the group.


root@localhost:mysql_jasonDB.sock [performance_schema]> select group_replication_set_as_primary('db999999-0010-0025-0014-000000001931');
--------------
select group_replication_set_as_primary('db999999-0010-0025-0014-000000001931')
--------------


ERROR 3910 (HY000): The function 'group_replication_set_as_primary' failed. Error processing configuration start message: The appointed primary member has a version that is greater than the one of some of the members in the group.
root@localhost:mysql_jasonDB.sock [performance_schema]> select * from replication_group_members;
复制

如何解决这个问题？我们查询一下官网文档关于mgr online rolling upgrade 帮助文档：

https://dev.mysql.com/doc/refman/8.0/en/group-replication-online-upgrade-methods.html

Rolling In-Group Upgrade
This method is supported provided that servers running a newer version are not generating workload to the group while there are still servers with an older version in it. In other words servers with a newer version can join the group only as secondaries. In this method there is only ever one group, and each server instance is removed from the group, upgraded and then rejoined to the group.
（官方文档上已经说明了新版的实例不能产生workload 向老版本的实例同步，换句话说就是升级后的新版本实例只能以standby 的形式加入MGR复制集，建议每个mysql 实例升级的步骤是1. 退出MGR 组 2.升级数据库软件 3.重新添加到MGR组）
我们现在主库上退出MGR复制组：

root@localhost:mysql_jasonDB.sock [performance_schema]> stop group_replication;
--------------
stop group_replication
--------------
Query OK, 0 rows affected (16.65 sec)

复制

观察主库的状态： MEMBER_STATE: OFFLINE

root@localhost:mysql_jasonDB.sock [performance_schema]> select * from replication_group_members;
--------------
select * from replication_group_members
--------------


*************************** 1. row ***************************
              CHANNEL_NAME: group_replication_applier
                 MEMBER_ID: db999999-0010-0025-0014-000000001951
               MEMBER_HOST: 10.29.234.18
               MEMBER_PORT: 3730
              MEMBER_STATE: OFFLINE
               MEMBER_ROLE:
            MEMBER_VERSION:
MEMBER_COMMUNICATION_STACK: XCom
1 row in set (0.00 sec)

复制

这个时候主库已经变成了只读的状态：

root@localhost:mysql_jasonDB.sock [performance_schema]> select @@read_only;
--------------
select @@read_only
--------------


+-------------+
| @@read_only |
+-------------+
|           1 |
+-------------+
1 row in set (0.00 sec)


root@localhost:mysql_jasonDB.sock [performance_schema]> select @@super_read_only;
--------------
select @@super_read_only
--------------


+-------------------+
| @@super_read_only |
+-------------------+
|                 1 |
+-------------------+
1 row in set (0.00 sec)

复制

我们观察一下MGR剩下2个成员的状态：已经有节点变成了新的主库

--------------
select * from replication_group_members
--------------


*************************** 1. row ***************************
              CHANNEL_NAME: group_replication_applier
                 MEMBER_ID: db999999-0010-0025-0014-000000001931
               MEMBER_HOST: 10.29.234.19
               MEMBER_PORT: 3730
              MEMBER_STATE: ONLINE
               MEMBER_ROLE: PRIMARY
            MEMBER_VERSION: 8.0.36
MEMBER_COMMUNICATION_STACK: XCom
*************************** 2. row ***************************
              CHANNEL_NAME: group_replication_applier
                 MEMBER_ID: db999999-0010-0025-0014-000000001941
               MEMBER_HOST: 10.29.234.20
               MEMBER_PORT: 3730
              MEMBER_STATE: ONLINE
               MEMBER_ROLE: SECONDARY
            MEMBER_VERSION: 8.0.36
MEMBER_COMMUNICATION_STACK: XCom
2 rows in set (0.00 sec)

复制

这个时候我们在老的主库上重复升级步骤6-8步骤:

root@localhost:mysql_jasonDB.sock [performance_schema]> select @@innodb_fast_shutdown;
--------------
select @@innodb_fast_shutdown
--------------




+------------------------+
| @@innodb_fast_shutdown |
+------------------------+
|                      1 |
+------------------------+
1 row in set (0.00 sec)



root@localhost:mysql_jasonDB.sock [performance_schema]> shutdown;
--------------
shutdown
--------------


Query OK, 0 rows affected (0.00 sec)


sudo tar -xvf /data/software/Percona-Server-8.0.36.tar.gz -C /data
sudo mv /data/Percona-Server-8.0.36-28-Linux.x86_64.glibc2.17 /data/percona8.0.36
sudo chown -R mysql:mysql  /data/percona8.0.36


cp /data/percona8.0.35/lib/plugin/component_keyring_file.cnf /data/percona8.0.36/lib/plugin/component_keyring_file.cnf
cp /data/percona8.0.35/bin/mysqld.my /data/percona8.0.36/bin/
/data/percona8.0.36/bin/mysqld_safe --defaults-file=/data/jasonDB/my_jasonDB.cnf  --user=mysql > /dev/null  2>&1 &

复制

我们检查一下集群的状态：

select * from replication_group_members
--------------


*************************** 1. row ***************************
              CHANNEL_NAME: group_replication_applier
                 MEMBER_ID: db999999-0010-0025-0014-000000001931
               MEMBER_HOST: 10.29.234.19
               MEMBER_PORT: 3730
              MEMBER_STATE: ONLINE
               MEMBER_ROLE: PRIMARY
            MEMBER_VERSION: 8.0.36
MEMBER_COMMUNICATION_STACK: XCom
*************************** 2. row ***************************
              CHANNEL_NAME: group_replication_applier
                 MEMBER_ID: db999999-0010-0025-0014-000000001941
               MEMBER_HOST: 10.29.234.20
               MEMBER_PORT: 3730
              MEMBER_STATE: ONLINE
               MEMBER_ROLE: SECONDARY
            MEMBER_VERSION: 8.0.36
MEMBER_COMMUNICATION_STACK: XCom
*************************** 3. row ***************************
              CHANNEL_NAME: group_replication_applier
                 MEMBER_ID: db999999-0010-0025-0014-000000001951
               MEMBER_HOST: 10.29.234.18
               MEMBER_PORT: 3730
              MEMBER_STATE: ONLINE
               MEMBER_ROLE: SECONDARY
            MEMBER_VERSION: 8.0.36
MEMBER_COMMUNICATION_STACK: XCom
3 rows in set (0.00 sec)

复制

如果有需要我们可以切回主库：

root@localhost:mysql_jasonDB.sock [performance_schema]> select group_replication_set_as_primary('db999999-0010-0025-0014-000000001951');
--------------
select group_replication_set_as_primary('db999999-0010-0025-0014-000000001951')
--------------


+--------------------------------------------------------------------------+
| group_replication_set_as_primary('db999999-0010-0025-0014-000000001951') |
+--------------------------------------------------------------------------+
| Primary server switched to: db999999-0010-0025-0014-000000001951         |
+--------------------------------------------------------------------------+
1 row in set (0.02 sec)

复制

至此我们实现了mysql mgr 8.0 小版本的滚动升级。
总结一下：
1.选择合适的mysql 小版本，避免踩雷，建议升级到满足需求的最低版本来规避一些未知的风险。
需要详细参考一个每个小版本的release note: https://dev.mysql.com/doc/relnotes/mysql/8.0/en/

2.Mysql 小版本的升级，同时要考虑到周边的中间件, 备份软件是否也要升级：proxysql， xtrabackup 等等
3.mysql 小版本升级也需要开发团队和测试团队的参与，进行完全回归测试。
(Mysql 小版本之间也会带来功能性的变化呦！！！)
4.如果你使用了keyring 加密表的组件，记得要拷贝组件文件，manifest 到新的版本软件中
cp {{OLD_MYSQL}}/lib/plugin/component_keyring_file.cnf {{NEW_MYSQL}}//lib/plugin/component_keyring_file.cnf
cp{{OLD_MYSQL}}/bin/mysqld.my {{NEW_MYSQL}}/bin/
5.关于 MGR online rolling upgrade 参考官方的推荐方式：退出MGR 组 2.升级数据库软件 3.重新添加到MGR组
（如果使用函数group_replication_set_as_primary 切换到高版本的主库，会报错误）
Have a fun 🙂 ！

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

Mysql 8.0 MGR 小版本滚动升级

评论