Note:

Migrate Google Cloud Platform CloudSQL for MySQL to Oracle Cloud Infrastructure MySQL Heatwave using Replication

Introduction

This tutorial explains about how to perform live migration of your Google Cloud Platform CloudSQL for MySQL to Oracle Cloud Infrastructure (OCI) MySQL Heatwave Database Service. With help of this article, we will be able to understand how we can use MySQL Replication between two different cloud providers and this will act as your document for successful migration to OCI Heatwave. In this tutorial, we will make use of following products :-

  1. GCP CloudSQL for MySQL
  2. MySQL Heatwave for Online Transaction Processing (OLTP)
  3. OCI Object Storage
  4. OCI Compute as Bastion
  5. MySQL Shell

Objectives

Connect Google Cloud Platform CloudSQL for MySQL to MySQL Heatwave Database Service and then start inbound replication to OCI DB System. We will cover the high level overview of connectivity between Google Cloud Platform and MySQL but detailed network possibilities between Google Cloud Platform CloudSQL and MySQL Heatwave can be referenced in official documentation.

Architecture

Below is a sample high level architecture of the solution.

Image main

The dotted line represents the flow of the data i.e. from GCP’s Cloud SQL for MySQL to Object storage for initial data set. Once the data is available in object storage, we will further use replication channels offered with OCI MySQL Heatwave DB System.

Prerequisites

As a part of this tutorial, we will be using Inbound Replication. With help of Inbound Replication, we use replication channel configured in MySQL Heatwave Database Service and with that we can send transactions from one location to another. Our sources can be different i.e. On-premises, Amazon Web Services (AWS), Microsoft Azure, Google Cloud Platform, and so on. In this tutorial, we are focusing on Google Cloud Platform Cloud SQL for MySQL and our target is MySQL Heatwave Database Service.

You can read more about the overview of MySQL Inbound Replication here. Keep this tutorial handy as it also provides detailed description on source configuration, creating replication user, creating a replication channel and it’s limitations.

We are dividing the whole process for migration into the following high level steps.

  1. Access to Existing Google Cloud Platform’s Cloud SQL for MySQL
  2. Preparation of Google Cloud Platform’s Cloud SQL for MySQL
  3. Bastion on OCI with MySQL Shell
  4. Networking Consideration between Google Cloud Platform Cloud SQL for MySQL and OCI Heatwave DB System
  5. Configuration of Object Storage Bucket on OCI
  6. Exporting data to OCI Object Storage
  7. Creation of new DB system on OCI
  8. Creating Replication Channel on OCI

Task 1: Access the Existing Google Cloud Platform Cloud SQL for MySQL

We are using the sample database hosted on Google Cloud Platform’s Cloud SQL for MySQL for our migration purpose. Existing MySQL Instance is hosted on public subnet which Google Cloud Platform allows, hence we are using that as our source database. Let’s assume Google Cloud Platform CloudSQL for MySQL is running enterprise edition with MySQL Version 8.0.

Image 1

Image 2

Once we know the public IP, we can whitelist our IP address and can easily connect with MySQL client. You can also use MySQL shell or gcloud utility to connect to the database. We can do the whitelisting of our public ip by adding in the networking section for Google Cloud Platform Cloud SQL for MySQL and click Save changes.

Image 3

Note: There are other IP’s which are added here as well, we will talk about them in the later sections.

Once we are able to connect, we can query the existing databases in the Google Cloud Platform CloudSQL for MySQL instance. For our migration purpose, we will only focus on sample database Sakila.

Image 4

Task 2: Prepare Google Cloud Platform Cloud SQL for MySQL

As we are using inbound replication, we need to perform two steps on the source server. We have to create an user that will be used for replication and also we need to ensure that we have GTID’s enable on our source server. With GTID’s ( Global Transaction Identifier), every transaction can be identified and tracked as it is committed on the originating source server. The same transaction then gets applied to target replica.

  1. Create Replication user and assign roles using the following commands.

    mysql> create user repl@'%' IDENTIFIED BY 'yourreplpassword' REQUIRE SSL;
mysql> grant replication slave on *.* to repl@'%';
mysql> select user from mysql.user;;

  2. Ensure GTID’s are enabled. By default GTIDs are enabled but in case they are not, you can enable them.

    mysql>show global variables like 'gtid%';

    Image 5

Task 3: Deploy Bastion on OCI with MySQL Shell

With Oracle Cloud Infrastructure, MySQL DB system endpoints are not directly accessible from the internet. It is due to enhanced security and it gets deployed with the help of private endpoint under the configured VCN. As a result, we need to connect the OCI MySQL Heatwave Database Service using a compute instance, VPN, Bastion or network load balancer to access the private endpoint.

You can choose any of the options mentioned above. In our case, we will use the compute instance that will act as Bastion for our MySQL Heatwave Database Service. How to use rest of the options, can be found here.

As we are using compute instance, we need to deploy the compute in the public subnet of our VCN whose private subnet will host MySQL Heatwave Database Service. While configuring the compute instance, you don’t need to worry about the resource configuration for the compute as it will only be used for installing MySQL shell or MySQL client and for connecting to Google Cloud Platform Cloud SQL for MySQL.

How to create a compute instance, can be found here.

As you can see in the below image, we have created the compute instance in the public subnet of VCN under which MySQL Heatwave Database Service will be hosted. This Bastion server will act as an intermediate between OCI and Google Cloud Platform, where we can access both the MySQL instances. In order to connect to the MySQL database instance, we can either use MySQL shell or MySQL client.

