Migrating from On-Premises MySQL to MySQL HeatWave

Migrating from On-Premises MySQL to MySQL HeatWave 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 Twitter : @freshdaz July 2023

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/  Twitter: @freshdaz Olivier DASINI

Upcoming Webinar Un plongeon au cœur de MySQL HeatWave Lakehouse
Copyright © 2023, Oracle and/or its affiliates. All rights reserved. 3 10 August 2023 10:00 a.m. CEST Save the date! Registration available soon on this page:

Agenda 1. MySQL – The state of the Dolphin 2. Migration to MySQL HeatWave - Discover & Assess 3. Migration to MySQL HeatWave - Plan Migration 4. Migration to MySQL HeatWave - Provision & Configure 5. Migration to MySQL HeatWave - Migrate your data 6. Migration to MySQL HeatWave - Check & Test 7. One database for OLTP / Analytics / Machine Learning / Lakehouse 4

5 MySQL – The state of the Dolphin Introduction

6 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 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 (HeatWave) https://www.mysql.com/products

7 Santiago San Jose Toronto Phoenix Chicago Montreal Ashburn Sao Paulo London Milan Saudi Arabia 2 Jeddah Amsterdam Stockholm Zurich Johannesburg Israel 2 Abu Dhabi Dubai Mumbai Hyderabad Singapore Seoul Chuncheon Osaka Tokyo Melbourne Sydney Vinhedo Frankfurt Newport April 2023 41 regions including Paris & Marseille; 10 more planned 12 Azure Interconnect Regions Microsoft Interconnect Azure Marseille Jerusalem Paris Madrid Chile 2 Colombia Queretaro Germany Sovereign Planned Spain Mexico 2 Serbia Commercial Commercial Planned Government Oracle Cloud Infrastructure Global Locations MySQL HeatWave Databases Service(s) is/are part of all of them And also Cloud @Customer & EU Soveriegn Cloud 100% renewable energy by 2025

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

11 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

12 • Full managed service – Easy provisioning • MySQL Enterprise Binaries – same binaries for OpenSource and Enterprise and Oracle Cloud usage • All MySQL Cloud instances are covered by Oracle Premier Support • Starts at 1 OCPU (= 2 Threads) • Shapes up to 64 OCPU/1TB AMD - E2, E3, E4 and Intel X7/X9/X9 Optimized • From 50GB up to 128TB • MySQL Managed Channels (Replication, Replication Channels Filters) available (+ w/o GTIDs) • MySQL Managed Read Replicas • MySQL High Availability - Recover Point Objective (RPO) = ZERO (Zero Data loss) • SLA (99.99% for 3-AD regions, 99.9% for Standalone DB Systems) • Automatic backup and Point in Time Recovery (PITR) available • Encryption at rest / Encryption in transit (TLS) • Automation via Terraform, SDK, API, CLI MySQL HeatWave for OLTP

13 MySQL Database Service: High Availability Fault-tolerant system with automatic failover & zero data loss through MySQL Group Replication • Recover Time Objective (RTO): Minutes • Recovery Point Objective (RPO): Zero • Single Click High Availability • Automatic Failover • Increased Uptime • Zero Data Loss

14 MySQL Database Service: Managed Read Replicas Enable applications to scale database read operations to millions of queries per second • Increase capacity for read-intensive workloads • Add and remove asynchronous Read Replicas for horizontal read elasticity • Easy to deploy and maintain • Built-in Load Balancer for the read-only endpoint • HA Cluster continues reliable & ready for switchover or failover • Users can add & remove read replicas & load balancers to DB Systems with just a few clicks, and the service takes care of deployment, configuration, monitoring, and maintenance • Applications can connect directly to read replicas or to a read-only endpoint that automatically balances the queries across all the replicas • The replication from the primary instance is asynchronous, so there is no performance impact on the writer instance • Each DB System supports up to 18 read replicas • Can be used in combination with the High Availability feature for an SLA of 99.99% and with Channels to deploy disaster recovery or cross-region topologies

15 MySQL Database Service: Channels (Replication) Asynchronous Replication • The source can be an MDS DB System • The target can be in a different region • You can also handle MySQL sources that don't have GTID enabled • Filters – Available templates help configure replication from AWS RDS/Aurora, Azure, Google Cloud, and AliCloud – You can also manually configure your own MySQL replication filters for even more flexibility, like filtering out only specific tables or renaming the schema on the target

16 Migration to MySQL HeatWave

17 • 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) 17 MySQL HeatWave Migration Program https://www.oracle.com/mysql/migration/ Copyright © 2023, Oracle and/or its affiliates Step-by-step migration guides

