mysql5.7.42到mysql8.2.0的升級(jí)(rpm方式)
更新時(shí)間：2024年03月27日 11:52:38 作者：運(yùn)維魔法師
隨著數(shù)據(jù)量的增長和業(yè)務(wù)需求的變更,我們可能需要升級(jí)MySQL,本文主要介紹了mysql5.7.42到mysql8.2.0的升級(jí)(rpm方式),具有一定的參考價(jià)值,感興趣的可以了解一下
注：本文在測(cè)試環(huán)境升級(jí)測(cè)試，建議先在測(cè)試環(huán)境驗(yàn)證。mysql-5.7.42為rpm安裝，所以用rpm方式升級(jí)
1、升級(jí)準(zhǔn)備
1、數(shù)據(jù)備份：在升級(jí)之前，務(wù)必對(duì)當(dāng)前的 MySQL 5.7 數(shù)據(jù)庫進(jìn)行完整備份，以防止數(shù)據(jù)丟失或損壞。確保備份文件的安全存儲(chǔ)，并測(cè)試其可恢復(fù)性。
2、安全性考慮：建議在測(cè)試環(huán)境中進(jìn)行升級(jí)測(cè)試，以模擬真實(shí)場(chǎng)景并檢測(cè)潛在問題。這有助于減少生產(chǎn)環(huán)境中的不確定性和風(fēng)險(xiǎn)。
1、使用mysql-shell 檢查工具檢查兼容性
下載地址：https://downloads.mysql.com/archives/shell/
上傳mysql-shell：
安裝 mysql-shell rpm 軟件包：：
rpm -Uvh mysql-shell-8.2.0-1.el7.x86_64.rpm --force --nodeps
查看 mysql-shel安裝版本：
mysqlsh --version
檢查該版本是否可以升級(jí)到MySQL 8.2.0：
mysqlsh -uroot -p -S /var/lib/mysql/mysql.sock -e "util.checkForServerUpgrade()"
檢查結(jié)果報(bào)告：
[root@zyl-server ~]# mysqlsh -uroot -p -S /var/lib/mysql/mysql.sock -e "util.checkForServerUpgrade()"
Please provide the password for 'root@/var%2Flib%2Fmysql%2Fmysql.sock': ********（zyl@2024）
Save password for 'root@/var%2Flib%2Fmysql%2Fmysql.sock'? [Y]es/[N]o/Ne[v]er (default No): Y
The MySQL server at /var%2Flib%2Fmysql%2Fmysql.sock, version 5.7.42 - MySQL
Community Server (GPL), will now be checked for compatibility issues for
upgrade to MySQL 8.2.0...
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
No issues found
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
db_zyl.InsertMultipleUsers - PROCEDURE uses obsolete NO_AUTO_CREATE_USER
sql_mode
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
29) Check for columns that have foreign keys pointing to tables from a diffrent
database engine.
No issues found
Errors: 0
Warnings: 1
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.
[root@zyl-server ~]#
檢查結(jié)果顯示：
沒有發(fā)現(xiàn)使用舊的時(shí)間類型的問題。
沒有發(fā)現(xiàn)MySQL 8.0 語法檢查問題。
沒有發(fā)現(xiàn)與新保留關(guān)鍵字沖突的數(shù)據(jù)庫對(duì)象使用問題。
沒有發(fā)現(xiàn)使用utf8mb3字符集的問題。
沒有發(fā)現(xiàn)mysql模式中的表名與MySQL 8.0中的新表沖突的問題。
沒有發(fā)現(xiàn)使用非原生分區(qū)引擎的分區(qū)表的問題。
沒有發(fā)現(xiàn)外鍵約束名稱超過64個(gè)字符的問題。
沒有發(fā)現(xiàn)使用已棄用MAXDB sql_mode標(biāo)志的問題。
發(fā)現(xiàn)某些數(shù)據(jù)庫對(duì)象具有過時(shí)的sql_mode選項(xiàng)，將在升級(jí)到8.0時(shí)清除。
沒有發(fā)現(xiàn)ENUM/SET列定義中包含超過255個(gè)字符的元素的問題。
沒有發(fā)現(xiàn)在共享表空間中使用分區(qū)表的問題。
沒有發(fā)現(xiàn)循環(huán)目錄引用的問題。
沒有發(fā)現(xiàn)使用已刪除函數(shù)的問題。
沒有發(fā)現(xiàn)使用已刪除的GROUP BY ASC/DESC語法的問題。
沒有發(fā)現(xiàn)已刪除的系統(tǒng)變量以進(jìn)行錯(cuò)誤日志記錄的問題。
沒有發(fā)現(xiàn)已刪除的系統(tǒng)變量的問題。
檢查到有新的默認(rèn)值的系統(tǒng)變量。
沒有發(fā)現(xiàn)零日期、日期時(shí)間和時(shí)間戳值的問題。
沒有發(fā)現(xiàn)由于文件刪除或損壞導(dǎo)致的模式不一致問題。
沒有發(fā)現(xiàn)被InnoDB識(shí)別為屬于不同引擎的表的問題。
沒有發(fā)現(xiàn)通過'check table x for upgrade'命令報(bào)告的問題。
發(fā)現(xiàn)新的默認(rèn)身份驗(yàn)證插件的注意事項(xiàng)。
沒有發(fā)現(xiàn)不能具有默認(rèn)值的列的問題。
沒有發(fā)現(xiàn)在5.7中使用無效的表名和模式名的問題。
沒有發(fā)現(xiàn)在5.7中存在的孤立例程的問題。
沒有發(fā)現(xiàn)在對(duì)象名稱中使用單個(gè)美元符號(hào)的已棄用用法的問題。
沒有發(fā)現(xiàn)在高于MySQL 5.7版本的MySQL服務(wù)器上無法工作的太大的索引的問題。
沒有發(fā)現(xiàn)在例程中使用已棄用的'.<table>'語法的問題。
沒有發(fā)現(xiàn)具有指向來自不同數(shù)據(jù)庫引擎的表的外鍵的列的問題。總結(jié)：
錯(cuò)誤：0
警告：1
注意事項(xiàng)：2
2、操作環(huán)境
1、查看當(dāng)前數(shù)據(jù)庫版本
mysql> select@@version; +-----------+ | @@version | +-----------+ | 5.7.42 | +-----------+ 1 row in set (0.00 sec) mysql>
2、操作系統(tǒng)版本
[root@zyl-server ~]# cat /etc/redhat-release CentOS Linux release 7.9.2009 (Core) [root@zyl-server ~]# [root@zyl-server ~]#
3、備份數(shù)據(jù)庫、my.cnf文件，停止mysql服務(wù)（重要）
[root@zyl-server ~]# mysqldump -hlocalhost -uroot -p --all-databases > /home/db_back_2024.sql Enter password: [root@zyl-server ~]# ll total 1317800 -rw-------. 1 root root 1419 Aug 30 2023 anaconda-ks.cfg -rw-------. 1 root root 453727744 Mar 13 19:40 image_mysql.tar -rwxrwxrwx. 1 mysql mysql 58 Mar 17 16:39 init-file drwxr-xr-x. 2 root root 88 Mar 11 10:59 mynginx-app -rw-r--r--. 1 root root 447283712 Mar 13 19:24 mysql-5.7-container02.tar -rw-r--r--. 1 root root 447283712 Mar 13 19:23 mysql-5.7-container.tar drwxr-xr-x. 9 mfs mfs 186 Feb 28 20:38 nginx-1.24.0 -rw-r--r--. 1 root root 1112471 Feb 28 20:37 nginx-1.24.0.tar.gz [root@zyl-server ~]# cd /home/ [root@zyl-server home]# ll total 876 -rw-r--r--. 1 root root 890551 Mar 17 16:58 db_back_2024.sql drwx------. 2 mfs mfs 99 Feb 29 21:49 mfs drwx------. 2 redis redis 99 Mar 3 02:43 redis drwx------. 3 zyl zyl 4096 Mar 16 13:47 zyl [root@zyl-server home]# [root@zyl-server home]# cp /etc/my.cnf /home/5.7.37_my.cnf [root@zyl-server home]# [root@zyl-server home]# [root@zyl-server home]# systemctl stop mysqld
4、上傳、解壓安裝包
下載mysql8.2.0:
https://downloads.mysql.com/archives/community/
創(chuàng)建"mysql8.2.0-bundle"的文件夾，用于存放解壓文件。
mkdir mysql8.2.0-bundle tar -xvf mysql-8.2.0-1.el7.x86_64.rpm-bundle.tar
5、查看已安裝的mysql-5.7.42有關(guān)的包
列出系統(tǒng)中所有與 mysql 相關(guān)的 RPM 軟件包。
rpm -qa|grep -i mysql
6、升級(jí)、按順序執(zhí)行
[root@zyl-server mysql8.2.0-bundle]# rpm -Uvh mysql-community-server-8.2.0-1.el7.x86_64.rpm --force --nodeps warning: mysql-community-server-8.2.0-1.el7.x86_64.rpm: Header V4 RSA/SHA256 Signature, key ID 3a79bd29: NOKEY Preparing... ################################# [100%] Updating / installing... 1:mysql-community-server-8.2.0-1.el################################# [ 50%] Cleaning up / removing... 2:mysql-community-server-5.7.42-1.e################################# [100%] [root@zyl-server mysql8.2.0-bundle]# rpm -Uvh mysql-community-client- mysql-community-client-8.2.0-1.el7.x86_64.rpm mysql-community-client-plugins-8.2.0-1.el7.x86_64.rpm [root@zyl-server mysql8.2.0-bundle]# rpm -Uvh mysql-community-client-8.2.0-1.el7.x86_64.rpm --force --nodeps warning: mysql-community-client-8.2.0-1.el7.x86_64.rpm: Header V4 RSA/SHA256 Signature, key ID 3a79bd29: NOKEY Preparing... ################################# [100%] Updating / installing... 1:mysql-community-client-8.2.0-1.el################################# [ 50%] Cleaning up / removing... 2:mysql-community-client-5.7.42-1.e################################# [100%] [root@zyl-server mysql8.2.0-bundle]# rpm -Uvh mysql-community-libs-8.2.0-1.el7.x86_64.rpm --force --nodeps warning: mysql-community-libs-8.2.0-1.el7.x86_64.rpm: Header V4 RSA/SHA256 Signature, key ID 3a79bd29: NOKEY Preparing... ################################# [100%] Updating / installing... 1:mysql-community-libs-8.2.0-1.el7 ################################# [ 50%] Cleaning up / removing... 2:mysql-community-libs-5.7.42-1.el7################################# [100%] [root@zyl-server mysql8.2.0-bundle]# rpm -Uvh mysql-community-common-8.2.0-1.el7.x86_64.rpm --force --nodeps warning: mysql-community-common-8.2.0-1.el7.x86_64.rpm: Header V4 RSA/SHA256 Signature, key ID 3a79bd29: NOKEY Preparing... ################################# [100%] Updating / installing... 1:mysql-community-common-8.2.0-1.el################################# [ 50%] Cleaning up / removing... 2:mysql-community-common-5.7.42-1.e################################# [100%] [root@zyl-server mysql8.2.0-bundle]#
7、升級(jí)驗(yàn)證
1、檢查登錄正常
systemctl start mysqld mysql -u root -p [root@zyl-server mysql8.2.0-bundle]# mysql -u root -p Enter password: （zyl@2024） Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 12 Server version: 8.2.0 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>
2、檢查升級(jí)版本
已由mysql-5.7.42 升級(jí)到 mysql-8.2.0 。
mysql> select@@version; +-----------+ | @@version | +-----------+ | 8.2.0 | +-----------+ 1 row in set (0.00 sec) mysql>
3、檢查數(shù)據(jù)
mysql> show databases; +--------------------+ | Database | +--------------------+ | db_zyl | | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 5 rows in set (0.00 sec) mysql> use db_zyl; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> mysql> mysql> show tables; +------------------+ | Tables_in_db_zyl | +------------------+ | users | +------------------+ 1 row in set (0.00 sec) mysql> select * from users; +----+----------+----------------------+-------------+---------------------+ | id | name | email | password | created_at | +----+----------+----------------------+-------------+---------------------+ | 1 | John Doe | john.doe@example.com | password123 | 2024-03-15 00:11:46 | | 2 | John Doe | john.doe@example.com | password123 | 2024-03-15 00:11:50 | | 3 | John Doe | john.doe@example.com | password123 | 2024-03-15 00:11:52 | | 4 | John Doe | john.doe@example.com | password123 | 2024-03-15 00:11:55 | | 5 | John Doe | john.doe@example.com | password123 | 2024-03-15 00:11:56 | +----+----------+----------------------+-------------+---------------------+ 5 rows in set (0.00 sec) mysql>
到此這篇關(guān)于mysql5.7.42到mysql8.2.0的升級(jí)(rpm方式)的文章就介紹到這了,更多相關(guān)mysql5.7.42升級(jí)mysql8.2.0內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
數(shù)據(jù)通常分布在多個(gè)表中,為了獲取全面的信息,需要進(jìn)行聯(lián)表查詢,本文主要介紹了MySQL的聯(lián)表查詢實(shí)現(xiàn),具有一定的參考價(jià)值,感興趣的可以了解一下2024-08-08
mysql條件查詢and or使用方法及優(yōu)先級(jí)實(shí)例分析
這篇文章主要介紹了mysql條件查詢and or使用方法及優(yōu)先級(jí),結(jié)合實(shí)例形式分析了mysql條件查詢and or基本功能、用法及優(yōu)先級(jí)相關(guān)操作技巧,需要的朋友可以參考下2020-04-04
本篇文章是對(duì)關(guān)于mysql 5.5設(shè)置字符集的問題進(jìn)行了詳細(xì)的分析介紹，需要的朋友參考下2013-06-06
MySQL初級(jí)入門篇之視圖的相關(guān)概念及應(yīng)用實(shí)例
Mysql中的視圖其實(shí)是一個(gè)虛擬表,使用時(shí)動(dòng)態(tài)檢索查詢數(shù)據(jù),下面這篇文章主要給大家介紹了關(guān)于MySQL初級(jí)入門篇之視圖的相關(guān)概念及應(yīng)用實(shí)例的相關(guān)資料,需要的朋友可以參考下2022-04-04
本文通過圖文并茂的形式給大家介紹了MySQL8.0.11安裝總結(jié)，非常不錯(cuò)，具有參考借鑒價(jià)值，需要的朋友參考下吧2018-05-05
這篇文章主要為大家詳細(xì)介紹了mysql 5.7.17 源碼編譯安裝配置方法圖文教程，具有一定的參考價(jià)值，感興趣的小伙伴們可以參考一下2017-01-01
MySQL 配置文件my.cnf 例子最詳細(xì)翻譯,需要的朋友可以看看。2009-05-05
MySQL的子查詢及相關(guān)優(yōu)化學(xué)習(xí)教程
這篇文章主要介紹了MySQL的子查詢及相關(guān)優(yōu)化學(xué)習(xí)教程,使用子查詢時(shí)需要注意其對(duì)數(shù)據(jù)庫性能的影響,需要的朋友可以參考下2015-11-11
mysql中使用MEM_ROOT來做內(nèi)存分配的部分，這篇文章會(huì)詳細(xì)解說MySQL中使用非常廣泛的MEM_ROOT的結(jié)構(gòu)體，同時(shí)省去debug部分的信息，僅分析正常情況下。需要的朋友可以參考一下2016-11-11
mysql常見約束有：1、非空約束“not?null”；2、唯一性約束“unique”；3、主鍵約束“primary?key”；4、外鍵約束“foreign?key”；5、默認(rèn)值約束“Default”等等。2022-10-10