Replicate AWS RDS Aurora to AWS RDS MySQL

#tutorial #devops #database #aws

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 :

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 :

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;

Get binlog file and current position :



SHOW MASTER STATUS;

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;

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