Search code examples
mysqlamazon-rds

MySQL Replication Error on AWS RDS: Access Denied for SUPER or REPLICATION_SLAVE_ADMIN Privileges

I am logged in to AWS RDS database using MySQL Workbench.

MySQL version: 8.0.35 Logged in using master user: admin@'%'

We do a replication from Master to Slave between a Windows Server and another Windows Server:

Windows Server (MySQL - Master) --> Windows Server (MySQL - Slave)

As we've changed to AWS, now we need to do the same replication but from RDS to a Windows server:

AWS RDS (MySQL - Master) --> Windows Server (MySQL - Slave)

To do the replication we run the following command:

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';

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

RDS user is master user (admin), there is another user rdsadmin, but is private to localhost. RDS does not give SUPER privilegies to admin user.

1 - Is there a way to give SUPER/REPLICATION_SLAVE_ADMIN privilegies to admin user, or through parameter group?

2 - If not, isn't it possible to do Master/Slave replication on RDS?

Solution

  • RDS doesn't grant users the SUPER privilege, even to the root user.

    Instead, AWS RDS implements a set of stored procedures to control replication. You can call the procedures, and the procedures manage replication in the manner prescribed by AWS.

    See https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/mysql-stored-proc-replicating.html for documentation on these procedures.