DEV Community

Cover image for Replicate AWS RDS Aurora to AWS RDS MySQL
Adrien Mornet for AWS Community Builders

Posted on

Replicate AWS RDS Aurora to AWS RDS MySQL

Image description

AWS encourages you to migrate from RDS MySQL to RDS Aurora by offering convenient features such as the ability to create an Aurora Read Replica from an RDS MySQL cluster and then promote it to Master. An almost downtime-free migration ! But how to do the same thing in the other way ? How to replicate an RDS Aurora Cluster to RDS MySQL or external MySQL Server ? Unfortunately AWS didn’t offer the ability to create a RDS MySQL Read Replica from an Aurora RDS cluster. I will detail in this post how to achieve it.

1. Create an RDS MySQL instance to be a Read Replica of an RDS Aurora Cluster

As mentioned in AWS documentation, to configure a MySQL DB instance to be a read replica of a MySQL instance you need to enable autocommit. Let’s create an RDS Parameter group.

Open RDS Console -> parameter group and click on Create parameter group button :

Image description

Image description

Once the parameter group is created, edit it and set autocommit = 1

Now you can create an RDS MySQL instance using this parameter group !

2. Modify you RDS Aurora cluster to enable Binary Logs

MySQL uses Binary Logs to save the database state : “The binary log contains “events” that describe database changes such as table creation operations or changes to table data.”

By default Binary Logs are not enabled on Aurora and we need to activate them. Let’s create an RDS Aurora DB cluster parameter group and enable Binary logs.

Create an RDS Aurora DB cluster parameter group, set binlog_format = MIXED, affect this new parameter group to your Aurora Cluster and restart your cluster :

Image description

3. Configure RDS Aurora Cluster to be ready for replication

Create a MySQL user on your RDS Aurora Cluster allowed to replicate :

CREATE USER 'repl_user'@'mydomain.com' IDENTIFIED WITH mysql_native_password BY 'password';
GRANT REPLICATION CLIENT, REPLICATION SLAVE ON *.* TO 'repl_user'@'mydomain.com';
FLUSH PRIVILEGES;
Enter fullscreen mode Exit fullscreen mode

Get binlog file and current position :

SHOW MASTER STATUS;
Enter fullscreen mode Exit fullscreen mode

Image description

We will use the value of “File” and “Position” columns in the next steps.

4. Configure RDS MySQL instance to replicate RDS Aurora Cluster

Connect to RDS MySQL instance and run the following command with your custom File and Position values :

CALL mysql.rds_set_external_master (
    'aurora_host'
  , 'aurora_port'
  , 'repl_user'
  , 'password'
  , 'mysql-bin-changelog.036743'
  , 9187
  , 0
);
CALL mysql.rds_start_replication;
Enter fullscreen mode Exit fullscreen mode

You’re done, your RDS MySQL is replicating your RDS Aurora Cluster

If you liked this post, you can find more on my blog https://adrien-mornet.tech/

References :

https://dev.mysql.com/doc/refman/8.0/en/binary-log.html

https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraMySQL.Migrating.RDSMySQL.Replica.html

https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/mysql_rds_set_external_master.html

Top comments (1)

Collapse
 
i1t profile image
Ivan Tashkinov

Thanks for the post, Adrien!

Do you think it should be technically possible to replicate AWS RDS Aurora to AWS RDS for PostgreSQL?

The official AWS documentation only describes replicating RDS to Aurora but not vice versa: neither for PostgreSQL, nor for MySQL.