mysql5.7.42到mysql8.2.0的升级(rpm方式)

热心的芝麻

注：本文在测试环境升级测试，建议先在测试环境验证。mysql-5.7.42为rpm安装，所以用rpm方式升级

1、升级准备

1、数据备份：在升级之前，务必对当前的 MySQL 5.7 数据库进行完整备份，以防止数据丢失或损坏。确保备份文件的安全存储，并测试其可恢复性。
2、安全性考虑：建议在测试环境中进行升级测试，以模拟真实场景并检测潜在问题。这有助于减少生产环境中的不确定性和风险。

1、使用mysql-shell 检查工具检查兼容性

下载地址：https://downloads.mysql.com/archives/shell/

上传mysql-shell：

安装 mysql-shell rpm 软件包：：

1. rpm -Uvh mysql-shell-8.2.0-1.el7.x86_64.rpm --force --nodeps

复制代码

查看 mysql-shel安装版本：

1. mysqlsh --version

复制代码

检查该版本是否可以升级到MySQL 8.2.0：

1. mysqlsh -uroot -p -S /var/lib/mysql/mysql.sock -e "util.checkForServerUpgrade()"

复制代码

检查结果报告：

1. [root@zyl-server ~]# mysqlsh -uroot -p -S /var/lib/mysql/mysql.sock -e "util.checkForServerUpgrade()"
   
2. Please provide the password for 'root@/var%2Flib%2Fmysql%2Fmysql.sock': ********（zyl@2024）
   
3. Save password for 'root@/var%2Flib%2Fmysql%2Fmysql.sock'? [Y]es/[N]o/Ne[v]er (default No): Y
   
4. The MySQL server at /var%2Flib%2Fmysql%2Fmysql.sock, version 5.7.42 - MySQL
   
5. Community Server (GPL), will now be checked for compatibility issues for
   
6. upgrade to MySQL 8.2.0...
   
7. 
   
8. 1) Usage of old temporal type
   
9.   No issues found
   
10. 
    
11. 2) MySQL 8.0 syntax check for routine-like objects
    
12.   No issues found
    
13. 
    
14. 3) Usage of db objects with names conflicting with new reserved keywords
    
15.   No issues found
    
16. 
    
17. 4) Usage of utf8mb3 charset
    
18.   No issues found
    
19. 
    
20. 5) Table names in the mysql schema conflicting with new tables in 8.0
    
21.   No issues found
    
22. 
    
23. 6) Partitioned tables using engines with non native partitioning
    
24.   No issues found
    
25. 
    
26. 7) Foreign key constraint names longer than 64 characters
    
27.   No issues found
    
28. 
    
29. 8) Usage of obsolete MAXDB sql_mode flag
    
30.   No issues found
    
31. 
    
32. 9) Usage of obsolete sql_mode flags
    
33.   Notice: The following DB objects have obsolete options persisted for
    
34.     sql_mode, which will be cleared during upgrade to 8.0.
    
35.   More information:
    
36.     https://dev.mysql.com/doc/refman/8.0/en/mysql-nutshell.html#mysql-nutshell-removals
    
37. 
    
38.   db_zyl.InsertMultipleUsers - PROCEDURE uses obsolete NO_AUTO_CREATE_USER
    
39.     sql_mode
    
40.   global system variable sql_mode - defined using obsolete NO_AUTO_CREATE_USER
    
41.     option
    
42. 
    
43. 10) ENUM/SET column definitions containing elements longer than 255 characters
    
44.   No issues found
    
45. 
    
46. 11) Usage of partitioned tables in shared tablespaces
    
47.   No issues found
    
48. 
    
49. 12) Circular directory references in tablespace data file paths
    
50.   No issues found
    
51. 
    
52. 13) Usage of removed functions
    
53.   No issues found
    
54. 
    
55. 14) Usage of removed GROUP BY ASC/DESC syntax
    
56.   No issues found
    
57. 
    
58. 15) Removed system variables for error logging to the system log configuration
    
59.   To run this check requires full path to MySQL server configuration file to be specified at 'configPath' key of options dictionary
    
60.   More information:
    
61.     https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-13.html#mysqld-8-0-13-logging
    
62. 
    
63. 16) Removed system variables
    
64.   To run this check requires full path to MySQL server configuration file to be specified at 'configPath' key of options dictionary
    
65.   More information:
    
66.     https://dev.mysql.com/doc/refman/8.0/en/added-deprecated-removed.html#optvars-removed
    
67. 
    
68. 17) System variables with new default values
    
69.   To run this check requires full path to MySQL server configuration file to be specified at 'configPath' key of options dictionary
    
