MySQL Master-Master Replication using RDS
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
[js]
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.
[/js]
After making all changes restart mysql
[js]
/etc/init.d/mysql restart
[/js]
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.
[js]
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 | | | |
+——————+———-+————–+——————+——————-+
[/js]
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.
[js]
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 | | | |
+—————————-+———-+————–+——————+——————-+
[/js]
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.
[js]
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;
[/js]
Check slave status by running below command. If every thing is good then out put would be like this
[js]
mysql -uroot -p<password> -h<RDS_EndPoint> -e "show slave status\G;" | grep Running
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
[/js]
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.
[js]
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;"
[/js]
Now slave activated and we can check slave replication by using below command
[js]
mysql -uroot -p<password> -e "show slave status\G;" | grep Running
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
[/js]
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
how to sync rds data to external mysql server or local mysql server,
one time in a day.
Can we have 3 master server replication setup or this will work with only 2 multi master.
Not getting master status records on AURORA CLUSTER
mysql> show master status;
Empty set (0.00 sec)
Any suggestion?
I ‘m not getting result of my command show master status on RDS AURORA.
mysql> show master status;
Empty set (0.00 sec)
Any suggestion?
Is this setup possible if the EC2 instances and RDS are in a private subnet in different regions?
I just tried the your technique with 2 RDS from 2 different regions and it works perfectly. Now we can have MASTER to MASTER sync within RDS across region. Thank you so much for sharing the post here, really appreciate it.
So you achieve Master Master cross region replication using only RDS. Any way you can share how? Been trying to figure it out with out using custom ec2 mysql instances!
Hi Suhas,
Can you plz help me how did u achieved this.
As i am facing issue when i am launching RDS instance and check for master status it show empty set!! (But according to RDS doc by default RDS bin logs are enabled)
Hi Suhas,
Can you plz help me how did u achieved this.
As i am facing issue when i am launching RDS instance and check for master status it show empty set!! (But according to RDS doc by default RDS bin logs are enabled)
Hi Rajdeep,
(I’m very new to MySQL replication) Please let me ask a question:
– Can we use this technique with the RDS Instance which is already being used as a Master on the existing RDS Replication? (Master already has it’s Read Replicas, provided by AWS) .. Or will they break?
Thank you.
Kind regards
We can’t use the same setup with existing Master-SLAVE RDS replication. Because the read replicas in AWS are not communicating to other master instance (EC2 server), So it would be hard to maintain replication state.
Can we create master-slave replication where both are RDS instances without creating a slave as read replica of master?
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.
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).
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.
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.
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.
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
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.
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
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’;
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
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.
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.
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.
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.
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