這份MySQL 5.7 到 8.0 的升級攻略，閉眼入吧……

一、前言

事出必有因，在這個月的某個計畫中，我們面臨了一項重要任務，即每年一次的等保測評整改。這次測評的重點是Mysql的一些高危漏洞，客戶要求我們無論如何必須解決這些漏洞。盡管我們感到無奈，但為了滿足客戶的要求，我們只能硬著頭皮進行升級。而碰巧的是，借著Mysql5.7停止更新的機會，我們決定研究一下Mysql5.7升級到Mysql8.0的過程。在本文的最後，我將分享一些在這次升級過程中遇到的問題。

升級需求：將5.7.43升級到8.0.34, 升級方式 in-place升級【關閉現有版本MySQL，將二進制或包替換成新版本並在現有數據目錄上啟動MySQL並執行升級任務的方式，稱為in-place升級】

二、MySQL生命周期

以下Mysql 生命周期-內容來自於互聯網

關於資料庫版本升級，一直都是熱議話題，對於升級的緣由各家也有所不同，有業務驅動的，有DBA自發驅動的，有規劃導向也有方向指引的……拋開各種原因，當升級這個決定落下來的時候，對於DBA手頭的幾百幾千套資料庫來說，就好比是一場動物大遷徙，滿滿的畫面感。

從Oracle釋出的版本生命周期規劃可以看到，Mysql5.7已經走到了生命周期的終點，意味著後續將不再為Mysql5.7提供官方更新、錯誤修復或安全修補程式。

三、MySQL8.0的新特性

四、升級建議

支持從MySQL5.7升級到MySQL8.0，註意僅支持GA版本之間的升級。

不支持跨大版本的升級，如從5.6升級到8.0是不支持的。

建議升級大版本前先升級到目前版本的最近小版本，如5.7先升級到5.7.43後再升級到8.0。

做好充足的備份！數據無價！

五、升級前準備

1、Mysql-shell 檢查工具相容性

在執行升級操作前需要做一些檢查工作，確認準備工作是否就緒，避免升級過程中出現異常。可以使用MySQL Shell使用util.checkForServerUpgrade進行檢查，返回內容包括不符合遷移要求的問題，error的問題需要遷移前修改。

Mysql-shell 下載地址：https://dev.mysql.com/downloads/shell/

選擇 Archives ，查詢更多版本

選擇當前最新的版本8.0.34，x84,64-bit

下載地址：https://downloads.mysql.com/archives/get/p/43/file/mysql-shell-8.0.34-linux-glibc2.12-x86-64bit.tar.gz

#下載包[[email protected] ~]# wget https://downloads.mysql.com/archives/get/p/43/file/mysql-shell-8.0.34-linux-glibc2.12-x86-64bit.tar.gz -C /root[[email protected] ~]# tar -xf mysql-shell-8.0.34-linux-glibc2.12-x86-64bit.tar.gz[[email protected] ~]# cd /root/mysql-shell-8.0.34-linux-glibc2.12-x86-64bit/bin[[email protected] bin]# ./mysqlsh -uroot -p -S /tmp/mysql.sock -e "util.checkForServerUpgrade()" > util.checkForServerUpgrade.log

輸出報告

