Live Migration from Azure Database for MySQL to MySQL Database Service on OCI

MySQL Database Service on OCI is all over the tech news with the latest HeatWave ML announcement [1][2][3][4][5].

This article explains how to perform a live migration of your Azure/MySQL to OCI/MySQL.

As Azure allows public connections directly to the database, this is what I will use to migrate and replicate the data between both clouds.

As this content is focused on the data migration, I won’t explore all the network possibilities to connect Azure and OCI like VPNs in this blog post. But of course this is a recommended method, certainly if you plan to keep the connection between both systems for a longer period.

Architecture

Above, you can see an illustration of the architecture and the process to migrate the data:

1. we prepare Azure Database for MySQL to use GTIDs
2. we use a Compute Instance on OCI where we have installed MySQL Shell
3. we configure OCI’s config file on the Compute Instance (to access Object Storage)
4. we open Azure firewall to allow connections from our Compute Instance’s public IP
5. we dump the data to Object Storage
6. we create a new MySQL Database Service Instance (with HeatWave) and we use the dump in Object Storage as initial data

When the MDS Instance is running, we need to create a replication channel:

To create such asynchronous replication link, we need to perform the following operations:

1. create a dedicated user for replication on Azure Database for MySQL
2. open the firewall on Azure to allow connection from our OCI NAT Gateway’s public IP
3. create a replication channel on MySQL Database Service Instance
4. fix some eventual issues due to inconsistent dump (more on this later)

Preparing Azure Database for MySQL

The Azure Database for MySQL I am using is MySQL Flexible server 8.0 (8.0.21).

As we want to keep or Azure Database for MySQL available during the migration (and maybe after while we perform tests on OCI), we will need to use MySQL Replication between Azure Cloud and Oracle Cloud Infrastructure.

By default, Azure Database for MySQL is not using GTIDs, we need then to enable them:

We need to use Azure’s web console and deploy the changes step-by-step:

And save the change:

After having deployed the change og enforce_gtid_consistency, we need to change the gtid_mode 3 times to enable it without restarting the system:

1. OFF_PERMISSIVE
2. ON_PERMISSIVE
3. ON

You must do the change one by one and save them each time. You are not allowed to go from gtid_mode=OFF to gtid_mode=ON directly. If you do, it will fail.

And finally, we need to create a dedicated user for the replication to OCI:

CREATE USER 'repl_oci'@'%' 
     IDENTIFIED BY 'C0mpl1c4t3d!Passw0rd' REQUIRE SSL;
GRANT REPLICATION SLAVE ON *.* TO 'repl_oci'@'%';   

If we don’t want to use the wildcard (%) as host, we can use the public IP of OCI VCN’s NAT Gateway. As we use the Azure Firewall, this is less important and will allow us to eventually replicate to other region with the same credentials if we want.

OCI Compute Instance

In this article I don’t cover the OCI account and VCN creation, please check previous posts or the OCI manual if you don’t know how to do it.[1][2] I assume you already have a VCN with two subnets.

In the public subnet we need to deploy a compute instance that will be used to dump the data to Object Storage. The same instance can be used to load the data in case you have already an existing MDS instance you want to use. If not, the load will be performed at the instance’s creation using the optimized Data Import feature.

If you prefer, it’s also possible to use MySQL Shell on a compute instance deployed on Azure Cloud as mentioned in this blog post by Chandan.

I use an always Free Ampere shape that is powerful and with multiple cores to benefit from MySQL Shell Dump & Load parallelism.

Then on that instance, install MySQL Shell ( sudo dnf install -y mysql-shell).

Before being able to connect to the Azure Database for MySQL instance, we need to open the firewall in Azure Cloud to allow connections from our OCI Compute Instance’s public IP:

We can test that we have access from our OCI Compute Instance MySQL Shell to Azure Database for MySQL:

Object Storage Bucket

On OCI console, we need to create a bucket to receive the dump:

On the Compute Instance we also need an oci config file that MySQL Shell will use to store the data in Object Storage.

We can create this config file from OCI Dashboard for our user (Identity -> User -> User Details):

We need to download the keys if we choose to generate them and copy the content of the config in ~/.oci/config. We need to set the private key’s location and filename:

This is the output example:

And we can already create a Pre-Authenticated Request URL to use later when we will restore/load the data to the MySQL Database Service (if we don’t do it, we can still create it later):

It’s important to Enable Object Listing feature.

Copy and save the generated URL for later.

