Replicate from MySQL 5.7 to MySQL Database Service

January 26, 2021

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

Plan

How to proceed?

Let’s split the problem in sub-parts:

Create a compute & a MDS instances
Create on the source a dedicated user to the replication
Dump the MySQL instance running on the OCI compute for being migrated to MDS
Load the dump in MDS
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:

ssh -i id_rsa  ubuntu@123.45.678.90

1	ssh -i id_rsa ubuntu@123.45.678.90

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:

$ mysqlsh --version
mysqlsh   Ver 8.0.23 for Linux on x86_64 - for MySQL 8.0.23 (MySQL Community Server (GPL))

1 2	$ mysqlsh --version mysqlsh Ver 8.0.23 for Linux on x86_64 - for MySQL 8.0.23 (MySQL Community Server (GPL))

$ mysqlsh root@localhost:3306 --sql

1	$ mysqlsh root@localhost:3306 --sql

SQL> SELECT version();
+------------+
| version()  |
+------------+
| 5.7.33-log |
+------------+

SQL> SELECT version();

+------------+

| version() |

+------------+

| 5.7.33-log |

+------------+

Then create the replication user with his relevant privileges:

SQL> CREATE USER rplAdmin@'10.0.1.%' IDENTIFIED WITH 'mysql_native_password' BY 'Repl1234c@' REQUIRE SSL; 


SQL> GRANT REPLICATION SLAVE on *.* to rplAdmin@'10.0.1.%';

SQL> CREATE USER rplAdmin@'10.0.1.%' IDENTIFIED WITH 'mysql_native_password' BY 'Repl1234c@' REQUIRE SSL;

SQL> GRANT REPLICATION SLAVE on *.* to rplAdmin@'10.0.1.%';

We can check the user:

SQL> SELECT user, host, plugin FROM mysql.user WHERE user = 'rplAdmin';
+-----------+----------+-----------------------+
| user      | host     | plugin                |
+-----------+----------+-----------------------+
| rplAdmin  | 10.0.1.% | mysql_native_password |
+-----------+----------+-----------------------+


SQL> SHOW GRANTS FOR rplAdmin@'10.0.1.%';
+---------------------------------------------------------+
| Grants for rplAdmin@10.0.1.%                            |
+---------------------------------------------------------+
| GRANT REPLICATION SLAVE ON *.* TO 'rplAdmin'@'10.0.1.%' |
+---------------------------------------------------------+

SQL> SELECT user, host, plugin FROM mysql.user WHERE user = 'rplAdmin';

+-----------+----------+-----------------------+

| user | host | plugin |

+-----------+----------+-----------------------+

| rplAdmin | 10.0.1.% | mysql_native_password |

+-----------+----------+-----------------------+

SQL> SHOW GRANTS FOR rplAdmin@'10.0.1.%';

+---------------------------------------------------------+

| Grants for rplAdmin@10.0.1.% |

+---------------------------------------------------------+

| GRANT REPLICATION SLAVE ON *.* TO 'rplAdmin'@'10.0.1.%' |

+---------------------------------------------------------+

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.

SQL> \JS

JS> util.checkForServerUpgrade()

SQL> \JS

JS> util.checkForServerUpgrade()

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

$ mkdir -p /home/ubuntu/backup/

1	$ mkdir -p /home/ubuntu/backup/

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

JS> util.dumpInstance("/home/ubuntu/backup/20210117", {ocimds: true, threads: 8, showProgress: true, compatibility: ["force_innodb", "strip_definers", "strip_restricted_grants", "strip_tablespaces"]})

1	JS> util.dumpInstance("/home/ubuntu/backup/20210117", {ocimds: true, threads: 8, showProgress: true, compatibility: ["force_innodb", "strip_definers", "strip_restricted_grants", "strip_tablespaces"]})

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 :

$ ssh -i id_rsa  ubuntu@123.45.678.90

1	$ ssh -i id_rsa ubuntu@123.45.678.90

connect to the MDS instance (10.0.1.23):

$ mysqlsh mdsAdmin@10.0.1.23

1	$ mysqlsh mdsAdmin@10.0.1.23

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

JS> util.loadDump("/home/ubuntu/backup/20210117",{ignoreVersion: true, updateGtidSet: "replace", threads: 8, waitDumpTimeout: 1800})

1	JS> util.loadDump("/home/ubuntu/backup/20210117",{ignoreVersion: true, updateGtidSet: "replace", threads: 8, waitDumpTimeout: 1800})

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

et voilà!

Wait for the Channel icon become green (ACTIVE)…

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

SQL > SHOW REPLICA STATUS\G

*************************** 1. row ***************************
             Replica_IO_State: Waiting for master to send event
                  Source_Host: 10.0.0.9
                  Source_User: rplAdmin
                  Source_Port: 3306
                           ...
           Replica_IO_Running: Yes
          Replica_SQL_Running: Yes 
                           ...
           Source_SSL_Allowed: Yes
                           ...
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: replication_channel
           Source_TLS_Version: TLSv1.2,TLSv1.3
                           ...

SQL > SHOW REPLICA STATUS\G

*************************** 1. row ***************************

Replica_IO_State: Waiting for master to send event

Source_Host: 10.0.0.9

Source_User: rplAdmin

Source_Port: 3306

...

Replica_IO_Running: Yes

Replica_SQL_Running: Yes

...

Source_SSL_Allowed: Yes

...

Auto_Position: 1

Replicate_Rewrite_DB:

Channel_Name: replication_channel

