Cross account and cross-region RDS MySQL DB replication: Architecture

This Blog has moved from Medium to blogs.tensult.com. All the latest content will be available there. Subscribe to our newsletter to stay updated.

In my previous blog, I have discussed various disaster recovery options available for RDS. I recommend you to go through that blog, it makes it easier to understand this blog.

Why cross account and cross region replication?

Cross region replication helps to quickly recover from AWS region wide failures. Also it will help to serve the customer faster as we can use replica for read traffic and few of them might be closer to replica’s region.

Cross account replication helps to recover data from replication account when our master AWS account is compromised and we have lost access to the account completely. Such incidents happened in the past where one of the AWS customer account got hacked and the attacker deleted all the data. AWS provides several mechanisms to protect the data but having separate backup account with very limited access and tighter controls will help in unforeseen circumstances.

Before we dive into setup lets understand the overall replication process.

Overall Process

Architecture

Explanation

• Both master and slave databases are not publicly accessible, this is a best practice to keep DB instances accessible from only application servers, hence we need to keep them in the private subnets with proper access restrictions using NACLs.
• We have connected both VPCs in different accounts and regions using VPC peering so that data never leaves the private network.
• We have added DMS and Slave DB in separate private subnets, this is another best practice, we need to keep every logically separated components in their own subnets so that we get a better control over network access.
• We can control DMS access to Slave DB instance using Security Group rules in DB Security Group 2 and NACLs on the subnet (172.20.3.0/24).
• We can control DMS access to the Master DB instance using NACLs on the subnet (10.0.1.0/24). Here using security group rules to restrict access to only DMS replication instance may not advisable as IP of the replication instance might change if Multi-AZ is enabled and we can’t restrict using DMS Security group id as that is in a different account.
• We have kept DMS replication instance in DR account than Production account as this instance is for DR purpose only so it is logical to keep in the DR account. This gives better control over overall DR setup and also this reduces attacks to the DR account by not exposing the info in the Production account else attacker may attempt to take down even the DR setup.

How does this work?

DMS reads Binary logs from Master MySQL DB instance and replays them on the Slve DB instance.

Source: AWS

Binary logs (binlogs):

MySQL DB keeps binlogs in order to support replication, binlogs are mainly all the modifications on DB, it won’t contain any read queries data. In RDS binlogs are enabled by Default with MIXED format but for DMS to work we need to change then to ROW format.

Please note: enabling binlogs might have a slight performance impact so test on the Dev-Setup before using on the production setup.

Supported DDL statements (Schema changes):

DDL statements are the queries, that are sent to the DB instance to change the DB schema and only the following are supported for MySQL.

• Create table
• Add column
• Drop column
• Rename column
• Change column data type

Supported DB engine versions:

MySQL 5.6 or above.

Requirements:

1. You must enable automatic backups.
2. “binlog retention hours” should be 24.
3. The binlog_format parameter should be set to ROW.
4. The binlog_checksum parameter should be set to NONE. This will be NONE by default if automatic backups are enabled.

Limitations:

• AUTO_INCREMENT attribute on a column won’t be set on the target instance.
• By default tables on the target is created with the InnoDB storage engine but this can be avoided if we create table manually on the target DB instance and migrate using do nothing mode.
• Above are the common ones and there are more limitations.

Preparation

Before we setup the replication process, lets prepare the source (master) DB instance.

Check if automated backups are enabled:

We can check from RDS console by select the Master DB instance and look for “Automated Back

RDS instance details in the console

If this is show Not Enabled then Modify the instance to enable.

Using AWS CLI:

aws rds describe-db-instances --db-instance-identifier myMasterDBInstanceId
# Look for BackupRetentionPeriod and verify that it is a NON-ZERO value.
# Incase this is 0 then we can change using following command:
aws rds modify-db-instance --db-instance-identifier mydbinstance    --backup-retention-period numberofday --preferred-backup-window "hh24:mi-hh24:mi"

Check if binlog_format is ROW

To change this parameter, we need to use custom Parameter group so first we need to create a Parameter group called replication and then update the binlog_format to ROW, then update the instance’s DB parameter group to replication. After that RDS will take sometime to modify the parameter group and then it will say pending-reboot and then we need to reboot the instance.

Creating Parameter group: replication

Update binlog_format to ROW of replication Parameter group

Modify the DB instance Parameter group to replication

Select appropriate option for When Apply Modifications

It should apply Parameter group changes

It should change to pending-reboot very soon

Now we need to reboot the instance, make sure you do this when there is very less traffic to the DB instance else application may fail to connect to the DB. If you have enabled Multi AZ then down time will be quite less and RDS switches traffic to stand by DB instance in another AZ.

After the reboot, it should say in-sync

Now our new parameter group is successfully applied to the Master DB instance.

AWS CLI instructions:

aws rds create-db-parameter-group --db-parameter-group-name replication --db-parameter-group-family MySQL5.7 --description "My DB params for replication"
aws rds modify-db-parameter-group --db-parameter-group-name mydbparametergroup --parameters "ParameterName=binlog_format,ParameterValue=ROW,ApplyMethod=immediate"
aws rds modify-db-instance --db-instance-identifier myMasterDBInstance --db-parameter-group-name replication
# Wait for sometime so that instance finish modification and then reboot the instance.
aws rds reboot-db-instance --db-instance-identifier myMasterDBInstance

Set binlog retention hours:

Now connect to the instance using MySQL command line or your favourite MySQL client and execute following query to set binlog retention hours.

call mysql.rds_set_configuration('binlog retention hours', 24);

Now Master instance is ready for replication and we need to setup DMS replication and Slave DB instance. The same procedure works for Master to Master replication and DMS service can be used replicate RDS MySQL DB to on-premise or EC2 MySQL DB instance too. We will explain the complete setup in our next blog.