MySQL8.0版本升级建议及各种场景的操作

升级版本选择原则和建议

MySQL的升级需要遵循以下几条原则和建议：

• 支持从MySQL5.7升级到8.0，注意仅支持GA版本之间的升级。
• 不支持跨大版本的升级，如从5.6升级到8.0是不支持的。
• 建议升级大版本前先升级到当前版本的最近小版本，如5.7先升级到5.7.35后再升级到8.0。
• 在大版本内部GA的小版本可以直接升级，如从8.0.9直接升级到8.0.26。

02

MySQL8.0的一些变化

在升级到8.0之前需要充分考虑版本变化带来的使用兼容性的问题，其中不兼容的部分需要特别关注，建议升级大版本前做充分的测试。下面简单介绍下MySQL8.0的部分变化。

• 数据字典

不同于之前的版本将字典数据存储在元数据文件和非事务系统表中，MySQL8.0将包含数据库对象的全局数据字典存储在事务表中。在使用上如果设置了innodb_read_only 参数会导致所有表的创建、删除、analyze、修改表引擎操作无法执行。CREATE TABLE dst_tbl LIKE src_tbl 要求src_tbl必须是base table。mysqldump和mysqlpump不会导出information_schema，不会导出MySQL Schema中数据字典表，需导出存储过程和事件需指定–routines和–events选项，并且用户需要全局selet权限。

• Authentication Plugin

MySQL8.0将默认身份验证插件从mysql_native_password变更为caching_sha2_password，客户端需要验证现有版本是否支持。

• Configuration Changes

• MySQL8.0开始只有InnoDB和NDB引擎支持分区表，升级前需确保不存在非InnoDB引擎的分区表。
• 部分error code被启用，详见https://dev.mysql.com/doc/refman/8.0/en/mysql-nutshell.html#mysql-nutshell-removals
• 默认字符集从latin1变更为utf8mb4，默认排序规则为utf8mb4_0900_ai_ci。注意这可能会导致新旧数据库对象的字符集不一致，造成隐式类型转换的问题。
• 8.0.11版本开始,如使用与初始化配置不同的lower_case_table_names值启动数据库时会报错。

• Server Changes

• 8.0.11开始部分账户管理功能被删除，如Grant命令修改用户非权限特性，NO_AUTO_CREATE_USER模式，PASSWORD() 函数和 old_passwords 系统变量。
• 8.0.11开始删除了部分兼容 SQL 模式：DB2、MAXDB、MSSQL、MYSQL323、MYSQL40、ORACLE、POSTGRESQL、NO_FIELD_OPTIONS、NO_KEY_OPTIONS、NO_TABLE_OPTIONS。
• 从 MySQL 8.0.3 开始，空间数据类型允许 SRID 属性，以明确指示存储在列中的值的空间参考系统 (SRS)。并删除了部分非ST_前缀的空间函数。详见https://dev.mysql.com/doc/refman/8.0/en/mysql-nutshell.html#mysql-nutshell-removals在线切换binlog format增加了更多限制。

• InnoDB Changes
• information_Schema中，innodb_sys_% 改名为 innodb_%
• zlib库版本从1.2.3升级到1.2.11。
• 只读变量innodb_directories应该包含file-per-table和绝对路径创建的通用表空间文件的路径。
• undo日志从系统表空间移出。默认会在innodb_undo_directory指定位置（未指定则在data dir）创建两个undo表空间。从5.7升级到8.0时innodb-fast-shutdown需设置成0。
• 8.0.17开始在创建表空间时，路径不允许含 circular directory reference(/../)；升级前可以查询INFORMATION_SCHEMA.INNODB_DATAFILES表检查表空间路径。mysql> CREATE TABLESPACE ts11 ADD DATAFILE  '/data/mysql/log/test/../ts11.ibd';    
ERROR 3121 (HY000): The ADD DATAFILE filepath cannot contain circular directory references.
mysql> CREATE TABLESPACE ts11 ADD DATAFILE  '/data/mysql/log/ts11.ibd';        
Query OK, 0 rows affected (10.02 sec)
• SQL change
• group by 不再支持DESC和ASC，GROUP BY {col_name | expr | position}, ... [WITH ROLLUP]]
• 保留字变更。https://dev.mysql.com/doc/refman/8.0/en/keywords.html
• 升级后建议测试optimizer hints，在8.0中部分hint可能不再适用。
• 部分默认配置的变化。

03

升级过程包含哪些操作

升级MySQL时需要对现版本MySQL中的数据字典和元数据等进行更新。在MySQL Schema中需更新数据字典表和系统表，在其他Schema中需要更新一些内置的MySQL持有的表，如performance_Schema、information_schema和sys schema等。

