This blog post will walk you through the process of setting up Master to Master database replication between an AWS Relation Database Service (RDS) MySQL server and an Ubuntu host running MySQL via the AWS Database Migration Service.
This tutorial assumes that you are running a clean installation of mysql-server on Ubuntu 16.05. It further assumes that your MySQL RDS instance is already running with the default configuration unchanged. Finally, this tutorial assumes that the databases you wish to replicate are already running on both servers with similar schemas.
Start by editing /etc/mysql/mysql.conf.d/mysqld.cnf on the Ubuntu server and un-commenting/adding the following lines:
bind-address = your.server.ip.address server-id = 1 log_bin = /var/log/mysql/mysql-bin.log binlog-format = ROW binlog_checksum = NONE binlog_row_image = FULL
Then, restart MySQL on the Ubuntu host.
Next, go to your RDS console and create a new parameter group with the same “Parameter group family” as your DB version on RDS.
Once created, click on the parameter group and edit the “binlog_format” variable from MIXED to ROW. Also change the “binlog_checksum” variable to NONE then save the changes.
You must then attach the parameter group you just created to your RDS instance and be sure to select “Apply immediately”.
Once that is done, you are ready to proceed to the DMS console. On this console, we will first use the “Get Started” tab. Here, we will be invited to create a replication instance. This is basically a server that will provide the compute resources to enable the replication between your source and target databases.
If your source and target databases are not within the same VPC and subnet, you may consider using a publicly accessible replication instance. Next, while the replication instance is being created, you will be invited to configure the source and target endpoints of your replication. Since we will start with AWS to Ubuntu replication, be sure to check “Select RDS DB instance” on the source side and enter your database credentials accordingly. Note: your database user for replication must have the appropriate permissions.
Once you have configured your source and target endpoints (and tested the connections), you are ready to select the databases you wish to replicate by way of creating a “replication task”. While creating the task, be sure to select “Replicate data changes only” if your data already exists on both sites (which this tutorial assumes). You may also opt to enable logging which will be stored on CloudWatch as I have below:
Finally, create the task and when you run it you should have Master – Slave replication working between AWS and your Ubuntu host. To complete the Master – Master setup, start by configuring two new endpoints from the “Endpoints” tab – one source and one target. In this case, the Ubuntu host’s credentials should be your source and RDS your target. Remember to “Select RDS DB instance” for the target.
Finally, create a new replication task from the “Tasks” tab and repeat the steps used to create the previous task everything should be working just fine. Be sure to test by performing a few CRUD transactions on both sides while noting that there may be a lag of a few seconds.
In closing, this tutorial has walked you through the process of setting up Master to Master database replication between an AWS RDS MySQL server and an Ubuntu host running MySQL via DMS. This is done by first setting up a Master – Slave setup one way and then the other.
The Hepta Analytics infrastructure team has deployed this setup in production for its clients and found it to be stable. DMS goes a step further by allowing for notifications to be setup either via SMS or email in case anything goes down which helps provide peace of mind. This is a fantastic service which enables companies with multiple sites to truly have a DRP which is ready to go and it also helps support application scaling and load balancing. Talk to us if you’re curious to learn more or implement this for your organization noting DMS can be used for purely non-RDS and non-MySQL source and target databases too.