mysql-5.7.42升级到mysql-8.2.0(二进制方式)

王皓爸爸

注：本文在测试环境升级测试，建议先在测试环境验证。在生产环境下还是先评估下，mysql-5.7.42为二进制方式安装，所以用mysql-8.2.0二进制包升级

1、操作环境


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、查看 Linux 系统上的 glibc（GNU C 库）版本（这里很重要，要下载对应的内核mysql版本）

1. ldd --version
   
2. 
   
3. 或者
   
4. rpm -q glibc
   
5. 
   
6. **查看当前系统中的libstdc++版本：**
   
7. 
   
8. strings /usr/lib64/libstdc++.so.6 | grep GLIBCXX
   
9. strings /usr/lib64/libstdc++.so.6 | grep CXXABI
   
10. strings /usr/lib64/libstdc++.so.6 | grep GLIBC
    
11. strings /usr/lib64/libstdc++.so.6 | grep GLIBCXX

复制代码

否则后面升级完成后，启动mysql会报错：

1. 报错如下：

复制代码

错误表明在启动MySQL服务时出现了一些问题。主要的错误信息是关于缺少特定库文件的版本，比如

1. GLIBCXX_3.4.20、CXXABI_1.3.9、CXXABI_1.3.8、GLIBCXX_3.4.21以及GLIBC_2.25

复制代码

。
这是由于安装的MySQL版本与系统中可用的标准库版本不兼容导致的（所以要对应下载相应的内核版本）。

