Migrating from MySQL 5.7 to MySQL 8 - Migrate your MySQL applications effectively

Migrating from MySQL 5.7 to MySQL 8 Olivier Dasini MySQL
Cloud Principal Solutions Architect EMEA [email protected] Blogs : www.dasini.net/blog/en : www.dasini.net/blog/fr Linkedin: www.linkedin.com/in/olivier-dasini December 2023 Migrate your MySQL applications effectively

Copyright © 2023, Oracle and/or its affiliates. All rights reserved.
2 Me, Myself & I  MySQL Geek  Addicted to MySQL for 15+ years  Playing with databases for 20+ years  MySQL Writer, Blogger and Speaker  Also: DBA, Consultant, Architect, Trainer, ...  MySQL Cloud Principal Solutions Architect EMEA at Oracle  Stay up to date!  Blog: www.dasini.net/blog/en  Linkedin: www.linkedin.com/in/olivier-dasini/ Olivier DASINI

Agenda 1. MySQL – The state of the Dolphin 2. Migrate to MySQL 8 a) Discover & Assess b) Plan Migration c) Provision & Configure d) Migrate your data e) Check & Test 3. Conclusion 3

4 MySQL – The state of the Dolphin Introduction

5 The MySQL universe - The view from the moon… MySQL Commercial/Enterprise MySQL Community + MySQL Enterprise Backup MySQL Enterprise Monitor MySQL Enterprise Authentication MySQL Enterprise Audit MySQL Enterprise TDE MySQL Enterprise Masking MySQL Enterprise Firewall MySQL Technical Support … MySQL Cluster CGE MySQL Cluster NDB + MySQL Enterprise + MySQL Cluster Manager MySQL Community MySQL Server MySQL Client, Workbench MySQL Shell MySQL GR plugin & InnoDB Cluster & Router MySQL Operator for Kubernetes MySQL Connector (C API, Java, Node.js, others) MySQL Support for MS VS Code (Preview) … MySQL Cluster NDB MySQL NDB Storage Engine MySQL NDB Operator for Kubernetes MySQL Cloud Services (PaaS) MySQL HeatWave Databases Services (for OLTP) MySQL HeatWave (for Analytics) MySQL HeatWave AutoML (for Machine Learning) MySQL HeatWave on AWS MySQL HeatWave on Azure MySQL HeatWave Lakehouse Community, Enterprise, Cloud Service (MySQL HeatWave) https://www.mysql.com/products

6 MySQL – The state of the Dolphin MySQL Enterprise Edition

7 Reduce the risk, complexity and cost MySQL Enterprise Edition Management Tools Advanced Features Support • Authentication • Audit • Encryption / TDE • Firewall • Data Masking • High Availability • Monitoring • Backup • Development • Administration • Migration •Technical Support •Oracle Certifications

8 Building Secure Applications with MySQL Enterprise Edition MySQL Security Architecture

9 MySQL – The state of the Dolphin MySQL HeatWave

10 MySQL HeatWave Cloud Service 100% developed, managed, and supported by the MySQL team Automation MySQL HeatWave Database High Availability Read Replicas Backup Query Acceleration AutoPilot AutoML Security Patch & Upgrade Provision & Configure OS OS Security Patch & Upgrade OS Installation Server Hardware Provisioning & Maintenance Storage Storage Provisioning & Maintenance Data Center Rack & Space Power, HVAC, Networking

11 … the MySQL Cloud services made by the MySQL Team The MySQL HeatWave “Family” “MySQL HeatWave” MySQL HeatWave for OLTP a.k.a MDS: (OLTP) General Purpose Optimized for OLTP MySQL HeatWave Lakehouse (Lakehouse) OLTP + Analytics (OLAP) + Machine Learning MySQL Heatwave (Warehouse) OLTP + Analytics (OLAP) + Machine Learning InnoDB Lakehouse InnoDB RAPID InnoDB RAPID

12 MySQL HeatWave Social ECommerce FinTech SaaS InnoDB HeatWave OLTP OLAP ML Tools Machine Learning Autopilot Lakehouse Database Exports MySQL HeatWave Analytics tools Database One Database for Transactional, Data Warehouse, Lakehouse & Machine Learning

