dasini.net – Diary of a MySQL expert

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

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:

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))

$ mysqlsh root@localhost:3306 --sql

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.%';

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.%' |
+---------------------------------------------------------+

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()

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

$ 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"]})

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

connect to the MDS instance (10.0.1.23):

$ 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})

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

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)…

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
                           ...

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

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
;

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

• MySQL Database Service
• Getting Started with MySQL Database Service
  • Options to Run MySQL Server in OCI (and use MySQL Analytics Service)
  • Migrate from on premise MySQL to MySQL Database Service
• MySQL Database Service – Inbound Replication
• MySQL Shell Utilities
• Oracle Cloud Infrastructure Free Tier
• Oracle Cloud Infrastructure (OCI)
• Overview of Object Storage
• MySQL Replication

Follow me on Linkedin

Watch my videos on my YouTube channel and subscribe.

My Slideshare account.

My Speaker Deck account.

Thanks for using HeatWave & MySQL!

Filed under:
MySQL by Olivier DASINI