70.   More information:
    
71.     https://mysqlserverteam.com/new-defaults-in-mysql-8-0/
    
72. 
    
73. 18) Zero Date, Datetime, and Timestamp values
    
74.   No issues found
    
75. 
    
76. 19) Schema inconsistencies resulting from file removal or corruption
    
77.   No issues found
    
78. 
    
79. 20) Tables recognized by InnoDB that belong to a different engine
    
80.   No issues found
    
81. 
    
82. 21) Issues reported by 'check table x for upgrade' command
    
83.   No issues found
    
84. 
    
85. 22) New default authentication plugin considerations
    
86.   Warning: The new default authentication plugin 'caching_sha2_password' offers
    
87.     more secure password hashing than previously used 'mysql_native_password'
    
88.     (and consequent improved client connection authentication). However, it also
    
89.     has compatibility implications that may affect existing MySQL installations.
    
90.     If your MySQL installation must serve pre-8.0 clients and you encounter
    
91.     compatibility issues after upgrading, the simplest way to address those
    
92.     issues is to reconfigure the server to revert to the previous default
    
93.     authentication plugin (mysql_native_password). For example, use these lines
    
94.     in the server option file:
    
95.    
    
96.     [mysqld]
    
97.     default_authentication_plugin=mysql_native_password
    
98.    
    
99.     However, the setting should be viewed as temporary, not as a long term or
    
100.     permanent solution, because it causes new accounts created with the setting
     
101.     in effect to forego the improved authentication security.
     
102.     If you are using replication please take time to understand how the
     
103.     authentication plugin changes may impact you.
     
104.   More information:
     
105.     https://dev.mysql.com/doc/refman/8.0/en/upgrading-from-previous-series.html#upgrade-caching-sha2-password-compatibility-issues
     
106.     https://dev.mysql.com/doc/refman/8.0/en/upgrading-from-previous-series.html#upgrade-caching-sha2-password-replication
     
107. 
     
108. 23) Columns which cannot have default values
     
109.   No issues found
     
110. 
     
111. 24) Check for invalid table names and schema names used in 5.7
     
112.   No issues found
     
113. 
     
114. 25) Check for orphaned routines in 5.7
     
115.   No issues found
     
116. 
     
117. 26) Check for deprecated usage of single dollar signs in object names
     
118.   No issues found
     
119. 
     
120. 27) Check for indexes that are too large to work on higher versions of MySQL
     
121. Server than 5.7
     
122.   No issues found
     
123. 
     
124. 28) Check for deprecated '.<table>' syntax used in routines.
     
125.   No issues found
     
126. 
     
127. 29) Check for columns that have foreign keys pointing to tables from a diffrent
     
128. database engine.
     
129.   No issues found
     
130. 
     
131. Errors:   0
     
132. Warnings: 1
     
133. Notices:  2
     
134. 
     
135. 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.
     
136. [root@zyl-server ~]#

复制代码

检查结果显示：

1. 没有发现使用旧的时间类型的问题。没有发现MySQL 8.0 语法检查问题。没有发现与新保留关键字冲突的数据库对象使用问题。没有发现使用utf8mb3字符集的问题。没有发现mysql模式中的表名与MySQL 8.0中的新表冲突的问题。没有发现使用非原生分区引擎的分区表的问题。没有发现外键约束名称超过64个字符的问题。没有发现使用已弃用MAXDB sql_mode标志的问题。发现某些数据库对象具有过时的sql_mode选项，将在升级到8.0时清除。没有发现ENUM/SET列定义中包含超过255个字符的元素的问题。没有发现在共享表空间中使用分区表的问题。没有发现循环目录引用的问题。没有发现使用已删除函数的问题。没有发现使用已删除的GROUP BY ASC/DESC语法的问题。没有发现已删除的系统变量以进行错误日志记录的问题。没有发现已删除的系统变量的问题。检查到有新的默认值的系统变量。没有发现零日期、日期时间和时间戳值的问题。没有发现由于文件删除或损坏导致的模式不一致问题。没有发现被InnoDB识别为属于不同引擎的表的问题。没有发现通过'check table x for upgrade'命令报告的问题。发现新的默认身份验证插件的注意事项。没有发现不能具有默认值的列的问题。没有发现在5.7中使用无效的表名和模式名的问题。没有发现在5.7中存在的孤立例程的问题。没有发现在对象名称中使用单个美元符号的已弃用用法的问题。没有发现在高于MySQL 5.7版本的MySQL服务器上无法工作的太大的索引的问题。没有发现在例程中使用已弃用的'.<table>'语法的问题。没有发现具有指向来自不同数据库引擎的表的外键的列的问题。
   
