AWS RDS Aurora Replication with Mysql

In my previous post, we learnt about how to launch RDS Aurora instance. The storage space of RDS Aurora is automatically scalable up to 64 TB and the read scaling can be achieved by creating up to 15 replicas in the cluster. I had migrated database into RDS Aurora to avail fault tolerance feature. Once the migration was completed I came across a new requirement which was to setup an on-premises replica copy for the development team.  So, today we are going to learn how to setup Master-Slave replication with RDS Aurora and self-managed MYSQL instance.

Here are few things which I assume you would have already setup:

1. A running RDS Aurora instance, if not please go through these steps.

2. Replication is only possible for MySQL 5.6 or higher version. Please go ahead and install MySQL on your on-premises server.

3. The Aurora Instance must be accessible from your on-premises instance. There are multiple ways to make it possible:

a) Setup VPN connection

b) Create SSH tunnel

c) Make Aurora publicly accessible.

Let’s get started:

1. Configure Aurora Instance as Master:
We need to grant replication privileges to a user to start replication between master and slave. First of all create a “replication” user in RDS Aurora with replication privillags on database “testdb”. This user is allowed to access only from IP 172.31.15.16, this IP belongs to my on-premises server. Login into MySQL and run command on terminal:

[js]
mysql> grant replication client, replication slave on *.* to ‘replication’@’172.31.15.16’ IDENTIFIED BY ‘password’;
[/js]

Now, modify cluster parameter group and change “binlog_format” value from OFF to MIXED.

Note: Never perform modification in default “cluster” parameter group.

To make this effective, the DB instance needs to be rebooted. So, to proceed with the further configuration, you have to reboot the instance.

Now, RDS Aurora will be working as master and if you want to update “binlog” retention period then use below command but the maximum retention period can be set to a maximum period of 720hr (30 days).
[js]
mysql> call mysql.rds_set_configuration(‘binlog retention hours’, 144);
[/js]
2. Export database:
Now, write down current binlog file name and position value. This will be required later to configure the slave server.

Next, you have to export database in SQL file and transfer this to slave server.
[js]
# mysqldump -uroot -ppassword -hauroradb.xxxxxxxxxxx.amazonaws.com –databases testdb –single-transaction –order-by-primary > testdb.sql
[/js]
3. Configure slave server:

Before starting replication first edit /etc/my.cnf file and uncomment server-id parameter. Unless you uncomment this it will not able to start the slave thread.

[js]
# vi /etc/my.cnf
server-id = 10 #any random number
[/js]
Now, source the database file and enable replication.
[js]
# mysql -uroot -ppassword testdb < testdb.sql

mysql> CHANGE MASTER TO MASTER_HOST='<aurora_end_point>’,MASTER_USER='<replication_user_name>’, MASTER_PASSWORD='<password>’, MASTER_LOG_FILE='<binlog_file_name>’, MASTER_LOG_POS= ‘<binlog_position>’;
[/js]

Now, check slave status and verify all the configurations.

If the whole configuration is correct start slave thread and check replication status again. If everything goes well both slave threads will be running.
[js]
mysql> start slave
[/js]

I hope this will help you to setup replication. We can test replication by creating one table in the master instance and then you can see this table being automatically mirrored in slave server.