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。

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

复制代码

升级前准备

• 查看支持的平台
  

MySQL 8.4.*支持的平台可查看官方文档，官方文档位置：
https://www.mysql.com/support/supportedplatforms/database.html
当前的操作系统平台为RHEL 8.10, MySQL 8.4是支持的.

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

复制代码

• 下载安装介质
  

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

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

复制代码

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

检查数据库版本

1. select @@version;

复制代码

1. mysql> select @@version;
2. +-----------+
3. | @@version |
4. +-----------+
5. | 8.0.35    |
6. +-----------+
7. 1 row in set (0.01 sec)
9. mysql>

复制代码

检查MySQL Shell版本信息

1. $ mysqlsh --version
2. 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,则直接跳过这一步.

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

复制代码

如上所示,这里判断MySQL Shell是RPM包安装方式
备份Shell配置文件（如~/.mysqlsh/目录）
~/.mysqlsh
RMP包升级

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

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

复制代码

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

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

复制代码

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

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

复制代码

二进制包升级

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

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

复制代码

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

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

复制代码

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

1. $ source ~/.bash_profile

复制代码

简单总结如下:

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

复制代码

MySQL Server升级

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

1. MySQL  dbtest04:3306 ssl  SQL > util.checkForServerUpgrade();
2. 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 1
3. MySQL  dbtest04:3306 ssl  SQL >
4. MySQL  dbtest04:3306 ssl  SQL >
5. MySQL  dbtest04:3306 ssl  SQL >
6. MySQL  dbtest04:3306 ssl  SQL >
7. MySQL  dbtest04:3306 ssl  SQL > \js
8. Switching to JavaScript mode...
9. MySQL  dbtest04:3306 ssl  JS > util.checkForServerUpgrade()
10. The MySQL server at dbtest04:3306, version 8.0.33 - MySQL Community Server -
11. GPL, will now be checked for compatibility issues for upgrade to MySQL 8.4.4.
12. To check for a different target server version, use the targetVersion option.
14. 1) Removed system variables (removedSysVars)
15.    No issues found
17. 2) System variables with new default values (sysVarsNewDefaults)
18.    Warning: Following system variables that are not defined in your
19.    configuration file will have new default values. Please review if you rely
20.    on their current values and if so define them before performing upgrade.
22.    binlog_transaction_dependency_tracking - default value will change from
23.       COMMIT_ORDER to WRITESET.
24.    group_replication_consistency - default value will change from EVENTUAL to
25.       BEFORE_ON_PRIMARY_FAILOVER.
26.    group_replication_exit_state_action - default value will change from
27.       READ_ONLY to OFFLINE_MODE.
28.    innodb_adaptive_hash_index - default value will change from ON to OFF.
29.    innodb_buffer_pool_in_core_file - default value will change from ON to OFF.
30.    innodb_buffer_pool_instances - default value will change from 8 (or 1 if
31.       innodb_buffer_pool_size < 1GB) to MAX(1, #vcpu/4).
32.    innodb_change_buffering - default value will change from all to none.
33.    innodb_doublewrite_files - default value will change from
34.       innodb_buffer_pool_instances * 2 to 2.
35.    innodb_doublewrite_pages - default value will change from
36.       innodb_write_io_threads to 128.
37.    innodb_flush_method - default value will change from fsynch (unix) or
38.       unbuffered (windows) to O_DIRECT.
39.    innodb_io_capacity - default value will change from 200 to 10000.
40.    innodb_io_capacity_max - default value will change from 200 to 2 x
41.       innodb_io_capacity.
42.    innodb_log_buffer_size - default value will change from 16777216 (16MB) to
43.       67108864 (64MB).
44.    innodb_log_writer_threads - default value will change from ON to OFF ( if
45.       #vcpu <= 32 ).
46.    innodb_numa_interleave - default value will change from OFF to ON.
47.    innodb_page_cleaners - default value will change from 4 to
48.       innodb_buffer_pool_instances.
49.    innodb_parallel_read_threads - default value will change from 4 to
50.       MAX(#vcpu/8, 4).
51.    innodb_purge_threads - default value will change from 4 to 1 ( if #vcpu <=
52.       16 ).
54.    More information:
55.      https://dev.mysql.com/blog-archive/new-defaults-in-mysql-8-0/
58. 3) Issues reported by 'check table x for upgrade' command (checkTableCommand)
59.    No issues found
61. 4) Checks for foreign keys not referencing a full unique index
62. (foreignKeyReferences)
63.    No issues found
65. 5) Check for deprecated or invalid user authentication methods.
66. (authMethodUsage)
67.    No issues found
69. 6) Check for deprecated or removed plugin usage. (pluginUsage)
70.    No issues found
72. 7) Check for deprecated or invalid default authentication methods in system
73. variables. (deprecatedDefaultAuth)
74.    No issues found
76. 8) Check for deprecated or invalid authentication methods in use by MySQL
77. Router internal accounts. (deprecatedRouterAuthMethod)
78.    No issues found
80. 9) Checks for errors in column definitions (columnDefinition)
81.    No issues found
83. 10) Check for allowed values in System Variables. (sysvarAllowedValues)
84.    No issues found
86. 11) Checks for user privileges that will be removed (invalidPrivileges)
87.    Verifies for users containing grants to be removed as part of the upgrade
88.    process.
90.    Notice: The following users have the SET_USER_ID privilege which will be
91.    removed as part of the upgrade process:
92.    - 'root'@'localhost'
94.    Solution:
95.    - If the privileges are not being used, no action is required, otherwise,
96.       ensure they stop being used before the upgrade as they will be lost.
100. 12) Checks for partitions by key using columns with prefix key indexes
101. (partitionsWithPrefixKeys)
102.    No issues found
103. Errors:   0
104. Warnings: 18
105. Notices:  2
107. 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.
108. MySQL  dbtest04:3306 ssl  JS >

