AWS RDS Aurora Replication with Mysql

08 / Feb / 2016 by Rajdeep Singh 4 comments

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.
1
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.

Screen Shot 2016-01-31 at 11.14.04 PM

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.

Screen Shot 2016-01-31 at 11.40.55 PM

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]
Screen Shot 2016-02-01 at 12.11.24 AM

Now, check slave status and verify all the configurations.

Screen Shot 2016-02-01 at 12.12.29 AM

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]

2

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.

FOUND THIS USEFUL? SHARE IT

comments (4)

  1. Mike P. Sinn

    Thanks for this great explanation! However, “Now, modify cluster parameter group and change “binlog_format” value from OFF to MIXED.” followed by “Note: Never perform modification in default “cluster” parameter group” seems contradictory. Also, I’m unable to change any values on “DB Parameter Group – Default parameter group for aurora5.6” or “DB Cluster Parameter Group – Default cluster parameter group for aurora5.6”. Am I supposed to create a new parameter group and apply it? Thanks for your help! 😀

    Reply

Leave a Reply to Mike P. Sinn Cancel reply

Your email address will not be published. Required fields are marked *