Dumping the data with MySQL Shell

We are now ready to dump the data from Azure to Object Storage on OCI:

Some options are mandatory to dump the data to Object Storage correctly:

• osBuckeName: is the name of the Object Storage Bucket
• osNamespace: is the option to identify the namespace for the bucket
• ocimds: enables checks and modifications for compatibility with MySQL Database Service
• consistent: when enabled, consistent data dumps by locking the instance for backup during the dump. LOCK INSTANCE FOR BACKUP cannot be executed on Azure Database for MySQL as the admin user is missing the BACKUP_ADMIN privilege. This will change with the next version of MySQL Shell.
• ociParManifest: generates a PAR for read access, only for MySQL Shell < 8.0.27

If you can stop the load on Azure Database for MySQL, it will be easier to start replication later, if not, more steps to fix some issues will be required.

MySQL Database Service Instance’s creation

It’s time to create our MySQL Database in OCI and load the initial data during the creation using the initial Data Import feature:

And we paste the PAR url we saved earlier or if we have not, we can just click to create a new one:

When the MDS Instance is deployed, we can connect to it and the initial data will be already imported. We can also check the value of the GTID executed:

We can already notice that the GTID executed are equal than the value when the dump started on Azure Database for MySQL. Of course we noticed that when the dump ended as there was still load, the value was higher and some of these transactions are already present in the loaded data.

If there was not load on the source database during the dump, there would not have been any difference (or very small if Azure’s internal monitoring would have created some write operations).

Replication Channel

Before creating the channel, we will allow the connection from our OCI VCN’s NAT gateway to Azure:

And we create the Replication Channel using the credentials we created earlier:

If there was no write load, the channel will be active and we are done:

If there was load and the dump was inconsistent with a GTID difference between the start and the end of the dump operation, the channel will turn orange.

As reminder the difference from the dump was:

from: 'c0e015ea-b3fe-11ec-8f8d-000d3a65c2a5:1-48667' 
  to: 'c0e015ea-b3fe-11ec-8f8d-000d3a65c2a5:1-48797'

If this is the case, it means that the replication stopped.

If we have a consistent backup, replication may also stop (see section Extra).

To illustrate this, I’m using some procedures I created in this article.

This means that the transaction c0e015ea-b3fe-11ec-8f8d-000d3a65c2a5:1-48668 was already applied and we need to skip it.

We will add it to the GTIDs already PURGED:

call sys.set_gtid_purged("+c0e015ea-b3fe-11ec-8f8d-000d3a65c2a5:48668");

And we need then ro resume the replication channel from OCI console:

We need to repeat those operations for all the transactions that fail between that GTID range.

We could directly use the latest GTID value from the dump but we could also loose data if for example one of these transaction we skip belongs to a table that was already dumped before the end of the dump process. For consistency it’s not recommended to skip them all at once, but to proceed step by step.

For example the GTID sequence 48698 should be skipped:

When done, there won’t be error anymore and the status will be ON:

Extra

Azure is also adding extra non-standard tables in mysql schema. The mysql schema is protected in MDS and doesn’t allow user changes. So, when those tables are populated in Azure and written to the binary log, replication will break:

SQL> show tables like '__az%';
+----------------------------------+
| Tables_in_mysql (__az%)          |
+----------------------------------+
| __az_action_history__            |
| __az_changed_static_configs__    |
| __az_replica_information__       |
| __az_replication_current_state__ |
+----------------------------------+

On the system I tested, only the table __az_replica_information__ was populated but fortunately, the changes were not written to the binary logs:

SQL> select * from __az_replica_information__\G
*************************** 1. row ***************************
             USER: azure_backup_user
  REPL_THUMBPRINT: 
  BINARY_LOG_FILE: mysql-bin.000005
EXECUTED_GTID_SET: NULL
     UPDATED_TIME: 2022-04-06 06:34:27

If the Azure database for MySQL is a replica, it seems that some changes are written into the binlogs and this will break the replication way to often. It’s recommended to use the main/first source to replicate to MDS directly.

Conclusion

Migrating data from Azure Cloud to OCI is not complicated. However if you want to replicate the data and you cannot stop writing on the source during the process, you will need some time to fix some replication issues with the current version of MySQL Shell. But once done, the replication will run without any problems other than the writes in the special tables in mysql schema (__az%__) if they are replicated (I was not able to find much info related to these tables).

Enjoy MySQL, enjoy MDS and enjoy your live migration to OCI !