How do I use MySQL Master/Slave replication on RDS for multiple instances?

0

To use MySQL Master/Slave replication on a standard MySQL database I use the following code:

CHANGE MASTER TO MASTER_HOST='123.123.12.12',
MASTER_USER='user',
MASTER_PORT=3306,
MASTER_PASSWORD='123456',
MASTER_LOG_FILE='binlog.000004',
MASTER_LOG_POS=7947 FOR CHANNEL '123.123.12.12';

START SLAVE FOR CHANNEL '123.123.12.12';

This code does not work on AWS RDS because there is no SUPER privilegies for any user we can access:

Error: Error Code: 1227. Access denied; you need (at least one of) the SUPER or REPLICATION_SLAVE_ADMIN privilege(s) for this operation

Therefore, we need to use the stored procedures from aws mysql-stored-proc-replicating documentation. I then I start the replication using the following command:

CALL mysql.rds_set_external_master (
  host_name
  , host_port
  , replication_user_name
  , replication_user_password
  , mysql_binary_log_file_name
  , mysql_binary_log_file_location
  , ssl_encryption
);

CALL mysql.rds_start_replication;

It works fine! But the problem is I wanted to do this replication from RDS to multiple different Windows Server (with MySQL installed). To do that I would need to use FOR CHANNEL '123.123.12.12';.

  1. Is there a way I access SUPER PRIVILEGIES in RDS to run the standard CHANGE MASTER TO MASTER_HOST instead of the procedure?
  2. If not, is there another way to replicate from RDS to a MySQL installed in a Windows Server? Considering multiples Windows Server.
Soon
asked 11 days ago97 views
3 Answers
1

AWS have documented this here: https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/MySQL.Procedural.Exporting.NonRDSRepl.html, however, this isn't really for continuous use, only to export from RDS for MySQL to an external non RDS Database.

If you would like to do this on an ongoing basis to external MySQL databases from an RDS MySQL instance - then consider looking at the AWS Data Migration Service.

AWS
EXPERT
answered 11 days ago
AWS
SUPPORT ENGINEER
reviewed 11 days ago
  • My replication needs to be connected 24/7 to sync the changes whenever they are new changes. Using AWS DMS to run a task 24/7 isn't go to be a cost unsustainable cost? If so, mabe my option is to sync myself with a listener script, right?

1

Hi,

You should use RDS read replicas for what you what to achieve: https://aws.amazon.com/rds/features/read-replicas/

It's designed to allow multiple copies of your database to be distributed on different RDS instances.

Best,

Didier

profile pictureAWS
EXPERT
answered 11 days ago
AWS
SUPPORT ENGINEER
reviewed 11 days ago
  • Question is about using multiple non-RDS read replicas (hosts running MySQL on WIndows) with RDS being the master. Multiple RDS read replicas are available when they are all RDS.

0
AWS
MODERATOR
philaws
answered 11 days ago
  • The question is about replicating with RDS as the source, not multiple sources too RDS.

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions