Cross-Account AWS Aurora MYSQL Replication for Financial Reporting and Business Continuity

In today’s digital era, data has become one of the most valuable assets, with its value skyrocketing due to the increasing reliance on data-driven insights and decision-making. Whenever there’s a need to move data from one place to another, organizations take it very seriously. Data movement, whether between regions or AWS accounts, demands careful planning to ensure security, compliance, and operational efficiency.

In this blog, we will walk you through the step-by-step process of setting up cross-account replication between two Amazon Aurora MySQL clusters in different AWS accounts, leveraging VPC Peering for secure communication. Whether you need to replicate production data for reporting or maintain redundancy for disaster recovery, this guide will offer a comprehensive solution for your needs.

Read More: How B2B Organizations Can Build a Winning Lead Lifecycle Management Strategy

Problem Statement

A financial enterprise needed to replicate data from their production Aurora MySQL database (in Account A) to a separate AWS account (Account B) for reporting purposes. The goal was to maintain data redundancy and ensure business continuity while running resource-intensive, read-only analytics queries on the replica in Account B without impacting the performance of the production environment. Additionally, the solution needed to be secure, cost-efficient, and scalable to handle future growth.

Challenges

• Ensuring data security and compliance when transferring sensitive financial data between accounts.
• Reducing the impact on the production system while enabling real-time reporting in a separate account.
• Establishing a secure, low-latency network connection between the two AWS accounts.
• Automating the setup to minimize human intervention and potential errors, while also making the solution easy to maintain.

Solutions

Cross-account replication is a powerful feature in AWS that enables replication of data between different AWS accounts. In this blog, we’ll walk you through the process of setting up replication between two Amazon Aurora MySQL clusters, residing in two different AWS accounts, using VPC Peering for secure networking.

RDS Replication

Why Cross-Account Replication?

Our organization needed to replicate data from a source Aurora MySQL cluster in one AWS account to a destination Aurora cluster in another AWS account. This setup was required for redundancy and for running a read-only replica for reporting purposes, isolated in a separate account. The constraint imposed for the replication pipeline was that it had to be safe, cost-effective, and scalable leveraging the AWS infrastructure, while also being low on overheads and maintenance.

For financial enterprises, this approach offers certain very pertinent advantages, such as:

• Data Security: Given that sensitive information is held in different AWS accounts, the risks against unauthorized access are greatly enhanced. In the event of an account being compromised, the account segmentation ensures that sensitive information is not lost.
• Cost Control: By placing reporting along with read-heavy operations in a separate AWS account and leaving production of the data to another account, organizations are able to contain resource use and expenditures.
• Business Continuity: Cross-account replication helps in making sure that data is retrievable even when there is a failure of the system or it is unreachable because of some events that have taken place at the source environment.

Overview of the Process

1. Establish Networking Between AWS Accounts: Set up VPC Peering between the VPCs in the source and destination AWS accounts.
2. Enable Binary Logging in Aurora MySQL: Enable binary logging in the source Aurora MySQL cluster.
3. Set Up Replication: Create and configure a replication user and initiate the replication process from the source Aurora MySQL to the target Aurora MySQL.

Step 1: Setting Up VPC Peering Between Two AWS Accounts

VPC Peering allows you to route traffic between VPCs securely. Here’s how to establish a VPC Peering connection between the VPCs in two different AWS accounts.

VPC Peering Steps

1. Open the Amazon VPC Console:
   • Go to the VPC Dashboard.
   • In the navigation pane, click on Peering Connections.
2. Create Peering Connection:
   • Choose Create Peering Connection.
   • Fill out the details:
     • Name: Optionally, give the peering connection a descriptive name.
     • VPC ID (Requester): Select the VPC in your account from which you want to create the peering connection.
     • Account: Choose Another account.
     • Account ID: Enter the AWS Account ID of the Accepter VPC (the target AWS account).
     • VPC ID (Accepter): Enter the ID of the target account’s VPC.
   • Click on Create Peering Connection.
3. Accept the Peering Request in the Target Account:
   • In the target AWS account, open the VPC Console.
   • Go to Peering Connections, and you’ll see a pending peering request.
   • Accept the peering request.
4. Update Route Tables:
   • After the peering connection is established, update the Route Tables in both VPCs to route traffic between the two VPCs.
5. Configure Security Groups:
   • Ensure that the security groups in both accounts allow the necessary traffic. Specifically, for database connections, allow TCP port 3306 (MySQL default port) between the VPC CIDRs.

Important Notes:

