MYSQL数据库升级，从MYSQL5.7到MySQL8MySQL in-place升级数据库从5.7 到8.0.36

MySQL in-place升级数据库从5.7 到8.0.36

升级前检查
升级中
升级后
回滚

升级前检查

mysql版本升级，升级方式有就地升级和逻辑升级2种方法，这次就用就地升级，之前不同版本主备库的升级方式，后续慢慢补充进来

In-Place Upgrade（就地升级）：就地升级关闭旧的MySQL服务器，用MySQL8的文件夹替换MySQL5.7文件夹，重新启动MySQL，以及升级现有安装中需要升级的任何剩余部分。
逻辑升级有几种：一般常用的是mysqldump导入导出以及搭建逻辑备库，这个方法后续慢慢更新

使用mysqlsh升级前检查

根据自己的操作系统选择版本进行下载mysql shell，升级前的数据库检查
下载地址：https://dev.mysql.com/downloads/shell/
linux查看glibc版本
ldd --version

[root@D2-TEST bin]# ldd --version
ldd (GNU libc) 2.28
Copyright ? 2018 Free Software Foundation, Inc.
This is free software; see the source for copying conditions. There is NO
warranty; not even for MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
Written by Roland McGrath and Ulrich Drepper.
上传到服务器，解压tar包，并添加到环境变量中
tar -xvf mysql-shell-8.0.36-linux-glibc2.28-x86-64bit.tar.gz

vi ~/.bash_profile
PATH=

P

A

T

H

:

PATH:

PATH:HOME/bin:/u01/software/mysql-shell-8.0.36-linux-glibc2.28-x86-64bit/bin
使用工具进行升级前的检查,检查项有可能返回三种 Upgrade_issue，分别是 ERROR、WARNING、NOTICE。
ERROR：必须整改，否则升级失败。
WARNING: 可能需要整改，否则升级后有功能异常或不符合预期。
NOTICE: 升级程序会帮您自动处理好的，仅仅通知您升级程序会干这操作。

使用语句： mysqlsh -uroot -p12345678 -e “util.checkForServerUpgrade();”

或者以下语法
/u01/mysql5.7/mysqlsh/bin/mysqlsh – util check-for-server-upgrade { --user=root --socket=/tmp/mysql.sock } --target-version=8.0.36 --config-path=/etc/my5.cnf

[root@dD2-TEST bin]# /u01/mysql5.7/mysqlsh/bin/mysqlsh – util check-for-server-upgrade { --user=root --socket=/tmp/mysql.sock } --target-version=8.0.36 --config-path=/etc/my5.cnf
Please provide the password for ‘root@/tmp%2Fmysql.sock’: ********
Save password for ‘root@/tmp%2Fmysql.sock’? [Y]es/[N]o/Ne[v]er (default No):
The MySQL server at /tmp%2Fmysql.sock, version 5.7.16-log - MySQL Community
Server (GPL), will now be checked for compatibility issues for upgrade to MySQL
8.0.36…
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
eser.durian_task.APP_CLUSID - column’s default character set: utf8
。。。。。。
中间省略
eser.t_vit_qtion.STUS - column’s default character set: utf8
1. Table names in the mysql schema conflicting with new tables in 8.0
  No issues found
2. Partitioned tables using engines with non native partitioning
  No issues found
3. Foreign key constraint names longer than 64 characters
  No issues found
4. Usage of obsolete MAXDB sql_mode flag
  No issues found
5. 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
1. ENUM/SET column definitions containing elements longer than 255 characters
  No issues found
2. Usage of partitioned tables in shared tablespaces
  No issues found
3. Circular directory references in tablespace data file paths
  No issues found
4. Usage of removed functions
  No issues found
5. Usage of removed GROUP BY ASC/DESC syntax
  No issues found
6. Removed system variables for error logging to the system log configuration
  No issues found
7. Removed system variables
  No issues found
8. System variables with new default values
  Warning: Following system variables that are not defined in your
  configuration file will have new default values. Please review if you rely on
  their current values and if so define them before performing upgrade.
  More information:
  https://mysqlserverteam.com/new-defaults-in-mysql-8-0/
