Replication features of MySQL HeatWave - A technical ‘Run Through‘ in Oracle Cloud Infrastructure

MySQL Replication is probably the most widely used feature of MySQL and one of the foundations of MySQL's success over the past 25+ years.

Replication is available as a fully managed service in Oracle Cloud MySQL HeatWave in a number of ways:

Inbound & outbound replication channels to replicate MySQL instances across geographies as well as from/to local MySQL instances
Offload analytical and machine learning workloads from any MySQL instance to MySQL HeatWave (for In-Memory Query Acceleration)
High availability based on MySQL group replication (RTO=0!)
(New) MySQL read replication to deploy and maintain up to 18 read replicas for scaling out/in read-intensive workloads

Attend this session to learn how the replication features in MySQL HeatWave enable you to build and scale next-generation services.

Transcript

1. Replication features of MySQL HeatWave A technical ‘Run Through‘ in Oracle Cloud Infrastructure Olivier Dasini MySQL Cloud Principal Solutions Architect EMEA olivier.dasini@oracle.com Blogs : www.dasini.net/blog/en : www.dasini.net/blog/fr Linkedin: www.linkedin.com/in/olivier-dasini Twitter : @freshdaz April 2023
2. Upcoming Hands-On Lab Running WordPress with MySQL HeatWave Copyright © 2023, Oracle and/or its affiliates. All rights reserved. 2 11 May 2023 10:00 a.m. to 12:00 p.m. CEST Register now with the QR code!
3. Copyright © 2023, Oracle and/or its affiliates. All rights reserved. 3 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
4. Copyright © 2023, Oracle and/or its affiliates. All rights reserved. Agenda 1. Introduction: The State of the Dolphin & MySQL Summit 2023 2. Database Replication (basic concepts) 3. MySQL HeatWave High Availability 4. MySQL HeatWave Read Replicas 5. MySQL HeatWave Replication with OCI (e.g., DR) 6. MySQL HeatWave Replication from/to OCI (Migrations, connect HeatWave Analytics) 4
5. Copyright © 2023, Oracle and/or its affiliates. All rights reserved. 5 MySQL – The state of the Dolphin Introduction
6. Copyright © 2023, Oracle and/or its affiliates. All rights reserved. • MySQL is 25 years+ around • Managed Service with latest MySQL • Easy provisioning & SLA • Automation via Terraform, CLI, SDK, API • (MySQL) Oracle Premier Support included for free • One click: • Manual, Automatic Backups • Point-in-time Recovery • MySQL with HeatWave cluster: OLAP and OLTP and ML together in one database MySQL HeatWave Intro 6 MySQL - The world’s most popular open source database MySQL - The world’s most popular open source database
7. Copyright © 2023, Oracle and/or its affiliates. All rights reserved. Copyright © 2023, Oracle and/or its affiliates. All rights reserved. MySQL HeatWave – single database for OLTP, OLAP and ML No ETL, real-time analytics, no change to existing MySQL applications No ETL, real-time analytics, no change to existing MySQL applications OLTP Applications Social ECommerce FinTech SaaS Analytics Tools ML Tools OLTP OLAP ML Copyright © 2023, Oracle and/or its affiliates. All rights reserved. 7
8. Copyright © 2023, Oracle and/or its affiliates. All rights reserved. 8 … … the Oracle MySQL Cloud services 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) (Beta) MySQL Heatwave (Analytics) OLTP + Analytics (OLAP) + Machine Learning InnoDB Lakehouse InnoDB RAPID InnoDB RAPID
9. Copyright © 2023, Oracle and/or its affiliates. All rights reserved. 9 … … the Oracle MySQL Cloud services 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) (Beta) MySQL Heatwave (Analytics) OLTP + Analytics (OLAP) + Machine Learning InnoDB Lakehouse InnoDB RAPID InnoDB RAPID
10. Copyright © 2023, Oracle and/or its affiliates. All rights reserved. 10 … … the Oracle MySQL Cloud services 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) (Beta) MySQL Heatwave (Analytics) OLTP + Analytics (OLAP) + Machine Learning InnoDB Lakehouse InnoDB RAPID InnoDB RAPID
11. Copyright © 2023, Oracle and/or its affiliates. All rights reserved. 11 MySQL – The state of the Dolphin MySQL Summit 2023 Thursday, March 23 Oracle Campus Redwood Shores, California https://www.mysql.com/news-and-events/events/mysql-summit.html
13. Copyright © 2023, Oracle and/or its affiliates. All rights reserved. Areas of MySQL innovation Security & High availability Developer productivity Analytics, machine learning, autopilot Lakehouse: Processing on object store Management & Observability Multi cloud MySQL Summit 2023: MySQL Summit 2023: https://www.mysql.com/news-and-events/events/mysql-summit.html https://www.mysql.com/news-and-events/events/mysql-summit.html 13 https://www.mysql.com/news-and-events/events/mysql-summit.html
14. Copyright © 2023, Oracle and/or its affiliates. All rights reserved. • New MySQL LTS vs. MySQL Innovation Release • MySQL Shell for VS Code (already Beta) • MySQL REST Service, MRS (Progressive Web Apps) • MySQL HeatWave autoML (Machine Learning) – Interactive ML console (AWS) – Unsupervised anomaly detection – Recommender system – Multivariate time series forecasting • Auto shape prediction with explanation for OLTP workloads (AWS) • MySQL Observability in OCI • MySQL HeatWave Lakehouse MySQL Summit 2023 14 What has been announced (short summary…) What has been announced (short summary…) https://www.mysql.com/news-and-events/events/mysql-summit.html
15. Copyright © 2023, Oracle and/or its affiliates. All rights reserved. 15 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 MySQL HeatWave Databases Service(s) is/are part of all of them And also Cloud @Customer 100% renewable energy by 2025
16. Copyright © 2023, Oracle and/or its affiliates. All rights reserved. 16 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/ Coming soon: EU Oracle Sovereign Cloud Oracle’s EU sovereign cloud offering is isolated from Oracle Global Public Cloud…
17. Copyright © 2023, Oracle and/or its affiliates. All rights reserved. 17 Database Replication Enable data from one source to be copied to one or more replicas
18. Copyright © 2023, Oracle and/or its affiliates. All rights reserved. Multiple copies of data with replication process 18
19. Copyright © 2023, Oracle and/or its affiliates. All rights reserved. MySQL Replication Overview 19 SOURCE REPLICA
20. Copyright © 2023, Oracle and/or its affiliates. All rights reserved. Binary Log • Logical replication log recording Source changes (binary log) • Row or statement based format (may be intermixed) • Each transaction is split into groups of events • Control events: Rotate, Format Description, GTID, and mode... 20
21. Copyright © 2023, Oracle and/or its affiliates. All rights reserved. 21 MySQL HeatWave –High Availability MySQL "always-on"
22. Copyright © 2023, Oracle and/or its affiliates. All rights reserved. Thoughts from the Business Thoughts from the Business High Availibility – Why? • MySQL should be available for a continues period • Hardware, Technology and networks occasionally can fail • It's essential that the MySQL service always remains functional • But what happens when the service is down, costs? • Perfect for pretty much every application which needs HA e.g. CMS, LAMP, IOT etc. • MySQL On-Prem solution: MySQL InnoDB Cluster 22 https://docs.oracle.com/en-us/iaas/mysql-database/doc/business-continuity.html
23. Copyright © 2023, Oracle and/or its affiliates. All rights reserved. Availability Domain 1 Block Storage Service server Automatic duplication of Object Storage (MySQL Backups) Oracle Cloud Region server Object Storage Service (MySQL Backups) Availability Domain 2 Block Storage Service server server Object Storage Service (MySQL Backups) Availability Domain 3 Block Storage Service server server Object Storage Service (MySQL Backups) • Oracle Enterprise Hardware (redundant components) • MySQL data persisted to multiple storage devices within in Availability Domain. • Automatic backups are duplicated across Availability Domains. • In the event of many failure conditions, a new MySQL service is spun up in the same AD, attached to Block Storage Service and service resumes. • In the event of failure of entire AD, service can be restored from backup in a surviving AD. • Recovery time is dependent on failure scenario and conditions. Service Availability & Data Durability MySQL HeatWave Databases MySQL HeatWave Databases Service Service – – An An introduction introduction 23 https://docs.oracle.com/en-us/iaas/mysql-database/doc/recovery-time-objective-rto-and-recovery-point-objective-rpo.html
24. Copyright © 2023, Oracle and/or its affiliates. All rights reserved. Full managed MySQL HA Full managed MySQL HA MySQL HeatWave Database Service HA • Based on MySQL Group Replication/Paxos • Feature can be Enabled & Disabled (short outage only) • Single click High Availability – Standalone MDS: SLA 99.9% – Single AD Region: SLA 99.95% – Multi AD Regions: SLA 99.99% • Automatic Failover • Planned Switchover • Increase Uptime • Reduce Downtime during a failure event (RTO: Minutes) • Zero Data Loss during a failure event (RPO: Zero) 24 https://docs.oracle.com/en-us/iaas/mysql-database/doc/high-availability.html
25. Copyright © 2023, Oracle and/or its affiliates. All rights reserved. Automatic Application Failover Automatic Application Failover High Availability 25
26. Copyright © 2023, Oracle and/or its affiliates. All rights reserved. 26 MySQL HeatWave –Replication option I Read Replicas
27. Copyright © 2023, Oracle and/or its affiliates. All rights reserved. Thoughts from the Business Thoughts from the Business Read Replicas– Why? • It's essential that the MySQL service is always running optimally without an impact coming from the SQL load/Scaling • What is the impact if not? • Replication enables data from one MySQL server (source) to be copied (near to real-time) to one or more MySQL servers (replicas) • ‘Scale-Out’ scenarios – Write to the source, read from the replicas • Offload read queries • Perfect for applications which do have a read/write connections e.g. WordPress, Drupal, Shopify. ... etc. • MySQL On-Prem solution: MySQL InnoDB ReplicaSet / MySQL Asynchronous Replication 27
28. Copyright © 2023, Oracle and/or its affiliates. All rights reserved. Full managed MySQL Replication Full managed MySQL Replication Managed Read Replicas 28 High performance by scaling your reads. • A single click creates a Read Replica • Provision • Launch • Setup Replication • Monitor and Manage • Read Replicas are associated with a DB System (SO or HA) • RO endpoints in the DB System • Up to 18 max per DB System (19 SO or 21 HA server) • Requires a shape of 4 OCPUs or larger • CLI, SDK and Terraform support
29. Copyright © 2023, Oracle and/or its affiliates. All rights reserved. Use Your Replicas Efficiently Use Your Replicas Efficiently Load Balancer 29 When using Read Replicas a Load Balancer Endpoint is automatically provisioned in your DB System • Managed by the service • Materializes as a Read-Only endpoint • Round robins traffic across Read Replicas • Manages Read Replica backends automatically
30. Copyright © 2023, Oracle and/or its affiliates. All rights reserved. 30 MySQL HeatWave –Replication option II Replication to other OCI MySQL Instances
31. Copyright © 2023, Oracle and/or its affiliates. All rights reserved. Thoughts from the Business Thoughts from the Business Replication to other OCI MySQL Instances • Replication enables data from one MySQL server (source) to be copied (near to real-time) to one or more MySQL servers (replicas) • Standby server outside of my OCI region (within OCI) / Disaster Recovery (Multi AD regions?) • It's essential that you get access to your data, what if not for 1h / 1day / 1month / ever? • For all application which creates/serves business critical data which should never be lost e.g. Financial data, HR data etc • MySQL On-Prem solution: MySQL InnoDB ReplicaSet / MySQL Asynchronous Replication 31
32. Copyright © 2023, Oracle and/or its affiliates. All rights reserved. MySQL HeatWave Channels Automated Replication Channels (within OCI) Automated Replication Channels (within OCI) 32 • Channels are automated setups of MySQL Replication • A wizard is needed because you don’t have required privileges • Async - row-based - single source replication only Prepare OCI network • All MySQL servers reside within OCI • Local VCN peering (within region) – LPGs • Remote VCN peering (across regions) – RPCs • Peering VCNs (across regions) – DRGs • Servers replicate via MySQL Port (3306) – adopt Security Lists Prepare MySQL • Setup MySQL Source, setup MySQL Replica • Export your data from MySQL Source • Import the data to the MySQL Replica(s) • Create new user for Replication only MySQL Source https://blogs.oracle.com/mysql/post/how-to-setup-high-availability-and-replication-inside-oci-for-mysql-heatwave
33. Copyright © 2023, Oracle and/or its affiliates. All rights reserved. MySQL HeatWave Channels Automated Replication Channels (within OCI) Automated Replication Channels (within OCI) 33 Setup Channel • Channel must be in compartment of the Target DB • Target DB creates connection to Source DB (!) • (1) Source connection • Hostname, Port, User, Password • Optional: SSL Mode (e.g., X509 PEM file) • Optional: Source GTID settings (UUID settings, Binlog name, Offset) • (2) Target DB system • Select a DB from your compartment (no user credentials needed) https://blogs.oracle.com/mysql/post/how-to-setup-high-availability-and-replication-inside-oci-for-mysql-heatwave
34. Copyright © 2023, Oracle and/or its affiliates. All rights reserved. MySQL HeatWave Channels Automated Replication Channels (within OCI) Automated Replication Channels (within OCI) 34 • (3) Configure Channel filter (or use templates) • Like DO_DB/Table or IGNORE_DB/Table • Templates for AWS, Azure, Google, Alibaba • (4) Monitor & Manage your MySQL Channel • Disable/Reset/Resume
35. Copyright © 2023, Oracle and/or its affiliates. All rights reserved. 35 MySQL HeatWave –Replication option III Inbound and Outbound Replication to/from none OCI MySQL instances
36. Copyright © 2023, Oracle and/or its affiliates. All rights reserved. Thoughts from the Business Thoughts from the Business Inbound and Outbound Replication to/from none OCI MySQL instances • Replication enables data from one MySQL server (source) to be copied (near to realtime) to one or more MySQL servers (replicas) • You want to do an ‘online’ Migration with minimum downtime • You want to leverage HeatWave Analytics or ML in the Cloud • Means: • We want a MySQL HeatWave Servers to act as a Replica (Inbound Replication) • Source Server can be 5.7 (ideally 8.0) • A copy of the MySQL HeatWave/Cloud in an on-prem datacenter • Means: • We want a MySQL HeatWave Servers to act as a Source for an on-prem Replica (Outbound Replication) • Replica Server must be 8.0 36
37. Copyright © 2023, Oracle and/or its affiliates. All rights reserved. On-Premises Oracle Cloud Infrastructure MySQL Replication Primary/Source instance Existing on-premises application Analytic processing in the cloud Analytical Query Application MySQL HeatWave & ML Inbound Replication to OCI MySQL instances 37
38. Copyright © 2023, Oracle and/or its affiliates. All rights reserved. No difference to Replication within OCI! No difference to Replication within OCI! Inbound Replication to OCI MySQL instances • Main challenge – Network and protection of the on-premise/Source Server • Inbounds Replication means that the Replica (here MySQL HeatWave) connects (outgoing) to an available MySQL Server (Source)/IP/Hostname • You can use Site-to-Site VPN, FastConnect or OpenVPN Access Server for the connection • Use a recent MySQL Server version as a Source (for latest SSL) – minimum MySQL 5.7.9 • Restrict access to the Source Server (Firewall, Network, Port) • Force SSL/TLS usage • Use strong authentication methods (like X509) • Configure you VCN (Security Lists) • Import/Export your data (make a copy of your Source Server) • Use MySQL Channels as before • Start Replicating… 38 https://docs.oracle.com/en-us/iaas/mysql-database/doc/inbound-replication.html
39. Copyright © 2023, Oracle and/or its affiliates. All rights reserved. On-Premises Oracle Cloud Infrastructure MySQL Replication Secondary/Replica instance Copy of MySQL HeatWave MySQL in the cloud MySQL HeatWave Outbound Replication from OCI MySQL instances 39
40. Copyright © 2023, Oracle and/or its affiliates. All rights reserved. We need an external OCI access for MySQL HeatWave We need an external OCI access for MySQL HeatWave Outbound Replication from OCI MySQL instances • Main challenge – VPN or External IP for MySQL HeatWave needed • Outbound Replication means that the Source (here MySQL HeatWave) accepts an incoming SQL connections • You can use Site-to-Site VPN, FastConnect or OpenVPN Access Server for the connection • Don’t forget to protect your Replica/On-prem MySQL • Force SSL/TLS usage for MySQL • External access possible via Network Load Balancer 1. Setup a Network Security Group (allow access for IP from MySQL Replica Server) 2. Configure a LB Listener (add Network Security Group / Replica Server) 3. Configure a LB Backend 4. Specify Health Check Policy 5. Configure Backend of the Load Balancer to access MySQL Source 40 https://docs.oracle.com/en-us/iaas/mysql-database/doc/outbound-replication.html
41. Copyright © 2023, Oracle and/or its affiliates. All rights reserved. We need an external OCI access for MySQL HeatWave We need an external OCI access for MySQL HeatWave Outbound Replication from OCI MySQL instances Remaining steps • Add a new Replication user on your Source Server • Enable/enforce GTID on your Replica • Import/Export your data (make a copy of your Source Server) • Configure your MySQL Replica to use Replication (using Auto positioning) • CHANGE REPLICATION SOURCE TO SOURCE_HOST = <DBSystemIPAddress>, SOURCE_PORT = 3306, SOURCE_USER = <rplUser>, SOURCE_PASSWORD = <rplUserPassword>, SOURCE_SSL=1, SOURCE_AUTO_POSITION = 1; • Start Replication and check • mysql> START REPLICA; • SHOW REPLICA STATUS \G; 41
42. Copyright © 2023, Oracle and/or its affiliates. All rights reserved. 42 MySQL The world’s most popular open source database
43. Copyright © 2023, Oracle and/or its affiliates. All rights reserved. 43 Follow us on Social Media “Data is the Oxygen of Business”
44. Upcoming Hands-On Lab Running WordPress with MySQL HeatWave Copyright © 2023, Oracle and/or its affiliates. All rights reserved. 44 11 May 2023 10:00 a.m. to 12:00 p.m. CEST Register now with the QR code!
45. Copyright © 2023, Oracle and/or its affiliates. All rights reserved. 45 Merci! Q&R Olivier Dasini MySQL Cloud Principal Solutions Architect EMEA olivier.dasini@oracle.com Blogs : www.dasini.net/blog/en : www.dasini.net/blog/fr Linkedin: www.linkedin.com/in/olivier-dasini Twitter : @freshdaz
47. Copyright © 2023, Oracle and/or its affiliates. All rights reserved. 4 7 Keynote: MySQL State of the Dolphin - MySQL Summit 2023 https://www.youtube.com/@mysql 47
48. Copyright © 2023, Oracle and/or its affiliates. All rights reserved. MySQL Long-Term Support (LTS) Releases • stable: bugfix & security patches only • backwards compatibility • every 2 years • support lifecycle: 5y premier + 3y extended MySQL Versioning: LTS & Innovation Releases MySQL Innovation Releases • leading-edge innovations • easy migration between LTS & Innovation • every quarter • support lifecycle: short term Keynote: MySQL State of the Dolphin - MySQL Summit 2023 https://www.youtube.com/@mysql 48
49. Copyright © 2023, Oracle and/or its affiliates. All rights reserved. Metrics • MySQL metrics to diagnose and troubleshoot problems • Create alarms on key metrics based on thresholds Fleet monitoring • Unified fleet view of MySQL databases across Oracle Cloud and on-premises Drill down to a specific MySQL from the Fleet • Quickly identify expensive queries that impact the performance of their applications • Visualize query activity to gain further insight into performance beyond query statistics • Filter for specific query problems like full table scans and bad indexes using advanced global search options MySQL Observability in OCI 49
50. Copyright © 2023, Oracle and/or its affiliates. All rights reserved. 50 • MySQL HeatWave is available in 40+ Oracle Cloud Regions (Public Cloud) • … also, on Oracle Cloud@Customer (Dedicated Region) (Dedicated Cloud) • … connect your local MySQL with Public Cloud (Hybrid Cloud) • … it is part of the Microsoft Azure ODSA (Multi Cloud) • … it is part of the Amazon Web Services [native] offering (Multi Cloud) MySQL HeatWave “One cloud is rarely the answer”