Replicate AWS RDS Aurora to AWS RDS MySQL
Tags: tutorial, devops, database, aws
Published: 2023-03-04 09:31:28

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/mtx5mj4b0q5fklxio4o0.png)


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](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/fccack663w74iy601pj7.png)

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/vx7bslsn1y2akmye19nu.png)

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](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/a1fho55ct1ezfg0sn78m.png)


## 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;
```

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/km6il86xm69ysf0xkulq.png)

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