13 • MySQL HeatWave is available in Oracle Cloud Infrastructure • … but also in Amazon Web Services & Microsoft Azure MySQL HeatWave - Ready for the distributed cloud Optimized to deliver the best price performance in each cloud

14 100% Developed, Managed, and Supported by the Oracle MySQL Team Same MySQL Database for On-Prem, Hybrid, & Cloud Deployments Customer managed Fully managed Hybrid MySQL Database Hybrid Deployments MySQL HeatWave Upstream MySQL 100% Developed by Oracle with the latest updates and features. MySQL Enterprise Edition Data protection and regulatory compliance using advanced security. OLTP, OLAP, ML, Lakehouse One fully managed database service for OLTP, OLAP, ML, Lakehouse workloads. Multicloud One fully managed database service available in OCI, Amazon AWS, and Microsoft Azure. 100% Compatible 100% compatible with on-premises MySQL. No lock-in to a fork or cloud service. Flexibility Move and deploy MySQL workloads across cloud & on-premises environments.

Agenda 1. MySQL – The state of the Dolphin 2. Migrate to MySQL 8 a) Discover & Assess b) Plan Migration c) Provision & Configure d) Migrate your data e) Check & Test 3. Conclusion 15

16 Discover & Assess

17 Discover & Assess - Why Upgrade ?  MySQL 8 features – Instant Add Column, JSON Document Store, CTE, Window Functions, JSON, GIS, Functional indexes, Hash Joins, Histograms, Check Constraints, ...  Ease of use of a fully managed cloud service → MySQL HeatWave – Easy provisioning, Automated backup, PITR, High Availability, Managed read replicas, ...  Security improvements – New Authentication Plugin, SSL/TLS, SQL Roles, Tables & Log Encryption, Password management, ...  Performance/Scalability improvements – Cost Model, Histograms, P_S, I_S, Scaling R/W workloads, Resource Groups, ...  Manageability – Set Persist, Invisible Indexes, Restart command, Instant Add Column, ...  Reduce tech debt for the MySQL installation – Softer upgrades  Stay on fully supported release  ... Because “MySQL HeatWave Is Great” :) but also for...

18 Discover & Assess - Why Upgrade ? Stay on fully supported release Release GA Date Premier Support End Extended Support End Sustaining Support End MySQL 5.0 Oct 2005 Dec 2011 N/A Indefinite MySQL 5.1 Dec 2008 Dec 2013 N/A Indefinite MySQL 5.5 Dec 2010 Dec 2015 Dec 2018 Indefinite MySQL 5.6 Feb 2013 Feb 2018 Feb 2021 Indefinite MySQL 5.7 Oct 2015 Oct 2020 Oct 2023 Indefinite MySQL 8.0 Apr 2018 Apr 2023 Feb 2026 Indefinite https://www.oracle.com/support/premier

19 MySQL Versions Release Cadence https://blogs.oracle.com/mysql/post/introducing-mysql-innovation-and-longterm-support-lts-versions MySQL Innovation and Long-Term Support (LTS) versions

20 MySQL Innovation and Long-Term Support (LTS) versions • We are now transitioning to a versioning model where you can choose between 2 kinds of releases: – Innovation – Long-Term Support (LTS) • Both Innovation and LTS are production-grade quality • If you desire access to the latest features and improvements and enjoy staying on top of the latest technologies, the MySQL Innovation releases might be the best fit for you – These releases are ideal for developers and DBAs working in fast-paced development environments with high levels of automated tests and modern continuous integration techniques for faster upgrade cycles • On the other hand, if your environment requires established behavior, then the LTS releases are the way to go – These releases contain only necessary fixes, so they reduce the risks associated with changes in the database software's behavior https://blogs.oracle.com/mysql/post/introducing-mysql-innovation-and-longterm-support-lts-versions