back_log - default value will change
character_set_server - default value will change from latin1 to utf8mb4
collation_server - default value will change from latin1_swedish_ci to
utf8mb4_0900_ai_ci
event_scheduler - default value will change from OFF to ON
explicit_defaults_for_timestamp - default value will change from OFF to ON
innodb_autoinc_lock_mode - default value will change from 1 (consecutive) to
2 (interleaved)
innodb_flush_method - default value will change from NULL to fsync (Unix),
unbuffered (Windows)
innodb_flush_neighbors - default value will change from 1 (enable) to 0
(disable)
innodb_max_dirty_pages_pct - default value will change from 75 (%) 90 (%)
innodb_max_dirty_pages_pct_lwm - default value will change from_0 (%) to 10
(%)
innodb_undo_log_truncate - default value will change from OFF to ON
innodb_undo_tablespaces - default value will change from 0 to 2
log_error_verbosity - default value will change from 3 (Notes) to 2 (Warning)
log_slave_updates - default value will change from OFF to ON
master_info_repository - default value will change from FILE to TABLE
max_allowed_packet - default value will change from 4194304 (4MB) to 67108864
(64MB)
max_error_count - default value will change from 64 to 1024
optimizer_trace_max_mem_size - default value will change from 16KB to 1MB
performance_schema_consumer_events_transactions_current - default value will
change from OFF to ON
performance_schema_consumer_events_transactions_history - default value will
change from OFF to ON
relay_log_info_repository - default value will change from FILE to TABLE
slave_rows_search_algorithms - default value will change from ‘INDEX_SCAN,
TABLE_SCAN’ to ‘INDEX_SCAN, HASH_SCAN’
table_open_cache - default value will change from 2000 to 4000
transaction_write_set_extraction - default value will change from OFF to
XXHASH64
1. Zero Date, Datetime, and Timestamp values
  No issues found
2. Schema inconsistencies resulting from file removal or corruption
  No issues found
3. Tables recognized by InnoDB that belong to a different engine
  No issues found
4. Spatial data columns created in MySQL 5.6
  No issues found
5. Issues reported by ‘check table x for upgrade’ command
  No issues found
6. 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
7. Columns which cannot have default values
  No issues found
8. Check for invalid table names and schema names used in 5.7
  No issues found
9. Check for orphaned routines in 5.7
  No issues found
10. Check for indexes that are too large to work on higher versions of MySQL
  Server than 5.7
  No issues found
11. Check for deprecated ‘.
  
  ’ syntax used in routines.
  No issues found
12. Check for columns that have foreign keys pointing to tables from a diffrent
  database engine.
  No issues found
Errors: 0
Warnings: 404
Notices: 1

NOTE: No fatal errors were f
下载对应glibc版本的MySQL 8.0.36 binaries (mysql-8.0.36-linux-glibc2.28-x86_64.tar.xz)
下载地址： https://dev.mysql.com/downloads/mysql/
更改mysql5.7数据库的参数，要不然启动时候需要数据库恢复（前滚&回滚）
mysql> show variables like ‘%shutdown%’;
±------------------------------------±------+
| Variable_name | Value |
±------------------------------------±------+
| innodb_buffer_pool_dump_at_shutdown | ON |
| innodb_fast_shutdown | 1 |
±------------------------------------±------+
2 rows in set (0.00 sec)

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

mysql> show variables like ‘%shutdown%’;
±------------------------------------±------+
| Variable_name | Value |
±------------------------------------±------+
| innodb_buffer_pool_dump_at_shutdown | ON |
| innodb_fast_shutdown | 0 |
±------------------------------------±------+

升级中

1.关闭5.7版本数据库
service mysqld stop

或者
mysqladmin -uroot -p12345678 -S /u01/mysql5.7/mysql5.7.sock shutdown

