Upgrading to MySQL 8.0+: a More Automated Upgrade Experience - - PowerPoint PPT Presentation

Upgrading to MySQL 8.0+: a More Automated Upgrade Experience

Dmitry Lenev, Software Developer Oracle/MySQL, November 2018 Dmitry Lenev, Software Developer Oracle/MySQL, November 2018

Safe Harbor Statement

The following is intended to outline our general product direction. It is intended for information purposes only, and may not be incorporated into any contract. It is not commitment to deliver any material, code, or functionality, and should not be relied upon in making purchasing decisions. The development, release, and timing of any features or functionality described for Oracle’s products remains at the sole discretion of Oracle.

Agenda

• Introduction
• A straightforward upgrade to MySQL 8.0
• Upgrade to MySQL 8.0 in detail
• A better future post MySQL 8.0

Introduction

Upgrading in general Upgrading in general

Why upgrade MySQL installation?

• Security concerns
• Bug fjxes
• Performance and Scalability
• New features
• Reduce tech debt for installation

━ Multiple version upgrade is complex (5.6 → 5.7 → 8.0) ━ E.g., deprecated functionality in 5.7, removed in 8.0

Why upgrade MySQL installation?

• Security concerns
• Bug fjxes
• Performance and Scalability
• New features
• Reduce tech debt for installation

━ Multiple version upgrade is complex (5.6 → 5.7 → 8.0) ━ E.g., deprecated functionality in 5.7, removed in 8.0

Why upgrade MySQL installation?

• Security concerns
• Bug fjxes
• Performance and Scalability
• New features
• Reduce tech debt for installation

━ Multiple version upgrade is complex (5.6 → 5.7 → 8.0) ━ E.g., deprecated functionality in 5.7, removed in 8.0

Why upgrade MySQL installation?

• Security concerns
• Bug fjxes
• Performance and Scalability
• New features
• Reduce tech debt for installation

━ Multiple version upgrade is complex (5.6 → 5.7 → 8.0) ━ E.g., deprecated functionality in 5.7, removed in 8.0

Why upgrade MySQL installation?

• Security concerns
• Bug fjxes
• Performance and Scalability
• New features
• Reduce tech debt for installation

━ Multiple version upgrade is complex (5.6 → 5.7 → 8.0) ━ E.g., deprecated functionality in 5.7, removed in 8.0

Why do we postpone upgrades?

• Do we have required knowledge?
• Do we have required resources?
• Do we have time?
• Cost for doing the actual work necessary to upgrade

━ For DBAs and possibly consultants

• Can we afgord it?

━ Cost for lost business during the switchover, depending on downtime

Why do we postpone upgrades?

• Do we have required knowledge?
• Do we have required resources?
• Do we have time?
• Cost for doing the actual work necessary to upgrade

━ For DBAs and possibly consultants

• Can we afgord it?

━ Cost for lost business during the switchover, depending on downtime

Why do we postpone upgrades?

• Do we have required knowledge?
• Do we have required resources?
• Do we have time?
• Cost for doing the actual work necessary to upgrade

━ For DBAs and possibly consultants

• Can we afgord it?

━ Cost for lost business during the switchover, depending on downtime

Why do we postpone upgrades?

• Do we have required knowledge?
• Do we have required resources?
• Do we have time?
• Cost for doing the actual work necessary to upgrade

━ For DBAs and possibly consultants

• Can we afgord it?

━ Cost for lost business during the switchover, depending on downtime

Why do we postpone upgrades?

• Do we have required knowledge?
• Do we have required resources?
• Do we have time?
• Cost for doing the actual work necessary to upgrade

━ For DBAs and possibly consultants

• Can we afgord it?

━ Cost for lost business during the switchover, depending on downtime

Biting the Bullet

• At some point upgrade needs to happen!
• DBA input regarding upgrading MySQL:

━ Reducing risk and cost is key ━ T

• tal duration of upgrade should be short

━ For customer apps, keep old MySQL behavior by default, change behavior later ━ Want to test new version gradually ━ When switching downtime should be minimal

Biting the Bullet

• At some point upgrade needs to happen!
• DBA input regarding upgrading MySQL:

━ Reducing risk and cost is key ━ T

• tal duration of upgrade should be short

━ For customer apps, keep old MySQL behavior by default, change behavior later ━ Want to test new version gradually ━ When switching downtime should be minimal

Biting the Bullet

• At some point upgrade needs to happen!
• DBA input regarding upgrading MySQL:

━ Reducing risk and cost is key ━ T

• tal duration of upgrade should be short

━ For customer apps, keep old MySQL behavior by default, change behavior later ━ Want to test new version gradually ━ When switching downtime should be minimal

Biting the Bullet

• At some point upgrade needs to happen!
• DBA input regarding upgrading MySQL:

━ Reducing risk and cost is key ━ T

• tal duration of upgrade should be short

━ For customer apps, keep old MySQL behavior by default, change behavior later ━ Want to test new version gradually ━ When switching downtime should be minimal

Biting the Bullet

• At some point upgrade needs to happen!
• DBA input regarding upgrading MySQL:

━ Reducing risk and cost is key ━ T

• tal duration of upgrade should be short

━ For customer apps, keep old MySQL behavior by default, change behavior later ━ Want to test new version gradually ━ When switching downtime should be minimal

Biting the Bullet

• At some point upgrade needs to happen!
• DBA input regarding upgrading MySQL:

━ Reducing risk and cost is key ━ T

• tal duration of upgrade should be short

━ For customer apps, keep old MySQL behavior by default, change behavior later ━ Want to test new version gradually ━ When switching downtime should be minimal

