Upgrading to MySQL 8? Meet the MySQL Shell Upgrade Checker Utility

September 27, 2019

Author

Daniel Guzmán Burgos

MySQL

Share this Post:

MySQL Shell Upgrade Checker Utility MySQL Shell is a pretty nice piece of software. Is not just another mysql client but it is also a tool that offers scripting capabilities for JavaScript and Python. And one of the coolest things you can do with it is to check if your MySQL 5.7 server is ready for an upgrade or not. Enter: Upgrade Checker Utility.

MySQL Shell Upgrade Checker Utility

So what is it? It is a script that will check your MySQL 5.7 instance for compatibility errors and issues with upgrading. It’s important to notice the word “check”. It doesn’t fix. Just check. Fix is on you, friendly DBA (or we can happily assist with it).

But isn’t there something that already does that? Close, but no. The mysqlchk program and the –check-upgrade parameter does something similar: Invokes the CHECK TABLE …. FOR UPGRADE command. The Upgrade Checker goes beyond that. There are also instructions to perform the same checks independently, available here: https://dev.mysql.com/doc/refman/8.0/en/upgrade-prerequisites.html

Now, how can I use it? First of all, you’ll have to install the MySQL Shell package:

yum install -y mysql-shell.x86_64<br>….<br>….<br>Transaction test succeeded<br>Running transaction<br>  Installing : mysql-shell-8.0.17-1.el7.x86_64                                                                      1/1<br>  Verifying  : mysql-shell-8.0.17-1.el7.x86_64                                                                      1/1<br><br>Installed:<br>  mysql-shell.x86_64 0:8.0.17-1.el7<br>

1	yum install -y mysql-shell.x86_64<br>….<br>….<br>Transaction test succeeded<br>Running transaction<br> Installing : mysql-shell-8.0.17-1.el7.x86_64 1/1<br> Verifying : mysql-shell-8.0.17-1.el7.x86_64 1/1<br><br>Installed:<br> mysql-shell.x86_64 0:8.0.17-1.el7<br>

Now you are ready to perform the check. Is a simple as executing this one-liner:

mysqlsh root@localhost -e “util.checkForServerUpgrade();”

[root@mysql2 ~]# mysqlsh root@localhost -e "util.checkForServerUpgrade();"<br>The MySQL server at /var%2Flib%2Fmysql%2Fmysql.sock, version 5.7.27-log - MySQL<br>Community Server (GPL), will now be checked for compatibility issues for<br>upgrade to MySQL 8.0.17...<br><br>1) Usage of old temporal type<br>  No issues found<br><br>2) Usage of db objects with names conflicting with reserved keywords in 8.0<br>  No issues found<br><br>3) Usage of utf8mb3 charset<br>  No issues found<br><br>4) Table names in the mysql schema conflicting with new tables in 8.0<br>  No issues found<br><br>5) Partitioned tables using engines with non native partitioning<br>  No issues found<br><br>6) Foreign key constraint names longer than 64 characters<br>  No issues found<br><br>7) Usage of obsolete MAXDB sql_mode flag<br>  No issues found<br><br>8) Usage of obsolete sql_mode flags<br>  No issues found<br><br>9) ENUM/SET column definitions containing elements longer than 255 characters<br>  No issues found<br><br>10) Usage of partitioned tables in shared tablespaces<br>  No issues found<br><br>11) Circular directory references in tablespace data file paths<br>  No issues found<br><br>12) Usage of removed functions<br>  No issues found<br><br>13) Usage of removed GROUP BY ASC/DESC syntax<br>  No issues found<br><br>14) Removed system variables for error logging to the system log configuration<br>  To run this check requires full path to MySQL server configuration file to be specified at 'configPath' key of options dictionary<br>  More information:<br>    https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-13.html#mysqld-8-0-13-logging<br><br>15) Removed system variables<br>  To run this check requires full path to MySQL server configuration file to be specified at 'configPath' key of options dictionary<br>  More information:<br>    https://dev.mysql.com/doc/refman/8.0/en/added-deprecated-removed.html#optvars-removed<br><br>16) System variables with new default values<br>  To run this check requires full path to MySQL server configuration file to be specified at 'configPath' key of options dictionary<br>  More information:<br>    https://mysqlserverteam.com/new-defaults-in-mysql-8-0/<br><br>17) Schema inconsistencies resulting from file removal or corruption<br>  No issues found<br><br>18) Issues reported by 'check table x for upgrade' command<br>  No issues found<br><br>19) New default authentication plugin considerations<br>  Warning: The new default authentication plugin 'caching_sha2_password' offers<br>    more secure password hashing than previously used 'mysql_native_password'<br>    (and consequent improved client connection authentication). However, it also<br>    has compatibility implications that may affect existing MySQL installations.<br>    If your MySQL installation must serve pre-8.0 clients and you encounter<br>    compatibility issues after upgrading, the simplest way to address those<br>    issues is to reconfigure the server to revert to the previous default<br>    authentication plugin (mysql_native_password). For example, use these lines<br>    in the server option file:<br><br>    [mysqld]<br>    default_authentication_plugin=mysql_native_password<br><br>    However, the setting should be viewed as temporary, not as a long term or<br>    permanent solution, because it causes new accounts created with the setting<br>    in effect to forego the improved authentication security.<br>    If you are using replication please take time to understand how the<br>    authentication plugin changes may impact you.<br>  More information:<br>    https://dev.mysql.com/doc/refman/8.0/en/upgrading-from-previous-series.html#upgrade-caching-sha2-password-compatibility-issues<br>    https://dev.mysql.com/doc/refman/8.0/en/upgrading-from-previous-series.html#upgrade-caching-sha2-password-replication<br><br>Errors:   0<br>Warnings: 1<br>Notices:  0<br>

