MySQL Database Service High Availability & Disaster Recovery - Prepare your applications for a disaster recovery scenario

Preparing your applications for a disaster recovery scenario is very important for mission critical production systems. Implementing a robust high availability architecture that can keep the service running in case of IT failure or damage is paramount.

In this webinar, we will explain how to setup different high availability architectures for disaster recovery with MySQL Database Service. We will look at how to configure primary and secondary nodes with automatic data replication in other data centers, and how to setup the network, router tables and security lists.

Do not miss this opportunity to ask your questions directly to the MySQL team!

Transcript

1. MySQL Database Service High Availability & Disaster Recovery Prepare your applications for a disaster recovery scenario 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
2. Copyright © 2022, 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
3. Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 3 Prerequisites 3 Topics not covered
4. • MySQL Shell is installed (used as MySQL client) – https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-install.html – MySQL Shell is also available on the OCI Cloud Shell • https://docs.oracle.com/en-us/iaas/Content/API/Concepts/cloudshellintro.htm • OCI CLI configuration file (used for MySQL Shell dump utility) – https://docs.oracle.com/en-us/iaas/Content/API/Concepts/sdkconfig.htm • OCI user must be part of a group with the suitable policies – https://docs.oracle.com/en-us/iaas/Content/Identity/Concepts/policygetstarted.htm • (Optionally) A dedicated compartment (named Disaster_Recovery in this presentation) Prerequisites Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 4
5. Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 5 Introduction 5 IT Disasters & Outages
6. IT Disasters & Outages: Primary Causes Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 6 On-site power failure is the biggest cause of significant outages
7. IT Disasters & Outages: Costs are Rising Copyright © 2022, Oracle and/or its affiliates. All rights reserved. Over half had experienced an outage costing more than $100,000 7
8. Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 5-Hour computer outage cost $150 million. The airline eventually canceled about 1,000 flights on the day of the outage and grounded an additional 1,000 flights over the following days. Millions of websites offline after fire at French cloud services firm. The fire is expected to cost the company more than €105 million. Tens of thousands of passengers were stranded in cities around the world due to cancellation of about 130 flights and the delay of 200. Millions of bank customers were unable to access online accounts. The bank took almost 2 days to recover and get back to normal functioning. 8 IT Disasters & Outages: Examples
9. Concepts – RTO & RPO • RTO: Recovery Time Objective • How long does it take to recover from a single failure • RPO: Recovery Point Objective • How much data can be lost when a failure occurs Business Requirements Copyright © 2022, Oracle and/or its affiliates. All rights reserved. Types of Failure • High Availability: Single Server Failure, Network Partition • Disaster Recovery: Full Region/Network Failure • Human Error: Little Bobby Tables 9
10. Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 10 Architecture 10 What we want to achieve
11. Global Architecture Overview Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 11
12. Simplified Architecture (what we’re going to do) Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 12
13. Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 13 Plan 13 A step-by-step guide
14. • 1/ Region 1 Network Setup : FRA – a) Create the VCN – b) Create the DRG – c) Attach the DRG to the VCN – d) Update Security List for the Private Subnet • 2/ Region 2 network setup : MAR – a) Create the VCN – b) Create the DRG – c) Attach the DRG to the VCN – d) Update Security List for the Private Subnet • 3/ Create the High Availability MySQL DB System • 4/ Create the Remote Peering Connection (RPC) – a) Create RPC in FRA Region – b) Get FRA Region RPC's OCID – c) Create RPC in MAR Region – d) Establish connection between FRA & MAR Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 14 • 5/ Update Route Tables (RT) – a) Update RT in FRA Region – b) Update RT in MAR Region • 6/ Connect to the HA MySQL DB System – a) Create a Bastion in FRA Region – b) Create a Bastion's session – c) Connect to MDS HA in FRA Region – d) Add some test data – e) Add the replication user • 7/ Create an Object Storage Bucket to backup the database • 8/ Backup primary database into MAR Region • 9/ Create the replica in MAR Region • 10/ Setup the replication • 11/ Connect to the replica MySQL DB System Detailed Plan
15. • 1/ Region 1 Network Setup : FRA – a) Create the VCN – b) Create the DRG – c) Attach the DRG to the VCN – d) Update Security List for the Private Subnet • 2/ Region 2 network setup : MAR – a) Create the VCN – b) Create the DRG – c) Attach the DRG to the VCN – d) Update Security List for the Private Subnet • 3/ Create the High Availability MySQL DB System • 4/ Create the Remote Peering Connection (RPC) – a) Create RPC in FRA Region – b) Get FRA Region RPC's OCID – c) Create RPC in MAR Region – d) Establish connection between FRA & MAR Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 15 • 5/ Update Route Tables (RT) – a) Update RT in FRA Region – b) Update RT in MAR Region • 6/ Connect to the HA MySQL DB System – a) Create a Bastion in FRA Region – b) Create a Bastion's session – c) Connect to MDS HA in FRA Region – d) Add some test data – e) Add the replication user • 7/ Create an Object Storage Bucket to backup the database • 8/ Backup primary database into MAR Region • 9/ Create the replica in MAR Region • 10/ Setup the replication • 11/ Connect to the replica MySQL DB System Detailed Plan
16. Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 16 Region 1 Network Setup 16 Frankfurt (FRA)
17. Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 17
18. • Provide customizable and private cloud networks in OCI • Just like a traditional data center network, the VCN provides customers with complete control over their cloud networking environment • This includes assigning private IP address spaces, creating subnets and route tables, and configuring stateful firewalls • https://docs.oracle.com/en-us/iaas/Content/Network/Tasks/managingVCNs.htm Virtual Cloud Network (VCN) Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 18 A virtual version of a traditional network on which your instance runs
19. • Use the Network wizard – Start VCN Wizard – Create VCN with internet Connectivity • Details – VCN Name: VCN_DR_FRA – Compartment: Disaster_Recovery – VCN CIDR Block: 10.0.0.0/16 – Public Subnet CIDR Block: 10.0.0.0/24 – Private Subnet CIDR Block: 10.0.1.0/24 FRA: Create the Virtual Cloud Network (VCN) Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 19
20. • A DRG acts as a virtual router, providing a path for traffic between your on-premises networks and VCNs, and can also be used to route traffic between VCNs • Using different types of attachments, custom network topologies can be constructed using components in different regions and tenancies • https://docs.oracle.com/en-us/iaas/Content/Network/Tasks/managingDRGs.htm Dynamic Routing Gateway (DRG) Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 20 A virtual router to which you can attach resources like VCN, RPC, IPSec tunnels...
21. • Details – Name: DRG_FRA – Compartment: Disaster_Recovery FRA: Create the Dynamic Routing Gateway (DRG) Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 21
22. • Click on “Create Virtual Cloud Network Attachment” • Details – Attachment Name: DRG_FRA_attachment – Choose a DRG in Disaster_Recovery: DRG_FRA – Select a VCN: VCN_DR_FRA FRA: Attach the Dynamic Routing Gateway to the Virtual Cloud Network Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 22
23. • A security list acts as a virtual firewall for an instance, with ingress and egress rules that specify the types of traffic allowed in and out • Each security list is enforced at the VNIC level • However, you configure your security lists at the subnet level, which means that all VNICs in a given subnet are subject to the same set of security lists • The security lists apply to a given VNIC whether it's communicating with another instance in the VCN or a host outside the VCN. • https://docs.oracle.com/en-us/iaas/Content/Network/Concepts/securitylists.htm Security List Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 23 A virtual firewall
24. • Security List for Private Subnet-VCN_DR_FRA – Add Ingress Rules • Details – Source Type: CIDR – Source CIDR: 10.0.0.0/8 – IP Protocol: TCP – Source Range Port: All – Destination Port Range: 3306,33060 – Description: MySQL ports FRA: Update Security List for the Private Subnet Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 24 Allow connection for MySQL (3306 & 33060) from FRA and MAR VCNs
25. • 1/ Region 1 Network Setup : FRA – a) Create the VCN – b) Create the DRG – c) Attach the DRG to the VCN – d) Update Security List for the Private Subnet • 2/ Region 2 network setup : MAR – a) Create the VCN – b) Create the DRG – c) Attach the DRG to the VCN – d) Update Security List for the Private Subnet • 3/ Create the High Availability MySQL DB System • 4/ Create the Remote Peering Connection (RPC) – a) Create RPC in FRA Region – b) Get FRA Region RPC's OCID – c) Create RPC in MAR Region – d) Establish connection between FRA & MAR Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 25 • 5/ Update Route Tables (RT) – a) Update RT in FRA Region – b) Update RT in MAR Region • 6/ Connect to the HA MySQL DB System – a) Create a Bastion in FRA Region – b) Create a Bastion's session – c) Connect to MDS HA in FRA Region – d) Add some test data – e) Add the replication user • 7/ Create an Object Storage Bucket to backup the database • 8/ Backup primary database into MAR Region • 9/ Create the replica in MAR Region • 10/ Setup the replication • 11/ Connect to the replica MySQL DB System Detailed Plan
26. Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 26 Region 2 Network Setup 26 Marseille (MAR)
27. Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 27
28. MAR: Create the Virtual Cloud Network (VCN) Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 28 • Use the Network wizard – Start VCN Wizard – Create VCN with internet Connectivity • Details – VCN Name: VCN_DR_MAR – Compartment: Disaster_Recovery – VCN CIDR Block: 10.1.0.0/16 – Public Subnet CIDR Block: 10.1.0.0/24 – Private Subnet CIDR Block: 10.1.1.0/24
29. MAR: Create the Dynamic Routing Gateway (DRG) Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 29 • Details – Name: DRG_MAR – Compartment: Disaster_Recovery
30. MAR: Attach the the Dynamic Routing Gateway to the Virtual Cloud Network Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 30 • Click on “Create Virtual Cloud Network Attachment” • Details – Attachment Name: DRG_MAR_attachment – Choose a DRG in Disaster_Recovery: DRG_MAR – Select a VCN: VCN_DR_MAR
31. MAR: Update Security List for the Private Subnet Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 31 • Security List for Private Subnet-VCN_DR_MAR – Add Ingress Rules • Details – Source Type: CIDR – Source CIDR: 10.0.0.0/8 – IP Protocol: TCP – Source Range Port: All – Destination Port Range: 3306,33060 – Description: MySQL ports • Allow connection for MySQL (3306 & 33060) from FRA and MAR VCNs
32. • 1/ Region 1 Network Setup : FRA – a) Create the VCN – b) Create the DRG – c) Attach the DRG to the VCN – d) Update Security List for the Private Subnet • 2/ Region 2 network setup : MAR – a) Create the VCN – b) Create the DRG – c) Attach the DRG to the VCN – d) Update Security List for the Private Subnet • 3/ Create the High Availability MySQL DB System • 4/ Create the Remote Peering Connection (RPC) – a) Create RPC in FRA Region – b) Get FRA Region RPC's OCID – c) Create RPC in MAR Region – d) Establish connection between FRA & MAR Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 32 • 5/ Update Route Tables (RT) – a) Update RT in FRA Region – b) Update RT in MAR Region • 6/ Connect to the HA MySQL DB System – a) Create a Bastion in FRA Region – b) Create a Bastion's session – c) Connect to MDS HA in FRA Region – d) Add some test data – e) Add the replication user • 7/ Create an Object Storage Bucket to backup the database • 8/ Backup primary database into MAR Region • 9/ Create the replica in MAR Region • 10/ Setup the replication • 11/ Connect to the replica MySQL DB System Detailed Plan
33. Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 33 Create the High Availability MySQL DB System 33 Provide automatic failover and zero data loss
35. • MySQL Database Service is a fully managed Oracle Cloud Infrastructure native service, developed, managed, and supported by the MySQL team in Oracle • Oracle automates all tasks such as backup and recovery, database and operating system patching, and so on • You are responsible solely for managing your data, schema designs, and access policies • https://docs.oracle.com/en-us/iaas/mysql-database/index.html MySQL Database Service (MDS) Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 35 A fully-managed OCI service, developed, managed, and supported by the MySQL team
36. • Details – Create in compartment: Disaster_Recovery – Name: MDSHA_FRA – Description: MySQL HA in Frankfurt – VCN: VCN_DR_FRA – Description: Private Subnet MySQL Database Service High Availability Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 36 Run 3-instance MySQL DB System providing automatic failover and zero data loss Note the cluster IP when active: e.g.: 10.0.1.85
37. • 1/ Region 1 Network Setup : FRA – a) Create the VCN – b) Create the DRG – c) Attach the DRG to the VCN – d) Update Security List for the Private Subnet • 2/ Region 2 network setup : MAR – a) Create the VCN – b) Create the DRG – c) Attach the DRG to the VCN – d) Update Security List for the Private Subnet • 3/ Create the High Availability MySQL DB System • 4/ Create the Remote Peering Connection (RPC) – a) Create RPC in FRA Region – b) Get FRA Region RPC's OCID – c) Create RPC in MAR Region – d) Establish connection between FRA & MAR Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 37 • 5/ Update Route Tables (RT) – a) Update RT in FRA Region – b) Update RT in MAR Region • 6/ Connect to the HA MySQL DB System – a) Create a Bastion in FRA Region – b) Create a Bastion's session – c) Connect to MDS HA in FRA Region – d) Add some test data – e) Add the replication user • 7/ Create an Object Storage Bucket to backup the database • 8/ Backup primary database into MAR Region • 9/ Create the replica in MAR Region • 10/ Setup the replication • 11/ Connect to the replica MySQL DB System Detailed Plan
38. Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 38 Create the Remote Peering Connection (RPC) 38 Connect VCNs between FRA & MAR
39. Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 39
40. • Remote VCN peering is the process of connecting two VCNs in different regions (but the same tenancy) • The peering allows the VCNs' resources to communicate using private IP addresses without routing the traffic over the internet or through your on-premises network • Without peering, a given VCN would need an internet gateway and public IP addresses for the instances that need to communicate with another VCN in a different region • https://docs.oracle.com/en-us/iaas/Content/Network/Tasks/remoteVCNpeering.htm Remote VCN Peering Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 40 Connecting two VCNs in different regions
41. • Details – Name: RPC_from_FRA_to_MAR – Create in compartment: Disaster_Recovery Create RPC in FRA Region Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 41
42. • Copy OCID of RPC_from_FRA_to_MAR – e.g.: ocid1.remotepeeringconnection.oc1.eu-frankfurt-1.aaaaabbbcccccddddeeee.... Get FRA Region RPC's OCID Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 42
43. • Details – Name: RPC_from_MAR_to_FRA – Create in compartment: Disaster_Recovery Create RPC in MAR Region Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 43
44. • Click on “RPC_from_MAR_to_FRA“ then on “Establish Connection” • Details – Region: eu-frankfurt-1 – Remote Peering Connection OCID: <RPC_from_FRA_to_MAR OCID> • e.g.: ocid1.remotepeeringconnection.oc1.eu-frankfurt-1.aaaaabbbcccccddddeeee…. Establish connection between FRA & MAR Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 44
45. • 1/ Region 1 Network Setup : FRA – a) Create the VCN – b) Create the DRG – c) Attach the DRG to the VCN – d) Update Security List for the Private Subnet • 2/ Region 2 network setup : MAR – a) Create the VCN – b) Create the DRG – c) Attach the DRG to the VCN – d) Update Security List for the Private Subnet • 3/ Create the High Availability MySQL DB System • 4/ Create the Remote Peering Connection (RPC) – a) Create RPC in FRA Region – b) Get FRA Region RPC's OCID – c) Create RPC in MAR Region – d) Establish connection between FRA & MAR Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 45 • 5/ Update Route Tables (RT) – a) Update RT in FRA Region – b) Update RT in MAR Region • 6/ Connect to the HA MySQL DB System – a) Create a Bastion in FRA Region – b) Create a Bastion's session – c) Connect to MDS HA in FRA Region – d) Add some test data – e) Add the replication user • 7/ Create an Object Storage Bucket to backup the database • 8/ Backup primary database into MAR Region • 9/ Create the replica in MAR Region • 10/ Setup the replication • 11/ Connect to the replica MySQL DB System Detailed Plan
46. Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 46 Update Route Tables (RT) 46 Send traffic out of the VCNs
47. • Your VCN uses route tables to send traffic out of the VCN (for example, to the internet, to your on- premises network, or to a peered VCN) • These route tables have rules that look and act like traditional network route rules you might already be familiar with • Each rule specifies a destination CIDR block and the target (the next hop) for any traffic that matches that CIDR • https://docs.oracle.com/en-us/iaas/Content/Network/Tasks/managingroutetables.htm Route Tables Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 47 Send traffic out of the VCN
48. • Add Route Table for Private Subnet-VCN_DR_MAR – Target Type: Dynamic Routing Gateway – Destination CIDR Block: 10.1.0.0/16 – Description: Route for MAR • Add Default Route Table for VCN_DR_MAR – Target Type: Dynamic Routing Gateway – Destination CIDR Block: 10.1.0.0/16 – Description: Route for MAR Update RT in FRA Region Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 48 Add the rules to join the MAR network in the default and private-subnet routing tables
49. • Add Route Table for Private Subnet-VCN_DR_FRA – Target Type: Dynamic Routing Gateway – Destination CIDR Block: 10.0.0.0/16 – Description: Route for FRA • Add Default Route Table for VCN_DR_FRA – Target Type: Dynamic Routing Gateway – Destination CIDR Block: 10.0.0.0/16 – Description: Route for FRA Update RT in MAR Region Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 49 Add the rules to join the FRA network in the default and private-subnet routing tables
50. • Frankfurt Network Visualizer Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 50 • Marseille
51. • 1/ Region 1 Network Setup : FRA – a) Create the VCN – b) Create the DRG – c) Attach the DRG to the VCN – d) Update Security List for the Private Subnet • 2/ Region 2 network setup : MAR – a) Create the VCN – b) Create the DRG – c) Attach the DRG to the VCN – d) Update Security List for the Private Subnet • 3/ Create the High Availability MySQL DB System • 4/ Create the Remote Peering Connection (RPC) – a) Create RPC in FRA Region – b) Get FRA Region RPC's OCID – c) Create RPC in MAR Region – d) Establish connection between FRA & MAR Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 51 • 5/ Update Route Tables (RT) – a) Update RT in FRA Region – b) Update RT in MAR Region • 6/ Connect to the HA MySQL DB System – a) Create a Bastion in FRA Region – b) Create a Bastion's session – c) Connect to MDS HA in FRA Region – d) Add some test data – e) Add the replication user • 7/ Create an Object Storage Bucket to backup the database • 8/ Backup primary database into MAR Region • 9/ Create the replica in MAR Region • 10/ Setup the replication • 11/ Connect to the replica MySQL DB System Detailed Plan
52. Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 52 Connect to the HA MySQL DB System 52 Using OCI Bastion
54. • Bastions are logical entities that provide secured, public access to target resources in the cloud that you cannot otherwise reach from the internet • Bastions reside in a public subnet and establish the network infrastructure needed to connect a user to a target resource in a private subnet • Integration with the IAM service provides user authentication and authorization • Bastions provide an extra layer of security through the configuration of CIDR block allowlists • Client CIDR block allowlists specify what IP addresses or IP address ranges can connect to a session hosted by the bastion • https://docs.oracle.com/en-us/iaas/Content/Bastion/home.htm Bastion Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 54 Restricted and time-limited access to target resources that don't have public endpoints
55. • Details – Bastion name: BastionFRA – Target VCN: VCN_DR_FRA – Target subnet: Private Subnet-VCN_DR_FRA – CIDR block allowlist: <IP_address/xx> Create a Bastion in FRA Region Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 55
56. • Details – Session type: SSH port forwarding session – Session name: Session – IPaddress: HA MySQL DB System IP address e.g. 10.0.1.85 – Port: 3306 – Add SSH Key... • When active copy and run the SSH command – Update <privatekey> (related to the public key uploaded) – Update <localPort> (free port on your system) – e.g.: $ ssh -i ssh-private-key.key -N -L 3333:10.0.1.85:3306 -p 22 ocid1.bastionsession.oc1.eu-frankfurt- 1.aabbccddeeffgghhiijjkkllmmnnoo@host.bastion.eu-frankfurt-1.oci.oraclecloud.com Create a Bastion's session Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 56
57. • Connect using MySQL Shell • Details: – User: <MySQL user> – Host: localhost – Port: <port chosen in previous step> e.g. 3333 • Example: $ mysqlsh admin@localhost:3333 --sql Connect to MDS HA in FRA Region Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 57
58. SQL> CREATE SCHEMA app; CREATE TABLE app.t1(i int primary key AUTO_INCREMENT, dt DATETIME); SHOW TABLES IN app; INSERT INTO app.t1 VALUES (NULL, NOW()); SELECT * FROM app.t1; Add some test data Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 58 • In order to simulate a “real” system
59. SQL> CREATE USER 'repl_mar'@'10.1.1.%' IDENTIFIED BY '!s3Cu4eP4$$' REQUIRE SSL; GRANT REPLICATION SLAVE ON *.* TO 'repl_mar'@'10.1.1.%'; SELECT User, Host, Repl_slave_priv, ssl_type FROM mysql.user WHERE user = 'repl_mar'; Add replication user Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 59
60. • 1/ Region 1 Network Setup : FRA – a) Create the VCN – b) Create the DRG – c) Attach the DRG to the VCN – d) Update Security List for the Private Subnet • 2/ Region 2 network setup : MAR – a) Create the VCN – b) Create the DRG – c) Attach the DRG to the VCN – d) Update Security List for the Private Subnet • 3/ Create the High Availability MySQL DB System • 4/ Create the Remote Peering Connection (RPC) – a) Create RPC in FRA Region – b) Get FRA Region RPC's OCID – c) Create RPC in MAR Region – d) Establish connection between FRA & MAR Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 60 • 5/ Update Route Tables (RT) – a) Update RT in FRA Region – b) Update RT in MAR Region • 6/ Connect to the HA MySQL DB System – a) Create a Bastion in FRA Region – b) Create a Bastion's session – c) Connect to MDS HA in FRA Region – d) Add some test data – e) Add the replication user • 7/ Create an Object Storage Bucket to backup the database • 8/ Backup primary database into MAR Region • 9/ Create the replica in MAR Region • 10/ Setup the replication • 11/ Connect to the replica MySQL DB System Detailed Plan
61. Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 61 Create an Object Storage Bucket 61 To backup the database
62. • The Oracle Cloud Infrastructure Object Storage service is an internet-scale, high-performance storage platform that offers reliable and cost-efficient data durability • The Object Storage service can store an unlimited amount of unstructured data of any content type, including analytic data and rich content, like images and videos • With Object Storage, you can safely and securely store or retrieve data directly from the internet or from within the cloud platform • https://docs.oracle.com/en-us/iaas/Content/Object/home.htm Object Storage Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 62 Safely and securely store or retrieve data
63. • In MAR region • Details – Region: MAR – Compartment: Disaster_Recovery – Bucket Name: mdsPrimaryDBDump1 Create an Object Storage Bucket to backup the primary database into MAR Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 63
64. • 1/ Region 1 Network Setup : FRA – a) Create the VCN – b) Create the DRG – c) Attach the DRG to the VCN – d) Update Security List for the Private Subnet • 2/ Region 2 network setup : MAR – a) Create the VCN – b) Create the DRG – c) Attach the DRG to the VCN – d) Update Security List for the Private Subnet • 3/ Create the High Availability MySQL DB System • 4/ Create the Remote Peering Connection (RPC) – a) Create RPC in FRA Region – b) Get FRA Region RPC's OCID – c) Create RPC in MAR Region – d) Establish connection between FRA & MAR Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 64 • 5/ Update Route Tables (RT) – a) Update RT in FRA Region – b) Update RT in MAR Region • 6/ Connect to the HA MySQL DB System – a) Create a Bastion in FRA Region – b) Create a Bastion's session – c) Connect to MDS HA in FRA Region – d) Add some test data – e) Add the replication user • 7/ Create an Object Storage Bucket to backup the database • 8/ Backup primary database into MAR Region • 9/ Create the replica in MAR Region • 10/ Setup the replication • 11/ Connect to the replica MySQL DB System Detailed Plan
65. Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 65 Backup primary database into MAR 65 Using MySQL Shell dump utility
66. • Backup will be done by MySQL Shell and stored automatically into a Bucket • MySQL Shell is using OCI CLI configuration file – Region parameter must be properly set: • region = eu-marseille-1 • Please check if your Bastion's session is still up Backup primary database Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 66 $ mysqlsh admin@localhost:3333 JS> util.dumpInstance("", {osBucketName: "mdsPrimaryDBDump1", osNamespace: "<oci_namespace>", ocimds: true, ociParManifest: true, ociParExpireTime: "2022- 03-28T00:20:00.000+02:00", compatibility: ["strip_restricted_grants"]})
67. • 1/ Region 1 Network Setup : FRA – a) Create the VCN – b) Create the DRG – c) Attach the DRG to the VCN – d) Update Security List for the Private Subnet • 2/ Region 2 network setup : MAR – a) Create the VCN – b) Create the DRG – c) Attach the DRG to the VCN – d) Update Security List for the Private Subnet • 3/ Create the High Availability MySQL DB System • 4/ Create the Remote Peering Connection (RPC) – a) Create RPC in FRA Region – b) Get FRA Region RPC's OCID – c) Create RPC in MAR Region – d) Establish connection between FRA & MAR Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 67 • 5/ Update Route Tables (RT) – a) Update RT in FRA Region – b) Update RT in MAR Region • 6/ Connect to the HA MySQL DB System – a) Create a Bastion in FRA Region – b) Create a Bastion's session – c) Connect to MDS HA in FRA Region – d) Add some test data – e) Add the replication user • 7/ Create an Object Storage Bucket to backup the database • 8/ Backup primary database into MAR Region • 9/ Create the replica in MAR Region • 10/ Setup the replication • 11/ Connect to the replica MySQL DB System Detailed Plan
68. Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 68 Create the replica in MAR 68 From the backup
70. • MDS is created from the backup • Details – Create in compartment: Disaster_Recovery – Name: MDS1_MAR – Description: MDS_1 in Marseille – Standalone – … – VCN: VCN_DR_MAR – Description: Private Subnet – … – Show Advanced Options -> Data Import • “Click here to create a PAR URL for an existing bucket.” • Select a bucket in Disaster_Recovery: mdsPrimaryDBDump1 • Enter a prefix: @.manifest.json MySQL Database Service Standalone Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 70 Single-instance MySQL DB System
71. Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 71 MySQL Database Service 71 100% developed, managed, & supported by the MySQL team
72. MySQL Database Service – Highlights Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 72
73. MySQL Database Service costs less Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 73 MySQL Database Service: Standard E3 AMD 16GB/Core, all regions have the same price. Amazon RDS: Intel R5 16GB/Core, AWS US East. Azure: Memory Optimized Intel 20GB/Core, MS Azure US-East. Google: High Memory N1 Standard Intel 13GB/Core, GCP Northern Virginia. Configuration: 100 OCPUs, 1 TB Storage. MySQL Database Service Amazon RDS Microsoft Azure Google Cloud SQL $0 $50,000 $100,000 $150,000 $200,000 $250,000 $65,833 $215,652 $212,974 $170,244 Annual cost for a 100 OCPUs, 1TB Storage configuration
74. MySQL Database Service: High Availability, multi AD Region 74 Copyright © 2021, Oracle and/or its affiliates. All rights reserved. Deployed across fault and availability domains Native MySQL Group Replication No data loss in case of failure (RPO=0) Online and fast fail-over (RTO=minutes) Inbound Replication MySQL Database Service Applications (Secondary) (Secondary) (Primary) AD1 MySQL Group Replication AD2 AD3 Fault-tolerant system with automatic failover & zero data loss through Group Replication
75. MySQL HeatWave - MySQL in-Memory Query Accelerator Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 75 Easily run high performance analytics against your MySQL database, no ETL required Single MySQL database for OLTP & analytics applications All existing applications work without any changes Extreme performance: 400x faster than MySQL, scales to thousands of cores OLAP Applications OLTP Applications OLTP Engine Analytics Engine MySQL Database Service
76. MySQL HeatWave performance and price comparison Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 76 30TB TPCH, MySQL HeatWave is faster, cheaper & easier to use than all the competitive database services 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 www.oracle.com/mysql/heatwave/performance
77. • 1/ Region 1 Network Setup : FRA – a) Create the VCN – b) Create the DRG – c) Attach the DRG to the VCN – d) Update Security List for the Private Subnet • 2/ Region 2 network setup : MAR – a) Create the VCN – b) Create the DRG – c) Attach the DRG to the VCN – d) Update Security List for the Private Subnet • 3/ Create the High Availability MySQL DB System • 4/ Create the Remote Peering Connection (RPC) – a) Create RPC in FRA Region – b) Get FRA Region RPC's OCID – c) Create RPC in MAR Region – d) Establish connection between FRA & MAR Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 77 • 5/ Update Route Tables (RT) – a) Update RT in FRA Region – b) Update RT in MAR Region • 6/ Connect to the HA MySQL DB System – a) Create a Bastion in FRA Region – b) Create a Bastion's session – c) Connect to MDS HA in FRA Region – d) Add some test data – e) Add the replication user • 7/ Create an Object Storage Bucket to backup the database • 8/ Backup primary database into MAR Region • 9/ Create the replica in MAR Region • 10/ Setup the replication • 11/ Connect to the replica MySQL DB System Detailed Plan
78. Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 78 Setup the replication 78 Between the 2 regions
80. • In MAR region • Details – Create in compartment: Disaster_Recovery – Name: Replication_from_Frankfurt – Description: Replication Channel from Frankfurt – Hostname: <MDS in FRA> e.g. 10.0.1.85 – Port: 3306 – Username: repl_mar – Password: !s3Cu4eP4$$ – SSL Mode: Required (REQUIRED) Create the Replication Channel Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 80 Asynchronous Replication
81. • 1/ Region 1 Network Setup : FRA – a) Create the VCN – b) Create the DRG – c) Attach the DRG to the VCN – d) Update Security List for the Private Subnet • 2/ Region 2 network setup : MAR – a) Create the VCN – b) Create the DRG – c) Attach the DRG to the VCN – d) Update Security List for the Private Subnet • 3/ Create the High Availability MySQL DB System • 4/ Create the Remote Peering Connection (RPC) – a) Create RPC in FRA Region – b) Get FRA Region RPC's OCID – c) Create RPC in MAR Region – d) Establish connection between FRA & MAR Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 81 • 5/ Update Route Tables (RT) – a) Update RT in FRA Region – b) Update RT in MAR Region • 6/ Connect to the HA MySQL DB System – a) Create a Bastion in FRA Region – b) Create a Bastion's session – c) Connect to MDS HA in FRA Region – d) Add some test data – e) Add the replication user • 7/ Create an Object Storage Bucket to backup the database • 8/ Backup primary database into MAR Region • 9/ Create the replica in MAR Region • 10/ Setup the replication • 11/ Connect to the replica MySQL DB System Detailed Plan
82. Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 82 Connect to the replica 82
83. • Details – Bastion name: BastionMAR – Target VCN: VCN_DR_MAR – Target subnet: Private Subnet-VCN_DR_MAR – CIDR block allowlist: <IP_address/xx> Create a Bastion in MAR Region Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 83
84. • Details – Session type: SSH port forwarding session – Session name: Session – IPaddress: Replica IP address e.g. 10.1.1.48 – Port: 3306 – Add SSH Key... • When active copy and run the SSH command – Update <privatekey> (related to the public key uploaded) – Update <localPort> (free port on your system) – e.g.: $ ssh -i ssh-private-key.key -N -L 4444:10.1.1.48:3306 -p 22 ocid1.bastionsession.oc1.eu-marseille- 1.aabbccddeeffgghhiijjkkllmmnnoo@host.bastion.eu-marseille-1.oci.oraclecloud.com Create a Bastion's session Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 84
85. • Connect using MySQL Shell • Details: – User: <MySQL user> – Host: localhost – Port: <port chosen in previous step> e.g. 4444 • Example: $ mysqlsh admin@localhost:4444 --sql Connect to Replica MDS in MAR Region Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 85
86. SQL> SHOW REPLICA STATUS\G Check replica state Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 86 • Checking Replication Status SQL> SHOW SCHEMAS; SHOW TABLES IN app; SELECT * FROM app.t1; • Look the data /!\ Warning: You should not run any write query on the replica
87. Current Architecture (what we’ve just done) Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 87
88. Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 88 Miscellaneous 88
89. • The CLI is a small-footprint tool that you can use on its own or with the Console to complete Oracle Cloud Infrastructure tasks • The CLI provides the same core functionality as the Console, plus additional commands • Some of these, such as the ability to run scripts, extend Console functionality • The configuration file name and default location (on Linux) is /home/opc/.oci/config OCI CLI Configuration File Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 89 https://docs.oracle.com/en-us/iaas/Content/API/Concepts/sdkconfig.htm • It should have the following information: user: OCID of the user calling the API fingerprint: Fingerprint for the public key that was added to this user key_file: Full path and filename of the private key tenancy: OCID of your tenancy region: An Oracle Cloud Infrastructure region
90. • MySQL Shell is an advanced client and code editor for MySQL • In addition to the provided SQL functionality, similar to mysql, MySQL Shell provides scripting capabilities for JavaScript and Python and includes APIs for working with MySQL • X DevAPI enables you to work with both relational and document data (MySQL Document Store) • AdminAPI enables you to work with InnoDB Cluster, InnoDB ClusterSet, and InnoDB ReplicaSet • Tuto: Connect to MySQL Database Service Using MySQL Shell – http://dasini.net/blog/2021/09/21/discovering-mysql-database-service-episode-8-connect-to-mysql-database-service-using-mysql-shell/ MySQL Shell Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 90 https://dev.mysql.com/doc/mysql-shell/8.0/en/ MySQL Server 5.7 MySQL 8.0 Upgrade Checker Prompt Themes Auto Completion & Command History Document StoAre X Dev API SQL CLI Output Formats (Table, JSON, Tabbed) Batch Execution JavaScript Python SQL importJSON Dump Utilities InnoDB Cluster/ ReplicaSet / ClusterSet MySQL Server 8.0
91. • Oracle Cloud Infrastructure Cloud Shell gives you access to an always available Linux shell directly in the Oracle Cloud Infrastructure Console • You can use the shell to interact with resources like MySQL Database Service, Oracle Container Engine for Kubernetes cluster, Oracle Autonomous Database, … • Cloud Shell provides: – An ephemeral machine to use as a host for a Linux shell, pre-configured with the latest version of the OCI Command Line Interface (CLI) and a number of useful tools – 5GB of storage for your home directory – A persistent frame of the Console which stays active as you navigate to different pages of the console • Tuto: Connect to MySQL Database Service Using Cloud Shell – http://dasini.net/blog/2021/10/05/discovering-mysql-database-service-episode-10-connect-to-mysql-database-service-using-oci-cloud-shell/ OCI Cloud Shell Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 91 https://docs.cloud.oracle.com/iaas/Content/API/Concepts/cloudshellintro.htm
92. • The Inter-Region Latency dashboard in the Console provides the average network round-trip latency (round-trip time or RTT) for all pairs of regions in an Oracle Cloud Infrastructure realm • It provides a view of network health and metrics for networking service elements across all regions • These metrics are not specific to your tenancy and are intended for planning purposes only OCI Inter-Region Latency Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 92 https://docs.oracle.com/en-us/iaas/Content/Network/Concepts/inter_region_latency.htm
93. • The replica must apply the source's transactions before the source's binary log is purged • The default value of binlog_expire_logs_seconds is 3600 seconds (1 hour) – After their expiration period ends, binary log files can be automatically removed – Possible removals happen at startup and when the binary log is flushed • If you require a longer value, before provisioning your HA instance, you must create a new configuration with the required expiration value and create a new source DB System using the new configuration – This will obviously consume more disk space on your instances • binlog_expire_logs_seconds – https://dev.mysql.com/doc/refman/8.0/en/replication-options-binary-log.html#sysvar_binlog_expire_logs_seconds MySQL binary log expiration period Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 93 https://docs.oracle.com/en-us/iaas/mysql-database/doc/replication.html#MYAAS-GUID-68D4C78D-43DE-4733-9BD8-0AF2AC21116A
94. Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 94 Test Drive MySQL Database Service For Free Today Get $300 in credits and try MySQL Database Service free for 30 days. https://www.oracle.com/cloud/free/
95. Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 95 Resources
96. Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 96  Introducing the MySQL Database Service https://blogs.oracle.com/mysql/introducing-the-mysql-database-service  Migrate from on premise MySQL to MySQL Database Service https://blogs.oracle.com/mysql/migrate-from-on-premise-mysql-to-mysql-database-service  Setup Disaster Recovery for OCI MySQL Database Service https://lefred.be/content/setup-disaster-recovery-for-oci-mysql-database-service/  Cost Estimator https://www.oracle.com/cloud/cost-estimator.html  OCI Free Trial https://www.oracle.com/cloud/free/  Documentations https://docs.cloud.oracle.com/en-us/iaas/mysql-database/index.html https://www.oracle.com/mysql/  MySQL Database Service - Infographic https://www.oracle.com/a/ocom/docs/mysql/mysql-database-service-infographic.pdf  MySQL Database Service - Ebook https://www.oracle.com/a/ocom/docs/mysql/mysql-database-service-ebook.pdf MySQL Database Service Resources
97. Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 97  Discovering MySQL Database Service – Episode 1 – Introduction http://dasini.net/blog/2021/08/03/discovering-mysql-database-service-episode-1-introduction/  Discovering MySQL Database Service – Episode 2 – Create a compartment http://dasini.net/blog/2021/08/10/discovering-mysql-database-service-episode-2-create-a-compartment/  Discovering MySQL Database Service – Episode 3 – Create a Virtual Cloud Network http://dasini.net/blog/2021/08/17/discovering-mysql-database-service-episode-3-create-a-virtual-cloud-network/  Discovering MySQL Database Service – Episode 4 – Dump your MySQL data into an Object Storage bucket http://dasini.net/blog/2021/08/24/discovering-mysql-database-service-episode-4-dump-your-mysql-data-into-an-object-storage-bucket/  Discovering MySQL Database Service – Episode 5 – Create a MySQL DB system from a MySQL Shell dump http://dasini.net/blog/2021/08/31/discovering-mysql-database-service-episode-5-create-a-mysql-db-system-from-a-mysql-shell-dump/  Discovering MySQL Database Service – Episode 6 – Update the Private Subnet Security List http://dasini.net/blog/2021/09/07/discovering-mysql-database-service-episode-6-update-the-private-subnet-security-list/  Discovering MySQL Database Service – Episode 7 – Use a Bastion SSH port forwarding session http://dasini.net/blog/2021/09/14/discovering-mysql-database-service-episode-7-use-a-bastion-ssh-port-forwarding-session/  Discovering MySQL Database Service – Episode 8 – Connect to MySQL Database Service Using MySQL Shell http://dasini.net/blog/2021/09/21/discovering-mysql-database-service-episode-8-connect-to-mysql-database-service-using-mysql-shell/  Discovering MySQL Database Service – Episode 9 – Connect to MySQL Database Service Using MySQL Workbench http://dasini.net/blog/2021/09/28/discovering-mysql-database-service-episode-9-connect-to-mysql-database-service-using-mysql-workbench/  Discovering MySQL Database Service – Episode 10 – Connect to MySQL Database Service Using OCI Cloud Shell http://dasini.net/blog/2021/10/05/discovering-mysql-database-service-episode-10-connect-to-mysql-database-service-using-oci-cloud-shell/ Discovering MySQL Database Service Tutorials where I show you, step by step, how to use MDS and some other OCI services
98. Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 98  Highlights: Announcing MySQL Database Service—New HeatWave Innovations https://youtu.be/FUbnOTlJ7mI  Demonstration of MySQL HeatWave Autopilot https://youtu.be/CyuBxBryJVA  Tetris.co speeds real-time insights with MySQL HeatWave https://www.oracle.com/customers/tetris-co/  Red3i increases insights by 1,000X with MySQL HeatWave https://www.oracle.com/customers/red3i/  FANCOMI accelerates ad analytics by 10X with MySQL HeatWave https://www.oracle.com/customers/fancomi/  Customer feedack: Tetris.co https://youtu.be/TrYAluHRXXs  Customer feedack: Fan Communications https://youtu.be/SiBHyYPlSNU MySQL Database Service — New HeatWave Innovations (2021) https://www.oracle.com/events/live/mysql-heatwave-innovations/
99. Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 99 Follow us on Social Media
100. Copyright © 2022, Oracle and/or its affiliates. All rights reserved. 100 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