MySQL 8.0.35 单实例升级到MySQL 8.4.x

因为MySQL 8.0.x版本即将于2026年4月结束服务支持，计划将手头MySQL 8.0.35升级到MySQL 8.4.8这个LTS版本. 下面是之前在测试环境的一些测试总结.仅供参考!

注意：之前官方只提供了MySQL 8.4.5,所以下面测试案例为MySQL 8.4.5,生产环境建议升级到8.4.8 LTS这个版本. 两者升级方式其实是一样的.

升级到MySQL 8.4.5有下面三种方式.我们打算以In-Place方式直接升级到MySQL8.4.5。

There are three main ways to upgrade MySQL; read the associated documentation to determine which type of upgrade is best suited for your situation.- An in-place upgrade: replacing the MySQL Server packages.- A logical upgrade: exporting SQL from the old MySQL instance to the new.- A replication topology upgrade: account for each server's topology role.

升级前准备

1. 查看支持的平台

MySQL 8.4.*支持的平台可查看官方文档，官方文档位置：

https://www.mysql.com/support/supportedplatforms/database.html

当前的操作系统平台为RHEL 8.10, MySQL 8.4是支持的.

$ more /etc/redhat-release 
Red Hat Enterprise Linux release 8.10 (Ootpa)

2. 下载安装介质

这里下载的安装介质如下所示

mysql-8.4.5-linux-glibc2.28-x86_64-minimal.tar.xz
mysql-shell-8.4.5-linux-glibc2.28-x86-64bit.tar.gz
mysql-router-8.4.5-linux-glibc2.28-x86_64.tar.xz
mysql-shell-8.4.5-1.el8.x86_64.rpm

因为我们的MySQL数据库都是二进制方式安装的. 所以本文只关注MySQL二进制安装方式的升级. 其它方式本文不做考虑.

环境检查

检查数据库版本

select @@version;

mysql> select @@version;
+-----------+
| @@version |
+-----------+
| 8.0.35    |
+-----------+
1 row in set (0.01 sec)mysql> 

检查MySQL Shell版本信息

$ mysqlsh --version
mysqlsh   Ver 8.0.35 for Linux on x86_64 - for MySQL 8.0.35 (MySQL Community Server (GPL))

如果单机环境没有安装MySQL Sell的话,也建议安装MySQL Shell工具,感觉这个工具非常好用.

MySQL Shell升级

首先,你应该判断MySQL Shell的安装方式, 然后根据安装方式(二进制包/源码/RPM包)选择下载对应的MySQL Shell安装介质. 如果没有安装MySQL Shell,则直接跳过这一步.

$ rpm -qa | grep mysql-shell
mysql-shell-8.0.35-1.el8.x86_64

如上所示,这里判断MySQL Shell是RPM包安装方式

备份Shell配置文件（如~/.mysqlsh/目录）

~/.mysqlsh

RMP包升级

不同的Linux发行版本,可能使用不同的安装方式

sudo rpm -Uvh mysql-shell-<version>.rpm (RHEL)
sudo dpkg -i mysql-shell_<version>.deb (Debian)

下面是实际环境升级例子：

rpm -Uvh mysql-shell-8.4.5-1.el8.x86_64.rpm
或
sudo rpm -Uvh mysql-shell-8.4.5-1.el8.x86_64.rpm

验证安装成功后的MySQL Shell版本

$ mysqlsh --version
mysqlsh   Ver 8.4.5 for Linux on x86_64 - for MySQL 8.4.5 (MySQL Community Server (GPL))

二进制包升级

解压MySQL Shell的二进制包到指定目录(/opt/mysql/)

tar -xvf mysql-shell-8.4.5-linux-glibc2.28-x86-64bit.tar.gz -C /opt/mysql/
cd /opt/mysql/
mv mysql-shell-8.4.5-linux-glibc2.28-x86-64bit mysqlsh

检查/修改配置环境变量~/.bash_profile

export PATH=$PATH:/opt/mysql/mysqlsh/bin

执行下面命令使之生效,并检查MySQL Shell版本信息. 如果之前的路径一致的情况下,可以跳过这个步骤.

$ source ~/.bash_profile