[root@mysql2 ~]# mysqlsh root@localhost -e "util.checkForServerUpgrade();" The MySQL server at /var%2Flib%2Fmysql%2Fmysql.sock, version 5.7.27-log - MySQL Community Server (GPL), will now be checked for compatibility issues for upgrade to MySQL 8.0.17... 1) Usage of old temporal type No issues found 2) Usage of db objects with names conflicting with reserved keywords in 8.0 No issues found 3) Usage of utf8mb3 charset No issues found 4) Table names in the mysql schema conflicting with new tables in 8.0 No issues found 5) Partitioned tables using engines with non native partitioning No issues found 6) Foreign key constraint names longer than 64 characters No issues found 7) Usage of obsolete MAXDB sql_mode flag No issues found 8) Usage of obsolete sql_mode flags No issues found 9) ENUM/SET column definitions containing elements longer than 255 characters No issues found 10) Usage of partitioned tables in shared tablespaces No issues found 11) Circular directory references in tablespace data file paths No issues found 12) Usage of removed functions No issues found 13) Usage of removed GROUP BY ASC/DESC syntax No issues found 14) Removed system variables for error logging to the system log configuration To run this check requires full path to MySQL server configuration file to be specified at 'configPath' key of options dictionary More information: https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-13.html#mysqld-8-0-13-logging 15) Removed system variables To run this check requires full path to MySQL server configuration file to be specified at 'configPath' key of options dictionary More information: https://dev.mysql.com/doc/refman/8.0/en/added-deprecated-removed.html#optvars-removed 16) System variables with new default values To run this check requires full path to MySQL server configuration file to be specified at 'configPath' key of options dictionary More information: https://mysqlserverteam.com/new-defaults-in-mysql-8-0/ 17) Schema inconsistencies resulting from file removal or corruption No issues found 18) Issues reported by 'check table x for upgrade' command No issues found 19) 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: 0 Warnings: 1 Notices: 0

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.

Cool, right?

Some things were missing due to lack of parameter. Let’s pass it.

util.checkForServerUpgrade('root@localhost:3306', {"password":"password", "targetVersion":"8.0.11", "configPath":"/etc/my.cnf"})<br>

1	util.checkForServerUpgrade('root@localhost:3306', {"password":"password", "targetVersion":"8.0.11", "configPath":"/etc/my.cnf"})<br>

Now we have 22 warnings instead of one!

13) System variables with new default values<br>  Warning: Following system variables that are not defined in your<br>    configuration file will have new default values. Please review if you rely on<br>    their current values and if so define them before performing upgrade.<br>  More information:<br>    https://mysqlserverteam.com/new-defaults-in-mysql-8-0/<br><br>  back_log - default value will change<br>  character_set_server - default value will change from latin1 to utf8mb4<br>  collation_server - default value will change from latin1_swedish_ci to<br>    utf8mb4_0900_ai_ci<br>  event_scheduler - default value will change from OFF to ON<br>  explicit_defaults_for_timestamp - default value will change from OFF to ON<br>  innodb_autoinc_lock_mode - default value will change from 1 (consecutive) to<br>    2 (interleaved)<br>  innodb_flush_method - default value will change from NULL to fsync (Unix),<br>    unbuffered (Windows)<br>  innodb_flush_neighbors - default value will change from 1 (enable) to 0<br>    (disable)<br>  innodb_max_dirty_pages_pct - default value will change from 75 (%)  90 (%)<br>  innodb_max_dirty_pages_pct_lwm - default value will change from_0 (%) to 10<br>    (%)<br>  innodb_undo_log_truncate - default value will change from OFF to ON<br>  innodb_undo_tablespaces - default value will change from 0 to 2<br>  log_error_verbosity - default value will change from 3 (Notes) to 2 (Warning)<br>  max_allowed_packet - default value will change from 4194304 (4MB) to 67108864<br>    (64MB)<br>  max_error_count - default value will change from 64 to 1024<br>  optimizer_trace_max_mem_size - default value will change from 16KB to 1MB<br>  performance_schema_consumer_events_transactions_current - default value will<br>    change from OFF to ON<br>  performance_schema_consumer_events_transactions_history - default value will<br>    change from OFF to ON<br>  slave_rows_search_algorithms - default value will change from 'INDEX_SCAN,<br>    TABLE_SCAN' to 'INDEX_SCAN, HASH_SCAN'<br>  table_open_cache - default value will change from 2000 to 4000<br>  transaction_write_set_extraction - default value will change from OFF to<br>    XXHASH64<br>

13) 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) 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 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

There’s some work to do there.

And now the question:

Does it work with Percona Server for MySQL? Yes, it does!

To avoid messing with repos I just got the Shell from the MySQL downloads:

wget https://dev.mysql.com/get/Downloads/MySQL-Shell/mysql-shell-8.0.17-linux-glibc2.12-x86-64bit.tar.gz; tar -xvzf mysql-shell-8.0.17-linux-glibc2.12-x86-64bit.tar.gz;  cd mysql-shell-8.0.17-linux-glibc2.12-x86-64bit/bin/;./mysqlsh<br>

1	wget https://dev.mysql.com/get/Downloads/MySQL-Shell/mysql-shell-8.0.17-linux-glibc2.12-x86-64bit.tar.gz; tar -xvzf mysql-shell-8.0.17-linux-glibc2.12-x86-64bit.tar.gz; cd mysql-shell-8.0.17-linux-glibc2.12-x86-64bit/bin/;./mysqlsh<br>

In conclusion, doing the pre-checks required to go from MySQL 5.7 to MySQL 8 is easier than ever with the MySQL Shell Upgrade Checker Utility. Besides reading the release notes before doing so, the only necessary step to perform is now a command that will leave little room for mistakes. Happy upgrade!