作者：
蔡柱梁
在這個月的某個項目中，我們面臨了一項重要任務，即每年一次的等保測評整改。這次測評的重點是MySQL的一些高危漏洞，我們被要求解決這些漏洞。因此，我們決定研究一下MySQL5.7升級到MySQL8.0的過程。在本文的最后，我將分享一些在這次升級過程中遇到的問題。
作者 | 蔡柱梁
審校 | 重樓
1.前言
在這個月的某個項目中，我們面臨了一項重要任務，即每年一次的等保測評整改。這次測評的重點是MySQL的一些高危漏洞，我們被要求解決這些漏洞。因此，我們決定研究一下MySQL5.7升級到MySQL8.0的過程。在本文的最后，我將分享一些在這次升級過程中遇到的問題。
升級需求：將5.7.43升級到8.0.34, 升級方式 in-place升級【關閉現有版本MySQL，將二進制或包替換成新版本并在現有數據目錄上啟動MySQL并執行升級任務的方式，稱為in-place升級】
原版本 | 5.7.43 | CentOS Linux release 7.9.2009 |
新版本 | 8.0.34 | CentOS Linux release 7.9.2009 |
2.MySQL生命周期
以下MySQL生命周期-內容來自于互聯網
從Oracle發布的版本生命周期規劃可以看到，MySQL 5.7已經走到了生命周期的終點，意味著后續將不再為MySQL 5.7提供官方更新、錯誤修復或安全補丁。
3.MySQL 8.0的新特性
- 默認字符集由latin1變為utf8mb4
- MyISAM系統表全部換成InnoDB表
- JSON特性增強
- 支持不可見索引，支持直方圖
- sql_mode參數默認值變化
- 默認密碼策略變更
- 新增角色管理
- 支持窗口函數，支持Hash join
4.升級建議
支持從MySQL 5.7升級到MySQL 8.0，注意僅支持GA版本之間的升級。
不支持跨大版本的升級，如從5.6升級到8.0是不支持的。
建議升級大版本前先升級到當前版本的最近小版本，如5.7先升級到5.7.43后再升級到8.0。
做好充足的備份！數據無價！
5.升級前準備
5.1 MySQL-shell 檢查工具兼容性
在執行升級操作前需要做一些檢查工作，確認準備工作是否就緒，避免升級過程中出現異常。MySQL Shell使用util.checkForServerUpgrade進行檢查，返回內容包括不符合遷移要求的問題，error的問題需要遷移前修改。
MySQL-shell 下載地址：https://dev.mysql.com/downloads/shell/
選擇 Archives ，查詢更多版本。
選擇當前最新的版本8.0.34，x84,64-bit 。
#下載包
[root@srebro.cn ~]# wget https://downloads.mysql.com/archives/get/p/43/file/mysql-shell-8.0.34-linux-glibc2.12-x86-64bit.tar.gz -C /root
[root@srebro.cn ~]# tar -xf mysql-shell-8.0.34-linux-glibc2.12-x86-64bit.tar.gz
[root@srebro.cn ~]# cd /root/mysql-shell-8.0.34-linux-glibc2.12-x86-64bit/bin
[root@srebro.cn bin]# ./mysqlsh -uroot -p -S /tmp/mysql.sock -e "util.checkForServerUpgrade()" > util.checkForServerUpgrade.log輸出報告
The MySQL server at /tmp%2Fmysql.sock, version 5.7.39-log - MySQL Community
Server (GPL), will now be checked for compatibility issues for upgrade to MySQL
8.0.34...
1) Usage of old temporal type
No issues found
2) MySQL 8.0 syntax check for routine-like objects
No issues found
3) Usage of db objects with names conflicting with new reserved keywords
No issues found
4) Usage of utf8mb3 charset
Warning: The following objects use the utf8mb3 character set. It is
recommended to convert them to use utf8mb4 instead, for improved Unicode
support.
More information:
https://dev.mysql.com/doc/refman/8.0/en/charset-unicode-utf8mb3.html
mysql - schema's default character set: utf8
test - schema's default character set: utf8
5) Table names in the mysql schema conflicting with new tables in 8.0
No issues found
6) Partitioned tables using engines with non native partitioning
No issues found
7) Foreign key constraint names longer than 64 characters
No issues found
8) Usage of obsolete MAXDB sql_mode flag
No issues found
9) Usage of obsolete sql_mode flags
Notice: The following DB objects have obsolete options persisted for
sql_mode, which will be cleared during upgrade to 8.0.
More information:
https://dev.mysql.com/doc/refman/8.0/en/mysql-nutshell.html#mysql-nutshell-removals
global system variable sql_mode - defined using obsolete NO_AUTO_CREATE_USER
option
10) ENUM/SET column definitions containing elements longer than 255 characters
No issues found
11) Usage of partitioned tables in shared tablespaces
No issues found
12) Circular directory references in tablespace data file paths
No issues found
13) Usage of removed functions
No issues found
14) Usage of removed GROUP BY ASC/DESC syntax
No issues found
15) Removed system variables for error logging to the system log configuration
To run this check requires full path to MySQL server configuration file to be specified at 'configPath' key of options dictionary
More information:
https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-13.html#mysqld-8-0-13-logging
16) Removed system variables
To run this check requires full path to MySQL server configuration file to be specified at 'configPath' key of options dictionary
More information:
https://dev.mysql.com/doc/refman/8.0/en/added-deprecated-removed.html#optvars-removed
17) System variables with new default values
To run this check requires full path to MySQL server configuration file to be specified at 'configPath' key of options dictionary
More information:
https://mysqlserverteam.com/new-defaults-in-mysql-8-0/
18) Zero Date, Datetime, and Timestamp values
No issues found
19) Schema inconsistencies resulting from file removal or corruption
No issues found
20) Tables recognized by InnoDB that belong to a different engine
No issues found
21) Issues reported by 'check table x for upgrade' command
No issues found
22) New default authentication plugin considerations
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:
[mysqld]
default_authentication_plugin=mysql_native_password
However, 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.
If you are using replication please take time to understand how the
authentication plugin changes may impact you.
More information:
https://dev.mysql.com/doc/refman/8.0/en/upgrading-from-previous-series.html#upgrade-caching-sha2-password-compatibility-issues
https://dev.mysql.com/doc/refman/8.0/en/upgrading-from-previous-series.html#upgrade-caching-sha2-password-replication
23) Columns which cannot have default values
No issues found
24) Check for invalid table names and schema names used in 5.7
No issues found
25) Check for orphaned routines in 5.7
No issues found
26) Check for deprecated usage of single dollar signs in object names
No issues found
27) Check for indexes that are too large to work on higher versions of MySQL
Server than 5.7
No issues found
28) Check for deprecated '.<table>' syntax used in routines.
No issues found
Errors: 0
Warnings: 3
Notices: 1
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.從輸出報告可以看出，升級檢查器在28個方面進行了檢查，最終得出3個警告信息和1個提示。
消除警告：
Usage of utf8mb3 charset 在 MySQL 8.0版本之前，默認字符集為latin1 ，utf8字符集指向的是utf8mb3 。從MySQL8.0開始，數據庫的默認編碼將改為utf8mb4 ；為了避免新舊對象字符集不一致的情況，可以在配置文件將字符集和校驗規則設置為舊版本的字符集和比較規則。
New default authentication plugin considerations，密碼認證插件變更。為了避免連接問題，可以仍采用5.7的mysql_native_password認證插件。
消除提示：
Usage of obsolete sql_mode flags：MySQL 8.0 版本sql_mode不支持NO_AUTO_CREATE_USER，要避免配置的sql_mode中帶有NO_AUTO_CREATE_USER。
通過以上的例子，可以發現，MySQL Shell提供的升級檢查工具能夠幫助我們檢測版本兼容性，減輕升級工作負擔。
5.2 邏輯備份MySQL數據
which mysqldump
/home/application/mysql/app/bin/mysqldump
# --routines 備份存儲過程和函數；--set-gtid-purged=OFF: 禁用GTID（全局事務標識）;xxx1,XXX2 表示庫名，備份多個庫用空格做為間隔
/home/application/mysql/app/bin/mysqldump -uroot -p --routines --set-gtid-purged=OFF --databases XXX1 XXX2 > /root/all-database-20231026.sql5.3 優雅的停止數據庫
# 進入原5.7 mysql命令行 正確關閉數據庫
[root@srebro.cn ~]# mysql -uroot -p'srebro'
mysql> select version();
+------------+
| version() |
+------------+
| 5.7.43-log |
+------------+
1 row in set (0.00 sec)
mysql> show variables like 'innodb_fast_shutdown';
+----------------------+-------+
| Variable_name | Value |
+----------------------+-------+
| innodb_fast_shutdown | 1 |
+----------------------+-------+
1 row in set (0.00 sec)
# 確保數據都刷到硬盤上，更改成0InnoDB 關閉模式。
如果值為 0，InnoDB 會在關閉前進行緩慢關閉、完全清除和更改緩沖區合并。
如果值為 1（默認值），InnoDB 會在關閉時跳過這些操作，這個過程稱為快速關閉。
如果值為 2，InnoDB 刷新其日志并冷關機，就好像 MySQL 崩潰了；沒有提交的事務丟失，但崩潰恢復操作使下一次啟動需要更長的時間。在仍然緩沖大量數據的極端情況下，緩慢關閉可能需要幾分鐘甚至幾小時。
mysql> set global innodb_fast_shutdown=0;
Query OK, 0 rows affected (0.00 sec)
mysql> shutdown;
Query OK, 0 rows affected (0.00 sec)
mysql> exit
Bye
[root@cmdb ~]# ps -ef | grep mysql
root 30990 30934 0 16:12 pts/0 00:00:00 grep --color=auto mysql5.4 備份MySQL 數據目錄,安裝目錄和配置文件
--確認數據庫狀態為關閉狀態
[root@srebro.cn ~]# systemctl status mysqld
--數據目錄備份
[root@srebro.cn ~]# cp -r /home/application/mysql/data /home/application/mysql/data_bak_`date +%F`
--安裝目錄備份
[root@srebro.cn ~]# cp -r /home/application/mysql/app/ /home/application/mysql/app_bak_`date +%F`
--配置文件備份
[root@srebro.cn ~]# cp /etc/my.cnf /etc/my.cnf_`date +%F`
5.5 下載并解壓MySQL8.0
https://dev.mysql.com/downloads/
選擇 Archives ，查詢更多版本。
下載地址：https://downloads.mysql.com/archives/get/p/23/file/mysql-8.0.34-linux-glibc2.12-x86_64.tar.xz 選擇mysql-8.0.34-linux-glibc2.12-x86_64.tar.xz。
#執行以下步驟解壓tar包：
# 安裝包上傳至原安裝包目錄下 我的是/home/application/mysql
[root@srebro.cn ~]# cd /home/application/mysql
[root@srebro.cn mysql]# wget https://downloads.mysql.com/archives/get/p/23/file/mysql-8.0.34-linux-glibc2.12-x86_64.tar.xz
[root@srebro.cn mysql]# tar -xf mysql-8.0.34-linux-glibc2.12-x86_64.tar.xz
# 文件夾重命名為mysql8
[root@srebro.cn mysql]# mv mysql-8.0.34-linux-glibc2.12-x86_64 mysql8
# 更改文件夾所屬
[root@srebro.cn mysql]# chown -Rf mysql:mysql /home/application/mysql/mysql8
# 刪除安裝包
[root@srebro.cn mysql]# rm -rf mysql-8.0.34-linux-glibc2.12-x86_64.tar.xz6.升級
6.1 修改 my.cnf 配置文件
因5.7版本與8.0版本參數有所不同，為了能順利升級，我們需要更改部分配置參數。主要注意sql_mode、basedir、密碼認證插件及字符集設置，其他參數最好還是按照原5.7的來，不需要做調整。下面展示5.7和8.0的配置文件,注意備份原來配置文件。
6.1.1 MySQL5.7_my.cnf 配置文件
[mysql]
socket=/tmp/mysql.sock
default-character-set=utf8
[mysqld]
user=mysql
basedir=/home/application/mysql/app
datadir=/home/application/mysql/data
character_set_server=utf8
collation-server=utf8_general_ci
#日志時間
log_timestamps=SYSTEM
port=3306
socket=/tmp/mysql.sock
max_connections=1000
max_allowed_packet=500M
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
#慢日志
long_query_time=3
slow_query_log=ON
slow_query_log_file=/home/application/mysql/slow_query.log
#錯誤日志
log-error=/home/application/mysql/mysql-error.log
#binlog配置
server_id=150
log-bin=mysql-bin
max_binlog_size = 100M
binlog_format=row
log_slave_updates
expire_logs_days=7
#只能用IP地址檢查客戶端的登錄，不用主機名
skip-name-resolve=16.1.2 MySQL8.0_my.cnf 配置文件
[mysql]
socket=/tmp/mysql.sock
default-character-set=utf8
[mysqld]
user=mysql
#日志時間
log_timestamps=SYSTEM
port=3306
socket=/tmp/mysql.sock
max_connections=1000
max_allowed_packet=500M
#只能用IP地址檢查客戶端的登錄，不用主機名
skip-name-resolve=1
#binlog配置
server_id=150
log-bin=mysql-bin
max_binlog_size = 100M
binlog_format=row
log_slave_updates
expire_logs_days=7
#慢日志
long_query_time=3
slow_query_log=ON
slow_query_log_file=/home/application/mysql/slow_query.log
#錯誤日志
log-error=/home/application/mysql/mysql-error.log
#for8.0
sql_mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
basedir=/home/application/mysql/mysql8
datadir=/home/application/mysql/data
character_set_server=utf8
collation-server=utf8_general_ci
# 默認使用"mysql_native_password"插件認證
default_authentication_plugin=mysql_native_password
# 創建新表時將使用的默認存儲引擎
default-storage-engine=INNODB6.2 執行升級程序
在MySQL 5.7升級的時候，MySQL啟動后還需執行mysql_upgrade后重啟MySQL。MySQL8.0.16開始，MySQL 不推薦使用mysql_upgrade；直接使用 mysqld_safe 直接啟動。
關于--upgrade=的一些參數：
--upgrade=AUTO MySQL升級所有過時的內容。
--upgrade=NONE MySQL跳過升級步驟，可能會導致報錯。
--upgrade=MINIMAL MySQL在必要時升級數據字典表，information_schema和information_schema。這可能會導致部分功能不能正常使用，例如MGR 。
--upgrade=FORCE MySQL會升級所有的內容，這會檢查所有schema的所有對象，導致MySQL需要更長的時間啟動。此模式下，MySQL會重新創建系統表if they are missing。
[root@srebro.cn ~]# /home/application/mysql/mysql8/bin/mysqld_safe --defaults-file=/etc/my.cnf --user=mysql --upgrade=FORCE
2023-10-30T07:33:23.595626Z mysqld_safe Logging to '/home/application/mysql/mysql-error.log'.
2023-10-30T07:33:23.620303Z mysqld_safe Starting mysqld daemon with databases from /home/application/mysql/data
如出現一直卡住，不用擔心！
新開一個窗口，可觀察下錯誤日志看是否報錯/home/application/mysql/mysql-error.log 然后登錄數據庫測試。
[root@srebro.cn ~]# mysql -uroot -p'srebro'
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 8.0.34 MySQL Community Server - GPL
Copyright (c) 2000, 2022, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.34 |
+-----------+
1 row in set (0.00 sec)
mysql> 6.3 修改MySQL環境變量
由于basedir 從/home/application/mysql/app 變成了 /home/application/mysql/mysql8，需要修改下環境變量信息：
[root@srebro.cn ~]# vim /etc/profile
export PATH=$PATH:/home/application/mysql/mysql8/bin
.......
#使環境變量生效
[root@srebro.cn ~]# source /etc/profile
#驗證下mysql環境變量
#退出當前終端
[root@srebro.cn ~]# exit
[root@srebro.cn ~]# which mysql
/home/application/mysql/mysql8/bin/mysql
[root@srebro.cn ~]# mysql -V
mysql Ver 8.0.34 for Linux on x86_64 (MySQL Community Server - GPL)6.4 停止mysqld_safe進程，使用systemd管理MySQL 8.0
[root@cmdb ~]# kill -9 `ps -ef | grep mysql | awk '{print $2}'`
#確認沒有mysql進程
[root@cmdb ~]# ps -ef | grep mysql
#使用systemd管理mysql8
#修改原先的ExecStart中，basedir的路徑，改為mysql8 的路徑
[root@cmdb ~]# vim /etc/systemd/system/mysqld.service
[Unit]
Descriptinotallow=MySQL Server
Documentatinotallow=man:mysqld
Documentatinotallow=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/home/application/mysql/mysql8/bin/mysqld --defaults-file=/etc/my.cnf6.5 配置MySQL 8.0開機自啟&啟動MySQL 8.0
#reload下systemd
[root@cmdb ~]# systemctl daemon-reload
#加入開機自啟動
[root@cmdb ~]# systemctl enable mysqld
Created symlink from /etc/systemd/system/multi-user.target.wants/mysqld.service to /etc/systemd/system/mysqld.service.
#啟動mysql8數據庫
[root@cmdb ~]# systemctl start mysqld
[root@cmdb ~]# ps -ef | grep mysql
mysql 9497 1 36 14:59 ? 00:00:01 /home/application/mysql/mysql8/bin/mysqld --defaults-file=/etc/my.cnf
root 9544 8560 0 14:59 pts/0 00:00:00 grep --color=auto mysql
#登錄數據庫驗證
[root@cmdb ~]# mysql -uroot -p'srebro'
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.34 MySQL Community Server - GPL
Copyright (c) 2000, 2023, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.34 |
+-----------+
1 row in set (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| srebro |
| sys |
| test |
+--------------------+
5 rows in set (0.00 sec)
mysql> 7.遇到的問題
7.1 問題一
在升級MySQL8.0后，關于JDBC中SSL連接的一些報錯信息，如下圖：
經排查發現，MySQL 8.0數據庫默認開啟了SSL認證，且之前MySQL 5.7.39 也是默認開啟了SSL認證。代碼和JDBC驅動版本都沒有變化，那很有可能就是MySQL 8.0 中對于SSL的一個變化。咨詢了DBA 朋友，專業的解釋是，在5.7.31的時候SSL在源碼中貌似沒有真正的起作用，后面版本完善了這塊的內容。倘若，不使用SSL去連接，就需要按照如下的方法去處理：
- 從數據庫方面，直接在my.cnf 中 添加skip_ssl 參數，從源頭上關閉SSL 認證的方式
- 從代碼層面，在JDBC 連接中，使用 &useSSL=false 參數，表示不使用SSL 認證
7.2 問題二
MySQL報錯unblock with ‘mysqladmin flush-hosts’，報錯如下：
JDBC連接報錯，報錯內容 ERROR 1129 (HY000): Host '192.168.1.34' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'
原因：同一個ip在短時間內產生太多，中斷的數據庫連接而導致的阻塞；而中斷的原因是有些業務使用SSL去連接數據庫，導致登錄失敗，登錄被鎖。
臨時解決方法：使用mysqladmin flush-hosts 命令清理一下hosts文件，mysqladmin -u xxx -p flush-hosts,從根本上去解決，就需要排查什么異常的連接導致阻塞，登錄被鎖，比如上面提到的SSL認證的問題。
參考資料
- https://www.modb.pro/db/1715541568826990592
- https://www.modb.pro/db/530848
- https://www.modb.pro/db/1716302208709517312
作者介紹
蔡柱梁，51CTO社區編輯，從事Java后端開發8年，做過傳統項目廣電BOSS系統，后投身互聯網電商，負責過訂單，TMS，中間件等。
責任編輯：華軒
來源：
51CTO
