MySQL 5.7.31升级到8.0.26

1、升级前准备

1.1.mysql-shell验证

下载shell验证文件：

https://downloads.mysql.com/archives/shell/

解压验证：

[root@mysql mysql]# tar -zxvf mysql-shell-8.0.26-linux-glibc2.12-x86-64bit.tar.gz 
[root@mysql mysql]# cd mysql-shell-8.0.26-linux-glibc2.12-x86-64bit/bin
[root@mysql bin]# ./mysqlsh 
MySQL Shell 8.0.26

Copyright (c) 2016, 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 '\?' for help; '\quit' to exit.
 MySQL  JS > util.checkForServerUpgrade('root@192.168.56.88:3306', {"password":"1234qwer", "targetVersion":"8.0.26", "configPath":"/etc/my.cnf"})
The MySQL server at 192.168.56.88:3306, version 5.7.31-log - MySQL Community
Server (GPL), will now be checked for compatibility issues for upgrade to MySQL
8.0.26...

1) Usage of old temporal type
  No issues found

2) Usage of db objects with names conflicting with new reserved keywords
  No issues found

3) Usage of utf8mb3 charset
  No issues found

4) Table names in the mysql schema conflicting with new tables in 8.0
  No issues found

5) Partitioned tables using engines with non native partitioning
  No issues found

6) Foreign key constraint names longer than 64 characters
  No issues found

7) Usage of obsolete MAXDB sql_mode flag
  No issues found

8) 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

9) ENUM/SET column definitions containing elements longer than 255 characters
  No issues found

10) Usage of partitioned tables in shared tablespaces
  No issues found

11) Circular directory references in tablespace data file paths
  No issues found

12) Usage of removed functions
  No issues found

13) Usage of removed GROUP BY ASC/DESC syntax
  No issues found

14) Removed system variables for error logging to the system log configuration
  Check failed: Invalid group at line 5 of file '/etc/my.cnf': [client] socket = /mysql/my3306/mysql.sock.
  More information:
    https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-13.html#mysqld-8-0-13-logging

15) Removed system variables
  Check failed: Invalid group at line 5 of file '/etc/my.cnf': [client] socket = /mysql/my3306/mysql.sock.
  More information:
    https://dev.mysql.com/doc/refman/8.0/en/added-deprecated-removed.html#optvars-removed

16) System variables with new default values
  Check failed: Invalid group at line 5 of file '/etc/my.cnf': [client] socket = /mysql/my3306/mysql.sock.
  More information:
    https://mysqlserverteam.com/new-defaults-in-mysql-8-0/

17) Zero Date, Datetime, and Timestamp values
  Warning: By default zero date/datetime/timestamp values are no longer allowed
    in MySQL, as of 5.7.8 NO_ZERO_IN_DATE and NO_ZERO_DATE are included in
    SQL_MODE by default. These modes should be used with strict mode as they will
    be merged with strict mode in a future release. If you do not include these
    modes in your SQL_MODE setting, you are able to insert
    date/datetime/timestamp values that contain zeros. It is strongly advised to
    replace zero values with valid ones, as they may not work correctly in the
    future.
  More information:
    https://lefred.be/content/mysql-8-0-and-wrong-dates/

  global.sql_mode - does not contain either NO_ZERO_DATE or NO_ZERO_IN_DATE
    which allows insertion of zero dates
  session.sql_mode -  of 2 session(s) does not contain either NO_ZERO_DATE or
    NO_ZERO_IN_DATE which allows insertion of zero dates

18) Schema inconsistencies resulting from file removal or corruption
  No issues found

19) Tables recognized by InnoDB that belong to a different engine
  No issues found

20) Issues reported by 'check table x for upgrade' command
  No issues found

21) 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

Errors:   0
Warnings: 3
Notices:  1

或其他方式：
[root@mysql bin]#./mysqlsh --socket=/mysql/my3306/mysql.sock --user=root -e "util.checkForServerUpgrade()"
[root@mysql bin]#./mysqlsh -- util checkForServerUpgrade root@192.168.56.88:3306 --target-version=8.0.26 --output-format=JSON --config-path=/etc/my.cnf

