MySQL数据库管理-rpm包mysql数据库升级8.0.30-8.0.31

1 mysql小版本升级

本文档适用于rpm安装的mysql服务的小版本升级。例如从8.0.30升级至8.0.31。

1.1 确认版本信息

rpm -qa|grep mysql
mysql -V

1.2 下载安装包

MySQL :: Download MySQL Community Server

以升级8.0.31为例，下载mysql-8.0.31-1.el7.x86_64.rpm-bundle.tar

1.3 解压

tar -xvf mysql-8.0.31-1.el7.x86_64.rpm-bundle.tar 

1.4 逻辑备份数据库

mysqldump -u root -h host -p --all-databases > /data/backdb.sql

1.5 停服务

service mysqld stop
systemctl stop mysqld

1.5 备份数据库配置文件及数据文件

a、/etc/my.cnf（默认位置） 配置文件

b、/var/lib/mysql（默认位置） 数据文件，具体位置见配置文件

1.7 升级安装

[root@centos7 mysql8.0.31]# rpm -Uvh mysql-community-*
警告：mysql-community-client-8.0.31-1.el7.x86_64.rpm: 头V4 RSA/SHA256 Signature, 密钥 ID 3a79bd29: NOKEY
准备中...                          ################################# [100%]
正在升级/安装...
   1:mysql-community-common-8.0.31-1.e################################# [  4%]
   2:mysql-community-client-plugins-8.################################# [  9%]
   3:mysql-community-libs-8.0.31-1.el7################################# [ 13%]
   4:mysql-community-client-8.0.31-1.e################################# [ 17%]
   5:mysql-community-icu-data-files-8.################################# [ 22%]
   6:mysql-community-server-8.0.31-1.e################################# [ 26%]
   7:mysql-community-server-debug-8.0.################################# [ 30%]
   8:mysql-community-test-8.0.31-1.el7################################# [ 35%]
   9:mysql-community-devel-8.0.31-1.el################################# [ 39%]
  10:mysql-community-libs-compat-8.0.3################################# [ 43%]
  11:mysql-community-embedded-compat-8################################# [ 48%]
  12:mysql-community-debuginfo-8.0.31-################################# [ 52%]
正在清理/删除...
  13:mysql-community-devel-8.0.30-1.el################################# [ 57%]
  14:mysql-community-debuginfo-8.0.30-################################# [ 61%]
  15:mysql-community-server-debug-8.0.################################# [ 65%]
  16:mysql-community-server-8.0.30-1.e################################# [ 70%]
  17:mysql-community-client-8.0.30-1.e################################# [ 74%]
  18:mysql-community-libs-compat-8.0.3################################# [ 78%]
  19:mysql-community-libs-8.0.30-1.el7################################# [ 83%]
  20:mysql-community-embedded-compat-8################################# [ 87%]
  21:mysql-community-common-8.0.30-1.e################################# [ 91%]
  22:mysql-community-icu-data-files-8.################################# [ 96%]
  23:mysql-community-client-plugins-8.################################# [100%]

7、确认新版本信息和数据完整性

[root@centos7 mysql8.0.31]# mysql -V
mysql  Ver 8.0.31 for Linux on x86_64 (MySQL Community Server - GPL)
​
[root@centos7 mysql8.0.31]# mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 8.0.31 MySQL Community Server - GPL
​
Copyright (c) 2000, 2022, 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 mysql5.7升级至8.0（8.0.31）

2.1 下载rpm包和检测升级工具

[root@host mysql8]# ll
总用量 930212
-rw-r--r-- 1 root root 906106880 10月 28 09:52 mysql-8.0.31-1.el7.x86_64.rpm-bundle.tar
-rw-r--r-- 1 root root  46425904 10月 31 09:13 mysql-shell-8.0.31-1.el7.x86_64.rpm

2.2 安装mysql-shell,检测数据库

跨版本升级最好先将小版本提升带最新。

#安装mysql-shell工具
[root@host mysql8]# rpm -ivh mysql-shell-8.0.31-1.el7.x86_64.rpm 
警告：mysql-shell-8.0.31-1.el7.x86_64.rpm: 头V4 RSA/SHA256 Signature, 密钥 ID 3a79bd29: NOKEY
准备中...                          ################################# [100%]
正在升级/安装...
   1:mysql-shell-8.0.31-1.el7         ################################# [100%]
​
#执行升级检测
[root@host mysql8]# mysqlsh root@127.0.0.1:3306 -e 'util.checkForServerUpgrade();'
Please provide the password for 'root@127.0.0.1:3306': *********
Save password for 'root@127.0.0.1:3306'? [Y]es/[N]o/Ne[v]er (default No): Y
The MySQL server at 127.0.0.1:3306, version 5.7.38 - MySQL Community Server
(GPL), will now be checked for compatibility issues for upgrade to MySQL
8.0.31...
​
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
  Warning: The following objects use the utf8mb3 character set. It is
    recommended to convert them to use utf8mb4 instead, for improved Unicode
    support.
  More information:
    https://dev.mysql.com/doc/refman/8.0/en/charset-unicode-utf8mb3.html
