因為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.
升級前準備
- 查看支持的平台
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)
- 下載安裝介質
這裏下載的安裝介質如下所示
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 1
MySQL 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 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.
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 ).
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 found
4) Checks for foreign keys not referencing a full unique index
(foreignKeyReferences)
No issues found
5) Check for deprecated or invalid user authentication methods.
(authMethodUsage)
No issues found
6) Check for deprecated or removed plugin usage. (pluginUsage)
No issues found
7) Check for deprecated or invalid default authentication methods in system
variables. (deprecatedDefaultAuth)
No issues found
8) Check for deprecated or invalid authentication methods in use by MySQL
Router internal accounts. (deprecatedRouterAuthMethod)
No issues found
9) Checks for errors in column definitions (columnDefinition)
No issues found
10) Check for allowed values in System Variables. (sysvarAllowedValues)
No issues found
11) Checks for user privileges that will be removed (invalidPrivileges)
Verifies for users containing grants to be removed as part of the upgrade
process.
Notice: The following users have the SET_USER_ID privilege which will be
removed 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: 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 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 shutdown
sudo 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 中的配置信息.