Upgrade to MySQL 8.0

The straightforward case The straightforward case

MySQL upgrade 5.7 → 8.0, smooth sailing!

• Read release notes: Conclusion – No problems
• Run upgrade_checker

me@siv20$ ./mysqlsh root:@localhost:3307 -e "util.checkForServerUpgrade();” The MySQL server at localhost:3307 will now be checked for compatibility issues for upgrade to MySQL 8.0... MySQL version: 5.7.24 - Source distribution 1) Usage of db objects with names conflicting with reserved keywords in 8.0 No issues found 2) Usage of utf8mb3 charset No issues found …… No known compatibility errors or issues for upgrading the target server to MySQL 8 were found.

MySQL upgrade 5.7 → 8.0, smooth sailing!

• Read release notes: Conclusion – No problems
• Run upgrade_checker

me@siv20$ ./mysqlsh root:@localhost:3307 -e "util.checkForServerUpgrade();” The MySQL server at localhost:3307 will now be checked for compatibility issues for upgrade to MySQL 8.0... MySQL version: 5.7.24 - Source distribution 1) Usage of db objects with names conflicting with reserved keywords in 8.0 No issues found 2) Usage of utf8mb3 charset No issues found …… No known compatibility errors or issues for upgrading the target server to MySQL 8 were found.

MySQL upgrade 5.7 → 8.0, smooth sailing!

• Read release notes: Conclusion – No problems
• Run upgrade_checker

me@siv20$ ./mysqlsh root:@localhost:3307 -e "util.checkForServerUpgrade();” The MySQL server at localhost:3307 will now be checked for compatibility issues for upgrade to MySQL 8.0... MySQL version: 5.7.24 - Source distribution 1) Usage of db objects with names conflicting with reserved keywords in 8.0 No issues found 2) Usage of utf8mb3 charset No issues found …… No known compatibility errors or issues for upgrading the target server to MySQL 8 were found.

MySQL upgrade 5.7 → 8.0, smooth sailing!

• Backup your data directory
• Install and start MySQL 8.0

━ In-place upgrade

• Run mysql_upgrade

me@siv20$ ./mysql_upgrade --socket=/me/mysql/mysql.sock --port=3307 --user=root Checking if update is needed. Checking server version. Running queries to upgrade MySQL server. Upgrading system table data. Checking system database. mysql.columns_priv OK mysql.component OK mysql.db OK ... Upgrade process completed successfully.

MySQL upgrade 5.7 → 8.0, smooth sailing!

• Backup your data directory
• Install and start MySQL 8.0

━ In-place upgrade

• Run mysql_upgrade

me@siv20$ ./mysql_upgrade --socket=/me/mysql/mysql.sock --port=3307 --user=root Checking if update is needed. Checking server version. Running queries to upgrade MySQL server. Upgrading system table data. Checking system database. mysql.columns_priv OK mysql.component OK mysql.db OK ... Upgrade process completed successfully.

MySQL upgrade 5.7 → 8.0, smooth sailing!

• Backup your data directory
• Install and start MySQL 8.0

━ In-place upgrade

• Run mysql_upgrade

me@siv20$ ./mysql_upgrade --socket=/me/mysql/mysql.sock --port=3307 --user=root Checking if update is needed. Checking server version. Running queries to upgrade MySQL server. Upgrading system table data. Checking system database. mysql.columns_priv OK mysql.component OK mysql.db OK ... Upgrade process completed successfully.

MySQL upgrade 5.7 → 8.0, smooth sailing!

• Backup your data directory
• Install and start MySQL 8.0

━ In-place upgrade

• Run mysql_upgrade

me@siv20$ ./mysql_upgrade --socket=/me/mysql/mysql.sock --port=3307 --user=root Checking if update is needed. Checking server version. Running queries to upgrade MySQL server. Upgrading system table data. Checking system database. mysql.columns_priv OK mysql.component OK mysql.db OK ... Upgrade process completed successfully.

MySQL upgrade 5.7 → 8.0, smooth sailing!

• Restart the server and look at messages in the error log

2018-10-06T09:11:28.167169Z 0 [System] [MY-010116] [Server] /me/mysqld (mysqld 8.0.12) starting as process 27147 2018-10-06T09:11:30.261139Z 0 [System] [MY-010931] [Server] /me/mysqld: ready for

• connections. Version: '8.0.12' socket: ’/me/mysql/mysql.sock' port: 3307 Source

distribution

• Verify that apps and services are working as expected

━ Simple installation – we assume that can be reactive about app issues.

MySQL upgrade 5.7 → 8.0, smooth sailing!

• Restart the server and look at messages in the error log

2018-10-06T09:11:28.167169Z 0 [System] [MY-010116] [Server] /me/mysqld (mysqld 8.0.12) starting as process 27147 2018-10-06T09:11:30.261139Z 0 [System] [MY-010931] [Server] /me/mysqld: ready for

• connections. Version: '8.0.12' socket: ’/me/mysql/mysql.sock' port: 3307 Source

distribution

• Verify that apps and services are working as expected

━ Simple installation – we assume that can be reactive about app issues.

Improvements to upgrade to MySQL 8.0

Goals for MySQL upgrade experience

• Upgrade to be faster and with lower risk

━ Eliminate legacy issues with metadata

• Transition from legacy metadata handling to transactional data dictionary
• The upgrade process will produce consistent data

dictionary

• Help DBAs upgrading to MySQL 8.0

━ Better support preparing for upgrade

• Added upgrade_checker to the MySQL Shell

━ Better support during upgrade

• Added upgrade checks to MySQL Server
• Prohibit legacy issues from entering MySQL 8.0 metadata store

