About SRGTechnical ArticlesRecruitment Information
About SRGTechnical ArticlesRecruitment Information
HOME/Technical Articles/MySQL replication between on-premise environment and Amazon Aurora via the internet

MySQL replication between on-premise environment and Amazon Aurora via the internet

This is Oniumi (@fat47) from the Service Reliability Group (SRG) of the Technology Headquarters.
#SRG(Service Reliability Group) is a group that mainly provides cross-sectional support for the infrastructure of our media services, improving existing services, launching new ones, and contributing to OSS.
This article is located in SRGDBWGWe will publish materials regarding the database provided by the DB Working Group for the entire company.
I hope this helps in some way.
 

overview

When migrating from an on-premises environment (including private cloud) to an AWS environment, you may need to migrate your database using MySQL replication.
In that case, you may want to use AWS Direct Connect, which creates a dedicated network between your on-premise environment and AWS.
This document describes how to securely configure MySQL replication over the Internet without using Direct Connect.
 

Verification environment

Source information

Server OS: CentOS7
Database: MySQL5.7.41

Destination

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

Preparation at the destination (AWS)

Create an S3 bucket for backups

In the destination AWS account, create an S3 bucket to store the DB backup.
 

Creating a DB cluster parameter group

ROW

Preparation at the source (on-premises)

Taking a full backup with XtraBackup and uploading it to S3

Use Percona's XtraBackup to take a full backup and upload it to S3.
The reason we don't use mysqldump for full backups is because it takes too long when the data size is large.
 
Installing XtraBackup
 
Running XtraBackup
Since we are uploading to S3 using the AWS CLI, please make sure you are logged in to the destination AWS environment using the AWS CLI.
For other 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 must configure your on-premises environment so that it can be accessed from the Internet.
Please change the settings of your load balancer and firewall in your on-premises environment.
To configure the firewall, you need to specify the global IP address of AWS Aurora and allow it.
This will be created after creating the Aurora cluster, so we will postpone it for now.
After the cluster is created, check the IP address of the writer instance endpoint.
example)
 

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

We will create a self-signed certificate for TLS communication between MySQL in an on-premises environment and Aurora.
When creating CA/server/client keys, you will be asked for CN, but you must specify a different CN for each. You will also be asked for other information, but you can just press Enter without entering anything.
 
Load the certificate file in my.cnf.
 
MySQL restart
 
Check if the certificate is read.
If have_openssl is set to YES then you're good to go.

Creating a user for replication

Create a replication user. In the sample, we set it to %, but we recommend that you narrow down the IP addresses properly.

Steps to set up replication on the destination (AWS)

Creating a cluster from S3 backup data

You can also create it using the AWS CLI, but this time we will create it from the console.
Open AWS RDS and select "Restore from S3" at the bottom of the screen.
 
Select the bucket that contains the backup data. In this example, it is "kikai-test"
You also need to select the version of Aurora. In this case, we will select Aurora 2.11.2.
An IAM role that allows RDS to access the S3 bucket is also automatically created.
Once you have entered the required information, click Create Cluster.
 

Checking the replication start position

Once the cluster is created, click on the cluster to open it and check the "Recent Events" 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.

Load the certificate file into the created cluster

Inject the certificate file into the cluster.
Let's load the certificate file created in the previous step into the Aurora cluster.
 
Make a note of the contents of the certificate file.
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 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 as it enables TLS connections.
 
Start replication and check the status.
 
This allowed us to successfully set up replication between on-premises and Aurora.

Conclusion

Replication could be established over the Internet without using Direct Connect.
There are many benefits to being able to use replication when migrating to AWS, so be sure to prepare it so that you can use it in an emergency.
 
I also plan to write a separate article about replication in the reverse direction (Aurora → on-premises) in the event of a switchback using this configuration.
 
SRG is looking for people to work with us. If you're interested, please contact us here.