2. 总结：
   
3. 错误：0警告：1注意事项：2

复制代码

2、操作环境

1、查看当前数据库版本

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

复制代码

2、操作系统版本

1. [root@zyl-server ~]#  cat /etc/redhat-release
   
2. CentOS Linux release 7.9.2009 (Core)
   
3. [root@zyl-server ~]#
   
4. [root@zyl-server ~]#

复制代码

3、备份数据库、my.cnf文件，停止mysql服务（重要）

1. [root@zyl-server ~]# mysqldump -hlocalhost -uroot -p --all-databases > /home/db_back_2024.sql
   
2. Enter password:
   
3. [root@zyl-server ~]# ll
   
4. total 1317800
   
5. -rw-------. 1 root  root       1419 Aug 30  2023 anaconda-ks.cfg
   
6. -rw-------. 1 root  root  453727744 Mar 13 19:40 image_mysql.tar
   
7. -rwxrwxrwx. 1 mysql mysql        58 Mar 17 16:39 init-file
   
8. drwxr-xr-x. 2 root  root         88 Mar 11 10:59 mynginx-app
   
9. -rw-r--r--. 1 root  root  447283712 Mar 13 19:24 mysql-5.7-container02.tar
   
10. -rw-r--r--. 1 root  root  447283712 Mar 13 19:23 mysql-5.7-container.tar
    
11. drwxr-xr-x. 9 mfs   mfs         186 Feb 28 20:38 nginx-1.24.0
    
12. -rw-r--r--. 1 root  root    1112471 Feb 28 20:37 nginx-1.24.0.tar.gz
    
13. [root@zyl-server ~]# cd /home/
    
14. [root@zyl-server home]# ll
    
15. total 876
    
16. -rw-r--r--. 1 root  root  890551 Mar 17 16:58 db_back_2024.sql
    
17. drwx------. 2 mfs   mfs       99 Feb 29 21:49 mfs
    
18. drwx------. 2 redis redis     99 Mar  3 02:43 redis
    
19. drwx------. 3 zyl   zyl     4096 Mar 16 13:47 zyl
    
20. [root@zyl-server home]#
    
21. 
    
22. [root@zyl-server home]# cp /etc/my.cnf /home/5.7.37_my.cnf
    
23. [root@zyl-server home]#
    
24. [root@zyl-server home]#
    
25. [root@zyl-server home]# systemctl stop mysqld

复制代码

4、上传、解压安装包

下载mysql8.2.0:
https://downloads.mysql.com/archives/community/

创建"mysql8.2.0-bundle"的文件夹，用于存放解压文件。

1. mkdir mysql8.2.0-bundle
   
2. 
   
3. tar -xvf mysql-8.2.0-1.el7.x86_64.rpm-bundle.tar

复制代码

5、查看已安装的mysql-5.7.42有关的包

列出系统中所有与 mysql 相关的 RPM 软件包。

1. rpm -qa|grep -i mysql

复制代码

6、升级、按顺序执行

1. [root@zyl-server mysql8.2.0-bundle]# rpm -Uvh mysql-community-server-8.2.0-1.el7.x86_64.rpm --force --nodeps
   
2. warning: mysql-community-server-8.2.0-1.el7.x86_64.rpm: Header V4 RSA/SHA256 Signature, key ID 3a79bd29: NOKEY
   
3. Preparing...                          ################################# [100%]
   
4. Updating / installing...
   
5.    1:mysql-community-server-8.2.0-1.el################################# [ 50%]
   
6. Cleaning up / removing...
   
7.    2:mysql-community-server-5.7.42-1.e################################# [100%]
   
8.    
   
9. [root@zyl-server mysql8.2.0-bundle]# rpm -Uvh mysql-community-client-
   
10. mysql-community-client-8.2.0-1.el7.x86_64.rpm          mysql-community-client-plugins-8.2.0-1.el7.x86_64.rpm  
    
11. [root@zyl-server mysql8.2.0-bundle]# rpm -Uvh mysql-community-client-8.2.0-1.el7.x86_64.rpm --force --nodeps
    
12. warning: mysql-community-client-8.2.0-1.el7.x86_64.rpm: Header V4 RSA/SHA256 Signature, key ID 3a79bd29: NOKEY
    
13. Preparing...                          ################################# [100%]
    
14. Updating / installing...
    
15.    1:mysql-community-client-8.2.0-1.el################################# [ 50%]
    
16. Cleaning up / removing...
    
17.    2:mysql-community-client-5.7.42-1.e################################# [100%]
    
