In the previous post, we saw how to deploy MySQL HeatWave on Amazon.
Multicloud refers to the coordinated use of cloud services from multiple providers. In addition to our previous post, where we deployed MySQL HeatWave on Amazon, we will explore how to connect with another cloud service.
Oracle has partnered with Microsoft to offer low-latency, private connectivity between Oracle Cloud and Microsoft Azure. Similarly, Oracle has partnered with Google to provide interconnectivity for Google Cloud.
For Amazon, we could use an external service like Megaport.
In this post, we won’t create a virtual private network, but we will connect our databases, forwarding the traffic to a PrivateLink Endpoint like this:
MySQL HeatWave and Compute Instance in OCI
The first step is to deploy a compute instance in Oracle Cloud Infrastructure (OCI), where we will install MySQL Shell and WordPress. This compute instance will be set up in the public subnet of our Virtual Cloud Network (VCN).
Additionally, we will create a MySQL HeatWave instance in OCI, which can only be deployed in a private subnet.
These steps are easy.
We need to note the IP of our MySQL HeatWave instance:
Object Store Bucket
We plan to replicate our data from the HeatWave instance in Amazon to our new instance in OCI. To achieve this, we will copy the current database content into the new OCI instance. For this operation, we will use the MySQL Dump & Load utility and transfer the data via a bucket in the Object Store.
We need then to create this bucket and create a PAR to access it:
When the bucket is created, we can create the Pre-Authenticated Request (PAR):
We will get the PAR Url and a dedicated endpoint, I recommend to use the later in our future requests:
Now it’s important to not take the dump and load it immediately before the connection between OCI and AWS is ready as most probably, we have not changed the default value of binlog_expire_logs_seconds (1h).
Amazon Load Balancer
We will utilize a load balancer to access our MySQL HeatWave instance hosted on AWS. Only our servers within the OCI VCN will be permitted to connect to this load balancer to access the database. We will also set up replication from MySQL HeatWave on AWS to MySQL HeatWave on OCI through the load balancer.
I could have also created a public available MySQL HeatWave instance in AWS, but it was not the case with our instance.
Let’s have a look at the target group we have created where only one instance is available as target:
The IP 172.31.88.135 is the IP of or private link.
We also need to allow access from our OCI NAT Gateway:
Dump & Load
We can now use the compute instance (EC2) we have on AWS to dump the database to our Object Store in OCI using the PAR:
We can verify that the dump is stored in the Object Storage Bucket:
This time we connect on the Compute Instance on OCI and we load the dump:
Please note that we use ignoreVersion: “true”.
Replication Channel
Before being able to replicate, we need to create a dedicated user. So, on the MySQL HeatWave instance in AWS we do:
SQL> CREATE USER repl_oci IDENTIFIED BY '<password>' REQUIRE SSL;
SQL> GRANT REPLICATION SLAVE ON *.* TO 'repl_oci';We also need to set the GTID_PURGED value from the dump we have, we need to check in the file @.json the value of gtid_executed:
"gtid_executed": "39393bac-f923-11ef-9862-12d206611f57:1-4326",
"gtid_executed_compression_period": "0",
"gtid_mode": "ON",
"gtid_owned": "",
"gtid_purged": "39393bac-f923-11ef-9862-12d206611f57:1-4314",Depending on the value of the variable gtid_executed in the MySQL HeatWave instance on OCI, we use the sys.set_gtid_purged procedure to include the transactions that are part of the dump.
SQL> call sys.set_gtid_purged("+39393bac-f923-11ef-9862-12d206611f57:1-4326");Mind the + sign.
Next, we can create a replication channel for our MySQL HeatWave instance in OCI:
We source’s hostname, we use the load balancer’s DNS name.
We also use SSL and GTIDs:
As Target DB System, we use our MySQL HeatWave Instance:
And our channel will become green and active:
In case of issue, please check this post.
We can verify using SHOW REPLICA STATUS\G
MySQL 10.0.1.95:3306 ssl mds SQL > show replica status\G
*************************** 1. row ***************************
Replica_IO_State: Waiting for source to send event
Source_Host: hw-lb-883424b79b9b7f6e.elb.us-east-1.amazonaws.com
Source_User: repl_oci
Source_Port: 3306
Connect_Retry: 60
Source_Log_File: binary-log.004285
Read_Source_Log_Pos: 95674
Relay_Log_File: relay-log-replication_channel.007022
Relay_Log_Pos: 497
Relay_Source_Log_File: binary-log.004285
Replica_IO_Running: Yes
Replica_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Source_Log_Pos: 95674
Relay_Log_Space: 1061
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Source_SSL_Allowed: Yes
Source_SSL_CA_File:
Source_SSL_CA_Path:
Source_SSL_Cert:
Source_SSL_Cipher:
Source_SSL_Key:
Seconds_Behind_Source: 0
Source_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Source_Server_Id: 3207886271
Source_UUID: 39393bac-f923-11ef-9862-12d206611f57
Source_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Replica_SQL_Running_State: Replica has read all relay log; waiting for more updates
Source_Retry_Count: 0
Source_Bind: 10.0.1.95
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Source_SSL_Crl:
Source_SSL_Crlpath:
Retrieved_Gtid_Set: 39393bac-f923-11ef-9862-12d206611f57:586-672:701-785:787-4338
Executed_Gtid_Set: 39393bac-f923-11ef-9862-12d206611f57:1-4338,
f04fd644-fb20-11ef-af0d-020017131a4b:1-3148
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name: replication_channel
Source_TLS_Version: TLSv1.2,TLSv1.3
Source_public_key_path:
Get_Source_public_key: 1
Network_Namespace: mysql
1 row in set (0.0008 sec)WordPress
Every time we access WordPress, it makes entries in the database. Our database architecture does not permit any writing in Oracle Cloud Infrastructure (OCI) because this is an asynchronous replication setup and not bidirectional, such architecture should be strictly prohibited!
If we plan to host a WordPress site in OCI, we must ensure that all write operations are directed to AWS. To achieve this, we should utilize a plugin like LudicrousDB.
Check this post to see how to configure LudicrouDB.
The only setup we need to do is to allow our Compute Instance in OCI to connect to our MySQL HeatWave instance in AWS.
We then add it’s public IP to the Security Group:
When done, we can access our WordPress on OCI in Read/Only (and all writes are sent to MySQL HeatWave in AWS):
And in AWS in read/write mode:
Conclusion
In this post, we explored how to connect to MySQL HeatWave instances across different clouds and replicate data from one instance to another. Additionally, we examined how to connect an application to various database instances hosted by different cloud providers.
MySQL HeatWave is part of the Oracle Multicloud offer, so the choice is easy if you need a MySQL DBaaS developed and managed by the MySQL Team!
