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.

ログインしていません。 ログイン 回答を投稿する。

優れた回答とは、質問に明確に答え、建設的なフィードバックを提供し、質問者の専門分野におけるスキルの向上を促すものです。

質問に答えるためのガイドライン

関連するコンテンツ