How to configure replication from AWS MySQL Aurora to External replica

In our previous post we explained that how to replicate your Mysql RDS to an external replica. Now we are going to explain how to replicate MySQL Aurora to an external replica which is running on EC2.

Amazon Aurora using a single cluster storage for Master and all the replica servers and applying redo logs instead of binlog, thats why its giving 5X faster performance. But here we are using binlogs for replication, so please never expect the same performance here.

Prerequisites:

  • Master – AWS MySQL Aurora 5.6.
  • Slave – External EC2 instance or any on-prem server (MySQL version must be 5.6 or later)
  • The Mysql port should listen on both servers.
  • If you are running the external replica on your on-prem then Aurora should accessible via public.
  • Create a specific user for replication.
  • Need a Custom DB Cluster parameter group.
  • Automated backups must be enabled.

Prepare your Aurora as Master:

Let's start with creating an user for replication. Execute the below command to create the user.

CREATE USER 'replica'@'%' IDENTIFIED BY 'slavepass';  
GRANT REPLICATION SLAVE ON *.* TO 'replica'@'%';  

Ensure that automatic backups are enabled. Then create a new DB Cluster parameter group and set binlog_format to MIXED. Modify the Aurora cluster and select the custom parameter group. Without applying the parameter group you can’t able to get the master status.

alt

you should reboot the instance for applying the parameter group change. Now, login to the MySQL and make a note of the master status.

show master status alt

Take the backup of necessary databases, here I have only one database.

mysqldump --single-transaction --routines --triggers --events -h XXX.ciqk2zkqxh3j.us-east-1.rds.amazonaws.com -u bhuvi –pxxxxx puc_aurora > /mnt/d/puc_aurora.sql  

alt

Prepare the Replica Server:

As mentioned in the prerequisites the external replica must be 5.6 or later.
Edit the my.cnf file and make the Server-id=2

vi /etc/mysql/my.cnf  

[mysqld]
server-id               = 2  

Restart the MySQL server to apply this change.
service mysql restart

Restore the dump.

mysql -u root –pxxx puc_aurora < /tmp/puc_aurora.sql  

alt Login to MySQL prompts and set the master details.

CHANGE MASTER TO  
master_host = 'RDS END Point name',  
master_port = 3306,  
master_user = '<Replication user>',  
master_passwowrd = '<Replication users pasword>',  
master_log_file='<bin log file name-noted from Master>',  
master_log_pos=<Log position-noted from master>;  
CHANGE MASTER TO  
MASTER_HOST='xxx.xxxx.rds.amazonaws.com',  
MASTER_USER='replica',  
MASTER_PASSWORD='slavepass',  
MASTER_LOG_FILE='mysql-bin-changelog.000002',  
MASTER_LOG_POS=120;

alt Start the replication.
start slave; alt Now its testing time, Go and create a new database and insert some rows on existing tables.

Im going to insert some values on puc_tbl.

insert into puc_tbl values (6);  
insert into puc_tbl values (7);  
insert into puc_tbl values (8);

alt

Yup, its working as we expected :)

comments powered by Disqus