Source_TLS_Version: TLSv1.2,TLSv1.3

...

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

SELECT 
   SERVICE_STATE, 
   HOST, 
   USER, 
   PORT,
   CHANNEL_NAME
FROM performance_schema.replication_connection_configuration 
   INNER JOIN performance_schema.replication_applier_status 
      USING (CHANNEL_NAME) 
WHERE CHANNEL_NAME = 'replication_channel'\G

*************************** 1. row ***************************
SERVICE_STATE: ON
         HOST: 10.0.0.9
         USER: rplAdmin
         PORT: 3306
 CHANNEL_NAME: replication_channel

SELECT

SERVICE_STATE,

HOST,

USER,

PORT,

CHANNEL_NAME

FROM performance_schema.replication_connection_configuration

INNER JOIN performance_schema.replication_applier_status

USING (CHANNEL_NAME)

WHERE CHANNEL_NAME = 'replication_channel'\G

*************************** 1. row ***************************

SERVICE_STATE: ON

HOST: 10.0.0.9

USER: rplAdmin

PORT: 3306

CHANNEL_NAME: replication_channel

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

SELECT
    concat(conn_status.channel_name, ' (', worker_id,')') AS channel,
    conn_status.service_state AS io_state,
    applier_status.service_state AS sql_state,
    format_pico_time(if(GTID_SUBTRACT(LAST_QUEUED_TRANSACTION, LAST_APPLIED_TRANSACTION) = "","0",
    abs(time_to_sec(if(time_to_sec(APPLYING_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP)=0,0,
    timediff(APPLYING_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP,now()))))) * 1000000000000) latency,
    format_pico_time((LAST_QUEUED_TRANSACTION_START_QUEUE_TIMESTAMP -
    LAST_QUEUED_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP) * 100000000000) transport_time,
    format_pico_time((LAST_QUEUED_TRANSACTION_END_QUEUE_TIMESTAMP -
    LAST_QUEUED_TRANSACTION_START_QUEUE_TIMESTAMP) * 1000000000000) time_to_relay_log,
    format_pico_time((LAST_APPLIED_TRANSACTION_END_APPLY_TIMESTAMP -
    LAST_APPLIED_TRANSACTION_START_APPLY_TIMESTAMP) * 1000000000000) apply_time,
    conn_status.LAST_QUEUED_TRANSACTION AS last_queued_transaction,
    applier_status.LAST_APPLIED_TRANSACTION AS last_applied_transaction
FROM
    performance_schema.replication_connection_status AS conn_status JOIN performance_schema.replication_applier_status_by_worker AS applier_status 
        ON applier_status.channel_name = conn_status.channel_name
;

SELECT

concat(conn_status.channel_name, ' (', worker_id,')') AS channel,

conn_status.service_state AS io_state,

applier_status.service_state AS sql_state,

format_pico_time(if(GTID_SUBTRACT(LAST_QUEUED_TRANSACTION, LAST_APPLIED_TRANSACTION) = "","0",

abs(time_to_sec(if(time_to_sec(APPLYING_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP)=0,0,

timediff(APPLYING_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP,now()))))) * 1000000000000) latency,

format_pico_time((LAST_QUEUED_TRANSACTION_START_QUEUE_TIMESTAMP -

LAST_QUEUED_TRANSACTION_ORIGINAL_COMMIT_TIMESTAMP) * 100000000000) transport_time,

format_pico_time((LAST_QUEUED_TRANSACTION_END_QUEUE_TIMESTAMP -

LAST_QUEUED_TRANSACTION_START_QUEUE_TIMESTAMP) * 1000000000000) time_to_relay_log,

format_pico_time((LAST_APPLIED_TRANSACTION_END_APPLY_TIMESTAMP -

LAST_APPLIED_TRANSACTION_START_APPLY_TIMESTAMP) * 1000000000000) apply_time,

conn_status.LAST_QUEUED_TRANSACTION AS last_queued_transaction,

applier_status.LAST_APPLIED_TRANSACTION AS last_applied_transaction

FROM

performance_schema.replication_connection_status AS conn_status JOIN performance_schema.replication_applier_status_by_worker AS applier_status

ON applier_status.channel_name = conn_status.channel_name

;

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.

My Speaker Deck account.

Thanks for using MySQL!

Olivier DASINI

MySQL Geek, author, blogger and speaker
I’m an insatiable hunger of learning.
—–

Blog: www.dasini.net/blog/en/
Twitter: https://twitter.com/freshdaz
SlideShare: www.slideshare.net/freshdaz
Youtube: https://www.youtube.com/channel/UC12TulyJsJZHoCmby3Nm3WQ
—–

Filed under:
MySQL by Olivier DASINI

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

??? ???? MySQL Database Service (4) - MDS - Oracle Cloud | Hoing

March 7th, 2021 at 17:00

[…] ?? ? ? ?? ? ????.Ref link.dasini.net/replicate-mysql-database-service [Link]docs.oracle.com/replication [Link]docs.oracle.com/Replication/GUID-94149D15 […]
MySQL in Amazon (RDS) | dasini.net - Diary of a MySQL experts

March 16th, 2021 at 10:35

[…] until now we have seen MDS (MySQL Database Service) and MySQL in Azure. As the Cloud technology keeps moving fast, I thought […]
HeatWave – A MySQL cloud feature to speed up your queries | dasini.net - Diary of a MySQL experts

April 13th, 2021 at 9:01

[…] 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. […]

dasini.net – Diary of a MySQL expert