Goals for MySQL upgrade experience

• Upgrade to be faster and with lower risk

━ Eliminate legacy issues with metadata

• Transition from legacy metadata handling to transactional data dictionary
• The upgrade process will produce consistent data

dictionary

• Help DBAs upgrading to MySQL 8.0

━ Better support preparing for upgrade

• Added upgrade_checker to the MySQL Shell

━ Better support during upgrade

• Added upgrade checks to MySQL Server
• Prohibit legacy issues from entering MySQL 8.0 metadata store

Goals for MySQL upgrade experience

• Upgrade to be faster and with lower risk

━ Eliminate legacy issues with metadata

• Transition from legacy metadata handling to transactional data dictionary
• The upgrade process will produce consistent data

dictionary

• Help DBAs upgrading to MySQL 8.0

━ Better support preparing for upgrade

• Added upgrade_checker to the MySQL Shell

━ Better support during upgrade

• Added upgrade checks to MySQL Server
• Prohibit legacy issues from entering MySQL 8.0 metadata store

Goals for MySQL upgrade experience

• Upgrade to be faster and with lower risk

━ Eliminate legacy issues with metadata

• Transition from legacy metadata handling to transactional data dictionary
• The upgrade process will produce consistent data

dictionary

• Help DBAs upgrading to MySQL 8.0

━ Better support preparing for upgrade

• Added upgrade_checker to the MySQL Shell

━ Better support during upgrade

• Added upgrade checks to MySQL Server
• Prohibit legacy issues from entering MySQL 8.0 metadata store

Goals for MySQL upgrade experience

• Upgrade to be faster and with lower risk

━ Eliminate legacy issues with metadata

• Transition from legacy metadata handling to transactional data dictionary
• The upgrade process will produce consistent data

dictionary

• Help DBAs upgrading to MySQL 8.0

━ Better support preparing for upgrade

• Added upgrade_checker to the MySQL Shell

━ Better support during upgrade

• Added upgrade checks to MySQL Server
• Prohibit legacy issues from entering MySQL 8.0 metadata store

MySQL Data Dictionary before MySQL 8.0

Data Dictionary Files FRM TRG OPT System T ables (mysql.*) user proc events InnoDB System T ables

MyISAM File system InnoDB

SQL

Transactional Data Dictionary in MySQL 8.0

Data Dictionary InnoDB

DD T able DD T able

DD T able

SQL

Upgrade to MySQL 8.0 in detail and how upgrade_checker helps

MySQL 8.0 features

• Read release notes

https://dev.mysql.com/doc/relnotes/mysql/8.0/en/ and https:// dev.mysql.com/doc/refman/8.0/en/upgrading.html

• Read blogs on https://mysqlserverteam.com
• New features in 8.0, and there is a lot of them

━ Transactional Data Dictionary, and Atomic DDL ━ Geography support ━ Roles ━ Persistent runtime confjguration ━ ++++

MySQL 8.0 Deprecation/Removals, Samples

• Features removed

━ Query Cache, Non-native partitioning

• Options and variables removed

━ Log_errors/Log_warnings replaced by log_error_verbosity ━ Secure_auth system var, --secure_auth client option MYSQL_SECURE_AUTH from mysql options ━ SQL modes removed

• Account management

━ The PASSWORD() function has been removed, using GRANT to create users

• Syntaxes afgected

━ EXTENDED and PARTITIONS keywords in EXPLAIN ━ \N as synonim for NULL in SQL

MySQL 8.0 Deprecation/Removals, Samples

• Features removed

━ Query Cache, Non-native partitioning

• Options and variables removed

━ Log_errors/Log_warnings replaced by log_error_verbosity ━ Secure_auth system var, --secure_auth client option MYSQL_SECURE_AUTH from mysql options ━ SQL modes removed

• Account management

━ The PASSWORD() function has been removed, using GRANT to create users

• Syntaxes afgected

━ EXTENDED and PARTITIONS keywords in EXPLAIN ━ \N as synonim for NULL in SQL

MySQL 8.0 Deprecation/Removals, Samples

• Features removed

━ Query Cache, Non-native partitioning

• Options and variables removed

━ Log_errors/Log_warnings replaced by log_error_verbosity ━ Secure_auth system var, --secure_auth client option MYSQL_SECURE_AUTH from mysql options ━ SQL modes removed

• Account management

━ The PASSWORD() function has been removed, using GRANT to create users

• Syntaxes afgected

━ EXTENDED and PARTITIONS keywords in EXPLAIN ━ \N as synonim for NULL in SQL

MySQL 8.0 Deprecation/Removals, Samples

• Features removed

━ Query Cache, Non-native partitioning

• Options and variables removed

━ Log_errors/Log_warnings replaced by log_error_verbosity ━ Secure_auth system var, --secure_auth client option MYSQL_SECURE_AUTH from mysql options ━ SQL modes removed

• Account management

━ The PASSWORD() function has been removed, using GRANT to create users

• Syntaxes afgected

━ EXTENDED and PARTITIONS keywords in EXPLAIN ━ \N as synonim for NULL in SQL

MySQL 8.0 Deprecation/Removals, Samples

• Features removed

━ Query Cache, Non-native partitioning

• Options and variables removed

━ Log_errors/Log_warnings replaced by log_error_verbosity ━ Secure_auth system var, --secure_auth client option MYSQL_SECURE_AUTH from mysql options ━ SQL modes removed

• Account management

━ The PASSWORD() function has been removed, using GRANT to create users

• Syntaxes afgected

━ EXTENDED and PARTITIONS keywords in EXPLAIN ━ \N as synonim for NULL in SQL