The MySQL server at /tmp/mysql.sock, version 5.7.39-log - MySQL CommunityServer (GPL), will now be checked for compatibility issues for upgrade to MySQL8.0.34...1) Usage of old temporal type No issues found2) MySQL 8.0 syntax checkfor routine-like objectsNo issues found3) Usageof db objects withnames conflicting withnew reserved keywordsNo issues found4) Usageof utf8mb3 charsetWarning: The following objects use the utf8mb3 character set. It is recommended toconvert them touse 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 defaultcharacterset: utf85) Tablenamesin the mysql schema conflicting withnewtablesin8.0No issues found6) Partitioned tablesusingengineswith non native partitioningNo issues found7) Foreignkeyconstraintnames longer than64charactersNo issues found8) Usageof obsolete MAXDB sql_mode flagNo issues found9) Usageof obsolete sql_mode flagsNotice: The following DB objects have obsolete options persisted for sql_mode, which will be cleared during upgradeto8.0. More information: https://dev.mysql.com/doc/refman/8.0/en/mysql-nutshell.html#mysql-nutshell-removalsglobalsystemvariable sql_mode - defined using obsolete NO_AUTO_CREATE_USERoption10) ENUM/SETcolumn definitions containing elements longer than255charactersNo issues found11) Usageof partitioned tablesinshared tablespacesNo issues found12) Circular directoryreferencesintablespacedatafile pathsNo issues found13) Usageof removed functionsNo issues found14) Usageof removed GROUPBYASC/DESC syntaxNo issues found15) Removed systemvariablesforerrorloggingto the systemlog configurationTo run this check requires fullpathto MySQL server configuration fileto be specified at'configPath'keyof options dictionary More information: https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-13.html#mysqld-8-0-13-logging16) Removed systemvariablesTo run this check requires fullpathto MySQL server configuration fileto be specified at'configPath'keyof options dictionary More information: https://dev.mysql.com/doc/refman/8.0/en/added-deprecated-removed.html#optvars-removed17) SystemvariableswithnewdefaultvaluesTo run this check requires fullpathto MySQL server configuration fileto be specified at'configPath'keyof options dictionary More information: https://mysqlserverteam.com/new-defaults-in-mysql-8-0/18) Zero Date, Datetime, andTimestampvaluesNo issues found19) Schema inconsistencies resulting fromfile removal orcorruptionNo issues found20) Tables recognized byInnoDB that belong to a different engineNo issues found21) Issues reported by'check table x for upgrade' commandNo issues found22) Newdefaultauthenticationplugin considerationsWarning: The newdefaultauthenticationplugin'caching_sha2_password' offers more secure password hashing than previously used 'mysql_native_password' (and consequent improved clientconnectionauthentication). However, it also has compatibility implications that may affect existing MySQL installations. If your MySQL installation must serve pre-8.0 clients and you encountercompatibility issues after upgrading, the simplest way to address those issues isto reconfigure the serverto revert to the previous defaultauthenticationplugin (mysql_native_password). For example, use these linesin the serveroptionfile: [mysqld] default_authentication_plugin=mysql_native_password However, the setting should be viewed astemporary, notas a long term orpermanent solution, because it causes new accounts created with the settingin effect to forego the improved authentication security.If you areusingreplication please take timeto understand how theauthenticationplugin 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-replication23) Columns which cannot have defaultvaluesNo issues found24) Checkfor invalid tablenamesandschemanames used in5.7No issues found25) Checkfor orphaned routines in5.7No issues found26) Checkfor deprecated usageof single dollar signs inobjectnamesNo issues found27) Checkforindexes that are too largetoworkon higher versionsof MySQLServerthan5.7No issues found28) Checkfor deprecated '.<table>' syntax used in routines.No issues foundErrors: 0Warnings: 3Notices: 1NOTE: No fatal errors were found that would prevent an upgrade, but some potential issues were detected. Please ensure that the reported issues arenot 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：Mysq8.0 版本sql_mode不支持NO_AUTO_CREATE_USER，要避免配置的sql_mode中帶有NO_AUTO_CREATE_USER。

透過以上的例子，可以發現，MySQL Shell提供的升級檢查工具能夠幫助我們檢測版本相容性，減輕升級工作負擔。

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.sql

3、優雅的停止資料庫

# 進入原5.7 mysql命令列 正確關閉資料庫[[email protected] ~]# 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> exitBye[root@cmdb ~]# ps -ef | grep mysqlroot 3099030934016:12 pts/000:00:00 grep --color=auto mysql

4、備份Mysql 數據目錄,安裝目錄 和配置檔

--確認資料庫狀態為關閉狀態[[email protected] ~]# systemctl status mysqld--數據目錄備份[[email protected] ~]# cp -r /home/application/mysql/data /home/application/mysql/data_bak_`date +%F`--安裝目錄備份[[email protected] ~]# cp -r /home/application/mysql/app/ /home/application/mysql/app_bak_`date +%F`--配置檔備份[[email protected] ~]# cp /etc/my.cnf /etc/my.cnf_`date +%F`

5、下載並解壓MySQL8

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[[email protected] ~]# cd /home/application/mysql[[email protected] mysql]# wget https://downloads.mysql.com/archives/get/p/23/file/mysql-8.0.34-linux-glibc2.12-x86_64.tar.xz[[email protected] mysql]# tar -xf mysql-8.0.34-linux-glibc2.12-x86_64.tar.xz# 資料夾重新命名為mysql8[[email protected] mysql]# mv mysql-8.0.34-linux-glibc2.12-x86_64 mysql8# 更改資料夾所屬[[email protected] mysql]# chown -Rf mysql:mysql /home/application/mysql/mysql8# 刪除安裝包[[email protected] mysql]# rm -rf mysql-8.0.34-linux-glibc2.12-x86_64.tar.xz

六、升級

1、修改my.cnf 配置檔

因5.7版本與8.0版本參數有所不同，為了能順利升級，我們需要更改部份配置參數。主要註意sql_mode、basedir、密碼認證外掛程式及字元集設定，其他參數最好還是按照原5.7的來，不需要做調整。下面展示5.7和8.0的配置檔,註意備份原來配置檔。

1）Mysql5.7_my.cnf 配置檔

[mysql]socket=/tmp/mysql.sockdefault-character-set=utf8[mysqld]user=mysqlbasedir=/home/application/mysql/appdatadir=/home/application/mysql/datacharacter_set_server=utf8collation-server=utf8_general_ci#日誌時間log_timestamps=SYSTEMport=3306socket=/tmp/mysql.sockmax_connections=1000max_allowed_packet=500Msql_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=3slow_query_log=ONslow_query_log_file=/home/application/mysql/slow_query.log#錯誤日誌log-error=/home/application/mysql/mysql-error.log#binlog配置server_id=150log-bin=mysql-binmax_binlog_size = 100Mbinlog_format=rowlog_slave_updatesexpire_logs_days=7#只能用IP地址檢查客戶端的登入，不用主機名skip-name-resolve=1