升级过程大致分为两个部分，升级数据字典和升级服务。

• 升级数据字典包括升级MySQL Schema中的数据字典表，以及performance schema，information_schema。升级数据字典表时，如当前版本低于期望版本，则服务器将创建新版本的数据字典表并将持久化的元数据拷贝到新表，在新旧表做原子性替换后重新初始化数据字典。服务器启动后会按需执行，可以使用 –upgrade=NONE（8.0.16以后）或–no-dd-upgrade （8.0.16之前）参数启动MySQL，阻止数据字典表的升级。

• 升级服务即其余升级任务，包括MySQL Schema中的非数据字典表，sys schema和用户schema。

在8.0.16之前需使用mysql_upgrade执行除数据字典表外的其余升级步骤，在8.0.16以后该步骤由MySQL服务在启动后执行。MySQL服务会根据升级到的版本以及in-place或logical升级的指示确定是否执行所有的升级步骤。

8.0.16开始启动参数–upgrade= 控制MySQL服务在启动时执行自动升级的动作。

--upgrade=AUTO MySQL升级所有过时的内容--upgrade=NONE MySQL跳过升级步骤，可能会导致报错--upgrade=MINIMAL MySQL在必要时升级数据字典表，information_schema和information_schema。这可能会导致部分功能不能正常使用，例如MGR。--upgrade=FORCE MySQL会升级所有的内容，这会检查所有schema的所有对象，导致MySQL需要更长的时间启动。此模式下MySQL会重新创建系统表 if they are missing。

04

升级前的检查

在执行升级操作前需要做一些检查工作，确认准备工作是否就绪，避免升级过程中出现异常。

• 可以使用MySQL Shell使用util.checkForServerUpgrade进行检查，返回内容包括不符合迁移要求的问题，error的问题需要迁移前修改。

下面的例子中就存在一个不兼容的问题，ymh.t1表是一个MyISAM引擎的分区表，需将该表引擎调整为innodb后方可升级。

  MySQL  JS > util.checkForServerUpgrade('root@127.0.0.1:3307', {"password":"XXXX", "targetVersion":"8.0.26", "configPath":"/etc/my3307.cnf"})The MySQL server at 127.0.0.1:3307, version 5.7.23-log - MySQL Community Server(GPL), will now be checked for compatibility issues for upgrade to MySQL8.0.26...1) Usage of old temporal type  No issues found2) Usage of db objects with names conflicting with new reserved keywords  No issues found3) Usage of utf8mb3 charset  No issues found4) Table names in the mysql schema conflicting with new tables in 8.0  No issues found5) Partitioned tables using engines with non native partitioning  Error: In MySQL 8.0 storage engine is responsible for providing its own    partitioning handler, and the MySQL server no longer provides generic    partitioning support. InnoDB and NDB are the only storage engines that    provide a native partitioning handler that is supported in MySQL 8.0. A    partitioned table using any other storage engine must be altered—either to    convert it to InnoDB or NDB, or to remove its partitioning—before upgrading    the server, else it cannot be used afterwards.  More information:    https://dev.mysql.com/doc/refman/8.0/en/upgrading-from-previous-series.html#upgrade-configuration-changes  ymh.t1 - MyISAM engine does not support native partitioning6) Foreign key constraint names longer than 64 characters  No issues found7) Usage of obsolete MAXDB sql_mode flag  No issues found8) Usage of obsolete sql_mode flags  No issues found9) ENUM/SET column definitions containing elements longer than 255 characters  No issues found10) Usage of partitioned tables in shared tablespaces  No issues found11) Circular directory references in tablespace data file paths  No issues found12) Usage of removed functions  No issues found13) Usage of removed GROUP BY ASC/DESC syntax  No issues found14) Removed system variables for error logging to the system log configuration  No issues found15) Removed system variables  No issues found16) 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  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_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)  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  transaction_write_set_extraction - default value will change from OFF to    XXHASH6417) 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 1 session(s) does not contain either NO_ZERO_DATE or    NO_ZERO_IN_DATE which allows insertion of zero dates18) Schema inconsistencies resulting from file removal or corruption  No issues found19) Tables recognized by InnoDB that belong to a different engine  No issues found20) Issues reported by 'check table x for upgrade' command  No issues found21) 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-replicationErrors:   1Warnings: 17Notices:  01 errors were found. Please correct these issues before upgrading to avoid compatibility issues.#修改t1表引擎为InnoDBmysql> alter table t1 engine=innodb;Query OK, 0 rows affected (0.02 sec)Records: 0  Duplicates: 0  Warnings: 0

MySQL Shell的util.checkForServerUpgrade工具主要做了以下检查，当然我们也可以手动进行相关的检查。