MySQL 8.0 Defaults Changes

There are a lot of changes to the defaults in 8.0, see https://mysqlserverteam.com/new-defaults-in-mysql-8-0/ Some important ones:

• Default characterset and collation to 'utf8mb4' and

'utf8mb4_0900_ai_ci’

• The default/preferred authentication plugin

‘caching_sha2_password’

• innodb_undo_tablespaces changed from 0 to 2
• log_bin has been changed from OFF to ON
• Upgrade from MySQL 5.7: ALTER INSTANCE ROTATE INNODB

MASTER KEY

MySQL 8.0 Defaults Changes

There are a lot of changes to the defaults in 8.0, see https://mysqlserverteam.com/new-defaults-in-mysql-8-0/ Some important ones:

• Default characterset and collation to 'utf8mb4' and

'utf8mb4_0900_ai_ci’

• The default/preferred authentication plugin

‘caching_sha2_password’

• innodb_undo_tablespaces changed from 0 to 2
• log_bin has been changed from OFF to ON
• Upgrade from MySQL 5.7: ALTER INSTANCE ROTATE INNODB

MASTER KEY

MySQL 8.0 Defaults Changes

There are a lot of changes to the defaults in 8.0, see https://mysqlserverteam.com/new-defaults-in-mysql-8-0/ Some important ones:

• Default characterset and collation to 'utf8mb4' and

'utf8mb4_0900_ai_ci’

• The default/preferred authentication plugin

‘caching_sha2_password’

• innodb_undo_tablespaces changed from 0 to 2
• log_bin has been changed from OFF to ON
• Upgrade from MySQL 5.7: ALTER INSTANCE ROTATE INNODB

MASTER KEY

MySQL 8.0 Defaults Changes

There are a lot of changes to the defaults in 8.0, see https://mysqlserverteam.com/new-defaults-in-mysql-8-0/ Some important ones:

• Default characterset and collation to 'utf8mb4' and

'utf8mb4_0900_ai_ci’

• The default/preferred authentication plugin

‘caching_sha2_password’

• innodb_undo_tablespaces changed from 0 to 2
• log_bin has been changed from OFF to ON
• Upgrade from MySQL 5.7: ALTER INSTANCE ROTATE INNODB

MASTER KEY

MySQL 8.0 Defaults Changes

There are a lot of changes to the defaults in 8.0, see https://mysqlserverteam.com/new-defaults-in-mysql-8-0/ Some important ones:

• Default characterset and collation to 'utf8mb4' and

'utf8mb4_0900_ai_ci’

• The default/preferred authentication plugin

‘caching_sha2_password’

• innodb_undo_tablespaces changed from 0 to 2
• log_bin has been changed from OFF to ON
• Upgrade from MySQL 5.7: ALTER INSTANCE ROTATE INNODB

MASTER KEY

MySQL 8.0 Defaults Changes

There are a lot of changes to the defaults in 8.0, see https://mysqlserverteam.com/new-defaults-in-mysql-8-0/ Some important ones:

• Default characterset and collation to 'utf8mb4' and

'utf8mb4_0900_ai_ci’

• The default/preferred authentication plugin

‘caching_sha2_password’

• innodb_undo_tablespaces changed from 0 to 2
• log_bin has been changed from OFF to ON
• Upgrade from MySQL 5.7: ALTER INSTANCE ROTATE INNODB

MASTER KEY

MySQL 8.0 Defaults Changes

• Especially note character set and collation defaults

changes!

• Note that for upgrading SCHEMAs from 5.7 to 8.0, each

schema has its own specifjed default charset and collation, so nothing needs to be done to preserve these

• Rolling upgrade with 5.7 master

━ For new schemas using defaults, you will get master charset and collation on 8.0 slaves too ━ New tables in existing schemas inherit schema defaults, so “no problem”.

MySQL 8.0 Defaults Changes

• Especially note character set and collation defaults

changes!

• Note that for upgrading SCHEMAs from 5.7 to 8.0, each

schema has its own specifjed default charset and collation, so nothing needs to be done to preserve these

• Rolling upgrade with 5.7 master

━ For new schemas using defaults, you will get master charset and collation on 8.0 slaves too ━ New tables in existing schemas inherit schema defaults, so “no problem”.

MySQL 8.0 Defaults Changes

• Especially note character set and collation defaults

changes!

• Note that for upgrading SCHEMAs from 5.7 to 8.0, each

schema has its own specifjed default charset and collation, so nothing needs to be done to preserve these

• Rolling upgrade with 5.7 master

━ For new schemas using defaults, you will get master charset and collation on 8.0 slaves too ━ New tables in existing schemas inherit schema defaults, so “no problem”.

MySQL upgrade_checker

• New tool in MySQL 5.7 shell
• upgrade_checker will check your MySQL 5.7 installation

readiness for upgrade

• Clean up legacy issues

━ Running the tool on 5.7 installation, users can make changes when time permits before upgrade

• Upgrade checker is in active development and more

checks will be added

━ Part of MySQL update releases

Note that we think it is unlikely that there are installations with all the issues explained in the following slides

MySQL upgrade_checker

• New tool in MySQL 5.7 shell
• upgrade_checker will check your MySQL 5.7 installation

readiness for upgrade

• Clean up legacy issues

━ Running the tool on 5.7 installation, users can make changes when time permits before upgrade

• Upgrade checker is in active development and more

checks will be added

━ Part of MySQL update releases

Note that we think it is unlikely that there are installations with all the issues explained in the following slides

MySQL upgrade_checker

• New tool in MySQL 5.7 shell
• upgrade_checker will check your MySQL 5.7 installation

readiness for upgrade