18.    
    
19. [root@zyl-server mysql8.2.0-bundle]# rpm -Uvh mysql-community-libs-8.2.0-1.el7.x86_64.rpm --force --nodeps
    
20. warning: mysql-community-libs-8.2.0-1.el7.x86_64.rpm: Header V4 RSA/SHA256 Signature, key ID 3a79bd29: NOKEY
    
21. Preparing...                          ################################# [100%]
    
22. Updating / installing...
    
23.    1:mysql-community-libs-8.2.0-1.el7 ################################# [ 50%]
    
24. Cleaning up / removing...
    
25.    2:mysql-community-libs-5.7.42-1.el7################################# [100%]
    
26.    
    
27. [root@zyl-server mysql8.2.0-bundle]#  rpm -Uvh mysql-community-common-8.2.0-1.el7.x86_64.rpm --force --nodeps
    
28. warning: mysql-community-common-8.2.0-1.el7.x86_64.rpm: Header V4 RSA/SHA256 Signature, key ID 3a79bd29: NOKEY
    
29. Preparing...                          ################################# [100%]
    
30. Updating / installing...
    
31.    1:mysql-community-common-8.2.0-1.el################################# [ 50%]
    
32. Cleaning up / removing...
    
33.    2:mysql-community-common-5.7.42-1.e################################# [100%]
    
34. [root@zyl-server mysql8.2.0-bundle]#

复制代码

7、升级验证

1、检查登录正常

1. systemctl start mysqld
   
2. 
   
3. mysql -u root -p
   
4. 
   
5. [root@zyl-server mysql8.2.0-bundle]# mysql -u root -p
   
6. Enter password: （zyl@2024）
   
7. Welcome to the MySQL monitor.  Commands end with ; or \g.
   
8. Your MySQL connection id is 12
   
9. Server version: 8.2.0 MySQL Community Server - GPL
   
10. 
    
11. Copyright (c) 2000, 2023, Oracle and/or its affiliates.
    
12. 
    
13. Oracle is a registered trademark of Oracle Corporation and/or its
    
14. affiliates. Other names may be trademarks of their respective
    
15. owners.
    
16. 
    
17. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
    
18. 
    
19. mysql>

复制代码

2、检查升级版本
已由mysql-5.7.42 升级到 mysql-8.2.0 。

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

复制代码

3、检查数据

1. mysql> show databases;
   
2. +--------------------+
   
3. | Database           |
   
4. +--------------------+
   
5. | db_zyl             |
   
6. | information_schema |
   
7. | mysql              |
   
8. | performance_schema |
   
9. | sys                |
   
10. +--------------------+
    
11. 5 rows in set (0.00 sec)
    
12. 
    
13. mysql> use db_zyl;
    
14. Reading table information for completion of table and column names
    
15. You can turn off this feature to get a quicker startup with -A
    
16. 
    
17. Database changed
    
18. mysql>
    
19. mysql>
    
20. mysql> show tables;
    
21. +------------------+
    
22. | Tables_in_db_zyl |
    
23. +------------------+
    
24. | users            |
    
25. +------------------+
    
26. 1 row in set (0.00 sec)
    
27. 
    
28. mysql> select * from users;
    
29. +----+----------+----------------------+-------------+---------------------+
    
30. | id | name     | email                | password    | created_at          |
    
31. +----+----------+----------------------+-------------+---------------------+
    
32. |  1 | John Doe | john.doe@example.com | password123 | 2024-03-15 00:11:46 |
    
33. |  2 | John Doe | john.doe@example.com | password123 | 2024-03-15 00:11:50 |
    
34. |  3 | John Doe | john.doe@example.com | password123 | 2024-03-15 00:11:52 |
    
35. |  4 | John Doe | john.doe@example.com | password123 | 2024-03-15 00:11:55 |
    
36. |  5 | John Doe | john.doe@example.com | password123 | 2024-03-15 00:11:56 |
    
37. +----+----------+----------------------+-------------+---------------------+
    
38. 5 rows in set (0.00 sec)
    
39. 
    
40. mysql>

复制代码

到此这篇关于mysql5.7.42到mysql8.2.0的升级(rpm方式)的文章就介绍到这了,更多相关mysql5.7.42升级mysql8.2.0内容请搜索脚本之家以前的文章或继续浏览下面的相关文章希望大家以后多多支持脚本之家！

来源:https://www.jb51.net/database/318721fpy.htm
免责声明：由于采集信息均来自互联网，如果侵犯了您的权益，请联系我们【E-Mail:cb@itdo.tech】 我们会及时删除侵权内容，谢谢合作！