简单总结如下:

$ cd /opt/mysql/
$ mv mysqlsh mysqlsh.20260120
$ cd /data/soft/
$ tar -xvf mysql-shell-8.4.5-linux-glibc2.28-x86-64bit.tar.gz -C /opt/mysql
$ cd /opt/mysql/
$ mv mysql-shell-8.4.5-linux-glibc2.28-x86-64bit mysqlsh

MySQL Server升级

使用MySQL Shell检查升级具体事项,如下例子所示:

 MySQL  dbtest04:3306 ssl  SQL > util.checkForServerUpgrade();
ERROR: 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'util.checkForServerUpgrade()' at line 1MySQL  dbtest04:3306 ssl  SQL > MySQL  dbtest04:3306 ssl  SQL > MySQL  dbtest04:3306 ssl  SQL > MySQL  dbtest04:3306 ssl  SQL > MySQL  dbtest04:3306 ssl  SQL > \js
Switching to JavaScript mode...MySQL  dbtest04:3306 ssl  JS > util.checkForServerUpgrade()
The MySQL server at dbtest04:3306, version 8.0.33 - MySQL Community Server -
GPL, will now be checked for compatibility issues for upgrade to MySQL 8.4.4.
To check for a different target server version, use the targetVersion option.1) Removed system variables (removedSysVars)No issues found2) System variables with new default values (sysVarsNewDefaults)Warning: Following system variables that are not defined in yourconfiguration file will have new default values. Please review if you relyon their current values and if so define them before performing upgrade.binlog_transaction_dependency_tracking - default value will change fromCOMMIT_ORDER to WRITESET.group_replication_consistency - default value will change from EVENTUAL toBEFORE_ON_PRIMARY_FAILOVER.group_replication_exit_state_action - default value will change fromREAD_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 ifinnodb_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 frominnodb_buffer_pool_instances * 2 to 2.innodb_doublewrite_pages - default value will change frominnodb_write_io_threads to 128.innodb_flush_method - default value will change from fsynch (unix) orunbuffered (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 xinnodb_io_capacity.innodb_log_buffer_size - default value will change from 16777216 (16MB) to67108864 (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 toinnodb_buffer_pool_instances.innodb_parallel_read_threads - default value will change from 4 toMAX(#vcpu/8, 4).innodb_purge_threads - default value will change from 4 to 1 ( if #vcpu <=16 ).More information:https://dev.mysql.com/blog-archive/new-defaults-in-mysql-8-0/3) Issues reported by 'check table x for upgrade' command (checkTableCommand)No issues found4) Checks for foreign keys not referencing a full unique index
(foreignKeyReferences)No issues found5) Check for deprecated or invalid user authentication methods.
(authMethodUsage)No issues found6) Check for deprecated or removed plugin usage. (pluginUsage)No issues found7) Check for deprecated or invalid default authentication methods in system
variables. (deprecatedDefaultAuth)No issues found8) Check for deprecated or invalid authentication methods in use by MySQL
Router internal accounts. (deprecatedRouterAuthMethod)No issues found9) Checks for errors in column definitions (columnDefinition)No issues found10) Check for allowed values in System Variables. (sysvarAllowedValues)No issues found11) Checks for user privileges that will be removed (invalidPrivileges)Verifies for users containing grants to be removed as part of the upgradeprocess.Notice: The following users have the SET_USER_ID privilege which will beremoved as part of the upgrade process:- 'root'@'localhost'Solution:- If the privileges are not being used, no action is required, otherwise,ensure they stop being used before the upgrade as they will be lost.12) Checks for partitions by key using columns with prefix key indexes
(partitionsWithPrefixKeys)No issues found
Errors:   0
Warnings: 18
Notices:  2NOTE: 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  dbtest04:3306 ssl  JS > 

关于升级到MySQL 8.4.5的MySQL参数变化以及调整,请参考官方文档. 这里不做过多阐述. 根据实际情况进行调整.

停止应用程序

此处略过.一般都有对应的流程与规范. 单机环境要确保业务已停止，保证业务数据的一致性.

确认应用是否停止

show processlist;

备份MySQL数据库

执行下面命令备份MySQL数据库.