• Clean up legacy issues

━ Running the tool on 5.7 installation, users can make changes when time permits before upgrade

• Upgrade checker is in active development and more

checks will be added

━ Part of MySQL update releases

Note that we think it is unlikely that there are installations with all the issues explained in the following slides

MySQL upgrade_checker

• New tool in MySQL 5.7 shell
• upgrade_checker will check your MySQL 5.7 installation

readiness for upgrade

• Clean up legacy issues

━ Running the tool on 5.7 installation, users can make changes when time permits before upgrade

• Upgrade checker is in active development and more

checks will be added

━ Part of MySQL update releases

Note that we think it is unlikely that there are installations with all the issues explained in the following slides

MySQL upgrade_checker

• New tool in MySQL 5.7 shell
• upgrade_checker will check your MySQL 5.7 installation

readiness for upgrade

• Clean up legacy issues

━ Running the tool on 5.7 installation, users can make changes when time permits before upgrade

• Upgrade checker is in active development and more

checks will be added

━ Part of MySQL update releases

Note that we think it is unlikely that there are installations with all the issues explained in the following slides

Issues detected by upgrade_checker

• Usage of old temporal types
• Confmicting db object names

and reserved keywords

• Usage of utf8mb3 charset
• Reserved table names in

mysql schema

• Foreign key names longer

than 64 chars

• Usage of obsolete

sql_modes

• ENUM/SET columns with

elements longer than 255 chars

• Usage of partitioned tables

in shared tablespaces

• Usage of removed functions
• Usage of removed GROUP

BY ASC/DESC

• Issues reported by “CHECK

TABLE … FOR UPGRADE” command

Issues detected by upgrade_checker

• Usage of old temporal types
• Confmicting db object names

and reserved keywords

• Usage of utf8mb3 charset
• Reserved table names in

mysql schema

• Foreign key names longer

than 64 chars

• Usage of obsolete

sql_modes

• ENUM/SET columns with

elements longer than 255 chars

• Usage of partitioned tables

in shared tablespaces

• Usage of removed functions
• Usage of removed GROUP

BY ASC/DESC

• Issues reported by “CHECK

TABLE … FOR UPGRADE” command

Issues detected by upgrade_checker

• Usage of old temporal types
• Confmicting db object names

and reserved keywords

• Usage of utf8mb3 charset
• Reserved table names in

mysql schema

• Foreign key names longer

than 64 chars

• Usage of obsolete

sql_modes

• ENUM/SET columns with

elements longer than 255 chars

• Usage of partitioned tables

in shared tablespaces

• Usage of removed functions
• Usage of removed GROUP

BY ASC/DESC

• Issues reported by “CHECK

TABLE … FOR UPGRADE” command

Issues detected by upgrade_checker

• Usage of old temporal types
• Confmicting db object names

and reserved keywords

• Usage of utf8mb3 charset
• Reserved table names in

mysql schema

• Foreign key names longer

than 64 chars

• Usage of obsolete

sql_modes

• ENUM/SET columns with

elements longer than 255 chars

• Usage of partitioned tables

in shared tablespaces

• Usage of removed functions
• Usage of removed GROUP

BY ASC/DESC

• Issues reported by “CHECK

TABLE … FOR UPGRADE” command

Issues detected by upgrade_checker

• Usage of old temporal types
• Confmicting db object names

and reserved keywords

• Usage of utf8mb3 charset
• Reserved table names in

mysql schema

• Foreign key names longer

than 64 chars

• Usage of obsolete

sql_modes

• ENUM/SET columns with

elements longer than 255 chars

• Usage of partitioned tables

in shared tablespaces

• Usage of removed functions
• Usage of removed GROUP

BY ASC/DESC

• Issues reported by “CHECK

TABLE … FOR UPGRADE” command

Issues detected by upgrade_checker

• Usage of old temporal types
• Confmicting db object names

and reserved keywords

• Usage of utf8mb3 charset
• Reserved table names in

mysql schema

• Foreign key names longer

than 64 chars

• Usage of obsolete

sql_modes

• ENUM/SET columns with

elements longer than 255 chars

• Usage of partitioned tables

in shared tablespaces

• Usage of removed functions
• Usage of removed GROUP

BY ASC/DESC

• Issues reported by “CHECK

TABLE … FOR UPGRADE” command

Issues detected by upgrade_checker

• Usage of old temporal types
• Confmicting db object names

and reserved keywords

• Usage of utf8mb3 charset
• Reserved table names in

mysql schema

• Foreign key names longer

than 64 chars

• Usage of obsolete

sql_modes

• ENUM/SET columns with

elements longer than 255 chars

• Usage of partitioned tables

in shared tablespaces

• Usage of removed functions
• Usage of removed GROUP

BY ASC/DESC

• Issues reported by “CHECK

TABLE … FOR UPGRADE” command

Issues detected by upgrade_checker

• Usage of old temporal types
• Confmicting db object names

and reserved keywords

• Usage of utf8mb3 charset
• Reserved table names in

mysql schema

• Foreign key names longer

than 64 chars

• Usage of obsolete

sql_modes

• ENUM/SET columns with

elements longer than 255 chars

• Usage of partitioned tables

in shared tablespaces

• Usage of removed functions
• Usage of removed GROUP

BY ASC/DESC

• Issues reported by “CHECK

TABLE … FOR UPGRADE” command

Issues detected by upgrade_checker

• Usage of old temporal types
• Confmicting db object names

and reserved keywords

• Usage of utf8mb3 charset
• Reserved table names in

mysql schema

• Foreign key names longer

than 64 chars

• Usage of obsolete

sql_modes

