MySQL master-master replication with AWS RDS and an on-premises instance

Share This:
AWS Master-Master Replication

AWS does not support master-master replication with its Relational Database Service running MySQL. It only allows you to setup replication with RDS as the master or slave. If you have an on-premises MySQL database server, and want to use RDS as a peer, making updates to either one and then have these updates propagate, you are out of luck. Or is there a way?

Here’s how to do it. In this setup, RDS instance is the master of on-premises slave server, and the on-premises server is master of RDS slave instance.

I’ll be using Ubuntu 18.04 as the base OS. Connectivity between on-premises server and AWS can be a VPN or AWS Direct Connect. You just have to make sure that IP routing is properly configured and port 3306 is open between the on-premises network and the AWS VPC where RDS instance is configured.

I am also assuming the RDS instance is going to be used to “prime” the setup. This means that the RDS instance data will be used to initialize both database server. It could be the other way around, in which case the setup process would be reversed. The on-premises server IP address is 10.0.0.101, the RDS instance address is 172.0.0.102. Unfortunately, AWS console does not display the IP address of the RDS instance. But you can find it in the AWS console by examining the network interfaces tab or by resolving the RDS instance endpoint URL:

$ dig xxxx.us-west-1.rds.amazonaws.com

With these assumptions, let’s first configure the RDS instance as master and on-premises instance as a slave.

Make sure that the RDS instance parameter group settings “auto_increment_increment” and “auto_increment_offset” are set to “2” and “1” respectively.

AWS RDS Auto Increment

Create RDS read-only replica of the master. Open AWS console, select RDS instance, and in Instance actions select “Create read replica”.

AWS RDS Read Replica

Wait for RDS master and read-only slave replica to show “Available” status.

Use a MySQL command line client to connect to the RDS master and run the following command:

mysql> call mysql.rds_set_configuration(‘binlog retention hours’, 24);

RDS does not give root user access via the command line interface. So, you have to use these AWS commands instead of native MySQL.

On RDS master create replication user:

mysql> CREATE USER ‘replicator’@’%’ IDENTIFIED BY ‘replicator’;mysql> GRANT REPLICATION SLAVE ON *.* TO ‘replicator’@’%’;

Here, the on-premises MySQL database is the slave, and the specific configuration would be “’on-premises-slave-replicator’@’10.0.0.101’”, where ’10.0.0.101’ is the IP address of the on-premises MySQL database server. As it is, we are using the ‘%’ wildcard which allows a user named ‘replicator’ to connect from any host. In production you’d want to restrict access to a specific host. We have configured the “slave” user on “master”database.

Use a MySQL command line client to connect to the RDS read-only replica and run the following commands:

mysql> call mysql.rds_stop_replication;mysql> SHOW SLAVE STATUS;

Save values of Exec_Master_Log_Pos and Relay_Master_Log_File.

Run mysqldump from RDS slave replica (assume RDS replica IP address is 172.0.0.103 and IP address of the on-premises server is 10.0.0.101) and import the backup into the on-premises MySQL database:

$ mysqldump –h172.0.0.103 –u root –p password --single-transaction --routines --triggers --databases test | mysql –h10.0.0.101 –u root –p password

This may take a few minutes if there is a lot of data or the network connection is slow. Alternatively, create the archive, upload it to the on-premises server and import it in a two-step process.

Use MySQL command line client to connect to the on-premises server. Configure is as a slave of RDS master:

mysql> CHANGE MASTER TO MASTER_HOST=’172.0.0.102', MASTER_USER=’replicator’, MASTER_PASSWORD=’replicator’, MASTER_LOG_FILE=’Relay_Master_Log_File’, MASTER_LOG_POS=Exec_Master_Log_Pos;

The user and password are what was configured previously on the RDS master side. The Relay_Master_Log_File and the Exec_Master_Log_Pos are values that were obtained earlier from the RDS slave replica. Note that Exec_Master_Log_Pos value does not have quotes around it. Use MASTER_HOST IP address of the RDS database instance (172.0.0.102 in this case).

