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
gefragt vor einem Monat151 Aufrufe
3 Antworten
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
EXPERTE
beantwortet vor einem Monat
AWS
SUPPORT-TECHNIKER
überprüft vor einem Monat
  • 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
EXPERTE
beantwortet vor einem Monat
AWS
SUPPORT-TECHNIKER
überprüft vor einem Monat
  • 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
beantwortet vor einem Monat
  • The question is about replicating with RDS as the source, not multiple sources too RDS.

Du bist nicht angemeldet. Anmelden um eine Antwort zu veröffentlichen.

Eine gute Antwort beantwortet die Frage klar, gibt konstruktives Feedback und fördert die berufliche Weiterentwicklung des Fragenstellers.

Richtlinien für die Beantwortung von Fragen