Image 6

Let’s install Mysql shell by running the following command.

`sudo yum install mysql-shell`

You can also refer to the documentation for more information.

Task 4: Verify Networking Considerations between Cloud SQL for MySQL and OCI MySQL Heatwave Database Service

One important thing here is to ensure that communication is successfully established between source and target server.

On Google Cloud Platform console, we need to ensure that public IP for our Bastion instance is whitelisted as well. This can be achieved using the same process which we followed above for connecting database with our MySQL Client.

On OCI console, communication needs to be established between Bastion compute instance which is hosted on public subnet of MySQLVCN and MySQL Heatwave Database Service hosted on private subnet within same VCN. We have to allow the traffic and we can open ingress rules with in Private subnet and allow connections.

Task 5: Configure Object Storage Bucket on OCI

We need to configure object storage on OCI for storing initial data dump from Google Cloud Platform CloudSQL for MySQL. This will be a one time activity and this data will be used to create the database on MySQL Heatwave Database Service.

For information about creation of bucket on OCI, see Creating a Bucket.

Image 7

Once the above bucket has been created, we need to establish link between object storage bucket and Bastion instance. For this purpose, we will use oci config file that will be used by MySQL Shell.

Note: The key_file parameter must be updated with the location of your private key generated on the OCI console during API key creation.

Task 6: Export data to OCI Object Storage

We will use the MySQL Shell utility on the Bastion server to export data from Google Cloud Platform’s cloud SQL for MySQL. The MySQL shell utility is a very powerful tool that helps to export whole instance at once and provide various options.

For this exercise, we will export the schema from Google Cloud Platform’s CloudSQL for Mysql database to our OCI object storage.

Let’s first connect to MySQL instance from our Bastion compute.

Image 12

util.dumpSchemas(["sakila"], "", { osBucketName: "MysqlBucket", osNamespace: "orasenatdpltintegration01", ocimds: true, ociParManifest: true, threads: 8, compatibility: ["force_innodb", "skip_invalid_accounts", "strip_definers", "strip_restricted_grants",  "strip_tablespaces"], dryRun: "true"})

The rest of the options can be used based on your requirement and more information can be found here.

Image 13

Once dry run is completed, examine the output and resolve issues. If we don’t have any compatibility issues, run without dryRun as below :-

Image 14

It provides you detailed information about the export activity and also provides the time duration. As it is sample database, with around 45k records, it finished quickly.

Once the process is successful, your data will now be present in your bucket that is part of your object storage.

Image 15

Task 7: Create a new DB system on OCI

For more details about how to configure the MySQL DB system, see Creating a DB System.

The only thing that we need to keep in mind is as we already have data in object storage, therefore let’s use that in order to configure the DB system. You can load the data later as well but this option saves a lot of time. For using this option, we need to create a PAR request.

Image 16

Image 17

Ensure you have saved the URL generated here as this will be used as below.

Image 18

Once you specify your PAR URL, the MySQL DB system gets automatically created with Sakila database.

To validate, run the following command.

Image 19

With this, we now have source database in target DB system. Now, we will create replication between source and target.

Task 8: Create a Replication Channel on OCI

Replication channels are used to connect your source database system to the target database system. All transactions are copied with the help of these channels. The creation of these channels is fairly easy as we just need to provide few details about our source and target. It is always recommended to use encrypted connection and that is why when we created the user, we specified the keyword SSL in earlier steps.

As the replication source could be different, we also allow channel filters that varies for different public cloud providers. More information, can be found here.

The image below shows different options that are present when you create Replication channel on OCI. We need to provide the source host name or public ip address along with port no. We are going to authenticate with help of Repl user that we created earlier.

Image 20

Image 21

We also specify the target db system that we have created for our replication and with replication filters we provide an option where you choose your replication filters from the drop-down menu as applicable.

If there is no further load on the source system post our export, the replication channel should activate and its completed. However, there are chances that the load is inconsistent due to GTID mismatch and necessary actions should be taken. There are different stages of replication channel, their details are mentioned here.

For details about the most common error messages that we may encounter during inbound replication configuration, see the troubleshooting guide.

Insert dummy data on Google Cloud Platform source as shown in the following image.

Image 21

Query the sample database on MySQL Heatwave Database Service.

Image 22

In the console, we can see that there is zero lag between the source and target and transactions are getting copied and committed. Hence, we can use replication channels for both disaster recovery and migration purpose. Based on proper planning and application downtime, we can perform cutover activity and use the MySQL Heatwave Database service that is your one stop solution for OLTP transactions and analytical workloads.

Next Steps

In this tutorial, we saw OCI MySQL Heatwave Database Service provide a fairly easy mechanism of using inbound replication. It supports multiple sources and even provides replication channel filters with respect to these sources. Inbound Replication can be used to bring you dataset to Oracle Cloud and use it’s heatwave offering. Not only this, MySQL Heatwave Lakehouse has been introduced as well where you can bring huge loads of data in terabytes in multiple formats. You can find more information on MySQL Heatwave Database Service and how it maximizes the query performance by providing In-Memory Query Accelerator.

Acknowledgments

More Learning Resources

Explore other labs on docs.oracle.com/learn or access more free learning content on the Oracle Learning YouTube channel. Additionally, visit education.oracle.com/learning-explorer to become an Oracle Learning Explorer.

For product documentation, visit Oracle Help Center.