On the on-premises MySQL server create filter to ignore tables created by AWS on the RDS instance (we don’t want them on the on-premises server):

mysql> CHANGE REPLICATION FILTER REPLICATE_WILD_IGNORE_TABLE = (‘mysql.rds\_%’)

Start replication slave on the on-premises server:

mysql> START SLAVE;

Check status:

mysql> SHOW SLAVE STATUS \G

If there are no errors, it should say:

Slave_IO_Running: YesSlave_SQL_Running: Yes

Delete RDS slave replica if it is no longer needed (it is not needed for this procedure).

Now, let’s configure replication from on-premises server master to AWS RDS server slave.

Connect to the on-premises server. I use SSH to login with bash shell. Open the /etc/mysql/my.cnf (that’s the default location in Ubuntu) in an editor and add the following configuration:

[mysqld]server-id=101auto-increment-increment = 2auto-increment-offset = 2log-bin=/var/lib/mysql/log-bin.logbinlog-ignore-db=mysql

The server-id can be any integer. Here we use a part of the host IP address (this is not configurable on the RDS side by the way). Restart the database server:

$ sudo /etc/init.d/mysql restart

or

$ sudo systemctl restart mysql

Use MySQL command line client to connect to the on-premises server. Create replication user:

mysql> CREATE USER ‘replicator’@’%’ IDENTIFIED BY ‘replicator’;mysql> GRANT REPLICATION SLAVE ON *.* TO ‘replicator’@’%’;

Here, the RDS instance is the slave, and the specific configuration would be ’rds-slave-replicator’@’172.0.0.102’, where ’172.0.0.102’ is the IP address of the RDS instance. As it is, we are using the ‘%’ wildcard which allows a ‘replicator’ user from any host to connect. Again, what this means is that the user login credentials will be used by the slave database to connect and perform replication.

While still connected to the on-premises MySQL database, retrieve and save the binlog location on the on-premises database server for use in the subsequent configuration step:

mysql> FLUSH TABLES WITH READ LOCK;mysql> SET GLOBAL read_only = ONmysql> SHOW MASTER STATUS;

Output will be something like this:

+----------------+----------+--------------+------------------+----+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | ...|
+----------------+----------+--------------+------------------+----+
| log-bin.000006 | 154 | | mysql | ...|
+----------------+----------+--------------+------------------+----+

Save the file and position values (log-bin.000006 and 154 in this case).

Make the database writable again:

mysql> SET GLOBAL read_only = OFF;mysql> UNLOCK TABLES;

Use MySQL command line client to connect to RDS instance. Configure RDS as the slave of the on-premises database server:

mysql> call mysql.rds_set_external_master (‘10.0.0.101’, 3306, ‘replicator’, ‘replicator’, ‘ log-bin.000006 ‘, 154, 0);

Use the IP address of the on-premises database server. The user id and the password are those of the user created on the on-premises server. The RDS server will use this information to connect to the on-premises server as a slave and perform replication. The log file and position values are from the on-premises server SHOW MASTER STATUS; command output.

Start replication on the RDS instance as a slave of the on-premises server:

mysql> call mysql.rds_start_replication;

Check status:

mysql> SHOW SLAVE STATUS \G

If there are no errors, it should say:

Slave_IO_Running: YesSlave_SQL_Running: Yes

Now you can create databases, add tables and data on both on-premises server and the AWS RDS database. The changes will be replicated from on-premises server to AWS RDS and from AWS RDS to the on-premises server.

This configuration has advantages and disadvantages. It uses existing MySQL functionality without resorting to any third-party tools. It works equally well with RDS or EC2-based databases (although I specifically wanted to leverage RDS). One major disadvantage in my opinion is that it is difficult to have a configuration that has more than two instances. A slave can have only one master! The workaround is to create a circular replication setup.

MySQL Replication

The downside with this setup is that there will be a single point of failure. If any instance in the circle fails, replication will be broken.

References

Share This:

Popular on WPSteam Right Now!

Leave a Reply

Your email address will not be published. Required fields are marked *

I need help with...