技术标签: msyql rpm方式升级 # 数据库--mysql 运维 Linux 专栏 mysql mysql5. 7升级8.2 # Linux运维笔记
The Begin点点关注,收藏不迷路
|
注:本文在测试环境升级测试,建议先在测试环境验证。mysql-5.7.42为rpm安装,所以用rpm方式升级
1、数据备份:在升级之前,务必对当前的 MySQL 5.7 数据库进行完整备份,以防止数据丢失或损坏。确保备份文件的安全存储,并测试其可恢复性。
2、安全性考虑:建议在测试环境中进行升级测试,以模拟真实场景并检测潜在问题。这有助于减少生产环境中的不确定性和风险。
下载地址: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 /var/lib/mysql/mysql.sock -e "util.checkForServerUpgrade()"
检查结果报告:
[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 ~]#
检查结果显示:
没有发现使用旧的时间类型的问题。
没有发现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>'语法的问题。
没有发现具有指向来自不同数据库引擎的表的外键的列的问题。
总结:
错误:0
警告:1
注意事项:2
1、查看当前数据库版本
mysql> select@@version;
+-----------+
| @@version |
+-----------+
| 5.7.42 |
+-----------+
1 row in set (0.00 sec)
mysql>
2、操作系统版本
[root@zyl-server ~]# cat /etc/redhat-release
CentOS Linux release 7.9.2009 (Core)
[root@zyl-server ~]#
[root@zyl-server ~]#
[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
下载mysql8.2.0:
https://downloads.mysql.com/archives/community/
创建"mysql8.2.0-bundle"的文件夹,用于存放解压文件。
mkdir mysql8.2.0-bundle
tar -xvf mysql-8.2.0-1.el7.x86_64.rpm-bundle.tar
列出系统中所有与 mysql 相关的 RPM 软件包。
rpm -qa|grep -i mysql
[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]#
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、检查升级版本
已由mysql-5.7.42 升级到 mysql-8.2.0 。
mysql> select@@version;
+-----------+
| @@version |
+-----------+
| 8.2.0 |
+-----------+
1 row in set (0.00 sec)
mysql>
3、检查数据
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>
The End点点关注,收藏不迷路
|
文章浏览阅读1.6k次。安装配置gi、安装数据库软件、dbca建库见下:http://blog.csdn.net/kadwf123/article/details/784299611、检查集群节点及状态:[root@rac2 ~]# olsnodes -srac1 Activerac2 Activerac3 Activerac4 Active[root@rac2 ~]_12c查看crs状态
文章浏览阅读1.3w次,点赞45次,收藏99次。我个人用的是anaconda3的一个python集成环境,自带jupyter notebook,但在我打开jupyter notebook界面后,却找不到对应的虚拟环境,原来是jupyter notebook只是通用于下载anaconda时自带的环境,其他环境要想使用必须手动下载一些库:1.首先进入到自己创建的虚拟环境(pytorch是虚拟环境的名字)activate pytorch2.在该环境下下载这个库conda install ipykernelconda install nb__jupyter没有pytorch环境
文章浏览阅读5.2k次,点赞19次,收藏28次。选择scoop纯属意外,也是无奈,因为电脑用户被锁了管理员权限,所有exe安装程序都无法安装,只可以用绿色软件,最后被我发现scoop,省去了到处下载XXX绿色版的烦恼,当然scoop里需要管理员权限的软件也跟我无缘了(譬如everything)。推荐添加dorado这个bucket镜像,里面很多中文软件,但是部分国外的软件下载地址在github,可能无法下载。以上两个是官方bucket的国内镜像,所有软件建议优先从这里下载。上面可以看到很多bucket以及软件数。如果官网登陆不了可以试一下以下方式。_scoop-cn
文章浏览阅读4.5k次,点赞2次,收藏3次。首先要有一个color-picker组件 <el-color-picker v-model="headcolor"></el-color-picker>在data里面data() { return {headcolor: ’ #278add ’ //这里可以选择一个默认的颜色} }然后在你想要改变颜色的地方用v-bind绑定就好了,例如:这里的:sty..._vue el-color-picker
文章浏览阅读640次。基于芯片日益增长的问题,所以内核开发者们引入了新的方法,就是在内核中只保留函数,而数据则不包含,由用户(应用程序员)自己把数据按照规定的格式编写,并放在约定的地方,为了不占用过多的内存,还要求数据以根精简的方式编写。boot启动时,传参给内核,告诉内核设备树文件和kernel的位置,内核启动时根据地址去找到设备树文件,再利用专用的编译器去反编译dtb文件,将dtb还原成数据结构,以供驱动的函数去调用。firmware是三星的一个固件的设备信息,因为找不到固件,所以内核启动不成功。_exynos 4412 刷机
文章浏览阅读2w次,点赞24次,收藏42次。Linux系统配置jdkLinux学习教程,Linux入门教程(超详细)_linux配置jdk
文章浏览阅读3.3k次,点赞5次,收藏19次。xlabel('\delta');ylabel('AUC');具体符号的对照表参照下图:_matlab微米怎么输入
文章浏览阅读119次。顺序读写指的是按照文件中数据的顺序进行读取或写入。对于文本文件,可以使用fgets、fputs、fscanf、fprintf等函数进行顺序读写。在C语言中,对文件的操作通常涉及文件的打开、读写以及关闭。文件的打开使用fopen函数,而关闭则使用fclose函数。在C语言中,可以使用fread和fwrite函数进行二进制读写。 Biaoge 于2024-03-09 23:51发布 阅读量:7 ️文章类型:【 C语言程序设计 】在C语言中,用于打开文件的函数是____,用于关闭文件的函数是____。
文章浏览阅读3.4k次,点赞2次,收藏13次。跟随鼠标移动的粒子以grid(SOP)为partical(SOP)的资源模板,调整后连接【Geo组合+point spirit(MAT)】,在连接【feedback组合】适当调整。影响粒子动态的节点【metaball(SOP)+force(SOP)】添加mouse in(CHOP)鼠标位置到metaball的坐标,实现鼠标影响。..._touchdesigner怎么让一个模型跟着鼠标移动
文章浏览阅读178次。项目运行环境配置:Jdk1.8 + Tomcat7.0 + Mysql + HBuilderX(Webstorm也行)+ Eclispe(IntelliJ IDEA,Eclispe,MyEclispe,Sts都支持)。项目技术:Springboot + mybatis + Maven +mysql5.7或8.0+html+css+js等等组成,B/S模式 + Maven管理等等。环境需要1.运行环境:最好是java jdk 1.8,我们在这个平台上运行的。其他版本理论上也可以。_基于java技术的停车场管理系统实现与设计
文章浏览阅读3.5k次。前言对于MediaPlayer播放器的源码分析内容相对来说比较多,会从Java-&amp;gt;Jni-&amp;gt;C/C++慢慢分析,后面会慢慢更新。另外,博客只作为自己学习记录的一种方式,对于其他的不过多的评论。MediaPlayerDemopublic class MainActivity extends AppCompatActivity implements SurfaceHolder.Cal..._android多媒体播放源码分析 时序图
文章浏览阅读2.4k次,点赞41次,收藏13次。java 数据结构与算法 ——快速排序法_快速排序法