• 不应存在以下问题
• 使用过时的数据类型和函数的表。如5.6.4之前的不支持小数点的时间格式。需在执行In-place升级前执行repair table修复。
• 没有孤立的.frm文件。
• 触发器不能缺失或空的definer或无效的creation context。
• 不应存在非InnoDB或NDB引擎的分区表，如存在需变更引擎或转换成非分区表。可通过以下SQL检查：

SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE ENGINE NOT IN ('innodb', 'ndbcluster') AND CREATE_OPTIONS LIKE '%partitioned%';

• 检查是否使用了8.0新增的保留字。
• MySQL Schema中不应存在8.0数据字典表同名的表，可用以下SQL检查：

SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE LOWER(TABLE_SCHEMA) = 'mysql' and LOWER(TABLE_NAME) IN ( 'catalogs', 'character_sets', 'check_constraints', 'collations', 'column_statistics', 'column_type_elements', 'columns', 'dd_properties', 'events', 'foreign_key_column_usage', 'foreign_keys', 'index_column_usage', 'index_partitions', 'index_stats', 'indexes', 'parameter_type_elements', 'parameters', 'resource_groups', 'routines', 'schemata', 'st_spatial_reference_systems', 'table_partition_values', 'table_partitions', 'table_stats', 'tables', 'tablespace_files', 'tablespaces', 'triggers', 'view_routine_usage', 'view_table_usage' );

• 不得有外键约束名称长度超过 64 个字符的表，如存在则删除后重建。可用以下SQL检查：

SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME IN   (SELECT LEFT(SUBSTR(ID,INSTR(ID,'/')+1),                INSTR(SUBSTR(ID,INSTR(ID,'/')+1),'_ibfk_')-1)    FROM INFORMATION_SCHEMA.INNODB_SYS_FOREIGN    WHERE LENGTH(SUBSTR(ID,INSTR(ID,'/')+1))>64);

• sql_mode系统参数不能含前面提到弃用模式。
• 视图中不能有显式定义超过64个字符的列名。可查看INFORMATION_SCHEMA.VIEWS检查。
• 表和存储过程中单个枚举和SET列元素不能超过255个字符或1020个字节。
• 升级到8.0.13版本前，包括系统表空间和通用表空间在内的共享表空间中不能存在表分区。

#5.7版本以下SQL检查SELECT DISTINCT NAME, SPACE, SPACE_TYPE FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES   WHERE NAME LIKE '%#P#%' AND SPACE_TYPE NOT LIKE 'Single';#8.0早期版本以下SQL检查SELECT DISTINCT NAME, SPACE, SPACE_TYPE FROM INFORMATION_SCHEMA.INNODB_TABLES   WHERE NAME LIKE '%#P#%' AND SPACE_TYPE NOT LIKE 'Single'; #如存在，可使用reorganize partition命令将共享表空间中的分区迁移到 file-per-table表空间 ALTER TABLE $table_name REORGANIZE PARTITION $partition_name   INTO (partition_definition TABLESPACE=innodb_file_per_table);

• 查询和存储过程中不能在group by子句中使用ASC或者DESC。
• 不能使用8.0中不支持的功能和配置参数。https://dev.mysql.com/doc/refman/8.0/en/added-deprecated-removed.html https://dev.mysql.com/doc/refman/8.0/en/mysql-nutshell.html#mysql-nutshell-removals
• 从 MySQL 8.0.19 开始，如果lower_case_table_names=1，升级过程会检查表和模式名称以确保所有字符都是小写。如果发现表或架构名称包含大写字符，升级过程将失败并显示错误。

mysql> SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME != LOWER(TABLE_NAME) AND TABLE_TYPE = 'BASE TABLE';mysql> SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME != LOWER(SCHEMA_NAME);

如果出现上述问题导致的升级失败，MySQL会将变更还原，这时删除redo log并重启5.7版本实例即可。注意关闭前一定设置innodb_fast_shutdown=0。05

Linux系统升级Binary或Package-based安装的MySQL

在这种场景下可以选择in-place或者logical方式进行升级。

• in-place升级

关闭现有版本MySQL，将二进制或包替换成新版本并在现有数据目录上启动MySQL并执行升级任务的方式，称为in-place升级。升级过程分为以下几步：

