MYSQL MULTI-MASTER REPLICATION ON AMAZON RDS

One great feature of modern relational databases is the ability to replicate changes automatically from one database server to another. This has many use cases, from having a backup that is ready to jump as a production server, to having a fresh copy of your data out of the online database for heavy load queries. In a project for ANII, we did some nice tricks over MySQL replication to meet project requirements and complexities that we would like to share.

MySQL uses the binary logs to keep track of
which data is already replicated, and which one is not.

The first thing we faced is that all those MySQL servers were hosted on Amazon RDS. This is no big thing, until you want to start messing with MySQL configurations and an inaccessible mysql.conf. Now a days (with latest versions of AWS RDS) more and more configurations are available trough parameter groups, so most configs are doable.

The next thing (a big one), is that our client wanted to replicate from several servers (called masters in the replication world) to only one server replica (called slave). If you think it for a moment, it makes sense: send all servers data to one replica server so you can have in one place all you need to do your analytics, and for sure save some money. This is great, until you come to realize that MySQL replication only allows one master per replica (for example, one master can have several replicas, but not the other way around).

This was a major requirement for ANII, so we needed to find a way to do this. Luckily, we found several posts that explain that you can set a master and start replicating, and after a while, stop and point the slave to another master, and start replicating that one. But before start each replication, we needed to tell the slave at which point of the binary logs did he stop the last time he was working with that master, so he can resume without losing anything. Tricky, isn’t it?

So, in the case you have 2 master servers and 1 slave:
– Master M1
– Master M2
– Slave S1

• You need to:
  1) S1 sets M1 as the source of replication
  2) Set the logs to the point you left M1 last time you replicated
  3) Start replication
  4) Run replication for a little while
  5) Stop replication and save the log status
  6) S1 sets M2 as the source of replication
  7) Set the logs to the point you left M2 last time you replicated
  8) Start replication
  9) Run replication for a little while
  10) Stop replication
  11) Go back to step 1

Sounds easy!

Check out our Success Case of ANII

We are BigCheese, an engineering in-house team.