Herein, we are looking into a solution of RDS master-master replication. Unfortunately, AWS don’t support master-master replication on RDS but we walk through a different approach, master-master replication between RDS and External MySql Instance. This External MySql instance might be EC2 or your own server. Here are few things those are already setup:-
1. MySql 5.6 running on EC2 instance.
2. DB does exist and synced on RDS and EC2 instance.
3. Port 3306 accessible between RDS and EC2 instance.
Let’s move to Configuration Steps:-
1. Configure Mysql Instance as Master.
2. Configure RDS as Master and then Slave.
3. Configure Mysql Instance as Slave.
1. Configure Mysql Instance as Master:-
First we setting up Mysql Instance as a Master, here some changes in my.cnf file
server-id = 14 # any unique number
log_bin = /var/lib/mysql/mysql-bin.log
replicate-wild-ignore-table=mysql.rds_% # exclude mysq.rds_* tales during replication from RDS
auto_increment_increment = 2 # total number of master server
auto_increment_offset = 2 # any number, but not higher then above value.
After making all changes restart mysql
We done with configuration of mysql, Now first have to create a user for replication and secondly check binlog file name with it’s position.
mysql -uroot -p
mysql> grant replication slave on *.* to ‘<REPLI_USER>’@'<RDS_IP>’ identified by ‘<REPLI_PASSWORD>’;
mysql> show master status;
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
| mysql-bin.000015 | 2249 | | | |
Record this output as we need these information later in next step.
2. Configure RDS as Master and then Slave:-
By default in RDS binlog already enabled and we can verify this by ruuning “show master status” command in mysql shell. We jut need to set two variables in “Parameter Groups”. So login to AWS console to set these parameter and don’t forget to reboot your instance.
If we need to exclude some table, then sorry to say RDS not come with this option but don’t bother about it we already did this in previous step. Here first we repeat some of above steps, like replication user creation and check master records.
mysql-uroot -p -h<RDS_HOST>;
mysql> grant replication slave on *.* to ‘<REPLI_USER>;’@'<MYSQL_INSTANCE_IP>’ identified by ‘<REPLI_PASSWORD>’;
mysql> show master status;
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |Executed_Gtid_Set |
| mysql-bin-changelog.074711 | 2249 | | | |
Note down these information as we need this to make Mysql instance slave of RDS. Now we are going to configure RDS as slave. Please make sure the file name and position value same as noted in step 1.
mysql -uroot -p<password> -h<RDS EndPonit>
mysq> call mysql.rds_set_external_master(‘<mysql_instance_ip>’,3306,'<REPLI_USER>’,'<REPLI_PASSWORD>’,’mysql-bin.000015′,2249,0);
mysql> call mysql.rds_start_replication;
Check slave status by running below command. If every thing is good then out put would be like this
mysql -uroot -p<password> -h<RDS_EndPoint> -e "show slave status\G;" | grep Running
3. Configure Mysql Instance as Slave:-
Now proceeding to make Mysql Instance slave of RDS. We need to repeat steps as we did earlier. Also here make sure value of position and file name match your information.
mysql -uroot -p;
mysql> CHANGE MASTER TO MASTER_HOST = ‘<rds_host_ip>’, MASTER_USER = ‘<REPLI_USER>’, MASTER_PASSWORD = ‘<REPLI_PASSWORD>’, MASTER_LOG_FILE = ‘mysql-bin-changelog.074711’, MASTER_LOG_POS = 2249;
mysql> start slave;"
Now slave activated and we can check slave replication by using below command
mysql -uroot -p<password> -e "show slave status\G;" | grep Running
So both DB replicating data from each other. We can test while creating one table in RDS and second table in Mysql Instance and you can see these tables mirrored on both server. We didn’t cover up much theoretical part, this was just a brief overview. You can write me your queries at rajdeeps[at]intelligrape.com.