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,
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.
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.
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
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
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
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
7. start replication on the
external-server and check status.
SHOW SLAVE STATUS \G
8. Delete the intermedia server if all good,
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.
CALL mysql.rds_set_external_master ('rds-master-server-ip', 3306, 'replication', 'password', 'mysql-bin-changelog.000031', 107, 0);
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.
We are a Professional AWS Managed company of experienced talented engineers. We are top skilled in AWS Architecture, DevOps, Monitoring and Security Solutions.