​
  ocp_test - schema's default character set: utf8
  ocp_test.t1.name - column's default character set: utf8
​
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
​
  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
​
Errors:   0
Warnings: 3
Notices:  1
​
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.

注意：在以上检测中，检测到error为0就可以正常升级了。

2.3 字符集注意事项

#数据库字符集若是为UTF8则需要指定参数文件字符集，MySQL8.0默认UTF8mb4
 
vim /etc/my.cnf
 
[client]
default-character-set=utf8
...
[mysqld]
...
character-set-client-handshake = FALSE
character-set-server = utf8
collation-server = utf8_unicode_ci
init_connect='SET NAMES utf8'
...

2.4 备份mysql,关闭MySQL实例

如果数据量不是非常大，建议同时做逻辑和物理备份。

#逻辑备份
[root@host mysql8]# mysqlpump -u root -p --all-databases > all-databases.sql
#物理备份
[root@host mysql8]# cp -r /var/lib/mysql /var/lib/mysql_5.7
[root@host mysql8]# systemctl stop mysqld

2.5 卸载5.7rpm包

#检查原rpm包
[root@host mysql8]# rpm -qa |grep mysql -i
zabbix-proxy-mysql-6.2.3-release1.el7.x86_64
mysql-community-libs-compat-5.7.38-1.el7.x86_64
mysql-community-embedded-5.7.38-1.el7.x86_64
mysql-community-client-5.7.38-1.el7.x86_64
mysql-shell-8.0.31-1.el7.x86_64
mysql-community-test-5.7.38-1.el7.x86_64
perl-DBD-MySQL-4.023-6.el7.x86_64
mysql-community-devel-5.7.38-1.el7.x86_64
mysql-community-libs-5.7.38-1.el7.x86_64
mysql-community-embedded-compat-5.7.38-1.el7.x86_64
php-mysql-5.4.16-48.el7.x86_64
mysql-community-embedded-devel-5.7.38-1.el7.x86_64
mysql-community-server-5.7.38-1.el7.x86_64
mysql-community-common-5.7.38-1.el7.x86_64
#通过以上命令，可以检测出原rpm包是以mysql-community开头的所有包
#因涉及到多个依赖关系，在卸载是注意不要卸载依赖关系。使用以下命令，强制卸载且不破坏依赖
[root@host mysql8]# rpm -qa|grep mysql-community | xargs rpm -e --nodeps

2.6 安装新版本rpm包

[root@host mysql8]# tar -xvf mysql-8.0.31-1.el7.x86_64.rpm-bundle.tar 
#使用yum localinstall可以解决依赖问题
[root@host mysql8]# yum localinstall mysql-community-* -y

2.7 启动myql，自动升级

[root@host mysql8]# systemctl start mysqld
注意查看日志文件，检查是否升级成功。

2022-10-31T01:39:35.450377Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.31) starting as process 8355
2022-10-31T01:39:35.485825Z 1 [System] [MY-011012] [Server] Starting upgrade of data directory.
2022-10-31T01:39:35.485873Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2022-10-31T01:39:35.869312Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2022-10-31T01:39:36.193512Z 0 [Warning] [MY-013129] [Server] A message intended for a client cannot be sent there as no client-session is attached. Therefore, we're sending the information to the error-log instead: MY-001287 - 'validate password plugin' is deprecated and will be removed in a future release. Please use validate_password component instead
2022-10-31T01:39:36.634381Z 2 [System] [MY-011003] [Server] Finished populating Data Dictionary tables with data.
2022-10-31T01:39:37.090241Z 5 [System] [MY-013381] [Server] Server upgrade from '50700' to '80031' started.
2022-10-31T01:39:40.860985Z 5 [System] [MY-013381] [Server] Server upgrade from '50700' to '80031' completed.
2022-10-31T01:39:40.942099Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2022-10-31T01:39:40.942147Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2022-10-31T01:39:40.956032Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.31'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL Community Server - GPL.
2022-10-31T01:39:40.956033Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /var/run/mysqld/mysqlx.sock

2.8 确认新版本信息和数据完整性

[root@host ~]# mysql -V
mysql  Ver 8.0.31 for Linux on x86_64 (MySQL Community Server - GPL)
[root@host ~]# mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 8.0.31 MySQL Community Server - GPL
 
Copyright (c) 2000, 2022, 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.
 

————————————————
版权声明：本文为CSDN博主「Winter Liu」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/m0_38004228/article/details/127901630