Migrating from AWS RDS/Aurora to MySQL HeatWave - Motivation, Process & Best Practices

MySQL HeatWave is a fully managed database service, powered by the HeatWave in-memory query accelerator.
It’s the only cloud service that combines transactions, real-time analytics across data warehouses and data lakes, and machine learning in one MySQL database—without the complexity, latency, risks, and cost of ETL duplication.
Migrating from older MySQL versions, which are now out of support, such as 5.7 or older will also be discussed .

Transcript

1. Migrating from AWS RDS/Aurora to MySQL HeatWave January 2024 Motivation, Process & Best Practices Olivier Dasini Cloud Solutions Architect @ Oracle MySQL olivier.dasini@oracle.com Blogs : www.dasini.net/blog/en : www.dasini.net/blog/fr Linkedin : www.linkedin.com/in/olivier-dasini Slides : https://speakerdeck.com/freshdaz
2. 2 Me, Myself & I 2 Olivier DASINI Copyright © 2024, Oracle and/or its affiliates. All rights reserved. 2  MySQL Geek  Addicted to MySQL for 15+ years  Playing with databases for 20+ years  MySQL Writer, Blogger and Speaker  Also former : DBA, Consultant, Architect, Trainer, ...  Cloud Solutions Architect at Oracle MySQL  Stay up to date!  Blog: www.dasini.net/blog/en  Linkedin: www.linkedin.com/in/olivier-dasini/  Slides: https://speakerdeck.com/freshdaz
3. Copyright © 2024, Oracle and/or its affiliates. All rights reserved. Agenda 1. OCI Overview 2. MySQL Overview 3. MySQL HeatWave features 4. Migrating from AWS 5. Summary 3
4. Oracle Cloud Infrastructure (OCI) The next-generation cloud designed to run any application, faster and more securely, for less 4 Copyright © 2024, Oracle and/or its affiliates
5. 48 regions in 24 countries including Paris & Marseille; 12 Azure Interconnect Regions Oracle Cloud Infrastructure Global Locations MySQL HeatWave Databases Service(s) is/are part of all of them MySQL HeatWave Databases Service(s) is/are part of all of them And also Cloud @Customer & EU Soveriegn Cloud 100% renewable energy by 2025 5 Copyright © 2024, Oracle and/or its affiliates January 2024 https://www.oracle.com/cloud/public-cloud-regions
6. BUSINESS ANALYTICS Analytics Cloud, Fusion Analytics Analytics SERVERLESS Events, Functions, API Gateway BUSINESS & INDUSTRY SaaS ERP, HCM, SCM, Sales, Marketing, Service, Vertical Industry APP INTEGRATION Integration Cloud, Workflow, Notifications, Email Delivery Applications Open Source DBs & Others MySQL MySQL, NoSQL, Postgres, Search Indexing, Distributed Cache ORACLE DBs ATP, ADW, DBCS VM/BM, JSON, Dedicated, Exadata, Exadata C@C Databases 40 + COMMERCIAL REGIONS / GOV REGIONS / CLOUD@CUSTOMER MESSAGING Streaming, Queueing, Service Connector BIG DATA Big Data, Data Flow, Data Integration, Data Catalog, Golden Gate AI SERVICES Data Science, Text Analytics, Vision Anomaly Detection Data & AI OS, VMWARE Autonomous Linux, OS Mgmt Service, Marketplace COMPUTE Bare metal, VM, CPUs, GPUs, HPC CONTAINERS Containers, Kubernetes, Service Mesh, Registry NETWORKING VCN, LB, Service Gateway, FC, VPN, Cluster Networking STORAGE NVMe, Block, File, Object, Archive, Data Transfer Core Infrastructure OBSERVABILITY Monitoring, Logging, Logging Analytics, Notifications, Events, Operations Insights, APM, Management Cloud CLOUD OPS IAM, Compartments, Tagging, Console, Cost Advisor SECURITY Cloud Guard, Security Zones, Vault, KMS, Data Safe, DDoS, WAF Governance & Administration INFRASTRUCTURE as CODE Resource Manager, Terraform, Ansible LOW CODE APEX, Digital Assistant APPDEV Visual Builder Studio, GraalVM, Helidon, SQL Developer, Shell, APIs/CLI/SDKs/Docs Developer services Complete cloud capabilities Copyright © 2024, Oracle and/or its affiliates.
7. Compare OCI with AWS, Azure, and GCP • Find the service you’re looking for with OCI Copyright © 2024, Oracle and/or its affiliates https://www.oracle.com/cloud/service-comparison/ https://www.oracle.com/cloud/service-comparison/
8. Oracle is far less expensive than other hyperscalers when it comes to public connectivity Source: https://cloud.google.com/network-connectivity/pricing#ncc-pricing, https://azure.microsoft.com/en-gb/pricing/details/bandwidth/, https://aws.amazon.com/ec2/pricing/on-demand/#Data_Transfer, https://www.oracle.com/uk/cloud/networking/pricing/#networking Volume tiers Oracle EU-Frankfurt Azure West Europe Google Frankfurt AWS EU (Frankfurt) >= 100 GB 1st GB >= 10 GB >= 1 TB >= 10 TB >= 150 TB >= 20 TB >= 50 TB >= 100 TB >= 350 TB >= 500 TB 0.00 0.01 0.02 0.03 0.04 0.05 0.06 0.07 0.08 0.09 Price (USD/GB) -81% -79% -83% Oracle from: 10 TB Price: 0.0085 USD Google from: 1 GB Price: 0.12 USD AWS from: 1 GB Price: 0.09 USD Azure from: 100 GB Price: 0.08 USD Google + Azure from: 10 TB Price: 0.065 USD AWS from: 150 TB Price: 0.05 USD Google from: 150 TB Price: 0.045 USD AWS from: 10 TB Price: 0.085 USD Azure from: 150 TB Price: 0.04 USD AWS from: 40 TB Price: 0.07 USD Based on published pricing as of April 9, 2023 Oracle truly supports your multi-cloud strategy and does NOT make it expensive to connect with other Clouds (like competitors) If you transfer 1 petabyte of data out every month, you’ll save over $46k a month on network charges High-level benefits Public connectivity pricing per volume tier (egress) Copyright © 2024, Oracle and/or its affiliates 9
9. REGIONAL INDUSTRY GOVERNMENT DoD DISA SRG IL5 JAB P-ATO CJIS VPAT-Section 508 EU Model Clauses Canada Protected B G-Cloud 12 HIPAA PCI DSS – Level 1 TISAX FISC IG Toolkit FINMA HDS APRA BACEN FFIEC GDPR [EU] CITC [Saudi Arabia] ENS [Spain] Cyber Essentials Plus [UK] Cloud Security Principles [UK] ISMS [Korea] PIPEDA [Canada] My Number [Japan] BSI C5 [Germany] C5 GxP Global, regional, and industry compliance Copyright © 2024, Oracle and/or its affiliates. GLOBAL SOC 1 : SOC 2 : SOC 3 9001 : 27001 : 27017 : 27018 : 20000-1 Level 2
10. MySQL The world's most popular open source database 11 Copyright © 2024, Oracle and/or its affiliates
11. Copyright © 2024, Oracle and/or its affiliates. All rights reserved. 12 MySQL is the #1 Open Source Database https://db-engines.com/en/ranking
12. Copyright © 2024, Oracle and/or its affiliates. All rights reserved. 13 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 2025 Feb 2026 Indefinite Tempos fugit! http://www.oracle.com/us/support/library/lifetime-support-technology-069183.pdf Why Upgrade ? - Support Life Cycle
13. Copyright © 2024, Oracle and/or its affiliates. All rights reserved. 14 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
14. Copyright © 2024, Oracle and/or its affiliates. All rights reserved. 15 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
15. Copyright © 2024, Oracle and/or its affiliates. All rights reserved. 16 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 (HeatWave) https://www.mysql.com/products
16. MySQL HeatWave One Database for OLTP, OLAP, ML & Lakehouse 17 Copyright © 2024, Oracle and/or its affiliates
17. Copyright © 2024, Oracle and/or its affiliates. All rights reserved. 18 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
18. Copyright © 2024, Oracle and/or its affiliates. All rights reserved. 19 … 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) MySQL HeatWave Analytics (Warehouse) OLTP + Analytics (OLAP) + Machine Learning InnoDB Lakehouse InnoDB RAPID InnoDB RAPID
19. Copyright © 2024, Oracle and/or its affiliates. All rights reserved. 20 OLTP
20. Copyright © 2024, Oracle and/or its affiliates MySQL HeatWave for OLTP – Highlights 1/2 Copyright © 2024, Oracle and/or its affiliates. All rights reserved. 21 A fully managed cloud service • Easy provisioning – Best default configuration for Performance, Security & Compliance – Fast Data Import • Managed manual & automatic backups with Point In Time Recovery • Always up-to-date • Automation via Terraform, CLI, SDK, API • Flexibility to grow as you go – Dynamic configuration changes – Online scale storage size – Online enable and disable High Availability – Online add and remove Read Replicas & Replication Channels • Metrics & Alarms – Enable you to measure useful quantitative data of the MySQL DB system
21. Copyright © 2024, Oracle and/or its affiliates MySQL HeatWave for OLTP – Highlights 2/2 Copyright © 2024, Oracle and/or its affiliates. All rights reserved. 22 A fully managed cloud service • Data Security – MySQL Database Service utilizes encryption to keep your data private • Encryption at rest / Encryption in transit (TLS) – Enforces use of private networks and security lists – Data masking functions – Identity federation – Password policy validation • Channel (Replication) – Inbound / Outbound Replication: asynchronous replication from or to MDS • Managed Read Replicas – Increase capacity for read-intensive workloads • Audit Service – MySQL Database integrates with the Oracle Cloud Infrastructure Audit Service • Standalone – Single-instance MySQL DB System • High Availablility – Guarantees if one instance fails, another takes over, with zero data loss and minimal downtime • HeatWave – A distributed, scalable, shared-nothing, in-memory, hybrid columnar, query processing engine designed for extreme performance
22. Copyright © 2024, Oracle and/or its affiliates. All rights reserved. 23 Analytics
23. Copyright © 2024, Oracle and/or its affiliates. All rights reserved. 24 MySQL HeatWave Optimized for Data Warehouse , Machine Learning, and OLTP
24. Copyright © 2024, Oracle and/or its affiliates. All rights reserved. 25 *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 400Gb, 64 cores MySQL HeatWave dramatically speeds up analytic queries
25. Copyright © 2024, Oracle and/or its affiliates. All rights reserved. 26 Already lowest cost in industry for data warehouse TPC-H 10TB price performance comparison 13x better than Redshift 28x better than Snowflake 28x better than BigQuery 62x better than Databricks 3 year reserved, paid upfront Standard Edition 1 year reserved 1 year reserved 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. https://www.oracle.com/mysql/heatwave/performance/#heatwave-on-oci
26. Copyright © 2024, Oracle and/or its affiliates. All rights reserved. 27 Lakehouse
27. Copyright © 2024, Oracle and/or its affiliates. All rights reserved. 28 MySQL HeatWave Lakehouse Process data in object store and transactional database  Process and query 100’s of TB of data in the object store — in a variety of file formats  Query data across MySQL, the object store, or both—using standard MySQL commands  Querying the database is as fast as querying the object store Scales from 16 GB to 512 TB
28. Copyright © 2024, Oracle and/or its affiliates. All rights reserved. 29 MySQL HeatWave can process data from multiple data sources e.g. Oracle Golden Gate, ... AWS Aurora AWS Redshift Data can be in a file or other databases → No requirement to have data in MySQL https://www.mysql.com/products/mysqlheatwave/lakehouse
29. Copyright © 2024, Oracle and/or its affiliates. All rights reserved. 30 Load & Query performance comparison – best in the industry 500 TB TPC-H HeatWave Lakehouse Snowflake Redshift Databricks Google BigQuery Annual Cost $1,742,036 $2,300,160 $1,544,268 $1,822,817 $1,446,900 Pricing Term PAYG Standard Edition 1 year upfront 1 year reserved 1 year reserved Load Time (hrs) 4.43 9.04 (2x slower) 40.86 (9.2x slower) 25.42 (5.7x slower) 38.2 (8.6x slower) Query Time (sec) 2,150 39,040 (18x slower) 32,715 (15x slower) 37,729 (17x slower) 76,180 (35x slower) MySQL HeatWave is faster to load & query data and still less expensive https://www.oracle.com/mysql/heatwave/performance/#heatwave-lakehouse
30. Copyright © 2024, Oracle and/or its affiliates. All rights reserved. 31 Provides flexibility to develop applications on object store without any performance, cost impact Same price-performance when data inside MySQL or in object store HeatWave HeatWave Lakehouse Snowflake Redshift Google Big Query Databricks 0 10 20 30 40 50 60 70 80 90 100 1.5 1.5 41.9 20.2 41.4 92.5 10TB TPC-H Price-Performance Price-Performance (cents) • 10 HeatWave Nodes, X-Large cluster for Snowflake; 10 nodes of ra3.4xlarge for Redshift; 800 slots for Google BigQuery; Large cluster for Databricks • Standard edition price for Snowflake; 3 yr upfront price for Redshift; 1 year reserved price for Google BigQuery and Databricks https://www.oracle.com/mysql/heatwave/performance/#heatwave-lakehouse
31. Copyright © 2024, Oracle and/or its affiliates. All rights reserved. 32 Machine Learning
32. Copyright © 2024, Oracle and/or its affiliates. All rights reserved. 33 In-database machine learning with MySQL HeatWave Accelerate ML initiatives, increase security, and reduce costs
33. Copyright © 2024, Oracle and/or its affiliates. All rights reserved. 34 HeatWave AutoML automates the ML lifecycle & all models can be explained Dataset Data preprocessing Algorithm selection Adaptive sampling Feature selection Hyper-parameter tuning Tuned model Model explainer Prediction explainer Regulatory compliance Fairness Repeatability Causality Trust HeatWave AutoML leverages Oracle AutoML technology to automate the process of training a ML model https://dev.mysql.com/doc/heatwave/en/heatwave-machine-learning.html
34. Copyright © 2024, Oracle and/or its affiliates. All rights reserved. 35 Classification Classify warranty claims Identify similar users Recommend movies Recommender System Loan default prediction Predict flight delay Rain fall prediction Regression Predict Advt spend ROI Demand forecasting Anomaly Detection Detect anomalous credit card spend Identify game hacker Fully automated in-database machine learning • In-database • Secure • Fully automated training • 25x faster than Redshift ML • Explainable • No additional cost Time-series forecasting Training, inference, explanation with HeatWave AutoML
35. Copyright © 2024, Oracle and/or its affiliates. All rights reserved. 36 https://blogs.oracle.com/mysql/post/introducing-vector-store-and-generative-ai-in-mysql-heatwave  Build ML models on data in object store or the database  Use the same APIs as used for data in the database  No additional cost – reuse the same HeatWave Cluster Training, inference and explanations on data in object store HeatWave AutoML also supports Lakehouse files
36.  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/ Copyright © 2024, Oracle and/or its affiliates. All rights reserved. 37
37. Copyright © 2024, Oracle and/or its affiliates Comparison with other cloud database ML solutions 38
38. Copyright © 2024, Oracle and/or its affiliates. All rights reserved. 39 MySQL Autopilot
39. Copyright © 2024, Oracle and/or its affiliates. All rights reserved. 40 Auto Scheduling Auto Change Propagation Auto Query Time Estimation Auto Query Plan Improvement NEW: Adaptive Query Execution Auto Thread Pooling Auto Error Recovery NEW: Autopilot indexing Auto Parallel Load Auto Data Placement Auto Encoding NEW: Auto Unload NEW: Auto Compression Adaptive Data Flow Auto Provisioning Auto Shape Prediction Auto Schema Inference Adaptive Data Sampling Workload-aware ML-powered automation Increases productivity and helps eliminate human errors
40. Generative AI and vector store Interact with MySQL HeatWave in natural language 41 Copyright © 2024, Oracle and/or its affiliates
41. Copyright © 2024, Oracle and/or its affiliates. All rights reserved. 42 Generative AI with MySQL HeatWave vector store  Users can query and retrieve information in natural language  Efficient searching of documents in HeatWave Lakehouse Users can interact with MySQL HeatWave in natural language https://blogs.oracle.com/mysql/post/introducing-vector-store-and-generative-ai-in-mysql-heatwave Private preview
42. Copyright © 2024, Oracle and/or its affiliates. All rights reserved. 43 Vector store provides context to LLM for more relevant results Users can interact with MySQL HeatWave in natural language https://blogs.oracle.com/mysql/post/introducing-vector-store-and-generative-ai-in-mysql-heatwave Private preview
43. Copyright © 2024, Oracle and/or its affiliates. All rights reserved. 44 Vector store provides context to LLM for more relevant results Users can interact with MySQL HeatWave in natural language https://blogs.oracle.com/mysql/post/introducing-vector-store-and-generative-ai-in-mysql-heatwave Private preview
44. Copyright © 2024, Oracle and/or its affiliates. All rights reserved. 45 Vector store provides context to LLM for more relevant results Users can interact with MySQL HeatWave in natural language https://blogs.oracle.com/mysql/post/introducing-vector-store-and-generative-ai-in-mysql-heatwave Private preview
45. Copyright © 2024, Oracle and/or its affiliates. All rights reserved. 46 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
46. Migrating from AWS 47 Copyright © 2024, Oracle and/or its affiliates
47. Copyright © 2024, Oracle and/or its affiliates. All rights reserved. 48 Why migrate from AWS?  Service costs  Data transfer costs  Performance not as envisaged  A requirement for real-time analytics, Machine Learning  Upgrade of MySQL version  Security & support – The Oracle Cloud (OCI) is a Gen 2 cloud – MySQL security fixes rolled out immediately in the Oracle Cloud • AWS waits for MySQL Community Edition releases – MySQL in the Oracle Cloud is the only MySQL database service that is fully developed, maintained and supported by MySQL engineers
48. Copyright © 2024, Oracle and/or its affiliates. All rights reserved. 49 Migration Paths RDS/Aurora using MySQL 8.0.x – MySQL HeatWave 8.0.y  Check suitability for upgrade and migrate using automated MySQL Shell tooling RDS/Aurora using MySQL 5.7.x – MySQL HeatWave 8.0.y  Check suitability for upgrade and migrate using automated MySQL Shell tooling RDS/Aurora using MySQL 5.6.x – MySQL HeatWave 8.0.y  MySQL does not support upgrades between more than one major version (5.7 major version before 8.0)  Upgrade to at least 5.7 using AWS processes then use MySQL Shell tooling to upgrade/migrate to HeatWave MySQL innovation releases: 8.1, 8.2, 8.3...  If you are innovating always go to the latest release – As of 16 Jan 2023, 8.3 is the current release – 8.4 will become the long term supported (LTS) release for MySQL 8
49. Copyright © 2024, Oracle and/or its affiliates. All rights reserved. 50 MySQL Shell Dump MDS Data Import or MySQL Shell Dump Load OCI Object Storage OCI Network AWS Network MySQL RDS / Aurora MySQL Database Service MySQL Migration from AWS to OCI Using MySQL Shell Dump & Load
50. Copyright © 2024, Oracle and/or its affiliates. All rights reserved. 51 MySQL Shell Overview 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... Interface for Development and Administration of MySQL
51. Copyright © 2024, Oracle and/or its affiliates. All rights reserved. 52 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  It is in active development and more checks will be added  Privileges needed: – Since 8.0.21: RELOAD, PROCESS & SELECT https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-utilities-upgrade.html 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("root@172.25.0.10", {outputFormat:'JSON'}) Upgrade checker utility that enables you to verify whether MySQL server instances are ready for upgrade
52. Copyright © 2024, Oracle and/or its affiliates. All rights reserved. 53
53. Copyright © 2024, Oracle and/or its affiliates. All rights reserved. 54 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
54. Copyright © 2024, Oracle and/or its affiliates. All rights reserved. 55 MySQL Shell dump utilities - dumpInstance & dumpSchemas  Logical Dump Utilities from MySQL Shell (start with MySQL Shell 8.0.21) – 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 Database Service DB System 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
55. Copyright © 2024, Oracle and/or its affiliates. All rights reserved. 56 MySQL Shell load utility - loadDump  Logical Load Utility from MySQL Shell (start with MySQL Shell 8.0.21) – 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 Database Service DB System – 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
56. Copyright © 2024, Oracle and/or its affiliates. All rights reserved. 57 Migration Overview Planning and preparation will pay dividends Scoping and planning Determine: • Which components will be migrated • Database instance or schema? • Database users to be included? • Application(s) as well as database? • Versioning • Source (AWS RDS/Aurora) • Target (MySQL HeatWave) • Whether connectors need upgrading • The impact to intersecting projects • Permitted downtime / unavailability • Cutover strategy • Big-bang or staged Baseline your database/application • Regression test suite Infrastructure build out AWS Side: • Host required for automation tooling (MySQL Shell) • Host must have visibility of the database instance and the internet • Reuse or instantiate an EC2 instance OCI Side: • Instantiate Virtual Cloud Network • (Instantiate) a MySQL HeatWave instance • Instantiate a bastion and install automation tooling (MySQL Shell) • In this case the bastion is an OCI Compute Instance – analogous to an AWS EC2 instance • Setup a bucket in Object Storage • Get API keys for secure transfer of data Migrate If the target version of the database is later than the source check the source is ready to be upgraded • Use MySQL Shell’s upgrade checker utility • Make fixes as necessary (and test) Dump the database to OCI Object Storage • Use MySQL Shell’s dump instance or dump schema utilities – security & speed Load the dumped database from Object Storage into MySQL HeatWave • Use MySQL Shell’s load dump utility Test using your regression test suit Perform app related tasks then cutover
57. Copyright © 2024, Oracle and/or its affiliates. All rights reserved. 58 Technical resources ✔ Webinaires MySQL en français : https://go.oracle.com/MySQLFrenchWebinars ✔ Migration de MySQL 5.7 vers MySQL 8 ✔ Migrer d’Amazon RDS à MySQL Database Service ✔ Migrer vos bases MySQL vers MySQL HeatWave ✔ MySQL HeatWave Migration Program : https://www.oracle.com/fr/mysql/migration/ ✔ Free step-by-step migration guides: Amazon RDS for MySQL ✔ Free step-by-step migration guides: Amazon RDS for MySQL (Live Migration) ✔ Free step-by-step migration guides: Amazon Aurora for MySQL ✔ Free step-by-step migration guides: Amazon Aurora for MySQL (Live Migration) ✔ Free step-by-step migration guides: MySQL on-premises ✔ Free MySQL HeatWave training courses Confidently migrate to MySQL HeatWave using a proven end-to-end approach https://speakerdeck.com/freshdaz
58. Copyright © 2024, Oracle and/or its affiliates. All rights reserved. 59 Real life feedback • How we migrate self hosted MariaDB on AWS to MySQL HeatWave on OCI with minimal outage – https://www.linkedin.com/pulse/how-we-migrate-self-hosted-mysql-oci-db-system-outage-adylkhanov/
59. Summary 60 Copyright © 2024, Oracle and/or its affiliates
60. Copyright © 2024, Oracle and/or its affiliates. All rights reserved. 61 Some Reminders 1. MySQL HeatWave in all its forms is the only 100% MySQL Database Service – Developed, maintained and supported by MySQL Engineers who are the source for MySQL 2. The price performance of MySQL HeatWave for Analytics is outstanding – Far less expensive than RedShift – Because it can perform OLTP and OLAP concurrently it easily overcomes Aurora performance issues 3. MySQL HeatWave is a single database for OLTP, OLAP, Warehousing and Machine Learning – Real-time analytics, runs SQL unchanged 4. OCI does not charge for the first 10TB of data egress per month – AWS charges for every gigabyte 5. MySQL Shell simplifies upgrading and migrating
61. Get $300 in credits and try free for 30 days 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 62 Copyright © 2024, Oracle and/or its affiliates
62. Follow us on Social Media “Data is the Oxygen of Business” 63 Copyright © 2024, Oracle and/or its affiliates
63. Merci! Q&R Olivier Dasini Cloud Solutions Architect @ Oracle MySQL olivier.dasini@oracle.com Blogs : www.dasini.net/blog/en : www.dasini.net/blog/fr Linkedin: www.linkedin.com/in/olivier-dasini Slides : https://speakerdeck.com/freshdaz Twitter : @freshdaz
65. Copyright © 2024, Oracle and/or its affiliates. All rights reserved. 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
66. Copyright © 2024, Oracle and/or its affiliates. All rights reserved. 67 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
67. Copyright © 2024, Oracle and/or its affiliates. All rights reserved. 68 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.
68. Copyright © 2024, Oracle and/or its affiliates. All rights reserved. 69 “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
69. MySQL Shell dump Commonly used options When dumping from AWS and loading into MySQL HeatWave on OCI Options that you will use in any migration to HeatWave on OCI osNameSpace • A string which uniquely identifies your area of object storage. Object storage is divided into buckets osBucketName • Where to dump to and load from. Note the outputUrl and url parameters give the folder name ociConfigFile • Where to find the authentication and encryption details to secure comms between endpoints ocimds • For dump commands only, set this to true, to ensure the dump will be compatible with MySQL HeatWave Options you are likely to use compatibility • For dump commands. Works with ocimds by modifying the dump such that it can be loaded into MySQL HeatWave targetVersion • Set to your target database’s version otherwise the dump will assume the version of MySQL Shell. ignoreVersion • Is used by loadDump() when the version you are migrating from differs to the targetVersion consistent • Used by dump commands to overcome AWS imposed locking constraints Usability options dryRun • Set to true for testing, remove or set to false when performing the actual run threads • Default is 4 – if you have lots of tables increase this value deferTableIndexes • Set to true (default is false). Building indexes at the end of a load is far more efficient than updating an index after every insert For a full list of options and how they relate to individual dump and load commands see the documentation 70 Copyright © 2024, Oracle and/or its affiliates