1. Mar 20 21:10:55 db-mysql mysqld[7127]: /usr/local/mysql/bin/my_print_defaults: /lib64/libstdc++.so.6: version `CXXABI_1.3.9' not found (required by /usr/local/mysql/bin/my_print_defaults)Mar 20 21:10:55 db-mysql mysqld[7127]: /usr/local/mysql/bin/my_print_defaults: /lib64/libstdc++.so.6: version `CXXABI_1.3.8' not found (required by /usr/local/mysql/bin/my_print_defaults)Mar 20 21:10:55 db-mysql mysqld[7127]: /usr/local/mysql/bin/my_print_defaults: /lib64/libstdc++.so.6: version `GLIBCXX_3.4.21' not found (required by /usr/local/mysql/bin/my_print_defaults)Mar 20 21:10:55 db-mysql mysqld[7127]: /usr/local/mysql/bin/my_print_defaults: /lib64/libc.so.6: version `GLIBC_2.25' not found (required by /usr/local/mysql/bin/../lib/private/libcrypto.so.3Mar 20 21:10:55 db-mysql mysqld[7127]: /usr/local/mysql/bin/my_print_defaults: /lib64/libstdc++.so.6: version `GLIBCXX_3.4.20' not found (required by /usr/local/mysql/bin/my_print_defaults)Mar 20 21:10:55 db-mysql mysqld[7127]: /usr/local/mysql/bin/my_print_defaults: /lib64/libstdc++.so.6: version `CXXABI_1.3.9' not found (required by /usr/local/mysql/bin/my_print_defaults)Mar 20 21:10:55 db-mysql mysqld[7127]: /usr/local/mysql/bin/my_print_defaults: /lib64/libstdc++.so.6: version `CXXABI_1.3.8' not found (required by /usr/local/mysql/bin/my_print_defaults)Mar 20 21:10:55 db-mysql mysqld[7127]: /usr/local/mysql/bin/my_print_defaults: /lib64/libstdc++.so.6: version `GLIBCXX_3.4.21' not found (required by /usr/local/mysql/bin/my_print_defaults)Mar 20 21:10:55 db-mysql mysqld[7127]: /usr/local/mysql/bin/my_print_defaults: /lib64/libc.so.6: version `GLIBC_2.25' not found (required by /usr/local/mysql/bin/../lib/private/libcrypto.so.3

复制代码

2、升级准备

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 /tmp/mysqld.sock -e "util.checkForServerUpgrade()"

复制代码

1. 注意:mysql.sock 地址在/etc/my.cnf 文件中查看。

复制代码

检查结果报告：

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

复制代码

检查结果显示：

1. 没有发现使用旧时态类型的问题没有发现与MySQL 8.0语法相关的问题没有发现与数据库对象名称与新保留关键字冲突的问题发现某些对象使用了utf8mb3字符集，建议转换为utf8mb4以获得更好的Unicode支持没有发现与mysql模式中的表名冲突的问题没有发现使用具有非本地分区的引擎的分区表的问题没有发现外键约束名称超过64个字符的问题没有发现使用已弃用的MAXDB sql_mode标志的问题发现一些DB对象已经使用了过时的sql_mode选项没有发现ENUM/SET列定义中包含超过255个字符的元素的问题没有发现在共享表空间中使用分区表的问题没有发现表空间数据文件路径中存在循环目录引用的问题没有发现使用已删除函数的问题没有发现已删除的GROUP BY ASC/DESC语法的问题无法运行需要指定完整MySQL服务器配置文件路径的日志系统变量检查无法运行需要指定完整MySQL服务器配置文件路径的已删除系统变量检查无法运行需要指定完整MySQL服务器配置文件路径的新默认值系统变量检查没有发现零日期、日期时间和时间戳值的问题没有发现由文件删除或损坏导致的模式不一致的问题没有发现被InnoDB识别为属于不同引擎的表的问题没有发现通过'check table x for upgrade'命令报告的问题发现新的默认身份验证插件引入了兼容性问题，建议在升级后重新配置服务器以恢复到以前的默认身份验证插件没有发现不能有默认值的列的问题没有发现在5.7中使用的无效表名和模式名的问题没有发现在5.7中遗留存储过程的问题没有发现在对象名称中使用单个美元符号的已弃用用法没有发现在高于MySQL Server 5.7版本上工作的索引过大的问题没有发现在例程中使用已弃用'.<table>'语法的问题没有发现具有外键指向不同数据库引擎的表的列的问题
   
2. 总结：
   
3. 错误：0警告：4注意事项：1

复制代码

总结来说，检查未发现会阻止升级的致命错误，但检测到了一些潜在问题。在升级之前，请确保报告的问题并不重要。

3、mysqldump 导出数据文件和备份my.cnf

1. [root@db-mysql ~]#  mysqldump -hlocalhost -uroot -p --all-databases > /home/db_back_2024.sql
   
2. Enter password:
   
3. [root@db-mysql ~]# cd /home/                 
   
4. [root@db-mysql home]# ll
   
5. total 872
   
6. -rw-r--r--  1 root   root     889253 Mar 20 20:25 db_back_2024.sql
   
7. drwx------  2 oracle oinstall    127 Mar 18 05:12 oracle
   
8. drwx------. 2 zyl    zyl          62 Mar 16 22:24 zyl
   
9. [root@db-mysql home]# cp /etc/my.cnf /home/5.7.37_my.cnf
   
10. 
    
11. [root@zyl-mysql home]#

复制代码

升级前先停止数据库。

1. [root@db-mysql home]# systemctl stop mysqld

复制代码

4、备份旧版mysql-5.7.42 安装目录下的文件和my.cnf文件（重要）

这里的安装目录在

1. /usr/local/mysql

复制代码

，根据自己实际备份。

1. cd /usr/local/
   
2. 
   
3. mkdir mysql-5.7.42_bk
   
4. 
   
5. ##全部备份 mysql目录下的文件
   
6. cp -R mysql/* mysql-5.7.42_bk
   
7. 
   
8. ### 或者直接压缩
   
9. ### tar -zcvf mysql-5.7.42_bk.tar.gz mysql
   
10. [root@db-mysql bin]# tar zcf mysql-5.7.42_bk.tar.gz mysql

复制代码

5、删除旧版安装目录/usr/local/mysql下(bin、docs、includ、lib、share、support-files、LICENSE、README)；

1. cd /usr/local/mysql
   
2. 
   
3. rm -rf bin
   
4. rm -rf docs
   
5. rm -rf include
   
6. rm -rf lib
   
7. rm -rf README
   
8. rm -rf LICENSE
   
9. rm -rf share
   
10. rm -rf support-files
    
11. rm -rf man

复制代码

6、备份mysqld 启动文件

先备份/etc/init.d/ 下的mysqld 再 删除/etc/init.d/ 下的mysqld

1. cd /etc/init.d/
   
2. 
   
3. tar zcf mysqld_5.7.42.tar.gz mysqld
   
4. 
   
5. rm -rf /etc/init.d/mysqld

复制代码

7、上传、解压安装包（开始升级）

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

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

1. mkdir /root/mysql8.2.0-glibc
   
2. 
   
3. ##解压
   
4. tar -xvf mysql-8.2.0-linux-glibc2.28-x86_64.tar -C /root/mysql8.2.0-glibc

复制代码

1. ##继续解压
   
2. cd /root/mysql8.2.0-glibc
   
3. 
   
4. tar -xvf mysql-8.2.0-linux-glibc2.28-x86_64.tar.xz

复制代码

将新解压的mysql-8.2.0目录复制到原来mysql安装目录下（/usr/local/mysql/），并修改文件权限。

1. [root@db-mysql mysql8.2.0-glibc]# mv mysql-8.2.0-linux-glibc2.17-x86_64/* /usr/local/mysql

复制代码

1. ##修改文件权限
   
2. 
   
3. chown -R mysql:mysql /usr/local/mysql

复制代码

8、复制新版启动文件到/etc/init.d/

将新解压的mysql-8.2.0 的mysqld复制到/etc/init.d/ 下。

1. cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld

复制代码

9、修改mysqld参数

1. vi /etc/init.d/mysqld

复制代码

和旧版保持一致。

1. datadir=/usr/local/mysql/data
   
2. basedir=/usr/local/mysql

复制代码

10、启动数据库

1. [root@db-mysql ~]# systemctl daemon-reload
   
2. [root@db-mysql ~]#
   
3. [root@db-mysql ~]# systemctl start mysqld
   
4. [root@db-mysql ~]#
   
5. [root@db-mysql ~]# systemctl status mysqld

复制代码

11、升级验证

1、检查登录正常

1. mysql -u root -p
   
2. 
   
3. [root@db-mysql mysql]# mysql -u root -p
   
4. Enter password:
   
5. Welcome to the MySQL monitor.  Commands end with ; or \g.
   
6. Your MySQL connection id is 10
   
7. Server version: 8.2.0 MySQL Community Server - GPL
   
8. 
   
9. Copyright (c) 2000, 2023, Oracle and/or its affiliates.
   
10. 
    
11. Oracle is a registered trademark of Oracle Corporation and/or its
    
12. affiliates. Other names may be trademarks of their respective
    
13. owners.
    
14. 
    
15. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
    
16. 
    
17. 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_update          |
   
6. | information_schema |
   
7. | mysql              |
   
8. | performance_schema |
   
9. | sys                |
   
10. +--------------------+
    
11. 5 rows in set (0.01 sec)
    
12. 
    
13. mysql> use db_update;
    
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> show tables;
    
19. +---------------------+
    
20. | Tables_in_db_update |
    
21. +---------------------+
    
22. | users               |
    
23. +---------------------+
    
24. 1 row in set (0.00 sec)
    
25. 
    
26. mysql> select * from users;
    
27. +----+---------+------+---------------------+
    
28. | id | name    | age  | email               |
    
29. +----+---------+------+---------------------+
    
30. |  1 | Alice   |   25 | alice@example.com   |
    
31. |  2 | Bob     |   30 | bob@example.com     |
    
32. |  3 | Charlie |   22 | charlie@example.com |
    
33. |  4 | David   |   28 | david@example.com   |
    
34. |  5 | Eve     |   35 | eve@example.com     |
    
35. +----+---------+------+---------------------+
    
36. 5 rows in set (0.00 sec)
    
37. 
    
38. mysql>

复制代码

到此这篇关于mysql-5.7.42升级到mysql-8.2.0 (二进制方式)的文章就介绍到这了,更多相关mysql-5.7.42升级到mysql-8.2.0 内容请搜索脚本之家以前的文章或继续浏览下面的相关文章希望大家以后多多支持脚本之家！

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