Amazon RDS MySQL and Master-master replication

Question

I can't find it from documentation, but it seems Master-Master replication is not supported at this time. Is this correct?

Answer 1

That is correct. RDS does not support Master-Master replication currently, so scaling horizontally for writes is not easily achievable, if that is your need. RDS does however support the ability to create multiple "read only" RDS slave instances, so scaling horizontally for reads is possible.

Here is RDS FAQ on replication

http://aws.amazon.com/rds/faqs/#replication

Answer 2

May be this link will be helpful.

https://aws.amazon.com/about-aws/whats-new/2017/11/sign-up-for-the-preview-of-amazon-aurora-multi-master/

Posted On: Nov 29, 2017

Amazon Aurora Multi-Master allows you to create multiple read/write master instances across multiple Availability Zones. This enables applications to read and write data to multiple database instances in a cluster, just as you can read across Read Replicas today.

Multi-Master clusters improve Aurora's already high availability. If one of your master instances fail, the other instances in the cluster will take over immediately, maintaining read and write availability through instance failures or even complete AZ failures, with zero application downtime. Today’s single-master Aurora supports one write instance and up to 15 promotable read-only replicas in a single database cluster. The primary writer instance can execute up to 200,000 write operations/sec on an r4.16xlarge. Workloads that require even higher write throughput will benefit from horizontally scaling out their writes with additional master instances. The preview will be available for the Aurora MySQL-compatible edition, and you can participate by filling out the signup form.

Amazon Aurora is a fully managed relational database that combines the performance and availability of high-end commercial databases with the simplicity and cost-effectiveness of open source databases.

Answer 3

Yes it is possible to do Master-Master replication in RDS MySql Engine. But it need some manipulation with instances. Pre-requisite: 1) Create Read replica of both instances for enabling binary logging. 2) Delete Read replica for both of them if not required. 3) Follow the instructions for master slave setup mentioned below.

On Master1 create replication user

grant replication slave on *.* to 'admin'@'%' identified by 'admin';
Query OK, 0 rows affected (0.00 sec)

note output of below command

show master status; +----------------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +----------------------------+----------+--------------+------------------+-------------------+ | mysql-bin-changelog.000007 | 120 | |
| | +----------------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec)

On Master2

mysql> call mysql.rds_set_external_master('master1.endpoint.amazonaws.com',3306,'admin','admin','**mysql-bin-changelog.000007**',**120**,0);
Query OK, 0 rows affected (0.05 sec)

mysql> call mysql.rds_start_replication;
+-------------------------+
| Message                 |
+-------------------------+
| Slave running normally. |
+-------------------------+
1 row in set (1.01 sec)

mysql -u admin123 -padmin123 -h master2.endpoint.amazonaws.com -e "show slave status\G;" | grep Running
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it

======================================================================

On Master2

grant replication slave on *.* to 'admin'@'%' identified by 'admin';
Query OK, 0 rows affected (0.00 sec)

show master status;
+----------------------------+----------+--------------+------------------+-------------------+
| File                       | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------------------------+----------+--------------+------------------+-------------------+
| **mysql-bin-changelog.000007** |      **120** |              |                  |                   |
+----------------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

On Master1

mysql> call mysql.rds_set_external_master('master2.endpoint.amazonaws.com',3306,'admin','admin','**mysql-bin-changelog.000007**',**120**,0);
Query OK, 0 rows affected (0.05 sec)

mysql> call mysql.rds_start_replication;
+-------------------------+
| Message                 |
+-------------------------+
| Slave running normally. |
+-------------------------+
1 row in set (1.01 sec)

mysql -u admin123 -padmin123 -h master1.endpoint.amazonaws.com -e "show slave status\G;" | grep Running
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it

Answer 4

As of 2021, multi-master is available in Aurora with some limitations*

https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/aurora-multi-master.html

Answer 5

Just researching it right now, but it seems they support now RDS being a slave to another MySQL instance (http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/mysql_rds_set_external_master.html). With the previously mentioned read-slave option a master-master replication should be doable

Answer 6

As of Nov. 29, 2017 Multi-Master is in preview for Amazon Aurora (MySQL Compatible DB)

https://aws.amazon.com/about-aws/whats-new/2017/11/sign-up-for-the-preview-of-amazon-aurora-multi-master/

We have had good results using Aurora as a MySQL 5.7 Db so I'd recommend taking a look.