Server Upgrade Checks with MySQL Shell
![](https://res.cloudinary.com/strozstuff/image/fetch/https://idmqjyw9i2ib.objectstorage.us-ashburn-1.oci.customer-oci.com/n/idmqjyw9i2ib/b/blog/o/2024/server-upgrade-check-mysql-shell/header.jpg)
Over the last few years, I have become quite smitten with MySQL Shell. For those who may not be familiar with MySQL Shell, it is a new(ish) command line interface (CLI) for connecting to and managing MySQL instances. During a recent episode of Inside MySQL: Sakila Speaks, Fred and I talked to Miguel Araujo about many of the helpful (and lesser known) features of MySQL Shell. This post is the fifth in a series about these “hidden gem” features.
The Problem
Compatibility concerns can often be the driving force behind deciding not to upgrade to a newer version of MySQL. Wouldn’t it be helpful if there was a tool that could look at our existing database and check if there are any issues with updating to a newer version? MySQL Shell has you covered.
The Solution
The global util
object in MySQL Shell has a method named checkForServerUpgrade()
that can check a current database and find any potential issue with upgrading to a newer version of MySQL.
The most straightforward syntax to check for upgrade compatability issues is the following command:
util.checkForServerUpgrade()
When this command is run, it will complete an upgrade check on the database instance MySQL Shell is connected to against the version of MySQL Shell being used to run the check (in my case, I am using version 8.4). In my case, I am checking a database running MySQL 8.0.37 for compatibility issues upgrading to MySQL 8.4. Here are the results of this check:
The MySQL server at 127.0.0.1:3306, version 8.0.37 - MySQL Community Server -
GPL, will now be checked for compatibility issues for upgrade to MySQL 8.4.0.
To check for a different target server version, use the targetVersion option.
1) Removed system variables (removedSysVars)
No issues found
2) System variables with new default values (sysVarsNewDefaults)
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://dev.mysql.com/blog-archive/new-defaults-in-mysql-8-0/
binlog_transaction_dependency_tracking - default value will change from
COMMIT_ORDER to WRITESET.
group_replication_consistency - default value will change from EVENTUAL to
BEFORE_ON_PRIMARY_FAILOVER.
group_replication_exit_state_action - default value will change from
READ_ONLY to OFFLINE_MODE.
innodb_adaptive_hash_index - default value will change from ON to OFF.
innodb_buffer_pool_in_core_file - default value will change from ON to OFF.
innodb_buffer_pool_instances - default value will change from 8 (or 1 if
innodb_buffer_pool_size < 1GB) to MAX(1, #vcpu/4).
innodb_change_buffering - default value will change from all to none.
innodb_doublewrite_files - default value will change from
innodb_buffer_pool_instances * 2 to 2.
innodb_doublewrite_pages - default value will change from
innodb_write_io_threads to 128.
innodb_flush_method - default value will change from fsynch (unix) or
unbuffered (windows) to O_DIRECT.
innodb_io_capacity - default value will change from 200 to 10000.
innodb_io_capacity_max - default value will change from 200 to 2 x
innodb_io_capacity.
innodb_log_buffer_size - default value will change from 16777216 (16MB) to
67108864 (64MB).
innodb_log_writer_threads - default value will change from ON to OFF ( if
#vcpu <= 32 ).
innodb_numa_interleave - default value will change from OFF to ON.
innodb_page_cleaners - default value will change from 4 to
innodb_buffer_pool_instances.
innodb_parallel_read_threads - default value will change from 4 to
MAX(#vcpu/8, 4).
innodb_purge_threads - default value will change from 4 to 1 ( if #vcpu <= 16
).
innodb_read_io_threads - default value will change from 4 to MAX(#vcpu/2, 4).
innodb_redo_log_capacity - default value will change from 104857600 (100MB)
to MIN ( #vcpu/2, 16 )GB.
3) Issues reported by 'check table x for upgrade' command (checkTableCommand)
No issues found
4) Check for deprecated or invalid user authentication methods.
(authMethodUsage)
Warning: The following users are using the 'mysql_native_password'
authentication method which is deprecated as of MySQL 8.0.0 and will be
removed in a future release.
Consider switching the users to a different authentication method (i.e.
caching_sha2_password).
- someDBUser@%
More information:
https://dev.mysql.com/doc/refman/8.0/en/caching-sha2-pluggable-authentication.html
5) Check for deprecated or removed plugin usage. (pluginUsage)
No issues found
6) Check for deprecated or invalid default authentication methods in system
variables. (deprecatedDefaultAuth)
No issues found
7) Check for deprecated or invalid authentication methods in use by MySQL
Router internal accounts. (deprecatedRouterAuthMethod)
No issues found
8) Checks for errors in column definitions (columnDefinition)
No issues found
9) Check for allowed values in System Variables. (sysvarAllowedValues)
No issues found
10) Checks for user privileges that will be removed (invalidPrivileges)
Verifies for users containing grants to be removed, since privileges are
removed as part of the upgrade, raises a NOTICE to inform the user about
users that will be losing invalid privileges
'someDBUser'@'%' - The user 'someDBUser'@'%' has the following
privileges that will be removed as part of the upgrade process: SET_USER_ID
'root'@'localhost' - The user 'root'@'localhost' has the following privileges
that will be removed as part of the upgrade process: SET_USER_ID
11) Checks for partitions by key using columns with prefix key indexes
(partitionsWithPrefixKeys)
No issues found
Errors: 0
Warnings: 21
Notices: 2
NOTE: No 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.
These results show no show-stopping compatibility issues, and most of the output involves details about updates to system variables. Given these results, upgrading to MySQL 8.4 should be possible without changing the existing database.
Check out the documentation for more information on what is checked when we run util.checkForServerUpgrade()
.
Checking Other Systems
With MySQL Shell, we can check other systems when running util.checkForServerUpgrade()
. The syntax for connecting to another system to run the check resembles the following command:
util.checkForServerUpgrade('user@example.com:3306', {"password":"password")
Method Options
There are other options available to check server compatibility. These options include:
targetVersion
- The MySQl version to which the upgrade will be checked.include
- A comma separated list of checks to be run.exclude
- A comma delimited list of checks to exclude.
For more information on the available options, run the command:
\? util.checkForServerUpgrade
Wrap-Up
Identifying compatibility issues when upgrading to a newer version of MySQL can be daunting and time-consuming. Addressing those issues can be even more daunting and time-consuming. Using util.checkForServerUpgrade()
in MySQL Shell, we are able to quickly identify any issues and verify that they have been addressed before moving forward with a system upgrade.
Photo by Online Marketing on Unsplash
Related Entries
- Running External Scripts in MySQL Shell
- Executing System Commands in MySQL Shell
- Getting Help in MySQL Shell
- Sandboxes in MySQL Shell
- MySQL Connection Status with MySQL Shell
- Managing MySQL Shell Configuration Options
- Advanced Data Dump with MySQL Shell
- Advanced Data Load with MySQL Shell
- MySQL Shell CLI Integration
- MySQL Shell Table Export Utility
- Copy Data Directly To A MySQL Instance With MySQL Shell
- Create an InnoDB Replica Set With MySQL Shell
- Using MySQL Shell to Get MySQL Diagnostic Information
- Customizing The MySQL Shell Prompt
- Extending MySQL Shell with Startup Scripts
- Extending MySQL Shell with Plugins