• The CIDR blocks of the VPCs in both accounts must not overlap.
• Ensure proper security group rules are set for database connections. For example, allow inbound traffic on port 3306 in the source VPC’s security group for the destination VPC CIDR and vice versa.

Step 2: Enabling Binary Logging in Aurora MySQL

By default, Aurora MySQL does not enable binary logs for replication to reader instances. To enable replication, we need to configure binary logging.

Steps to Enable Binary Logging:

1. Open the Amazon RDS Console:
   • Navigate to the RDS Console.
   • Select your Aurora MySQL Cluster.
2. Modify the Cluster Parameter Group:
   • In the Configuration tab, find the DB Cluster Parameter Group.
   • Click on Parameter group actions and choose Edit.
3. Change Binlog Format:
   • Find the binlog_format parameter.
   • Change its value to ROW (recommended for replication).
     

     binlog_format

4. Save Changes:
   • After updating the parameter, click Save Changes.
5. Reboot the Primary DB Instance:
   • To apply the new binlog format, reboot the primary instance in your Aurora cluster.

Note: Changing the binlog format will not take effect until the primary DB instance is rebooted.

Step 3: Setting Up Replication Between Aurora MySQL Clusters

1. Configure Binary Log Retention:
   To ensure that the binary logs are retained long enough for replication, increase the retention period by executing the following command in the source Aurora MySQL cluster:

   CALL mysql.rds_set_configuration('binlog retention hours', 48);

2. Create a Snapshot and Share it:
   • Take a Snapshot:
     In the source AWS account, create a snapshot of the Aurora MySQL cluster from which you want to replicate.
   • Share the Snapshot:
     After the snapshot is created, share it with the target AWS account. You can do this via the Amazon RDS Console by selecting the snapshot and choosing Share Snapshot.
   • Restore the Snapshot:
     In the target AWS account, restore the shared snapshot to create a new Aurora MySQL cluster.
3. Get Binlog Crash Recovery Position:
   Once the snapshot is restored in the target account, Aurora will provide a binlog crash recovery position via an event log. You’ll need this binlog position to set up incremental replication. The event message will look something like this:

   "Message": "Binlog position from crash recovery is mysql-bin-changelog.000002 154”

4. Create a Replication User in the Source Account:
   In the source Aurora MySQL cluster, create a replication user with the necessary privileges:

   mysql> CREATE USER 'repl_user'@'%' IDENTIFIED BY '<enter_your_password>';
   mysql> GRANT REPLICATION CLIENT, REPLICATION SLAVE ON *.* TO 'repl_user'@'%';

5. Set Up Replication in the Target Aurora Cluster:
   • Set the External Master:
     In the target Aurora MySQL cluster, use the following command to configure the source Aurora cluster as the external master:

     CALL mysql.rds_set_external_master ('repl-source.cluster-xxxx.us-east-1.rds.amazonaws.com',3306,
     'repl_user',
     '<enter_your_password>',
     'mysql-bin-changelog.000002',
     154,
     0
     );

   • Start Replication:
     After setting the external master, initiate replication by running:

     CALL mysql.rds_start_replication;

   • Check Replication Status:
     Use the following command to check the status of replication:

     mysql> SHOW SLAVE STATUS\G;

   • Stop Replication (If Needed):
     If you ever need to stop replication, use this command:

     CALL mysql.rds_stop_replication;

Troubleshooting and Challenges:

• CIDR Overlap: Initially, we faced the problem of the CIDR block of two Virtual Private Clouds interfering with each other, causing the VPC Peering connection to fail. This forced us to reconfigure the CIDR ranges.
• Security Group Misconfiguration: There was an, as it appeared, problem with some security rules under security groups, that caused connection attempts to the database to fail. This was fixed by permitting inbound connections to port 3306 from relevant CIDR ranges.
• Misconfigured Binary Log Retention: In earlier settings, the binary logs were crashed and purged very quickly which led to reasons that never allowed replication. This was rectified by increasing the retention time.

Best Practices for Cross-Account Replication

1. Automation: Consider using IaC to automated PC Peering, Aurora cluster configuration etc to minimizes human mistakes and increases effectiveness

2. Monitoring & Alerts: Ensure the replication process and it status is being monitored and laerting is setup  using AWS Cloudwatch

3. Security First: Do not forget to employ encryption mechanisms for data in transit between accounts and even the IAM roles assigned replication should be based on least privilege principle.

Conclusion

With this guide, you can easily set up cross-account replication between Aurora MySQL clusters using VPC Peering—smooth, secure, and no more data headaches! 🚀 It’s all about keeping your data synced and your operations running like a charm.