• ENUM/SET columns with

elements longer than 255 chars

• Usage of partitioned tables

in shared tablespaces

• Usage of removed functions
• Usage of removed GROUP

BY ASC/DESC

• Issues reported by “CHECK

TABLE … FOR UPGRADE” command

Issues detected by upgrade_checker

• Usage of old temporal types
• Confmicting db object names

and reserved keywords

• Usage of utf8mb3 charset
• Reserved table names in

mysql schema

• Foreign key names longer

than 64 chars

• Usage of obsolete

sql_modes

• ENUM/SET columns with

elements longer than 255 chars

• Usage of partitioned tables

in shared tablespaces

• Usage of removed functions
• Usage of removed GROUP

BY ASC/DESC

• Issues reported by “CHECK

TABLE … FOR UPGRADE” command

Issues detected by upgrade_checker

• Usage of old temporal types
• Confmicting db object names

and reserved keywords

• Usage of utf8mb3 charset
• Reserved table names in

mysql schema

• Foreign key names longer

than 64 chars

• Usage of obsolete

sql_modes

• ENUM/SET columns with

elements longer than 255 chars

• Usage of partitioned tables

in shared tablespaces

• Usage of removed functions
• Usage of removed GROUP

BY ASC/DESC

• Issues reported by “CHECK

TABLE … FOR UPGRADE” command

Issues detected by upgrade_checker: Example 1

The Transactional Data Dictionary is stored as tables in mysql schema, and should not confmict with user table names.

• Detect with SQL:

SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE LOWER(TABLE_SCHEMA) = 'mysql’ AND LOWER(TABLE_NAME) IN ('catalogs’, ’character_sets’....);

• ACTION: RENAME tables with confmicting names.

Issues detected by upgrade_checker: Example 1

The Transactional Data Dictionary is stored as tables in mysql schema, and should not confmict with user table names.

• Detect with SQL:

SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE LOWER(TABLE_SCHEMA) = 'mysql’ AND LOWER(TABLE_NAME) IN ('catalogs’, ’character_sets’....);

• ACTION: RENAME tables with confmicting names.

Issues detected by upgrade_checker: Example 1

The Transactional Data Dictionary is stored as tables in mysql schema, and should not confmict with user table names.

• Detect with SQL:

SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE LOWER(TABLE_SCHEMA) = 'mysql’ AND LOWER(TABLE_NAME) IN ('catalogs’, ’character_sets’....);

• ACTION: RENAME tables with confmicting names.

Issues detected by upgrade_checker: Example 2

In MySQL 5.7, several spatial functions available under multiple names were deprecated. E.g. PointFromText

• Detect with SQL

━ Use I_S to check view defjnitions. E.g:

select table_name from information_schema.views where view_definition like "%function%”

━ Do similar check for routines, triggers, events, generated columns

• ACTION: Use the new functions in 8.0 with prefjx ‘ST_’ or

‘MBR_’.

Issues detected by upgrade_checker: Example 2

In MySQL 5.7, several spatial functions available under multiple names were deprecated. E.g. PointFromText

• Detect with SQL

━ Use I_S to check view defjnitions. E.g:

select table_name from information_schema.views where view_definition like "%function%”

━ Do similar check for routines, triggers, events, generated columns

• ACTION: Use the new functions in 8.0 with prefjx ‘ST_’ or

‘MBR_’.

Issues detected by upgrade_checker: Example 2

In MySQL 5.7, several spatial functions available under multiple names were deprecated. E.g. PointFromText

• Detect with SQL

━ Use I_S to check view defjnitions. E.g:

select table_name from information_schema.views where view_definition like "%function%”

━ Do similar check for routines, triggers, events, generated columns

• ACTION: Use the new functions in 8.0 with prefjx ‘ST_’ or

‘MBR_’.

Issues detected by upgrade_checker: Example 3

Data types like old style decimals, old style TIME/ DATETIME/ TIMESTAMPS etc that have persisted up until MySQL 5.7 due to binary upgrade are not supported by MySQL 8.0.

• T

ables using these types can be identifjed by running CHECK TABLE … FOR UPGRADE or mysqlcheck tool with

• -check-upgrade option in MySQL 5.7
• ACTION: Fix through REPAIR TABLE and/or dump/reload

for old style varchar/decimal.

Issues detected by upgrade_checker: Example 3

Data types like old style decimals, old style TIME/ DATETIME/ TIMESTAMPS etc that have persisted up until MySQL 5.7 due to binary upgrade are not supported by MySQL 8.0.

• T

ables using these types can be identifjed by running CHECK TABLE … FOR UPGRADE or mysqlcheck tool with

• -check-upgrade option in MySQL 5.7
• ACTION: Fix through REPAIR TABLE and/or dump/reload

for old style varchar/decimal.

Issues detected by upgrade_checker: Example 3

Data types like old style decimals, old style TIME/ DATETIME/ TIMESTAMPS etc that have persisted up until MySQL 5.7 due to binary upgrade are not supported by MySQL 8.0.

• T

ables using these types can be identifjed by running CHECK TABLE … FOR UPGRADE or mysqlcheck tool with

• -check-upgrade option in MySQL 5.7
• ACTION: Fix through REPAIR TABLE and/or dump/reload

for old style varchar/decimal.

Once the upgrade_check runs clean

• Backup your data directory
• Install and start new MySQL 8.0 version
• Run mysql_upgrade
• Restart the server and inspect the error log
• Reconnect apps and verify that they work as expected
• Attempt to upgrade without a clean upgrade_checker

run will abort the upgrade

Once the upgrade_check runs clean

