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
已提問 1 個月前檢視次數 151 次
3 個答案
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
專家
已回答 1 個月前
AWS
支援工程師
已審閱 1 個月前
  • 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
專家
已回答 1 個月前
AWS
支援工程師
已審閱 1 個月前
  • 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
管理員
philaws
已回答 1 個月前
  • The question is about replicating with RDS as the source, not multiple sources too RDS.

您尚未登入。 登入 去張貼答案。

一個好的回答可以清楚地回答問題並提供建設性的意見回饋,同時有助於提問者的專業成長。

回答問題指南