2）Mysql8.0_my.cnf 配置檔

[mysql]socket=/tmp/mysql.sockdefault-character-set=utf8[mysqld]user=mysql#日誌時間log_timestamps=SYSTEMport=3306socket=/tmp/mysql.sockmax_connections=1000max_allowed_packet=500M#只能用IP地址檢查客戶端的登入，不用主機名skip-name-resolve=1#binlog配置server_id=150log-bin=mysql-binmax_binlog_size = 100Mbinlog_format=rowlog_slave_updatesexpire_logs_days=7#慢日誌long_query_time=3slow_query_log=ONslow_query_log_file=/home/application/mysql/slow_query.log#錯誤日誌log-error=/home/application/mysql/mysql-error.log#for8.0sql_mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTIONbasedir=/home/application/mysql/mysql8datadir=/home/application/mysql/datacharacter_set_server=utf8collation-server=utf8_general_ci# 預設使用"mysql_native_password"外掛程式認證default_authentication_plugin=mysql_native_password# 建立新表時將使用的預設儲存引擎default-storage-engine=INNODB

2、執行升級程式

在mysql5.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。[[email protected] ~]# /home/application/mysql/mysql8/bin/mysqld_safe --defaults-file=/etc/my.cnf --user=mysql --upgrade=FORCE2023-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 withdatabasesfrom /home/application/mysql/data會一直卡住不用擔心

新開一個視窗，可觀察下錯誤日誌看是否報錯/home/application/mysql/mysql-error.log 然後登入資料庫測試

[[email protected] ~]# mysql -uroot -p'srebro'Enter password: Welcome to the MySQL monitor. Commands endwith ; or \g.Your MySQL connection id is 12Server version: 8.0.34 MySQL Community Server - GPLCopyright (c) 2000, 2022, Oracle and/or its affiliates.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type'\c'toclear the currentinput statement.mysql> selectversion();+-----------+| version() |+-----------+| 8.0.34 |+-----------+1 row in set (0.00 sec)mysql>

3、修改Mysql環境變量

由於basedir 從/home/application/mysql/app 變成了 /home/application/mysql/mysql8，需要修改下環境變量資訊：

[[email protected] ~]# vim /etc/profileexport PATH=$PATH:/home/application/mysql/mysql8/bin.......#使環境變量生效[[email protected] ~]# source /etc/profile#驗證下mysql環境變量#結束當前終端[[email protected] ~]# exit [[email protected] ~]# which mysql/home/application/mysql/mysql8/bin/mysql[[email protected] ~]# mysql -Vmysql Ver 8.0.34for Linux on x86_64 (MySQL Community Server - GPL)

4、停止mysqld_safe行程，使用systemd管理Mysql8

[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]Description=MySQL ServerDocumentation=man:mysqldDocumentation=http://dev.mysql.com/doc/refman/en/using-systemd.htmlAfter=network.targetAfter=syslog.target[Install]WantedBy=multi-user.target[Service]User=mysqlGroup=mysqlExecStart=/home/application/mysql/mysql8/bin/mysqld --defaults-file=/etc/my.cnf

5、配置mysql8開機自啟&啟動mysql8

#reload下systemd[root@cmdb ~]# systemctl daemon-reload#加入開機自啟動[root@cmdb ~]# systemctl enable mysqldCreated 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 mysqlmysql 949713614:59 ? 00:00:01 /home/application/mysql/mysql8/bin/mysqld --defaults-file=/etc/my.cnfroot 95448560014:59 pts/000: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 8Server version:8.0.34 MySQL Community Server - GPLCopyright (c) 2000, 2023, Oracle and/or its affiliates.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.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>

七、一些問題

1、問題一

在升級Mysql8.0後，關於JDBC中SSL連線的一些報錯資訊，如下圖：

經排查發現，Mysql8.0 資料庫預設開啟了SSL認證，且之前Mysql5.7.39 也是預設開啟了SSL認證，程式碼和JDBC驅動版本都沒有變化，那很有可能就是Mysql8.0 中對於SSL的一個變化，咨詢了DBA 朋友，專業的解釋是，在5.7.31的時候SSL在源碼中貌似沒有真正的起作用，後面版本完善了這塊的內容。倘若，不使用SSL去連線，就 需要按照如下的方法去處理：

方法一： 從資料庫成面，直接在my.cnf 中 添加skip_ssl 參數，從源頭上關閉SSL 認證的方式

方法二： 從程式碼層面，在JDBC 連線中，使用 &useSSL=false 參數，表示不使用SSL 認證

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認證的問題。

> > > >

參考資料

作者丨運維小弟

來源丨公眾號：運維小弟（ID：SRE-BRO）

dbaplus社群歡迎廣大技術人員投稿，投稿信箱： [email protected]

活動推薦

2024 XCOPS智慧運維管理人年會·廣州站將於5月24日舉辦 ，深究大模型、AI Agent等新興技術如何落地於運維領域，賦能企業智慧運維水平提升，構建全面運維自治能力！ 碼上報名，享早鳥優惠。