• Backup your data directory
• Install and start new MySQL 8.0 version
• Run mysql_upgrade
• Restart the server and inspect the error log
• Reconnect apps and verify that they work as expected
• Attempt to upgrade without a clean upgrade_checker

run will abort the upgrade

Once the upgrade_check runs clean

• Backup your data directory
• Install and start new MySQL 8.0 version
• Run mysql_upgrade
• Restart the server and inspect the error log
• Reconnect apps and verify that they work as expected
• Attempt to upgrade without a clean upgrade_checker

run will abort the upgrade

Once the upgrade_check runs clean

• Backup your data directory
• Install and start new MySQL 8.0 version
• Run mysql_upgrade
• Restart the server and inspect the error log
• Reconnect apps and verify that they work as expected
• Attempt to upgrade without a clean upgrade_checker

run will abort the upgrade

Once the upgrade_check runs clean

• Backup your data directory
• Install and start new MySQL 8.0 version
• Run mysql_upgrade
• Restart the server and inspect the error log
• Reconnect apps and verify that they work as expected
• Attempt to upgrade without a clean upgrade_checker

run will abort the upgrade

Once the upgrade_check runs clean

• Backup your data directory
• Install and start new MySQL 8.0 version
• Run mysql_upgrade
• Restart the server and inspect the error log
• Reconnect apps and verify that they work as expected
• Attempt to upgrade without a clean upgrade_checker

run will abort the upgrade

Testing applications against new version

Apps might require testing against upgraded server. Some strategies:

• No testing (works when you can afgord being reactive)
• Upgrade one of slaves and test against it
• Mirroring load to test system (proxy and replay tools)
• Predefjned test scenarios
• On SQL layer, on app layer
• ...
• Benchmarks

Testing applications against new version

Apps might require testing against upgraded server. Some strategies:

• No testing (works when you can afgord being reactive)
• Upgrade one of slaves and test against it
• Mirroring load to test system (proxy and replay tools)
• Predefjned test scenarios
• On SQL layer, on app layer
• ...
• Benchmarks

Testing applications against new version

Apps might require testing against upgraded server. Some strategies:

• No testing (works when you can afgord being reactive)
• Upgrade one of slaves and test against it
• Mirroring load to test system (proxy and replay tools)
• Predefjned test scenarios
• On SQL layer, on app layer
• ...
• Benchmarks

Testing applications against new version

Apps might require testing against upgraded server. Some strategies:

• No testing (works when you can afgord being reactive)
• Upgrade one of slaves and test against it
• Mirroring load to test system (proxy and replay tools)
• Predefjned test scenarios
• On SQL layer, on app layer
• ...
• Benchmarks

Testing applications against new version

Apps might require testing against upgraded server. Some strategies:

• No testing (works when you can afgord being reactive)
• Upgrade one of slaves and test against it
• Mirroring load to test system (proxy and replay tools)
• Predefjned test scenarios
• On SQL layer, on app layer
• ...
• Benchmarks

Testing applications against new version

Apps might require testing against upgraded server. Some strategies:

• No testing (works when you can afgord being reactive)
• Upgrade one of slaves and test against it
• Mirroring load to test system (proxy and replay tools)
• Predefjned test scenarios
• On SQL layer, on app layer
• ...
• Benchmarks

A better future with MySQL 8.0 →

Upgrade MySQL 8.0 →

• How can we continue improving the upgrade?

━ We want to reduce time and risk even further

• Bulk of time spent for in-place upgrade of MySQL:

━ Harvest metadata for analysis ━ Examine all user tables

Upgrade MySQL 8.0 →

• How can we continue improving the upgrade?

━ We want to reduce time and risk even further

• Bulk of time spent for in-place upgrade of MySQL:

━ Harvest metadata for analysis ━ Examine all user tables

Transactional Data Dictionary in MySQL 8.0

Data Dictionary InnoDB

DD T able DD T able

DD T able

SQL

Upgrade MySQL 8.0 →

• MySQL now stores all metadata in InnoDB (Done)

━ Enables fast metadata analysis with SQL-queries

• Added metadata for versioning (Done)

━ The new mysqld executable knows which version it is upgrading from

• Improved protection of metadata good for security

reasons (Partly done)

━ Enforced metadata integrity

• Remove need for mysql_upgrade client (WIP)

━ Move functionality to mysqld ━ Docker container friendly

Upgrade MySQL 8.0 →

• MySQL now stores all metadata in InnoDB (Done)

━ Enables fast metadata analysis with SQL-queries

• Added metadata for versioning (Done)

━ The new mysqld executable knows which version it is upgrading from

• Improved protection of metadata good for security

reasons (Partly done)

━ Enforced metadata integrity

• Remove need for mysql_upgrade client (WIP)

━ Move functionality to mysqld ━ Docker container friendly

Upgrade MySQL 8.0 →

• MySQL now stores all metadata in InnoDB (Done)

━ Enables fast metadata analysis with SQL-queries

• Added metadata for versioning (Done)

━ The new mysqld executable knows which version it is upgrading from

• Improved protection of metadata good for security

reasons (Partly done)

━ Enforced metadata integrity

• Remove need for mysql_upgrade client (WIP)

━ Move functionality to mysqld ━ Docker container friendly

Upgrade MySQL 8.0 →

• MySQL now stores all metadata in InnoDB (Done)

━ Enables fast metadata analysis with SQL-queries

• Added metadata for versioning (Done)

━ The new mysqld executable knows which version it is upgrading from

• Improved protection of metadata good for security

reasons (Partly done)

━ Enforced metadata integrity

• Remove need for mysql_upgrade client (WIP)

━ Move functionality to mysqld ━ Docker container friendly