备份MySQL5.7整个目录
mkdir -p /u01/backup
cp /u01/mysql5.7/* /u01/backup
mysqldump备份整个数据库
/u01/mysql5.7/bin/mysqldump -uroot -p12345678 -q --single-transaction --events --routines --triggers --set-gtid-purged=OFF --all-databases |gzip > /root/all_db.gz

解压MySQL 8.0.36 binaries到MYSQL5.7的目录中，并覆盖，启动mysql
service mysqld start

或者
/u01/mysql5.7/bin/mysqld_safe --defaults-file=/etc/my5.cnf &

以下是mysqld.log的输出，可以看出，数据库启动时候自动升级了数据字典或者后续手动升级也可以

 		2024-01-23T08:33:35.169307Z 0 [Warning] [MY-000081] [Server] option 'server_id': unsigned value 101638420357 adjusted to 4294967295.
 		2024-01-23T08:33:35.169557Z 0 [System] [MY-010116] [Server] /u01/mysql5.7/bin/mysqld (mysqld 8.0.36) starting as process 504362
 		2024-01-23T08:33:35.185326Z 1 [System] [MY-011012] [Server] Starting upgrade of data directory.
 		2024-01-23T08:33:35.185440Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
 		2024-01-23T08:33:35.923313Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
 		2024-01-23T08:33:37.729442Z 2 [System] [MY-011003] [Server] Finished populating Data Dictionary tables with data.
 		2024-01-23T08:33:38.604014Z 5 [System] [MY-013381] [Server] Server upgrade from '50700' to '80036' started.
 		2024-01-23T08:33:43.481128Z 5 [System] [MY-013381] [Server] Server upgrade from '50700' to '80036' completed.
 		2024-01-23T08:33:44.583318Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
 		2024-01-23T08:33:44.583438Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
 		2024-01-23T08:33:44.896924Z 0 [Warning] [MY-011810] [Server] Insecure configuration for --pid-file: Location '/u01' in the path is accessible to all OS users. Consider choosing a different directory.
 		2024-01-23T08:33:44.921966Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /tmp/mysqlx.sock
 		2024-01-23T08:33:44.922144Z 0 [System] [MY-010931] [Server] /u01/mysql5.7/bin/mysqld: ready for connections. Version: '8.0.36'  socket: '/u01/mysql5.7/mysql5.7.sock'  port: 3306  MySQL Community Server - GPL.
 		2024-01-23T08:34:11.839745Z 11 [Warning] [MY-013360] [Server] Plugin mysql_native_password reported: ''mysql_native_password' is deprecated and will be removed in a future release. Please use caching_sha2_password instead'

升级后

升级数据字典

MySQL 8.0.16 的新的升級方式（参考：https://dev.mysql.com/doc/mysql-installation-excerpt/8.3/en/upgrading-what-is-upgraded.html）

/u01/mysql5.7/bin/mysqld --upgrade=NONE

说明：在 mysqld 額外添加了一個新的選項 --upgrade。可選值爲 NONE，AUTO，MINIMAL，FORCE。
NONE：不尝试升级
AUTO: 默认选项，MYSQL进行数据字典和服务升级
MINIMAL：仅升级数据字典
FORCE：強制升級，类似于 mysql_upgrade –force
MySQL 8.0.16 以前的升级方式：
The mysql_upgrade client is now deprecated. The actions executed by the upgrade client are now done by the server.

mysql_upgrade --uroot -p12345678 --socket=/u01/mysql5.7/mysql5.7.sock --port=3306

登录数据查看数据字典：
mysql> select user,host,plugin from user;
±-----------------±----------±----------------------+
| user | host | plugin |
±-----------------±----------±----------------------+
| root | % | mysql_native_password |
| mysql.infoschema | localhost | caching_sha2_password |
| mysql.session | localhost | caching_sha2_password |
| mysql.sys | localhost | mysql_native_password |
| root | localhost | mysql_native_password |
±-----------------±----------±----------------------+
5 rows in set (0.00 sec)
mysql> select user,host,plugin from user;
±-----------------±----------±----------------------+
| user | host | plugin |
±-----------------±----------±----------------------+
| root | % | mysql_native_password |
| mysql.infoschema | localhost | caching_sha2_password |
| mysql.session | localhost | caching_sha2_password |
| mysql.sys | localhost | mysql_native_password |
| root | localhost | mysql_native_password |
±-----------------±----------±----------------------+
5 rows in set (0.00 sec)
简单数据库操作
mysql> create table t1 (a int);
Query OK, 0 rows affected (0.02 sec)

mysql> insert into t1 values(1);
Query OK, 1 row affected (0.01 sec)

回滚

如果要回退的话只需要将新版本的base文件和数据文件移动到其他地方，将之前备份的MySQL 5.7的base路径和数据文件拷贝回原始位置，然后重新启动。

MySQL in-place升级数据库 从5.7 到8.0.36

升级前检查

升级中

升级后

回滚

MySQL in-place升级数据库从5.7 到8.0.36