随着版本的不断迭代，MySQL也有了升级检查工具，这个工具在MySQL Shell中，需要我们下载MySQL Shell，感觉MySQL官方也在推动这个工具的使用，大家不妨用一用，有些功能很强大，解压即用，没有限制，就当是DBA的一个MySQL管理工具吧。
以前在升级MySQL的时候，通常我们会查阅官方文档，看看新老版本之间有什么变化，比如参数的变动，然后根据变动我们调整对应的参数，现在我们有了新的选择 MySQL Shell, 它让升级检查更加简单，容易，方便，快捷。
其实升级检查很简单，运行它的一个函数即可，具体如下：
./bin/mysqlsh --socket=/tmp/mysql.sock --user=user -e “util.checkForServerUpgrade()”
或者我们也可以用下面命令指定具体详情：
mysqlsh – util checkForServerUpgrade root@localhost:3306 --target-version=8.0.19 --output-format=JSON --config-path=/etc/my.cnf
直接输出如下：
The MySQL server at example.com:3306, version
5.7.25-enterprise-commercial-advanced - MySQL Enterprise Server - Advanced Edition (Commercial),
will now be checked for compatibility issues for upgrade to MySQL 8.0.19…
- Usage of old temporal type
No issues found
- Usage of db objects with names conflicting with new reserved keywords
Warning: The following objects have names that conflict with new reserved keywords.
Ensure queries sent by your applications usequoteswhen referring to them or they will result in errors.
More information: https://dev.mysql.com/doc/refman/en/keywords.html
dbtest.System - Table name
dbtest.System.JSON_TABLE - Column name
dbtest.System.cube - Column name
- 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
dbtest.view1.col1 - column’s default character set: utf8
- Table names in the mysql schema conflicting with new tables in 8.0
No issues found
- 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
dbtest.part1_hash - MyISAM engine does not support native partitioning
- Foreign key constraint names longer than 64 characters
No issues found
- Usage of obsolete MAXDB sql_mode flag
No issues found
- Usage of obsolete sql_mode flags
No issues found
- ENUM/SET column definitions containing elements longer than 255 characters
No issues found
- Usage of partitioned tables in shared tablespaces
Error: The following tables have partitions in shared tablespaces. Before upgrading to 8.0 they need
to be moved to file-per-table tablespace. You can do this by running query like
‘ALTER TABLE table_name REORGANIZE PARTITION X INTO
(PARTITION X VALUES LESS THAN (30) TABLESPACE=innodb_file_per_table);’
More information: https://dev.mysql.com/doc/refman/8.0/en/mysql-nutshell.html#mysql-nutshell-removals
dbtest.table1 - Partition p0 is in shared tablespace tbsp4
dbtest.table1 - Partition p1 is in shared tablespace tbsp4
- Circular directory references in tablespace data file paths
No issues found
- Usage of removed functions
Error: Following DB objects make use of functions that have been removed in
version 8.0. Please make sure to update them to use supported alternatives
before upgrade.
More information:
https://dev.mysql.com/doc/refman/8.0/en/mysql-nutshell.html#mysql-nutshell-removals
dbtest.view1 - VIEW uses removed function PASSWORD
- Usage of removed GROUP BY ASC/DESC syntax
Error: The following DB objects use removed GROUP BY ASC/DESC syntax. They need to be altered so that
ASC/DESC keyword is removed from GROUP BY clause and placed in appropriate ORDER BY clause.
More information: https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-13.html#mysqld-8-0-13-sql-syntax
dbtest.view1 - VIEW uses removed GROUP BY DESC syntax
dbtest.func1 - FUNCTION uses removed GROUP BY ASC syntax
- Removed system variables for error logging to the system log configuration
No issues found
- Removed system variables
Error: Following system variables that were detected as being used will be
removed. Please update your system to not rely on them before the upgrade.
More information: https://dev.mysql.com/doc/refman/8.0/en/added-deprecated-removed.html#optvars-removed
log_builtin_as_identified_by_password - is set and will be removed
show_compatibility_56 - is set and will be removed
- 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
[…]
- 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
dbtest.date1.d - column has zero default value: 0000-00-00
- Schema inconsistencies resulting from file removal or corruption
No issues found
- Tables recognized by InnoDB that belong to a different engine
No issues found
- Issues reported by ‘check table x for upgrade’ command
No issues found
- 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: 7
Warnings: 36
Notices: 0
7 errors were found. Please correct these issues before upgrading to avoid compatibility issues.==
运行后即可直接看结果，按照输出的提示信息进行升级前各项的检查工作，更多详细信息，请参考官方文档：https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-utilities-upgrade.html
