How to Configure Replication between Aurora and Remote MySQL Server

Introduction:

In some cases, you require to create a replication between an AWS and non-AWS environments, suppose you need to have a read-only version of your database to reduce the latency, or for creating a disaster recovery storage. in such a situation, the best way to make sure you have up to date version of your original database is by creating replication, RDS replication is very common thing to setup between your AWS environment and on-prem,

What is MySQL Replication?

Replication enables data from one MySQL server (the master) to be replicated to one or more MySQL servers (the slaves), and is used to scale out read workloads, provide high availability and geographic redundancy, and offload backups and analytic jobs.

RDS & External MySQL

Amazon Aurora has a slightly different structure from management wise since it has its own replication engine and since you don’t root access for the engine, also the replication native commands are disabled and replaced with RDS replication commands.

Instructions:

Required Components:

• RDS Master DB – rds-master-server
• RDS Read-Only Slave – rds-slave-server
• External DB Server – external-server

As you notice we will need a third (intermediate) server to support which will be configured as a read-only replica and will reduce any unexpected impact on the RDS master server.

Also, make sure your security groups are configured properly for the external server  external-server to be able to access the rds master  rds-master-server

Steps:

1) Enable binlog on the master server rds-master-server to support RDS replication, binlog is basically the log history of all transactions made on the server which needs to be synced to the other servers to be applied there, configure a 24 hours retention period which means how long the transactions will be kept for transactions. you can increase this period if your slave server might be offline for more than 24 hours, but this will increase storage size.

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

2. create a replication user on the master server rds-master-server: this will be user login to be used by the slave server to access replication bin logs.

CREATE USER 'replication'rds-master-server-ip'%' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'replication'@'rds-master-server-ip';

3. Stop RDS replication on the slave server rds-slave-server to ensure the database is not changed while importing the dump

call mysql.rds_stop_replication;

4. Grab the values of Exec_Master_Log_Pos and Relay_Master_Log_File  by running the command below

SHOW SLAVE STATUS \G

5. dump the database you need to apply replication from the slave master rds-slave-server and import it to the external-server using MySQLDump export and MySQL import.

6. enable replication on the external server external-server

CHANGE MASTER TO MASTER_HOST='rds-master-server-ip', MASTER_USER='replication', MASTER_PASSWORD='password', MASTER_LOG_FILE='Relay_Master_Log_File', MASTER_LOG_POS=Exec_Master_Log_Pos;

replace replication details with the replication user you created earlier, same for Relay_Master_Log_File and Exec_Master_Log_Pos.

7. start replication on the external-server and check status.

START SLAVE;

SHOW SLAVE STATUS \G

8. Delete the intermedia server if all good,

RDS to RDS Replication?

If you need to have cross-account replication or implement replication on regions that do not support read replica like china region, you can use the above procedure too,

however since as we mentioned earlier that replication native commands are disabled are not available and replaced with rds replication commands, you need to replace the commands in step #6 and #7 to the commands below.

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

CALL mysql.rds_set_external_master ('rds-master-server-ip', 3306, 'replication', 'password', 'mysql-bin-changelog.000031', 107, 0); 

call mysql.rds_start_replication;

Conclusion

We discussed RDS replication in this article because I honestly felt lost when we tried to implement it first time using AWS documentation, however, it’s ok to refer to the documentation to see the list of other parameters to use for replication

We discussed different types of replication, RDS to Standalone, RDS to RDS, please free to contact us to get a free quote to set up RDS replication.