21 Discover & Assess - Before you start  Review "Changes in MySQL 8.0" for changes that may require action before upgrading – https://dev.mysql.com/doc/refman/8.0/en/upgrading-from-previous-series.html  Review "What Is New in MySQL 8.0" for added / deprecated / removed features – https://dev.mysql.com/doc/refman/8.0/en/mysql-nutshell.html  Review "Server and Status Variables and Options Added, Deprecated, or Removed in MySQL 8.0" – If you use deprecated or removed variables, an upgrade may require configuration changes – https://dev.mysql.com/doc/refman/8.0/en/added-deprecated-removed.html  Review "Release Notes" for information about fixes, changes, and new features – https://dev.mysql.com/doc/relnotes/mysql/8.0/en/  … https://dev.mysql.com/doc/refman/8.0/en/upgrade-before-you-begin.html

22 Is your MySQL server instance ready for upgrade?  Preliminary checks... – Usage of old temporal type? – Conflicting db object names and reserved keywords? – Usage of utf8mb3 charset? – Reserved tablenames in mysql schema? – FK names longer than 64 chars? – Usage of obsolete sql_mode? – ENUM/SET column definitions containing elements longer than 255 characters? – Usage of partitioned tables in shared tablespaces? – Usage of removed functions? – Usage of removed GROUP BY ASC/DESC? – Issues reported by ”check table x for upgrade” command? – ... https://dev.mysql.com/doc/refman/8.0/en/upgrade-prerequisites.html

23 Discover & Assess Upgrade Checker

24 MySQL Server 5.7 MySQL 8.0 Upgrade Checker Prompt Themes Auto Completion & Command History MySQL Server 8.0 Document Store X Dev API SQL CLI Output Formats (Table, JSON, Tabbed) Batch Execution JavaScript Python SQL importJSON Dump Utilities InnoDB Cluster / ReplicaSet / ClusterSet etc... MySQL Shell Overview Interface for Development and Administration of MySQL https://dev.mysql.com/doc/mysql-shell/8.0/en

25 MySQL Shell Upgrade Checker Utility - checkForServerUpgrade  Utility from MySQL Shell (start with MySQL Shell 8.0.13) – JS> util.checkForServerUpgrade()  Will check your MySQL 5.7 or 8.0 installation readiness for upgrade – Check for legacy issues – Run the tool on the 5.7 before upgrading! - Users can make changes when time permits before the upgrade – Always use latest version of MySQL Shell  Privileges needed: – Since 8.0.21: RELOAD, PROCESS & SELECT Examples: mysqlsh -- util check-for-server-upgrade { --user=root --host=172.25.0.10 } mysqlsh -e "util.checkForServerUpgrade({user:'root', host:'172.25.0.10'})" MYSQL JS> util.checkForServerUpgrade("[email protected]", {outputFormat:'JSON'}) Upgrade checker utility that enables you to verify whether MySQL server instances are ready for upgrade https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-utilities-upgrade.html

26 https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-utilities-upgrade.html

27 https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-utilities-upgrade.html MySQL Shell Upgrade Checker Utility - checkForServerUpgrade caching_sha2_password

28 MySQL Shell Upgrade Checker Utility - checkForServerUpgrade Upgrade checker utility that enables you to verify whether MySQL server instances are ready for upgrade  Result – Errors: – Warnings: – Notices: https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-utilities-upgrade.html

29 Discover & Assess – Some important questions...  Inventory data – What storage engine? – What is the size of your data? ; What character set / collation? – Any stored procedures / stored functions / triggers / events?  What Operating System?  Configuration and specification of the current architecture  Workload characteristics?  Estimate storage, compute and memory needs  SLA? ; Downtime allowed?  Backup policy? ; RTO? ; RPO? ; HA? ; DR?  Any compliance, regulatory, or auditing requirements?  Training? / Need expert guidance?  TCO  ...

30 Plan Migration

31 Plan Migration  Team members assigned to work on the migration  Offline / Near real-time – Export / Import; Replication; – Lift & Shift then migrate to MySQL HeatWave ? • OCI environment (Privileges, Network, Storage, MySQL HeatWave shape, …)  Data transfer (size, duration, HW/network sizing, ...)  Failure handling  Failback?  Migration timeline  Establish safe decommissioning criteria for the source environment  Monitoring  Automate!  Build a PoC allows you to validate / amend the plan and test – Performance testing (does DB meets expectations) – Load testing (how DB performs at peak demand) – Stress testing (when DB breaks)  ...