1.2. 备份原数据库

防止升级过程中出现问题，导致数据丢失
设置 innodb_fast_shutdown=0，将数据库关闭方式调整为慢关闭， 确保数据都刷到硬盘上，更改成0，关库后进行备份。

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

mysql> set global innodb_fast_shutdown=0;
Query OK, 0 rows affected (0.00 sec)

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

逻辑备份：

[root@mysql bakup]# /mysql/base5731/bin/mysqldump -S /mysql/my3306/mysql.sock  -uroot -p --all-databases --set-gtid-purged=off>/mysql/bakup/mysql5731all.sql

物理备份：

查看数据目录

mysql> show global variables like '%datadir%';
+---------------+-------------------------+
| Variable_name | Value                   |
+---------------+-------------------------+
| datadir       | /mysqldata/my3306/data/ |
+---------------+-------------------------+
1 row in set (0.00 sec)

1.3.下载MySQL8.0.26

https://downloads.mysql.com/archives/community/

2、本地升级过程

2.1. 修改mysql配置文件

修改实际使用的目录如
#basedir=/mysql/base8026
#datadir=/mydata

2.2 升级

解压软件

[root@mysql]# xz -d mysql-8.0.26-linux-glibc2.12-x86_64.tar.xz
[root@mysql]# tar -xvf mysql-8.0.26-linux-glibc2.12-x86_64.tar
[root@mysql]# chown -R mysql:mysql mysql-8.0.26-linux-glibc2.12-x86_64
[root@mysql]# mv mysql-8.0.26-linux-glibc2.12-x86_64/* ../

使用新软件启动数据库

[root@mysql bin]# /mysql/base8026/bin/mysqld_safe --defaults-file=/etc/my.cnf  --user=mysql   &

根据error.log提示进行修改
expire-logs-days=7 
改：
binlog_expire_logs_seconds=302400

sql_mode = STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,NO_ENGINE_SUBSTITUTION,ONLY_FULL_GROUP_BY,STRICT_ALL_TABLES
改：
sql_mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

去除以下参数：
sql_mode =NO_AUTO_CREATE_USER
query_cache_size = 0
query_cache_type=0
log_warnings=2
innodb_file_format=barracuda
innodb_support_xa=ON

若新版本小于8.0.16，那么需要使用 mysql_upgrade，之后版本不需要mysql_upgrade 修复用户表是自动完成的
当前是8.0.26，是不需要执行，执行会提示会自动完成

[root@mysql ~]# /mysql/base8026/bin/mysql_upgrade -uroot -p -S /mysql/my3306/mysql.sock
Enter password: 
The mysql_upgrade client is now deprecated. The actions executed by the upgrade client are now done by the server.
To upgrade, please start the new MySQL binary with the older data directory. Repairing user tables is done automatically. Restart is not required after upgrade.
The upgrade process automatically starts on running a new MySQL binary with an older data directory. To avoid accidental upgrades, please use the --upgrade=NONE option with the MySQL binary. The option --upgrade=FORCE is also provided to run the server upgrade sequence on demand.
It may be possible that the server upgrade fails due to a number of reasons. In that case, the upgrade sequence will run again during the next MySQL server start. If the server upgrade fails repeatedly, the server can be started with the --upgrade=MINIMAL option to start the server without executing the upgrade sequence, thus allowing users to manually rectify the problem.

3.逻辑备份升级

3.1 安装新环境

在新目录安装新版本软件，并做初始化

3.2 旧环境做逻辑备份

/mysql/base5731/bin/mysqldump -u root -p --add-drop-table --routines --events --databases aaaa bbb --force -S /mysql/my3306/mysql.sock> /mysql/data-all.sql

3.3 导入新环境

[root@mysql mysql]# /mysql/base8026/bin/mysql -u root -p < data-all.sql

注：只需要导业务库，不需要导系统库，系统库导入时会报错

[root@mysql mysql]# /mysql/base8026/bin/mysql -u root -p --force < data-for-upgrade.sql
Enter password: 
ERROR 3554 (HY000) at line 326: Access to system table 'mysql.innodb_index_stats' is rejected.

导入完成后重启数据库即可