Replicate from MySQL 5.7 to MySQL Database Service

January 26, 2021
Cloud from above by Olivier DASINI

MySQL Replication is a very common topology, widely used in various architecture.
People use it, among others, for High Availability, Read Scalability or Geographic Redundancy.

Another use case is to use MySQL Replication to seamlessly integrate a newer version of the server in your architecture.
Let’s say you are running MySQL 5.7 then you can easily setup a 8.0 instance as a replica of your 5.7.

Extending this idea it is also possible to replicate your MySQL 5.7 (or 8.0 obviously) to a MySQL Database Service (MDS) instance, the true MySQL PaaS on Oracle Cloud Infrastructure (OCI).

This is that story you are about to read now 🙂

Architecture

To make things clear, this is what the (final) architecture looks like:

  • An OCI Compute instance where MySQL 5.7.33 & MySQL Shell 8.0.23 are installed
    • Public IP : 123.45.678.90
    • Private IP : 10.0.0.9
  • A MDS instance
    • DB name : MDS2_FRA
    • Private IP : 10.0.1.23
  • A replication Channel
    • Channel name : mysqlchannel_MDS2_FRA
MySQL Replication topology from MySQL Enterprise Monitor
MySQL Replication topology from MySQL Enterprise Monitor

Plan

How to proceed?

Let’s split the problem in sub-parts:

  1. Create a compute & a MDS instances
  2. Create on the source a dedicated user to the replication
  3. Dump the MySQL instance running on the OCI compute for being migrated to MDS
  4. Load the dump in MDS
  5. Create a replication channel on MDS (from OCI to MDS)

Requirement

Before starting, please verify that your source follows the requirements.

Current limitations of MySQL Database Service Inbound Replication

  • Only Row-based replication supported
  • Only GTID-based replication is supported
  • Multi-source replication is not supported
  • Replication filters are not supported
  • Changes to the mysql schema are not replicated and cause replication to stop
  • Source and Replica must run with the same lower_case_table_names to avoid data transfer problems arising from letter case of database or table names in cross-platform topology
  • The inbound applier runs under the privileges of the DB System’s admin user

The full & up to date list is available here.

Create a compute & a MDS instances

The creation of thee 2 objects is out of the scope of this article.
You’ll find the relevant resources following these above links:

You can start using OCI for free : Oracle Cloud Infrastructure Free Tier

Create a MySQL DB System is very easy : Getting Started with MySQL Database Service

You also need to have a running compute instance attached to a public subnet on the same VCN as the MySQL DB System : Creating a Compute Instance

Indeed MySQL 5.7 and MySQL Shell 8.0 are installed on this compute instance : Installing MySQL Shell on Linux. (obviously it works also if you are on Windows).

Create on the source a dedicated user to the replication

Assuming the prerequisites above are fine and the security lists are updated, we can now proceed.

Connect by SSH, to the compute instance where MySQL 5.7 is installed:

Note:
If you are using Ubuntu (the case here) the user is ubuntu.
For others GNU Linux systems, the user should be opc.

Connect to the MySQL 5.7 instance using MySQL Shell 8.0.23:

Then create the replication user with his relevant privileges:

We can check the user:

Dump the MySQL instance running on OCI for being migrated to MDS

Now we want to put the current MySQL 5.7 data on the MDS instance.

In this context the best tools to do this job are MySQL Shell utilities.
We will use the instance dump utility.

The backup output can be stored on a Object Storage Bucket – I will write an article on that.
But today, let’s keep it simple. We’ll store the backup locally on the compute instance.

This strategy makes sense if you have enough free disk space on the compute. Also it will be faster to restore the data on MDS with this strategy than having the dump on the object storage.

A good practice when attempting to import data from MySQL 5.7 into MDS is to use MySQL Shell’s upgrade checker utility util.checkForServerUpgrade() to check the schemas and if needed proactively fix any compatibility issues identified.

We’re good to go, so let’s create the backup directory:

Then dump our MySQL 5.7 instance using util.dumpInstance() :

Please note ocimds & compatibility options.
Details are available in the documentation.

Load the dump in MDS

Now we must restore our dump.
We will use the Dump Loading Utility.

From the compute instance :

connect to the MDS instance (10.0.1.23):

Then load the MySQL 5.7 instance’s dump using util.loadDump() :

Please note the ignoreVersion & updateGtidSet options.
Documentation is available here.

Create a replication channel on MDS

Here is the easiest part.
Most of the work will be done using the OCI console \o/

The feature we are looking for is named Channel.
You can find it by clicking on “MySQL” then “Channel” and finally push the “Create Channel” button

Create Inbound MySQL Replication in MySQL Database Service in OCI

The following is pretty straightforward.
Below the main information:

Create in Compartment: Choose the right Compartment

Name (optional): ex. mysqlchannel_MDS2_FRA

Source Connection – Configure Connection to the MySQL Source
Hostname: 10.0.0.9

Username: rplAdmin
(from the replication user created on the source)

Password: Repl1234c@
(from the replication user created on the source)

SSL Mode: – set when create the replication user –
Required (REQUIRED)
Establish an encrypted connection.

Target – Configure the DB System target
Select a DB System: MDS2_FRA

Finally, push the Create Channel button

Create Inbound MySQL Replication in MySQL Database Service in OCI

et voilà!

Wait for the Channel icon become green (ACTIVE)…

MySQL Replication status from MySQL Enterprise Monitor
MySQL Replication status from MySQL Enterprise Monitor

In the mean time, if you have the soul of a DBA, you can use some commands, on the MDS instance.

SHOW REPLICA STATUS

Using the replication channel name (default: replication_channel) you can also use this query:

Last but not least, there is Lefred’s favorite replication query (c’est cadeau):

Péroraison

MySQL replication has always been easy to setup.
This is still true with MySQL 8.0.
This is even more true with MySQL Database Service 🙂

Easy, flexible and powerful. This is exactly what your application need.

References

Follow me on twitter

Watch my videos on my YouTube channel and subscribe.

My Slideshare account.

Thanks for using MySQL!

3 Responses to “Replicate from MySQL 5.7 to MySQL Database Service”

  1. […] ?? ? ? ?? ? ????.Ref link.dasini.net/replicate-mysql-database-service [Link]docs.oracle.com/replication [Link]docs.oracle.com/Replication/GUID-94149D15 […]

  2. […] until now we have seen MDS (MySQL Database Service) and MySQL in Azure. As the Cloud technology keeps moving fast, I thought […]

  3. […] Also for simplicity, I’m using MySQL 8.0. However, the following architecture is also relevant with MySQL 5.7.To understand how to setup a replication channel from MySQL 5.7 to MySQL Database Service, you can read : Replicate from MySQL 5.7 to MySQL Database Service. […]

Leave a Reply