About SRGArticlesRecruitment Information
About SRGArticlesRecruitment Information
HOME/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) mainly provides cross-sectional support for the infrastructure of our media services, improving existing services, launching new ones, contributing to OSS, etc.
This article is located in SRGDBWGWe will publish materials regarding the database that the (DB Working Group) provides to 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 to build a dedicated network between your on-premises environment and AWS.
This document describes how to set up secure 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 backups.
 

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 need to configure your on-premises environment so that it can be accessed from the Internet.
Please change the settings of your load balancer, firewall, etc. 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 you create 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 the MySQL server

We will create a self-signed certificate for TLS communication between MySQL and Aurora in the on-premises environment.
When you create CA/server/client keys, you will be asked for CN, but you should specify a different CN for each. You will be asked for other things, but you don't need to enter anything, just press Enter.
 
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 address properly.

Flow of replication settings on the destination (AWS)

Create 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 example, I chose 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.
 

Confirmation of the position where replication starts

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

Introduce the certificate file to 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 connection.
 
Start replication and check the status:
 
Now we have successfully established replication between on-premises and Aurora.

Conclusion

It was possible to set up replication 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 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 are interested, please contact us here.