18 Migrate to MySQL HeatWave with confidence in 5 easy steps 1. Discover & Assess 2. Plan Migration 3. Provision & Configure 4. Migrate your data 5. Check & Test

19 Migration to MySQL HeatWave Discover & Assess

20 Discover & Assess - Why Upgrade ?  Fully managed cloud service – Easy provisioning, Automated backup, PITR, High Availability, Managed read replicas,  MySQL 8 features – Instant Add Column, JSON Document Store, CTE, Window Functions, JSON, GIS, Functional indexes, Hash Joins, Histograms, Check Constraints, ...  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...

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

23 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

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

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

26 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

27 Migrate to MySQL HeatWave  Inventory data – Data from MySQL 5.x, 8 / MariaDB / Percona Server for MySQL? – 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  ... Discover & Assess

28 Migration to MySQL HeatWave Plan Migration

29 Migrate to MySQL HeatWave  Team members assigned to work on the migration  Offline / Near real-time – Export / Import; Replication; Lift & Shift then migrate to MySQL HeatWave ?  Data transfer (size, duration, HW/network sizing, ...)  Failure handling  Failback?  OCI environment (Privileges, Network, Storage, MySQL HeatWave shape, …)  Migration timeline  Establish safe decommissioning criteria for the source environment  Monitoring…  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)  ... Plan Migration

30 Migration to MySQL HeatWave Provision & Configure

31 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  … Provision & Configure

32 Migration to MySQL HeatWave Migrate your data

33 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

34 • Supports dump and load of instances, schemas and tables – Parallel loading of tables or table chunks – Progress state tracking – Resume and reset capability – Can add invisible columns with primary keys to tables – Splits larger tables in smaller chunks (reads 3GB/s+) – Loads chunks in parallel (writes 200MB/s+) • Support for native storage in OCI Object Storage • Recommended tool to migrate data to MySQL Database Services • Commands – util.dumpInstance() dump an entire database instance, including users – util.dumpSchemas() dump a set of schemas – util.dumpTables() dump a set of schemas – util.loadDump() load a dump into a target database MySQL Shell: Load & Dump utilities Create logical dumps & do logical restores, with a focus on ease of use, performance & integration https://dev.mysql.com/blog-archive/mysql-shell-dump-load-part-1-demo/

35 • Access to ObjectStorage & Valid Configuration File (user credentials, Tenancy OCID) • Export MySQL data (from an exiting MySQL environment – even 5.7!) – util.dumpInstance / util.dumpSchemas / util.dumpTables – to Oracle OCI Object Storage – to local files (none OCI) MySQL Shell Dump* for MySQL HeatWave https://docs.oracle.com/en-us/iaas/mysql-database/doc/exporting-mysql-instance.html https://docs.oracle.com/en-us/iaas/Content/API/Concepts/sdkconfig.htm e.g. util.dumpInstance( "<BucketPrefix>", { osBucketName: "<MDSBucket>", threads: <ThreadNumber>, ocimds: true, compatibility: [ "force_innodb", "strip_restricted_grants", "strip_definers", "create_invisible_pks", "skip_invalid_accounts", "strip_tablespaces" ] } )

36 • Import the MySQL data – Import data while creating a new MySQL HeatWave Environment – Import data to an existing MySQL HeatWave Environment e.g. util.loadDump("<URL>", {threads: <ThreadNumber>}) MySQL Shell LoadDump for MySQL HeatWave https://docs.oracle.com/en-us/iaas/mysql-database/doc/importing-using-data-import-feature.html

37 Migrate to MySQL HeatWave  Schema / data modification? Migrate your data

38 Migration to MySQL HeatWave Check & Test

39 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

40 MySQL HeatWave One database for OLTP Analytics / Machine Learning / Lakehouse

41 Continuous Innovation in MySQL HeatWave MySQL HeatWave • MySQL Autopilot • Scale out storage • Mixed workloads • Available in all regions • Encrypted communication • High availability • Replication channels • Scale to 64 nodes • More compute shapes • HeatWave Machine Learning • Real-time elasticity • HeatWave compression • Point-in-time recovery Nov 2020 Aug 2021 Apr 2022 HeatWave

42 MySQL HeatWave – Single database for OLTP, OLAP, ML Valorizing your data has never been so easy! https://www.mysql.com/products/mysqlheatwave

43 MySQL HeatWave In-Memory Query Accelerator

44 *Benchmark queries are derived from the TPC-H benchmark, but results are not comparable to published TPC-H benchmark results since they do not comply with the TPC-H specification 400G, 64 cores MySQL HeatWave dramatically speeds up analytic queries

45 MySQL HeatWave Analytics

