upgrading to mysql 8.0+: a more automated upgrade ......mysql upgrade 5.7 → 8.0, smooth sailing!...

Upgrading to MySQL 8.0+: a More Automated

Upgrade ExperienceDmitry Lenev, Software Developer

Oracle/MySQL, November 2018Dmitry 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 generalUpgrading in general

Why upgrade MySQL installation?

● Security concerns● Bug fixes● 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 afford 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━ Total 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

Upgrade to MySQL 8.0

The straightforward caseThe straightforward case

MySQL upgrade 5.7 → 8.0, smooth sailing!

● Read release notes: Conclusion – No problems● Run upgrade_checkerme@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.

……

● Backup your data directory● Install and start MySQL 8.0━ In-place upgrade

● Run mysql_upgrademe@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 OKmysql.db OK

Upgrade process completed successfully.

● Restart the server and look at messages in the error log2018-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.

● Restart the server and look at messages in the error log2018-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

MySQL Data Dictionary before MySQL 8.0

Data Dictionary

FRM TRG OPT

System Tables (mysql.*)

user procevents

InnoDB System Tables

MyISAM

File system

InnoDB

Transactional Data Dictionary in MySQL 8.0

Data Dictionary

InnoDBDD TableDD TableDD Table

Upgrade to MySQL 8.0 in detail and how upgrade_checker helps

MySQL 8.0 features

● Read release noteshttps://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 configuration━ ++++

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 affected━ EXTENDED and PARTITIONS keywords in EXPLAIN━ \N as synonim for NULL in SQL

create users

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

● Especially note character set and collation defaults changes!

● Note that for upgrading SCHEMAs from 5.7 to 8.0, each schema has its own specified 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

Issues detected by upgrade_checker

● Usage of old temporal types● Conflicting 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

sql_modes

Issues detected by upgrade_checker: Example 1

The Transactional Data Dictionary is stored as tables in mysql schema, and should not conflict 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 conflicting names.

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 definitions. 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 prefix ‘ST_’ or ‘MBR_’.

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.

● Tables using these types can be identified 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

Testing applications against new version

Apps might require testing against upgraded server.

Some strategies:● No testing (works when you can afford being reactive)● Upgrade one of slaves and test against it● Mirroring load to test system (proxy and replay tools)● Predefined 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

● 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

InnoDBDD TableDD TableDD Table

● 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

● 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 Server2. Change binaries to new MySQL Server version3. Adjust config, my.cnf of new server version4. 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 identifies potential issues

● In active development

● Faster upgrade process━ Fast metadata analysis━ Fast metadata upgrade━ Removal of mysql_upgrade

● Simplified 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

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/

upgrading to mysql 8.0+: a more automated upgrade ......mysql upgrade 5.7 → 8.0, smooth sailing!...

Documents