No SUPER Permissions When Restoring an RDS for MySQL Full Backup to a Local MySQL Database

Scenario

If you want to set up a local standby MySQL database for your RDS for MySQL instance, you can restore your instance data to the local database from full backups. When you run the change master command to establish a primary/standby relationship between the local database and your RDS for MySQL instance, the following error may occur:

Error 1227

ERROR 1227 (42000): Access denied; you need (at least one of) the SUPER privilege(s) for this operation 

Possible Causes

The root user of your RDS for MySQL instance does not have the SUPER permission.

Solution

Grant the SUPER permission to the root user. Do as follows:

1. To enable password-free authentication for the local MySQL database, add skip-grant-tables=on under [mysqld] in the my.cnf configuration file. Example:

   

2. Restart the mysqld process.

   systemctl restart mysqld

3. Log in to the local database as user rdsAdmin in password-free mode.

   mysql -urdsAdmin

4. Grant permissions to the root user.

   grant all on *.* to root @'%';

   flush privileges;

   

5. To disable password-free authentication, delete skip-grant-tables=on under [mysqld] from the my.cnf configuration file.
6. Restart the mysqld process.
7. Log in to the local database as root and check the permissions.

   

Run the change master command as the root user again.