32 Plan Migration Tools for backup & restore

33 MySQL Enterprise Backup  Online, non-locking backup and recovery – Complete MySQL instance backup (data and config) – Full & Partial backup and restore  Direct Cloud storage backups (S3, etc.)  Incremental backups & Point-in-time recovery  Advanced compressed and Strong encryption: AES 256  Backup to tape (SBT)  Backup validation  Optimistic backups  Cross-Platform (Windows, Linux, Unix)  Certified with Oracle Secure Backup, NetBackup, Tivoli, ... Ensure availability of end-user data

34 MySQL Shell dump utilities - dumpInstance & dumpSchemas  Logical Dump Utilities from MySQL Shell – Instance dump: util.dumpInstance() • Dump an entire database instance, including users – Schema dump: util.dumpSchemas() • Dump a set of schemas – Support the export of all schemas or a selected schema from an on-premise MySQL server instance into a set of local files or an Oracle Cloud Infrastructure Object Storage bucket – The schemas can then be imported into a MySQL HeatWave using MySQL Shell's new dump loading utility – Provide Oracle Cloud Infrastructure Object Storage streaming, MySQL Database Service compatibility checks and modifications, parallel dumping with multiple threads, and file compression Export data from a MySQL instance into a set of local files or an OCI Object Storage bucket https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-utilities-dump-instance-schema.html

35 MySQL Shell load utility - loadDump  Logical Load Utility from MySQL Shell – Load a dump: util.loadDump() • Load a dump into a target database – Support the import of schemas dumped using MySQL Shell's new instance dump utility and schema dump utility into a MySQL instance or a MySQL HeatWave – The dump loading utility provides data streaming from remote storage, parallel loading of tables or table chunks, progress state tracking, resume and reset capability, and the option of concurrent loading while the dump is taking place Import into a MySQL Server instance or a MySQL Database Service DB System of schemas dumped https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-utilities-load-dump.html

36 MySQL Shell Load & Dump utilities Benchmarks

37 Provision & Configure

38 Migrate to MySQL HeatWave  Must have an OCI account  Set up your Identity and Access Management (IAM) – In order to control who has access to your cloud resources  Network – VCN – Security Lists (Firewall) – VPN connection from OCI to on-prem (or FastConnect) – Route tables  Compute  Object Storage  MySQL Shell  MySQL HeatWave optimal shape  … Provision & Configure

39 Migrate your data

40 Before you Upgrade – Backups of Your Data!  Database upgrading is very serious work! – So backup your data!  Plan for the worst - Paranoia is not always a bad things – So backup your data!  Use your favorite backup tool – MySQL Enterprise Backup – dumpInstance() / dumpSchema() / loadDump() from MySQL Shell • For MySQL Server 5.6, 5.7 & 8.0 – mysqldump (!) – …  Double check your backups  Test your Restore procedure

41 Ready to migrate \o/

42 Just one more thing...

43 Did I mention BACKUP?

44 Migrate your data In-Place Upgrade

45 In-Place Upgrade  An in-place upgrade involves: – Shutting down the old MySQL server – Replacing the old MySQL binaries or packages with the new ones – Restarting MySQL on the existing data directory – Upgrading any remaining parts of the existing installation that require upgrading https://dev.mysql.com/doc/refman/8.0/en/upgrade-binary-package.html#upgrade-procedure-inplace

46 In-Place Upgrade workflow 1. Do all necessary preparations/plans/upgrade checker as explained earlier e.g. mysqlsh -- util check-for-server-upgrade { --user=admin --host=172.25.0.10 } 2. Create a backup of your MySQL instance (data, logs, config files) 3. Do a graceful shutdown of MySQL (innodb_fast_shutdown=0) 4. Upgrade MySQL 8.0 - replace the old binaries with the new ones 5. Start the new MySQL 8.0 instance using the new binaries and the new my.cnf 6. Check messages in the error log 7. Verify that apps and services are working as expected https://dev.mysql.com/doc/refman/8.0/en/upgrade-binary-package.html#upgrade-procedure-inplace