Upgrade MySQL 8.0 → the GREAT news

The traditional MySQL upgrade revisited:

• 1. Stop old MySQL Server
• 2. Change binaries to new MySQL Server version
• 3. Adjust confjg, my.cnf of new server version
• 4. Start new MySQL Server
• Analyze metadata and automatically upgrade, making upgrade

process fast

• 5. Run mysql_upgrade to possibly upgrade system and

user tables

• Potentially time consuming
• 6. Restart MySQL server
• Reduces downtime, container/docker friendly.

Upgrade MySQL 8.0 → the GREAT news

The traditional MySQL upgrade revisited:

• 1. Stop old MySQL Server
• 2. Change binaries to new MySQL Server version
• 3. Adjust confjg, my.cnf of new server version
• 4. Start new MySQL Server
• Analyze metadata and automatically upgrade, making upgrade

process fast

• 5. Run mysql_upgrade to possibly upgrade system and

user tables

• Potentially time consuming
• 6. Restart MySQL server
• Reduces downtime, container/docker friendly.

Upgrade MySQL 8.0 → the GREAT news

The traditional MySQL upgrade revisited:

• 1. Stop old MySQL Server
• 2. Change binaries to new MySQL Server version
• 3. Adjust confjg, my.cnf of new server version
• 4. Start new MySQL Server
• Analyze metadata and automatically upgrade, making upgrade

process fast

• 5. Run mysql_upgrade to possibly upgrade system and

user tables

• Potentially time consuming
• 6. Restart MySQL server
• Reduces downtime, container/docker friendly.

Upgrade MySQL 8.0 → the GREAT news

The traditional MySQL upgrade revisited:

• 1. Stop old MySQL Server
• 2. Change binaries to new MySQL Server version
• 3. Adjust confjg, my.cnf of new server version
• 4. Start new MySQL Server
• Analyze metadata and automatically upgrade, making upgrade

process fast

• 5. Run mysql_upgrade to possibly upgrade system and

user tables

• Potentially time consuming
• 6. Restart MySQL server
• Reduces downtime, container/docker friendly.

Summary: Upgrade MySQL 8.0 →

• Lowers risk

━ The upgrade_checker identifjes potential issues

• In active development
• Faster upgrade process

━ Fast metadata analysis ━ Fast metadata upgrade ━ Removal of mysql_upgrade

• Simplifjed upgrade process

━ Fewer steps ━ Automatic metadata upgrade

Summary: Upgrade MySQL 8.0 →

• Lowers risk

━ The upgrade_checker identifjes potential issues

• In active development
• Faster upgrade process

━ Fast metadata analysis ━ Fast metadata upgrade ━ Removal of mysql_upgrade

• Simplifjed upgrade process

━ Fewer steps ━ Automatic metadata upgrade

Summary: Upgrade MySQL 8.0 →

• Lowers risk

━ The upgrade_checker identifjes potential issues

• In active development
• Faster upgrade process

━ Fast metadata analysis ━ Fast metadata upgrade ━ Removal of mysql_upgrade

• Simplifjed upgrade process

━ Fewer steps ━ Automatic metadata upgrade

Recap: Upgrade to MySQL 8.0

• Prepare you upgrade (release notes, resources)
• DO run the upgrade_checker

━ Fix issues until it runs clean

• TEST your applications on MySQL 8.0
• DO your backup
• Upgrade to MySQL 8.0

Recap: Upgrade to MySQL 8.0

• Prepare you upgrade (release notes, resources)
• DO run the upgrade_checker

━ Fix issues until it runs clean

• TEST your applications on MySQL 8.0
• DO your backup
• Upgrade to MySQL 8.0

Recap: Upgrade to MySQL 8.0

• Prepare you upgrade (release notes, resources)
• DO run the upgrade_checker

━ Fix issues until it runs clean

• TEST your applications on MySQL 8.0
• DO your backup
• Upgrade to MySQL 8.0

Recap: Upgrade to MySQL 8.0

• Prepare you upgrade (release notes, resources)
• DO run the upgrade_checker

━ Fix issues until it runs clean

• TEST your applications on MySQL 8.0
• DO your backup
• Upgrade to MySQL 8.0

Recap: Upgrade to MySQL 8.0

• Prepare you upgrade (release notes, resources)
• DO run the upgrade_checker

━ Fix issues until it runs clean

• TEST your applications on MySQL 8.0
• DO your backup
• Upgrade to MySQL 8.0

Recap: Upgrade to MySQL 8.0

• Prepare you upgrade (release notes, resources)
• DO run the upgrade_checker

━ Fix issues until it runs clean

• TEST your applications on MySQL 8.0
• DO your backup
• Upgrade to MySQL 8.0

Try for yourself!

• Downloadable 8.0.13
• http://dev.mysql.com/
• Enjoy and give us feedback!
• Thank you!

MySQL Upgrade Resources

• https://dev.mysql.com/doc/refman/8.0/en/upgrading-strategies.html
• https://dev.mysql.com/doc/refman/8.0/en/upgrading-from-previous-series.html
• https://mysqlserverteam.com/inplace-upgrade-from-mysql-5-7-to-mysql-8-0/
• https://mysqlserverteam.com/upgrading-to-mysql-8-0-here-is-what-you-need-to-know/
• https://mysqlserverteam.com/mysql-shell-8-0-4-introducing-upgrade-checker-utility/
• https://mysqlserverteam.com/upgrading-to-mysql-8-0-with-spatial-data/
• https://mysqlserverteam.com/whats-new-in-mysql-8-0-generally-available/
• https://mysqlserverteam.com/upgrading-your-mysql-server-farm/