About SRGTechnical articleRecruitment Information
About SRGTechnical articleRecruitment Information
HOME/Technical article/Perform MySQL replication between an on-premises environment and Amazon Aurora over the internet.

Perform MySQL replication between an on-premises environment and Amazon Aurora over the internet.

This is Onikai (@fat47) from the Service Reliability Group (SRG) of the Technology Division.
#SRGThe Service Reliability Group primarily provides comprehensive support for the infrastructure surrounding our media services, focusing on improving existing services, launching new ones, and contributing to open-source software (OSS).
This article is located within SRG.DBWGThe DB Working Group is releasing database-related materials that it provides to the entire company.
I hope this is of some help.
 

overview

When migrating from an on-premises environment (including a private cloud) to an AWS environment, you may need to use MySQL replication to migrate your database.
In that case, you might consider using AWS Direct Connect to build a dedicated network between your on-premises environment and AWS.
This document describes how to securely set up MySQL replication over the internet without using Direct Connect.
 

Verification environment

Source information

Server OS: CentOS 7
Database: MySQL 5.7.41

Destination

AWS Aurora
 
We will set up an environment where the source MySQL is the master and Aurora is the slave.

Preparation on the destination (AWS)

Create an S3 bucket for backups.

Let's create an S3 bucket in the target AWS account to store the database backups.
 

Creating a DB cluster parameter group

ROW

Preparation at the source (on-premises)

Full backup using XtraBackup and uploading to S3

Use Percona's XtraBackup to create a full backup and upload it to S3.
The reason for not using mysqldump for full backups is that it takes too long when the data size is large.
 
Installing XtraBackup
 
Execute XtraBackup
Since we are uploading to S3 using the AWS CLI, please ensure that you are logged into the target AWS environment via the AWS CLI.
For other specified options, please refer to the official documentation below.
https://docs.percona.com/percona-xtrabackup/2.4/xtrabackup_bin/xbk_option_reference.html

Make the source server accessible via the internet.

You need to configure your on-premises environment so that it can be accessed from the internet.
Please configure your load balancer and firewall settings in your on-premises environment.
The firewall settings require you to specify and allow the global IP address of AWS Aurora,
This is created after the Aurora cluster is created, so we'll deal with it later.
After the cluster is created, check the IP address of the writer instance's endpoint.
example)
 

Create a self-signed certificate and apply it to the MySQL server.

We will create a self-signed certificate to enable TLS communication between the on-premises MySQL environment and Aurora.
When creating CA/server/client keys, you will be asked for a CN (Customer Name), and you should specify a different CN for each. You will be asked several other questions, but you can just press Enter without entering anything.
 
The certificate file is loaded in my.cnf.
 
MySQL restart
 
We will check if the certificate is being read.
If have_openssl is set to YES, then it's OK.

Create a user for replication

Create a replication user. While the sample uses a percentage (%), it's recommended to properly specify the IP address.

The process for setting up replication on the destination (AWS)

Create a cluster from S3 backup data

You can also create it using the AWS CLI, but this time we'll try creating it from the console.
Open AWS RDS and select "Restore from S3" at the bottom of the screen.
 
Select the bucket containing your backup data. In this example, it's "kikai-test".
You will also need to select the version of Aurora. Here, we will select Aurora 2.11.2.
We have also set up automatic creation of an IAM role that allows RDS to access the S3 bucket.
Once you have entered the necessary information, press "Create Cluster".
 

Confirm the position where replication will begin.

Once the cluster is created, click on it to open it and check the "Recent Events" section in the "Logs and Events" tab.
Here you can check the binlog file name and position when the backup was taken, so make a note of it.

Loading certificate files in the created cluster

Import the certificate file into the cluster.
Let's load the certificate file created in the previous step into the Aurora cluster.
 
Write down the contents of the certificate file in a memo.
https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/mysql-stored-proc-replicating.html#mysql_rds_import_binlog_ssl_material
 
Connect to the cluster and load the certificate shown above.
 

Start replication between the created cluster and on-premises.

Specify the position you noted down and set up replication with the on-premises side.
The last number, 1, is required because it means enabling TLS connection.
 
Start replication and check the status.
 
With this, we were able to successfully establish replication between our on-premises system and Aurora.

In conclusion

We were able to establish replication over the internet without using Direct Connect.
Using replication during an AWS migration offers many advantages, so it's a good idea to practice it so you're ready when the time comes.
 
Furthermore, I plan to write a separate article about reverse replication (Aurora → on-premises) in the context of rollback using this configuration.
 
SRG is looking for new team members. If you are interested, please contact us here.