47 Migrate your data Logical Upgrade

48 Logical Upgrade  A logical upgrade involves: – Export the data from the old MySQL instance using an utility such as • MySQL Shell Dump utilities : dumpInstance, dumpSchemas • mysqldump • ETL, scripts, ... • ... – Installing the new MySQL server – Import the data to your new MySQL instance https://dev.mysql.com/doc/refman/8.0/en/upgrade-binary-package.html#upgrade-procedure-logical

49 Logical Upgrade workflow 1. Do all necessary preparations/plans/upgrade checker as explained earlier e.g. mysqlsh -- util check-for-server-upgrade { --user=admin --host=172.25.0.10 } 2. Create a dump of your MySQL instance (+ logs, config files) Don’t forget your stored routines and your Event Scheduler objects 3. Shutdown your MySQL Server 4. Do a clean installation of the new MySQL Server version - Initialize a new data directory Copy the temporary 'root'@'localhost' password displayed to your screen or written to your error log for later use 5. Start the new MySQL 8.0 instance using the new binaries and the new my.cnf 6. Reset the root password ALTER USER USER() IDENTIFIED BY 'new password'; 7. Load the dump into the new MySQL server instance 8. Restart the MySQL Server with the --upgrade=FORCE option to perform the remaining upgrade tasks 9. Check messages in the error log 10.Verify that apps and services are working as expected https://dev.mysql.com/doc/refman/8.0/en/upgrade-binary-package.html#upgrade-procedure-logical

50 Migrate your data Migration to MySQL HeatWave

51 MySQL Shell Dump MDS Data Import or MySQL Shell Dump Load OCI Object Storage OCI Network Your Network MySQL On-premise, public or private cloud... MySQL Database Service Migration to MySQL HeatWave Using MySQL Shell Dump & Load

52 Migrate your data Using MySQL Replication

53 Using MySQL Replication (simplified)  Do all necessary preparations/plans as explained earlier  Provision a new instance with MySQL 8  Setup replication from MySQL 5.7 to MySQL 8  Test application on the replica – Possibly adapt applications  Cutover and decommission MySQL 5.7 MySQL 8 App MySQL 5.7 App Replication MySQL 5.7 App MySQL 8 MySQL 8

54 Check & Test

55 Migrate to MySQL HeatWave  Checks logs, data consistency, performance, behavior of the application, …  Functional testing (unit, integration & regression tests) – Are objects and data migrated correctly? – Are queries return expected results? – Can users use their services? – Is DB behavior consistent with the former one? – …  Automate ! Check & Test

56 Wrap-up

57 Migrate to MySQL - Recap  PREPARE your upgrade (release notes, resources)  Preliminary CHECKS – DO run the Upgrade Checker Utility : util.checkForServerUpgrade() – Fix issues until it runs clean  TEST your applications on MySQL 8.0 – Replication can help – Source : 5.7 → Secondary : 8.0  DO your BACKUP  UPGRADE to MySQL 8.0  Post CHECKS – logs, data consistency, performance, behavior of the application, ...

58 • MySQL on-premise – Migration planning questionnaire (PDF) – Migrate from MySQL on-premise to MySQL HeatWave • OCI Migration Guide (PDF) • OCI Live Migration Guide (PDF) • MySQL HeatWave AWS Migration Guide (PDF) • Amazon RDS for MySQL – Migration planning questionnaire – Migrate from Amazon RDS to MySQL HeatWave • OCI Migration Guide (PDF) • OCI Live Migration Guide (PDF) • MySQL HeatWave AWS Migration Guide (PDF) • Amazon Aurora – Migration planning questionnaire – Migrate from Amazon Aurora to MySQL HeatWave • OCI Migration Guide (PDF) • OCI Live Migration Guide (PDF) • MySQL HeatWave AWS Migration Guide (PDF) 58 MySQL HeatWave Migration Program https://www.oracle.com/mysql/migration/ Copyright © 2023, Oracle and/or its affiliates Step-by-step migration guides

