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

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 File system FRM TRG OPT SQL System T ables (mysql. * ) MyISAM user events proc InnoDB System T ables InnoDB

Transactional Data Dictionary in MySQL 8.0 Data Dictionary DD T able DD T able DD T able SQL InnoDB

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 ● ENUM/SET columns with ● Usage of old temporal types elements longer than 255 ● Confmicting db object names chars and reserved keywords ● Usage of partitioned tables ● Usage of utf8mb3 charset in shared tablespaces ● Reserved table names in ● Usage of removed functions mysql schema ● Usage of removed GROUP ● Foreign key names longer BY ASC/DESC than 64 chars ● Issues reported by “CHECK ● Usage of obsolete TABLE … FOR UPGRADE” sql_modes command

Issues detected by upgrade_checker ● ENUM/SET columns with ● Usage of old temporal types elements longer than 255 ● Confmicting db object names chars and reserved keywords ● Usage of partitioned tables ● Usage of utf8mb3 charset in shared tablespaces ● Reserved table names in ● Usage of removed functions mysql schema ● Usage of removed GROUP ● Foreign key names longer BY ASC/DESC than 64 chars ● Issues reported by “CHECK ● Usage of obsolete TABLE … FOR UPGRADE” sql_modes command

Issues detected by upgrade_checker ● ENUM/SET columns with ● Usage of old temporal types elements longer than 255 ● Confmicting db object names chars and reserved keywords ● Usage of partitioned tables ● Usage of utf8mb3 charset in shared tablespaces ● Reserved table names in ● Usage of removed functions mysql schema ● Usage of removed GROUP ● Foreign key names longer BY ASC/DESC than 64 chars ● Issues reported by “CHECK ● Usage of obsolete TABLE … FOR UPGRADE” sql_modes command

Issues detected by upgrade_checker ● ENUM/SET columns with ● Usage of old temporal types elements longer than 255 ● Confmicting db object names chars and reserved keywords ● Usage of partitioned tables ● Usage of utf8mb3 charset in shared tablespaces ● Reserved table names in ● Usage of removed functions mysql schema ● Usage of removed GROUP ● Foreign key names longer BY ASC/DESC than 64 chars ● Issues reported by “CHECK ● Usage of obsolete TABLE … FOR UPGRADE” sql_modes command

Issues detected by upgrade_checker ● ENUM/SET columns with ● Usage of old temporal types elements longer than 255 ● Confmicting db object names chars and reserved keywords ● Usage of partitioned tables ● Usage of utf8mb3 charset in shared tablespaces ● Reserved table names in ● Usage of removed functions mysql schema ● Usage of removed GROUP ● Foreign key names longer BY ASC/DESC than 64 chars ● Issues reported by “CHECK ● Usage of obsolete TABLE … FOR UPGRADE” sql_modes command

Issues detected by upgrade_checker ● ENUM/SET columns with ● Usage of old temporal types elements longer than 255 ● Confmicting db object names chars and reserved keywords ● Usage of partitioned tables ● Usage of utf8mb3 charset in shared tablespaces ● Reserved table names in ● Usage of removed functions mysql schema ● Usage of removed GROUP ● Foreign key names longer BY ASC/DESC than 64 chars ● Issues reported by “CHECK ● Usage of obsolete TABLE … FOR UPGRADE” sql_modes command

Issues detected by upgrade_checker ● ENUM/SET columns with ● Usage of old temporal types elements longer than 255 ● Confmicting db object names chars and reserved keywords ● Usage of partitioned tables ● Usage of utf8mb3 charset in shared tablespaces ● Reserved table names in ● Usage of removed functions mysql schema ● Usage of removed GROUP ● Foreign key names longer BY ASC/DESC than 64 chars ● Issues reported by “CHECK ● Usage of obsolete TABLE … FOR UPGRADE” sql_modes command

Issues detected by upgrade_checker ● ENUM/SET columns with ● Usage of old temporal types elements longer than 255 ● Confmicting db object names chars and reserved keywords ● Usage of partitioned tables ● Usage of utf8mb3 charset in shared tablespaces ● Reserved table names in ● Usage of removed functions mysql schema ● Usage of removed GROUP ● Foreign key names longer BY ASC/DESC than 64 chars ● Issues reported by “CHECK ● Usage of obsolete TABLE … FOR UPGRADE” sql_modes command

Issues detected by upgrade_checker ● ENUM/SET columns with ● Usage of old temporal types elements longer than 255 ● Confmicting db object names chars and reserved keywords ● Usage of partitioned tables ● Usage of utf8mb3 charset in shared tablespaces ● Reserved table names in ● Usage of removed functions mysql schema ● Usage of removed GROUP ● Foreign key names longer BY ASC/DESC than 64 chars ● Issues reported by “CHECK ● Usage of obsolete TABLE … FOR UPGRADE” sql_modes command

Issues detected by upgrade_checker ● ENUM/SET columns with ● Usage of old temporal types elements longer than 255 ● Confmicting db object names chars and reserved keywords ● Usage of partitioned tables ● Usage of utf8mb3 charset in shared tablespaces ● Reserved table names in ● Usage of removed functions mysql schema ● Usage of removed GROUP ● Foreign key names longer BY ASC/DESC than 64 chars ● Issues reported by “CHECK ● Usage of obsolete TABLE … FOR UPGRADE” sql_modes command

Issues detected by upgrade_checker ● ENUM/SET columns with ● Usage of old temporal types elements longer than 255 ● Confmicting db object names chars and reserved keywords ● Usage of partitioned tables ● Usage of utf8mb3 charset in shared tablespaces ● Reserved table names in ● Usage of removed functions mysql schema ● Usage of removed GROUP ● Foreign key names longer BY ASC/DESC than 64 chars ● Issues reported by “CHECK ● Usage of obsolete TABLE … FOR UPGRADE” sql_modes 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 DD T able DD T able DD T able SQL InnoDB

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.