MySQL Master-Master Replication using RDS

10 / Mar / 2015 by Rajdeep Singh 16 comments

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

/etc/init.d/mysql restart

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
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

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
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

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.

Thanks
Rajdeep Singh

Tag -

FOUND THIS USEFUL? SHARE IT

comments (16)

  1. Udit Sharma

    Can we create master-slave replication where both are RDS instances without creating a slave as read replica of master?

    Reply
    1. Rajdeep SinghRajdeep Singh Post author

      Yes, you can but there will be an overhead to manage replication on non root instance. I think it doesn’t make any sense to setup replication between two stand alone RDS instance. If you only want to configure master-slave then AWS read replica is the best choice. Let me know if you have a specific use case, we can discuss that.

      Reply
  2. Mahesh K Gareja

    Read Replica Replication Error – IOError: 1593, reason: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server ids; these ids must be different for replication to work (or the –replicate-same-server-id option must be used on slave but this does not always make sense; please check the manual before using it).

    Reply
    1. Rajdeep SinghRajdeep Singh Post author

      In your both mysql instance execute this command and check server id:
      mysql> SHOW VARIABLES LIKE ‘server_id';

      It should be different on all replication server.

      Reply
  3. Mahesh K Gareja

    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.
    ==> i m not able to create parameter like “auto_increment_increment” on rds.

    Reply
    1. Rajdeep SinghRajdeep Singh Post author

      It is not possible to create new configuration parameter, you can only edit existing available configuration. For that check assigned parameter group name in configuration detail of RDS instance and then in parameter section select your parameter group name. It will list you out all configuration setting and in filter section, you can search that configuration and edit it to desired value.

      Reply
  4. VISHAL KUMAR

    Hi Rajdeep,
    Thanks for writing down .. the approach but .when I tried from doing replication between rds and ec2
    can we set up the replication between rds and ec2

    Reply
    1. Rajdeep SinghRajdeep Singh Post author

      Yes, we can use EC2 instance as a master replica of RDS, that is what I have setup and shared it here. There would be a trouble is your data structure not designed well.

      Reply
  5. Raul

    Could you elaborate a little more on which users should already be created?
    When trying to run in RDS the grant replication slave on *.* to ‘user’@’10.0.0.185′ identified by ‘pwd';
    I get a ERROR : “ERROR 1045 (28000): Access denied for user ”
    So should we have certain users with certain permissions already enabled?
    Regards,
    Raul

    Reply
    1. Rajdeep SinghRajdeep Singh Post author

      Hi Rahul,

      Please use the same user name which you have passed during creation of RDS instance, which in my case is “root”. If still you are facing “access denied” error try to run grant on specific data base.

      grant replication slave on db_name.* to ‘user’@’10.0.0.185′ identified by ‘pwd';

      Reply
  6. Jon Brookes

    Hi Rajdeep,

    Great article! I guess this technique would also work with Aurora? Could you do it at an entire instance level rather than database?

    Thanks,
    Jon

    Reply
    1. Rajdeep SinghRajdeep Singh Post author

      Hi Jon, Thanks for writing down.

      First of all sorry for the delay in response, I didn’t get a chance to respond you from a long time. The aurora is itself a Multimaster replication so if you use this with aurora you will face replication error. As Each aurora instance have it’s own binlog file and location, the EC2 instance will throw you replication error message whenever the failover occurs.

      It is not possible to configure instance level replication. Because in RDS we are not having full access to MYSQL database but we can configure replication on multiple databases except MYSQL.

      Reply
  7. Arun

    Hi Rajdeep,

    How can we redirect read queries from application (e.g tomcat) to RDS cross region read replica? Any idea here..
    As AWS says, “Cross Region Read Replicas to serve read queries from an AWS region which is closer to user”. How to do this?

    Regards,
    Arun.

    Reply
    1. Rajdeep SinghRajdeep Singh Post author

      Hi Arun,
      Definitely, You can setup read replication between two different regions. You just need to make your master RDS instance will be accessible from other region and then set replication.
      To serve read traffic in different regions, create a replica of your environment (RDS, EC2 Instance, other dependent components) in that region. Then set latency based routing in Routr53 to route traffic to the nearest region.

      Reply
  8. nita

    Hi,
    Did you ever encounter any error like:
    Last_SQL_Errno: 1062
    Last_SQL_Error: Error ‘Duplicate entry ‘xxxxxx’ for key ‘PRIMARY” on query. Default database: ?

    I tried >> CALL mysql.rds_skip_repl_error;
    the error gone, but it will come again.

    tq.

    Reply
    1. Rajdeep SinghRajdeep Singh Post author

      The 1062 error encounter when a column has duplicate entry for the primary key constraint. There might be multiple reasons you are getting this error again and again. For a quick fix, I suggest you to delete the PRIMARY index on the table and create new PRIMARY key and make sure auto_increment column first field should not start from zero.

      In RDS, there is no parameter to skip 1062 error but on EC2 in my.cnf file you can set this value.
      slave-skip-errors=1062

      Reply

Leave a comment -