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

Replication features of MySQL HeatWave
A technical ‘Run Through‘ in Oracle Cloud Infrastructure
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
April 2023

View Slide

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!

View Slide

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

View Slide

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

View Slide

5
MySQL – The state of the Dolphin
Introduction

View Slide

• 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

View Slide

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
7

View Slide

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

View Slide

9
…
a.k.a MDS: (OLTP)
General Purpose
Optimized for OLTP
(Lakehouse)
(Beta)
MySQL Heatwave
(Analytics)
+ Machine Learning
InnoDB
Lakehouse

View Slide

10
…
a.k.a MDS: (OLTP)
General Purpose
Optimized for OLTP
(Lakehouse)
(Beta)
MySQL Heatwave
(Analytics)
+ Machine Learning
InnoDB
Lakehouse

View Slide

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

View Slide

12

View Slide

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
13

View Slide

• 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…)

View Slide

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

View Slide

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…

View Slide

17
Database Replication
Enable data from one source to be copied
to one or more replicas

View Slide

Multiple copies of data with replication process
18

View Slide

MySQL Replication Overview
19
SOURCE REPLICA

View Slide

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

View Slide

21
MySQL HeatWave –High Availability
MySQL "always-on"

View Slide

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

View Slide

Availability Domain 1
Block Storage
Service
server
Automatic duplication of Object Storage (MySQL Backups)
Oracle Cloud Region
server
Object Storage
Service
(MySQL Backups)
Block Storage
Service
server server
Object Storage
Service
(MySQL Backups)
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

View Slide

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

View Slide

Automatic Application Failover
Automatic Application Failover
High Availability
25

View Slide

26
MySQL HeatWave –Replication option
I
Read Replicas

View Slide

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

View Slide

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

View Slide

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

View Slide

30
II
Replication to other OCI MySQL Instances

View Slide

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

View Slide

MySQL HeatWave Channels
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

View Slide

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

View Slide

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

View Slide

35
III
Inbound and Outbound Replication to/from
none OCI MySQL instances

View Slide

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

View Slide

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

View Slide

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

View Slide

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

View Slide

We need an external OCI access for MySQL HeatWave
• 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

View Slide

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 = ,
SOURCE_PORT = 3306, SOURCE_USER = , SOURCE_PASSWORD =
, SOURCE_SSL=1, SOURCE_AUTO_POSITION = 1;
• Start Replication and check
• mysql> START REPLICA;
• SHOW REPLICA STATUS \G;
41

View Slide

42
MySQL
The world’s most popular open source database

View Slide

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

View Slide

Upcoming Hands-On Lab
Running WordPress with
MySQL HeatWave
44
11 May 2023
10:00 a.m. to 12:00 p.m. CEST
Register now with the QR code!

View Slide

45
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

View Slide

View Slide

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

View Slide

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

View Slide

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”

View Slide

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

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

Olivier DASINI

More Decks by Olivier DASINI

Other Decks in Technology

Featured

Transcript