Replicate from MySQL 5.7 to MySQL Database Service
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:
- 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.90Note:
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 --sqlSQL> 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.90connect to the MDS instance (10.0.1.23):
$ mysqlsh mdsAdmin@10.0.1.23Then 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_channelLast 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
- MySQL Database Service – Inbound Replication
- MySQL Shell Utilities
- Oracle Cloud Infrastructure Free Tier
- Oracle Cloud Infrastructure (OCI)
- Overview of Object Storage
- MySQL Replication
Watch my videos on my YouTube channel and subscribe.
Thanks for using HeatWave & MySQL!