/home/mysql/db_monitor/scripts/mysql_backup full

准备工作

检查有没有未提交的xa事务

mysql> xa recover;
Empty set (0.00 sec)

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.

这一步感觉没有必要处理, 一般来说,我都会跳过.官方文档建议将此参数innodb_fast_shutdown调整为2

SET GLOBAL innodb_fast_shutdown = 1; -- fast shutdown
SET GLOBAL innodb_fast_shutdown = 0; -- slow shutdown
SET GLOBAL innodb_fast_shutdown = 2; -- cold shutdown

关闭数据库服务

sudo systemctl stop mysqld.service
或
mysqladmin -u root -p shutdownsudo systemctl status mysqld.service

安装升级MySQL Server

$ tar -xvf mysql-8.4.5-linux-glibc2.28-x86_64.tar.xz  -C /opt/mysql/
$ cd /opt/mysql/
$ unlink  mysql8.0
$ ln -s /opt/mysql/mysql-8.4.5-linux-glibc2.28-x86_64 mysql8

保留之前的MySQL版本, 另外,修改环境变量和systemctl服务. 注意有些环境不用下面步骤, 这个测试环境的软连接为/opt/mysql/mysql8.0, 这里统一成规范/opt/mysql/mysql8

下面是安装之前的文件信息.

$ ls -lrt /opt/mysql/
total 0
drwxrwxr-x 9 mysql mysql 129 Mar  1  2024 mysql-8.0.35-linux-glibc2.28-x86_64
lrwxrwxrwx 1 mysql mysql  46 Mar  1  2024 mysql8.0 -> /opt/mysql/mysql-8.0.35-linux-glibc2.28-x86_64

$ vi ~/.bash_profile
# .bash_profile# Get the aliases and functions
if [ -f ~/.bashrc ]; then. ~/.bashrc
fi# User specific environment and startup programs
#export PATH=$PATH:/opt/mysql/mysql8.0/bin
export PATH=$PATH:/opt/mysql/mysql8/bin
export MYSQL_HISTFILE=/dev/null

source ~/.bash_profile 使之生效

vi /usr/lib/systemd/system/mysqld.service调整前:# Start main service
ExecStart=/opt/mysql/mysql8.0/bin/mysqld --defaults-file=/data/mysql/conf/my.cnf $MYSQLD_OPTS调整后# Start main service
ExecStart=/opt/mysql/mysql8/bin/mysqld --defaults-file=/data/mysql/conf/my.cnf $MYSQLD_OPTS

执行下面命令重新加载配置

systemctl daemon-reload

修改配置文件my.cnf

$ grep 8.0 my.cnf 
basedir=/opt/mysql/mysql8.0
plugin_dir=/opt/mysql/mysql8.0/lib/plugin  
lc-messages-dir=/opt/mysql/mysql8.0/share
#MySQL8.0就是这么做的，在设置了--skip-grant-tables参数的同时会自动开启--skip-networking。

修改后检查确认

$ grep 'mysql8' my.cnf 
basedir=/opt/mysql/mysql8
plugin_dir=/opt/mysql/mysql8/lib/plugin  
lc-messages-dir=/opt/mysql/mysql8/share

启动MySQL服务

sudo systemctl start mysqld.service

升级Xtrabackup

# rpm -Uvh percona-xtrabackup-84-8.4.0-3.1.el8.x86_64.rpm
warning: percona-xtrabackup-84-8.4.0-3.1.el8.x86_64.rpm: Header V4 RSA/SHA512 Signature, key ID 8507efa5: NOKEY
error: Failed dependencies:percona-xtrabackup-80 conflicts with percona-xtrabackup-84-8.4.0-3.1.el8.x86_64# rpm -evh percona-xtrabackup-80.x86_64
Preparing...                          ################################# [100%]
Cleaning up / removing...1:percona-xtrabackup-80-8.0.35-30.1################################# [100%]
#yum localinstall percona-xtrabackup-84-8.4.0-3.1.el8.x86_64.rpm

Zabbix配置更新

在/etc/zabbix/zabbix_agentd.d中更新模板template_db_mysql.conf 中的配置信息.