1. 完成升级前检查，并处理不合规问题。
2. 如果使用了XA事务，升级前通过命令xa recover未提交XA事务，并使用xa commit或xa rollback命令提交或回滚。
3. 如当前版本低于5.7.11且使用了加密表空间，升级前轮换keyring的master keyALTER INSTANCE ROTATE INNODB MASTER KEY。
4. 将innodb_fast_shutdown改为0或1。
5. 关闭现版本MySQL。
6. 升级MySQL二进制文件或软件包。
7. 在现有数据目录上启动新版本MySQL。如果有加密的 InnoDB 表空间，请使用 –early-plugin-load选项加载keyring插件。如升级失败请删除redolog，启动5.7版本并修复错误，设置innodb_fast_shutdown为0后关闭MySQL。再使用8.0版本MySQL启动。
8. 如目标版本小于8.0.16，MySQL启动后还需执行mysql_upgrade后重启MySQL。

如下所示：

#当前版本为5.7.23mysql> select @@global.version;+------------------+| @@global.version |+------------------+| 5.7.23-log       |+------------------+1 row in set (0.01 sec)#使用mysql shell 命令util.checkForServerUpgrade('root@127.0.0.1:3307', {"password":"XXXXX", "targetVersion":"8.0.26", "configPath":"/etc/my3307.cnf"}) 检查升级到目标版本8.0.26，确认没有error级别的问题Errors:   0Warnings: 17Notices:  0No 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.#检查没有未提交的xa事务mysql> xa recover;Empty set (0.00 sec)#将innodb_fast_shutdown改为0或1mysql> set global innodb_fast_shutdown=0;select @@global.innodb_fast_shutdown;Query OK, 0 rows affected (0.00 sec)+-------------------------------+| @@global.innodb_fast_shutdown |+-------------------------------+|                             0 |+-------------------------------+1 row in set (0.00 sec)#关闭MySQLmysql> shutdown;Query OK, 0 rows affected (0.00 sec)#因目标版本8.0.26，直接在现有数据目录上启动新版本MySQL。由MySQL服务执行升级任务，可指定--upgrade=FORCE参数[root@node1 ~]# cd /usr/local/mysql-8.0.26/bin/[root@node1 bin]# ./mysqld_safe  --defaults-file=/etc/my3307.cnf --user=mysql --upgrade=FORCE &[1] 106547[root@node1 bin]#  mysqld_safe Adding '/usr/lib/libtcmalloc.so' to LD_PRELOAD for mysqld2021-10-15T03:24:11.019020Z mysqld_safe Logging to '/data/mysql3307/log/mysqld.err'.2021-10-15T03:24:11.073416Z mysqld_safe Starting mysqld daemon with databases from /data/mysql3307/data#启动后查看当前服务版本，确认已升级到目标版本mysql> \s--------------mysql  Ver 8.0.26 for Linux on x86_64 (Source distribution)Connection id:          11Current database:Current user:           root@127.0.0.1SSL:                    Cipher in use is ECDHE-RSA-AES128-GCM-SHA256Current pager:          stdoutUsing outfile:          ''Using delimiter:        ;Server version:         8.0.26-debug Source distributionProtocol version:       10Connection:             127.0.0.1 via TCP/IPServer characterset:    utf8mb4Db     characterset:    utf8mb4Client characterset:    utf8mb4Conn.  characterset:    utf8mb4TCP port:               3307Binary data as:         HexadecimalUptime:                 2 min 39 secThreads: 2  Questions: 11  Slow queries: 0  Opens: 656  Flush tables: 4  Open tables: 35  Queries per second avg: 0.069--------------

• logical升级

逻辑升级是指使用逻辑备份从旧版本MySQL中导出数据，安装新版本MySQL并导入数据的升级方式。由于可能存在的不兼容问题会导致导入失败，导出前需要做升级前检查，导入前可能还需要对备份文件进行修改。

升级步骤如下：

1. 对旧版本数据做全量导出。mysqldump -u root -p  --add-drop-table --routines --events  --all-databases --force > data-for-upgrade.sql
2. 关闭当前版本数据库。
3. 安装8.0版本数据库并初始化（从error log中获取’root’@’localhost’用户初始密码）。
4. 在新的数据目录中启动MySQL8.0，并重置初始密码。
5. 将备份文件导入mysql -u root -p --force < data-for-upgrade.sql。如导出文件包含系统表，则不建议导入时开启GTID（gtid_mode=ON）。
6. 执行剩余的升级操作。目标版本大于8.0.16时需重启服务器，启动时使用–upgrade=FORCE参数。8.0.16之前的版本先执行mysql_upgrade再重启MySQL。

#8.0.16以后的版本mysqladmin -u root -p shutdownmysqld_safe --user=mysql --datadir=/path/to/8.0-datadir --upgrade=FORCE &#8.0.16之前的版本mysql_upgrade -u root -pmysqladmin -u root -p shutdownmysqld_safe --user=mysql --datadir=/path/to/8.0-datadir &#确认升级成功后，mysql schema中两张不再使用的表可以自行清理DROP TABLE mysql.event;DROP TABLE mysql.proc;