复制代码

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

1. show processlist;

复制代码

关闭数据库服务

1. /home/mysql/db_monitor/scripts/mysql_backup full

复制代码

安装升级MySQL Server

1. mysql> xa recover;
2. Empty set (0.00 sec)

复制代码

保留之前的MySQL版本, 另外,修改环境变量和systemctl服务. 注意有些环境不用下面步骤, 这个测试环境的软连接为/opt/mysql/mysql8.0, 这里统一成规范/opt/mysql/mysql8,所以才要这么多额外操作.如果提前规范好，则可省略很多步骤。
下面是安装之前的文件信息.

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

复制代码

1. sudo systemctl stop mysqld.service
2. 或
3. mysqladmin -u root -p shutdown
5. sudo systemctl status mysqld.service

复制代码

source ~/.bash_profile 使之生效

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

复制代码

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

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

复制代码

修改配置文件my.cnf

1. $ vi ~/.bash_profile
2. # .bash_profile
4. # Get the aliases and functions
5. if [ -f ~/.bashrc ]; then
6.         . ~/.bashrc
7. fi
9. # User specific environment and startup programs
10. #export PATH=$PATH:/opt/mysql/mysql8.0/bin
11. export PATH=$PATH:/opt/mysql/mysql8/bin
12. export MYSQL_HISTFILE=/dev/null

复制代码

修改后检查确认

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

复制代码

启动MySQL服务

1. systemctl daemon-reload

复制代码

升级Xtrabackup

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

复制代码

Zabbix配置更新

在/etc/zabbix/zabbix_agentd.d中更新模板template_db_mysql.conf 中的配置信息.
扫描上面二维码关注我如果你真心觉得文章写得不错，而且对你有所帮助，那就不妨帮忙“推荐"一下，您的“推荐”和”打赏“将是我最大的写作动力！本文版权归作者所有，欢迎转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出原文连接.
来源：程序园用户自行投稿发布，如果侵权，请联系站长删除
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！

懂技术并乐意极积无私分享的人越来越少。珍惜

收藏一下   不知道什么时候能用到