【MYSQL数据库升级，从MYSQL5.7到MySQL8】MySQL in-place升级数据库 从5.7 到8.0.36_mysql5.7 升到8.0.36

升级前检查

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

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

使用mysqlsh升级前检查

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

2. 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.

3. 上传到服务器，解压tar包，并添加到环境变量中
   tar -xvf mysql-shell-8.0.36-linux-glibc2.28-x86-64bit.tar.gz

   vi ~/.bash_profile
   PATH=$PATH:$HOME/bin:/u01/software/mysql-shell-8.0.36-linux-glibc2.28-x86-64bit/bin

4. 使用工具进行升级前的检查,检查项有可能返回三种 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

   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
       No issues found

   16. Removed system variables
       No issues found

   17. 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

   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. Spatial data columns created in MySQL 5.6
       No issues found

   22. Issues reported by ‘check table x for upgrade’ command
       No issues found

   23. 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

   24. Columns which cannot have default values
       No issues found

   25. Check for invalid table names and schema names used in 5.7
       No issues found

   26. Check for orphaned routines in 5.7
       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 ‘.

       ’ 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: 404
   Notices: 1

   NOTE: No fatal errors were f

5. 下载对应glibc版本的MySQL 8.0.36 binaries (mysql-8.0.36-linux-glibc2.28-x86_64.tar.xz)
   下载地址： https://dev.mysql.com/downloads/mysql/

6. 更改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
1
2

2. 备份MySQL5.7整个目录
   mkdir -p /u01/backup
   cp /u01/mysql5.7/* /u01/backup

3. 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

4. 解压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'
   1
   2
   3
   4
   5
   6
   7
   8
   9
   10
   11
   12
   13
   14

升级后

1. 升级数据字典

• 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

2. 登录数据查看数据字典：
   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)

3. 简单数据库操作
   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路径和数据文件拷贝回原始位置，然后重新启动。