Get $300 in credits and try free for 30 days
Copyright © 2023, Oracle and/or its affiliates Get started with MySQL HeatWave oracle.com/mysql/free Learn more about MySQL HeatWave oracle.com/mysql Request a guided workshop Ask your account manager

60 Follow us on Social Media “Data is the Oxygen of Business”

Merci! Q&R Olivier Dasini MySQL Cloud Principal Solutions Architect EMEA
[email protected] Blogs : www.dasini.net/blog/en : www.dasini.net/blog/fr Linkedin: www.linkedin.com/in/olivier-dasini

63 Contribution to MySQL Become part of the contributor community for the MySQL open-source project, https://forums.oracle.com/ords/apexds/post/contributing-code-to-mysql-8037 • What Contributor should have • A wish to change/fix something in MySQL or have a new feature • Downloaded MySQL source code http://dev.mysql.com/downloads/ • An account in bugs.mysql.com http://bugs.mysql.com or • Working GitHub account https://github.com • Signed Oracle Contribution Agreement (OCA) https://oca.opensource.oracle.com/ • OCA Is a short legal agreement which protects both you as a contributor and Oracle from legal attack. By signing the OCA, you agree that Oracle is legally allowed to use your code in Oracle software and that the code is, to the best of your knowledge, unencumbered by any patent issues

64 Oracle Operates MySQL at Cloud Scale to Deliver Innovations Faster High Availability Security Backup Patching AutoPilot Analytics Machine Learning Lakehouse Operate MySQL at Cloud Scale Oracle operates MySQL HeatWave across 41 public cloud regions in 22 countries, providing deep insights into product requirements. Multi-cloud operations across OCI, AWS and Azure increase intelligence. Improve MySQL Database Knowledge gained from operating MySQL HeatWave at scale results in improvements to MySQL Database including High Availability, Security, Analytics, Machine Learning, and more.

65 • Modernize transactional applications using Amazon Aurora, RDS for MySQL, Azure Database for MySQL, Google’s Cloud SQL for MySQL with supporting analytical workloads from Amazon Redshift, Snowflake, Azure Synapse, and Google BigQuery • Modernize mixed workload applications using Amazon Aurora, RDS for MySQL, Azure Database for MySQL, GCP Cloud SQL for MySQL (initially without separate analytics database) • Modernize mixed workload applications using MySQL either on-premises or in the cloud • Rely on MySQL HeatWave for in-database machine learning to avoid using a separate machine learning service with data coming from MySQL, MySQL-based services, or analytics databases Modernizing applications with MySQL HeatWave Key use cases

66 MySQL HeatWave customer momentum Data warehouse, machine learning and OLTP workloads https://www.oracle.com/customers/?product=mpd-cld-infra:db-services:mysql-heatwave

67 “Oracle announced MySQL HeatWave with Autopilot last August, which may very well have been the single greatest innovation in open source cloud databases in the last 20 years to that point. Now Oracle has gone beyond its original unifying of OLTP and OLAP in HeatWave, with MySQL HeatWave ML. Oracle is bringing all of the machine learning processing and models inside the database, so that customers not only avoid managing ML databases apart from the core database, but also eliminate the hassles of ETL, gaining speed, accuracy, and cost-effectiveness in the bargain.” “This latest announcement from Oracle is the third major release of MySQL HeatWave in just over 12 months. Oracle has delivered more cloud database innovations during that timeframe than most cloud database vendors have delivered in the last decade. Not only does the in-database HeatWave ML make Redshift ML look like yesterday’s tech in terms of engineering, performance and cost, but the latest MySQL HeatWave TPC-DS benchmarks demonstrate that Amazon Redshift, Snowflake, Azure Synapse and Google BigQuery are all slower and more expensive. It’s rather clear who’s innovating in cloud databases and who’s being complacent.” Feedback from analysts

Migrating from MySQL 5.7 to MySQL 8 - Migrate your MySQL applications effectively

Migrating from MySQL 5.7 to MySQL 8 - Migrate your MySQL applications effectively

More Decks by Olivier DASINI

Other Decks in Technology

Featured

Transcript