电商网站开发模板,微信公众号运营教程,网站怎么 备案,公司网站制作流程2016实现#xff1a;mysql-5.7.42 到 mysql-8.2.0 的升级#xff08;二进制方式#xff09; 1、操作环境1、查看当前数据库版本2、操作系统版本3、查看 Linux 系统上的 glibc#xff08;GNU C 库#xff09;版本#xff08;**这里很重要#xff0c;要下载对应的内核mysql版本… 实现mysql-5.7.42 到 mysql-8.2.0 的升级二进制方式 1、操作环境1、查看当前数据库版本2、操作系统版本3、查看 Linux 系统上的 glibcGNU C 库版本**这里很重要要下载对应的内核mysql版本** 2、升级准备1、使用mysql-shell 检查工具检查兼容性 3、mysqldump 导出数据文件和备份my.cnf4、备份旧版mysql-5.7.42 安装目录下的文件和my.cnf文件重要5、删除旧版安装目录/usr/local/mysql下(bin、docs、includ、lib、share、support-files、LICENSE、README)6、备份mysqld 启动文件7、上传、解压安装包开始升级8、复制新版启动文件到/etc/init.d/9、修改mysqld参数10、启动数据库11、升级验证 The Begin点点关注收藏不迷路 注本文在测试环境升级测试建议先在测试环境验证。在生产环境下还是先评估下mysql-5.7.42为二进制方式安装所以用mysql-8.2.0二进制包升级
1、操作环境
1、查看当前数据库版本
mysql selectversion;
-----------
| version |
-----------
| 5.7.42 |
-----------
1 row in set (0.00 sec)mysql 2、操作系统版本
[rootzyl-server ~]# cat /etc/redhat-release
CentOS Linux release 7.9.2009 (Core)
[rootzyl-server ~]#
[rootzyl-server ~]# 3、查看 Linux 系统上的 glibcGNU C 库版本这里很重要要下载对应的内核mysql版本
ldd --version或者
rpm -q glibc**查看当前系统中的libstdc版本**strings /usr/lib64/libstdc.so.6 | grep GLIBCXX
strings /usr/lib64/libstdc.so.6 | grep CXXABI
strings /usr/lib64/libstdc.so.6 | grep GLIBC
strings /usr/lib64/libstdc.so.6 | grep GLIBCXX 否则后面升级完成后启动mysql会报错 报错如下
错误表明在启动MySQL服务时出现了一些问题。主要的错误信息是关于缺少特定库文件的版本比如GLIBCXX_3.4.20、CXXABI_1.3.9、CXXABI_1.3.8、GLIBCXX_3.4.21以及GLIBC_2.25。
这是由于安装的MySQL版本与系统中可用的标准库版本不兼容导致的所以要对应下载相应的内核版本。
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
Mar 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.32、升级准备
1、数据备份在升级之前务必对当前的 MySQL 5.7 数据库进行完整备份以防止数据丢失或损坏。确保备份文件的安全存储并测试其可恢复性。
2、安全性考虑建议在测试环境中进行升级测试以模拟真实场景并检测潜在问题。这有助于减少生产环境中的不确定性和风险。
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检查该版本是否可以升级到MySQL 8.2.0
mysqlsh -uroot -p -S /tmp/mysqld.sock -e util.checkForServerUpgrade() 注意:mysql.sock 地址在/etc/my.cnf 文件中查看。 检查结果报告
[rootdb-mysql ~]# mysqlsh -uroot -p -S /tmp/mysqld.sock -e util.checkForServerUpgrade()
Please provide the password for root/tmp%2Fmysqld.sock: ******
Save password for root/tmp%2Fmysqld.sock? [Y]es/[N]o/Ne[v]er (default No):
The MySQL server at /tmp%2Fmysqld.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 typeNo issues found2) MySQL 8.0 syntax check for routine-like objectsNo issues found3) Usage of db objects with names conflicting with new reserved keywordsNo issues found4) Usage of utf8mb3 charsetWarning: The following objects use the utf8mb3 character set. It isrecommended to convert them to use utf8mb4 instead, for improved Unicodesupport.More information:https://dev.mysql.com/doc/refman/8.0/en/charset-unicode-utf8mb3.htmldb_update - schemas default character set: utf8db_update.users.name - columns default character set: utf8db_update.users.email - columns default character set: utf85) Table names in the mysql schema conflicting with new tables in 8.0No issues found6) Partitioned tables using engines with non native partitioningNo issues found7) Foreign key constraint names longer than 64 charactersNo issues found8) Usage of obsolete MAXDB sql_mode flagNo issues found9) Usage of obsolete sql_mode flagsNotice: The following DB objects have obsolete options persisted forsql_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-removalsglobal system variable sql_mode - defined using obsolete NO_AUTO_CREATE_USERoption10) ENUM/SET column definitions containing elements longer than 255 charactersNo issues found11) Usage of partitioned tables in shared tablespacesNo issues found12) Circular directory references in tablespace data file pathsNo issues found13) Usage of removed functionsNo issues found14) Usage of removed GROUP BY ASC/DESC syntaxNo issues found15) Removed system variables for error logging to the system log configurationTo run this check requires full path to MySQL server configuration file to be specified at configPath key of options dictionaryMore information:https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-13.html#mysqld-8-0-13-logging16) Removed system variablesTo run this check requires full path to MySQL server configuration file to be specified at configPath key of options dictionaryMore information:https://dev.mysql.com/doc/refman/8.0/en/added-deprecated-removed.html#optvars-removed17) System variables with new default valuesTo run this check requires full path to MySQL server configuration file to be specified at configPath key of options dictionaryMore information:https://mysqlserverteam.com/new-defaults-in-mysql-8-0/18) Zero Date, Datetime, and Timestamp valuesNo issues found19) Schema inconsistencies resulting from file removal or corruptionNo issues found20) Tables recognized by InnoDB that belong to a different engineNo issues found21) Issues reported by check table x for upgrade commandNo issues found22) New default authentication plugin considerationsWarning: The new default authentication plugin caching_sha2_password offersmore secure password hashing than previously used mysql_native_password(and consequent improved client connection authentication). However, it alsohas 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 thoseissues is to reconfigure the server to revert to the previous defaultauthentication plugin (mysql_native_password). For example, use these linesin the server option file:[mysqld]default_authentication_pluginmysql_native_passwordHowever, the setting should be viewed as temporary, not as a long term orpermanent solution, because it causes new accounts created with the settingin effect to forego the improved authentication security.If you are using replication please take time to understand how theauthentication 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-issueshttps://dev.mysql.com/doc/refman/8.0/en/upgrading-from-previous-series.html#upgrade-caching-sha2-password-replication23) Columns which cannot have default valuesNo issues found24) Check for invalid table names and schema names used in 5.7No issues found25) Check for orphaned routines in 5.7No issues found26) Check for deprecated usage of single dollar signs in object namesNo issues found27) Check for indexes that are too large to work on higher versions of MySQL
Server than 5.7No issues found28) Check for deprecated .table syntax used in routines.No issues found29) Check for columns that have foreign keys pointing to tables from a diffrent
database engine.No issues foundErrors: 0
Warnings: 4
Notices: 1NOTE: 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.检查结果显示
没有发现使用旧时态类型的问题
没有发现与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语法的问题
没有发现具有外键指向不同数据库引擎的表的列的问题总结错误0
警告4
注意事项1总结来说检查未发现会阻止升级的致命错误但检测到了一些潜在问题。在升级之前请确保报告的问题并不重要。
3、mysqldump 导出数据文件和备份my.cnf
[rootdb-mysql ~]# mysqldump -hlocalhost -uroot -p --all-databases /home/db_back_2024.sql
Enter password:
[rootdb-mysql ~]# cd /home/
[rootdb-mysql home]# ll
total 872
-rw-r--r-- 1 root root 889253 Mar 20 20:25 db_back_2024.sql
drwx------ 2 oracle oinstall 127 Mar 18 05:12 oracle
drwx------. 2 zyl zyl 62 Mar 16 22:24 zyl
[rootdb-mysql home]# cp /etc/my.cnf /home/5.7.37_my.cnf[rootzyl-mysql home]# 升级前先停止数据库。
[rootdb-mysql home]# systemctl stop mysqld4、备份旧版mysql-5.7.42 安装目录下的文件和my.cnf文件重要
这里的安装目录在/usr/local/mysql根据自己实际备份。 cd /usr/local/mkdir mysql-5.7.42_bk##全部备份 mysql目录下的文件
cp -R mysql/* mysql-5.7.42_bk### 或者直接压缩
### tar -zcvf mysql-5.7.42_bk.tar.gz mysql
[rootdb-mysql bin]# tar zcf mysql-5.7.42_bk.tar.gz mysql5、删除旧版安装目录/usr/local/mysql下(bin、docs、includ、lib、share、support-files、LICENSE、README) cd /usr/local/mysqlrm -rf bin
rm -rf docs
rm -rf include
rm -rf lib
rm -rf README
rm -rf LICENSE
rm -rf share
rm -rf support-files
rm -rf man6、备份mysqld 启动文件
先备份/etc/init.d/ 下的mysqld 再 删除/etc/init.d/ 下的mysqld
cd /etc/init.d/tar zcf mysqld_5.7.42.tar.gz mysqldrm -rf /etc/init.d/mysqld7、上传、解压安装包开始升级
下载mysql8.2.0:
https://downloads.mysql.com/archives/community/ 创建mysql8.2.0-glibc的文件夹用于存放解压文件。
mkdir /root/mysql8.2.0-glibc##解压
tar -xvf mysql-8.2.0-linux-glibc2.28-x86_64.tar -C /root/mysql8.2.0-glibc##继续解压
cd /root/mysql8.2.0-glibctar -xvf mysql-8.2.0-linux-glibc2.28-x86_64.tar.xz将新解压的mysql-8.2.0目录复制到原来mysql安装目录下/usr/local/mysql/并修改文件权限。
[rootdb-mysql mysql8.2.0-glibc]# mv mysql-8.2.0-linux-glibc2.17-x86_64/* /usr/local/mysql##修改文件权限chown -R mysql:mysql /usr/local/mysql8、复制新版启动文件到/etc/init.d/
将新解压的mysql-8.2.0 的mysqld复制到/etc/init.d/ 下。
cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld9、修改mysqld参数
vi /etc/init.d/mysqld和旧版保持一致。
datadir/usr/local/mysql/data
basedir/usr/local/mysql10、启动数据库
[rootdb-mysql ~]# systemctl daemon-reload
[rootdb-mysql ~]#
[rootdb-mysql ~]# systemctl start mysqld
[rootdb-mysql ~]#
[rootdb-mysql ~]# systemctl status mysqld11、升级验证
1、检查登录正常
mysql -u root -p[rootdb-mysql mysql]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.2.0 MySQL Community Server - GPLCopyright (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、检查升级版本
已由mysql-5.7.42 升级到 mysql-8.2.0 。
mysql selectversion;
-----------
| version |
-----------
| 8.2.0 |
-----------
1 row in set (0.00 sec)mysql 3、检查数据
mysql show databases;
--------------------
| Database |
--------------------
| db_update |
| information_schema |
| mysql |
| performance_schema |
| sys |
--------------------
5 rows in set (0.01 sec)mysql use db_update;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -ADatabase changed
mysql show tables;
---------------------
| Tables_in_db_update |
---------------------
| users |
---------------------
1 row in set (0.00 sec)mysql select * from users;
----------------------------------------
| id | name | age | email |
----------------------------------------
| 1 | Alice | 25 | aliceexample.com |
| 2 | Bob | 30 | bobexample.com |
| 3 | Charlie | 22 | charlieexample.com |
| 4 | David | 28 | davidexample.com |
| 5 | Eve | 35 | eveexample.com |
----------------------------------------
5 rows in set (0.00 sec)mysql ❤️ 写在最后一定要下载与Linux 系统上的 glibcGNU C 库版本对应的mysql版本 The End点点关注收藏不迷路