46 See documented performance comparisons that show how HeatWave is 6.5X faster than Amazon Redshift at half the cost, 1400X faster than Amazon Aurora at half the cost, and 5400X faster than Amazon RDS for MySQL at two-thirds the cost 30TB TPCH, MySQL HeatWave is faster, cheaper & easier to use than all the competitive database services MySQL HeatWave performance and price comparison www.oracle.com/mysql/heatwave/performance

47 MySQL HeatWave Machine Learning

48 MySQL HeatWave AutoML Accelerate ML initiatives, increase security, and reduce costs Build, train, deploy, & explain machine learning models within MySQL HeatWave, at no additional cost https://dev.mysql.com/doc/heatwave/en/mys-hwaml-machine-learning.html

49 MySQL HeatWave AutoML automates the ML lifecycle Dataset Data preprocessing Algorithm selection Adaptive sampling Feature selection Hyper-parameter tuning Tuned model Model explainer Prediction explainer Regulatory compliance Fairness Repeatability Causality Trust All models can be explained! https://blogs.oracle.com/mysql/post/mysql-heatwave-new-machine-learning-multicloud-capabilities Support of: • Regression • Classification • Recommender systems • Unsupervised anomaly detection • Multi-variate time series forecasting

50  Produces more accurate results  Trains models 25X faster on average  1% of the cost  Scales as more modes are added HeatWave AutoML vs Redshift ML: Benchmarks summary See Benchmark details: https://www.oracle.com/mysql/heatwave/performance/

51 MySQL HeatWave - OLTP + OLAP + ML Example : Loan Approval

52 MySQL HeatWave Lakehouse: Processing on Object Store

53 Process and query 100’s of TB of data in the object store — in a variety of file formats MySQL HeatWave Lakehouse InnoDB Object Store Devices Social Voice Sensors Events https://www.mysql.com/products/mysqlheatwave/lakehouse mysql> CREATE TABLE Sensor (date DATE, degree INT) ENGINE=DATALAKE ENGINE_ATTRIBUTE = ‘{“dialect”:{“format”:”csv”}, “file”: [{“prefix”:”temp_sensor_1.csv”}]}’ SECONDARY_ENGINE=RAPID; mysql> ALTER TABLE Sensor SECONDARY_LOAD; mysql> SELECT count(*) FROM Sensor, SALES WHERE Sensor.degrees >30 and Sensor.date=SALES.date;

54 MySQL HeatWave can process data from multiple data sources Oracle Golden Gate OCI Object Storage AWS Aurora AWS Redshift Data can be in a file or other databases → No requirement to have data in MySQL • Query data across MySQL, the object store, or both—using standard MySQL commands • Up to 400 TB of data—the HeatWave cluster scales to 512 nodes • Querying the database is as fast as querying the object store https://www.mysql.com/products/mysqlheatwave/lakehouse

55 Query performance of MySQL HeatWave Lakehouse Annual provisioned cost for HeatWave $1,589,036; for Redshift $2,261,760; for Snowflake $2,242,560 Pricing for Redshift is based on 1-year reserved instance, paid upfront. Snowflake is based on standard edition *Benchmark queries are derived from the TPC-H benchmarks, but results are not comparable to published TPC-H benchmark results since these do not comply with the TPC-H specifications. 17x faster than Snowflake, 6x faster than Redshift https://github.com/oracle/heatwave-tpch

56 Load performance of MySQL HeatWave Lakehouse Annual provisioned cost for HeatWave $1,589,036; for Redshift $2,261,760; for Snowflake $2,242,560 Pricing for Redshift is based on 1-year reserved instance, paid upfront. Snowflake is based on standard edition *Benchmark queries are derived from the TPC-H benchmarks, but results are not comparable to published TPC-H benchmark results since these do not comply with the TPC-H specifications. 8x faster than Redshift, 2.7x faster than Snowflake https://github.com/oracle/heatwave-tpch

57 • 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

58 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.

59 • 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

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

Upcoming Webinar Un plongeon au cœur de MySQL HeatWave Lakehouse
Copyright © 2023, Oracle and/or its affiliates. All rights reserved. 61 10 August 2023 10:00 a.m. CEST Save the date! Registration available soon on this page:

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

63 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 Twitter : @freshdaz

65 Customize your cloud to meet your sovereignty requirements Customize your cloud to meet your sovereignty requirements Located in the EU, operated by EU residents Offers data security and data sovereignty Access to the same services, value, and innovation as Public Cloud https://www.oracle.com/cloud/sovereign-cloud/ EU Oracle Sovereign Cloud Oracle’s EU sovereign cloud offering is isolated from Oracle Global Public Cloud…

Migrating from On-Premises MySQL to MySQL HeatWave

Migrating from On-Premises MySQL to MySQL HeatWave

More Decks by Olivier DASINI

Other Decks in Technology

Featured

Transcript