Mysql5.7.30 升级到最新版本Mysql8.0.27

因为Oracle MySQL Server 远程执行代码漏洞（CVE-2022- 21278、CVE-2022-21351）Oracle MySQL Server 存在远程执行代码漏洞。 利用该漏洞的攻击者，可在目标主机执行恶意代码，进一步控制 主机。

需要对正在运行的mysql进行升级。

当前版面mysql为5.7.30 ，目标升级版本为8.0.27

之前搭建的mysql版本设置可以看我上一个文章

CSDNhttps://mp.csdn.net/mp_blog/creation/editor/122596558

因环境需要，该次升级是在redhat系统下进行升级。同时因为跨大版本进行升级，最好对数据库进行全量备份。

cat /etc/redhat-release 
Red Hat Enterprise Linux Server release 7.4 (Maipo)

1.1 下载解压安装包

官网下载对应版本的tar包，可通过wget下载或者本地下载后上传。

下载地址：
https://downloads.mysql.com/archives/community/
选择mysql-8.0.27-el7-x86_64.tar.gz

安装包上传到原目录/data/apps/

解压

tar -zxvf mysql-8.0.27-el7-x86_64.tar.gz 

登录原版本mysql，执行下列命令

mysql>  show variables like 'innodb_fast_shutdown';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| innodb_fast_shutdown | 1     |
+----------------------+-------+
1 row in set (0.01 sec)

# 确保数据都刷到硬盘上，更改成0
mysql> 
mysql> set global innodb_fast_shutdown=0;
Query OK, 0 rows affected (0.00 sec)

#关闭数据库
mysql> shutdown;
Query OK, 0 rows affected (0.00 sec)

mysql> exit

解除原mysql软连接，进入 /usr/bin 目录

unlink mysql

因5.7版本与8.0版本参数有所不同，我们需要更改部分配置参数。下面展示下更改后的配置文件：

先复制原版本配置文件到新版本

cp /data/apps/mysql-5.7.30-linux-glibc2.12-x86_64/conf/my.cnf /data/apps/mysql-8.0.27-el7-x86_64/conf/

修改新版本my.cnf

[client]

default-character-set=utf8

socket=/data/apps/data/mysql/socket/mysql.sock



[mysqld]

character_set_server=utf8

lower_case_table_names=1

max_allowed_packet=512M

max_heap_table_size=1024M

tmp_table_size=1024M

max_connect_errors=20

max_connections=4500

max_user_connections=4500



#查询排序缓冲区大小，对order group起作用

sort_buffer_size=1024M

#读操作缓冲区大小

read_buffer_size=4096M

#开启log_bin用户使用函数需要

log_bin_trust_function_creators=1

#连接时长控制

wait_timeout=1000

interactive_timeout=1000

expire_logs_days=15

basedir=/data/apps/mysql

datadir=/data/apps/data/mysql/data

user=root

tmpdir=/data/apps/data/mysql/tmp

socket=/data/apps/data/mysql/socket/mysql.sock

pid-file=/data/apps/data/mysql/run/mysql.pid

log-error=/data/apps/data/mysql/logs/mysql.err

port=3307

#需要改的配置参数
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
collation_server = utf8_general_ci
default_authentication_plugin=mysql_native_password
#关闭SSL
skip_ssl


[mysql]

no-auto-rehash

default-character-set = utf8

回到/data/apps目录，创建软连接

ln -s mysql-8.0.27-el7-x86_64 mysql

修改/data/apps/mysql/support-files/mysql.server，设置basedir和datadir路径

# If you change base dir, you must also change datadir. These may get
# overwritten by settings in the MySQL configuration files.

basedir=/data/apps/mysql
datadir=/data/apps/data/mysql/data

保存后，直接service mysql start启动服务

cat /data/apps/data/mysql/logs/mysql.err

2022-01-24T02:57:52.510705Z 0 [Warning] [MY-000081] [Server] option 'read_buffer_size': unsigned value 4294967296 adjusted to 2147479552.
2022-01-24T02:57:52.510749Z 0 [Warning] [MY-011068] [Server] The syntax 'expire-logs-days' is deprecated and will be removed in a future release. Please use binlog_expire_logs_seconds instead.
2022-01-24T02:57:52.510790Z 0 [Warning] [MY-011068] [Server] The syntax '--ssl=off' is deprecated and will be removed in a future release. Please use --tls-version=invalid instead.
2022-01-24T02:57:52.513312Z 0 [Warning] [MY-010918] [Server] 'default_authentication_plugin' is deprecated and will be removed in a future release. Please use authentication_policy instead.
2022-01-24T02:57:52.513341Z 0 [System] [MY-010116] [Server] /data/apps/mysql/bin/mysqld (mysqld 8.0.27) starting as process 2478
2022-01-24T02:57:52.572074Z 0 [Warning] [MY-013242] [Server] --character-set-server: 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
2022-01-24T02:57:52.572085Z 0 [Warning] [MY-013244] [Server] --collation-server: 'utf8_general_ci' is a collation of the deprecated character set UTF8MB3. Please consider using UTF8MB4 with an appropriate collation instead.
2022-01-24T02:57:52.692936Z 1 [System] [MY-011012] [Server] Starting upgrade of data directory.
2022-01-24T02:57:52.693068Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2022-01-24T02:57:54.067446Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2022-01-24T02:57:57.314373Z 2 [System] [MY-011003] [Server] Finished populating Data Dictionary tables with data.
2022-01-24T02:57:59.226669Z 5 [System] [MY-013381] [Server] Server upgrade from '50700' to '80027' started.
2022-01-24T02:58:07.259527Z 5 [System] [MY-013381] [Server] Server upgrade from '50700' to '80027' completed.
2022-01-24T02:58:07.479272Z 0 [Warning] [MY-011302] [Server] Plugin mysqlx reported: 'Failed at SSL configuration: "SSL context is not usable without certificate and private key"'
2022-01-24T02:58:07.479521Z 0 [System] [MY-010931] [Server] /data/apps/mysql/bin/mysqld: ready for connections. Version: '8.0.27'  socket: '/data/apps/data/mysql/socket/mysql.sock'  port: 3307  MySQL Community Server - GPL.
2022-01-24T02:58:07.479802Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /tmp/mysqlx.sock

服务启动成功。

登录mysql进行验证，查询数据暂无问题，升级成功。

[root@xc4 support-files]# mysql -uroot -p******
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 8.0.27 MySQL Community Server - GPL

Copyright (c) 2000, 2021, 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> select version();
+-----------+
| version() |
+-----------+
| 8.0.27    |
+-----------+
1 row in set (0.00 sec)

mysql> use dsjpt
Database changed
mysql> show tables;
+-----------------+
| Tables_in_dsjpt |
+-----------------+
| test            |
+-----------------+
1 row in set (0.01 sec)

mysql> select * from test;
+------+------+
| id   | name |
+------+------+
|    1 | tom  |
+------+------+
1 row in set (0.00 sec)

mysql> 
mysql> 
mysql> \q
Bye