Solved RDS : Access denied; you need the SUPER privilege for thisoperation

Access denied; you need the SUPER privilege for this operation

You may get this error while trying to set values for RDS AURORA MySQL from the command line. It can be setting for long running queries or slow queries or many others.

If, you are sure you are trying to execute these changes using the master user then you can’t set these from command line.

For RDS Aurora you will have to make these changes through Parameter groups of DB and Cluster.

•  To make the change, login to your AWS RDS console.
• On the left side panel click on Parameter Groups and select the group associated with your RDS Cluster and node.
• Make changes in the parameter groups.
• Once you have saved the changes in parameter group it will start applying to your RDS cluster.

Some parameter changes will require reboot of your cluster while others can be done without reboot.  You will see pending-reboot in your cluster if it needs reboot to change the parameter.  For more details about parameter groups refer this AWS doc.

AWS Crash Course - RDS

Welcome to AWS Crash Course.
What is RDS?

• RDS is Relational Database Service of Amazon.
• It is part of its PaaS offering.
• A new DB instance can easily be launched from AWS management console.
• Complex administration process like patching, backup etc. are manged automatically by RDS.
• Amazon has its own relational database called Amazon Aurora.
• RDS also supports other popular database engines like MySQL, Oracle, SQL Server, PostgreSQL and MariaDB .

RDS Supports Multi AZ(Availability Zone) failovers.
What does that mean?
It means if your primary DB is down. Services will automatically failover to secondary DB in other AZ.

• Multi-AZ deployments for MySQL,Oracle and PostgreSQL engines utilizes synchronous physical replication to keep data on the standby up-to-date with Primary.
• Multi-AZ deployments for the SQL server engine use synchronous logical replication to achieve the same result, employing SQL server native mirroring tech.
• Both approaches safeguard your data in event of a DB instance failure or loss of AZ.
• Backups are taken from secondary DB which avoids I/O suspension to the primary.
• Restore’s are taken from secondary DB which avoids I/O suspension to the primary.
• You can force a failover from one AZ to another by rebooting your DB instance.

But RDS Multi AZ failover is not a scaling Solution.
Read Replicas are for Scaling.
What are Read Replicas?
As we discussed above Multi AZ is synchronous replication of DB.  While read replicas are asynchronous replication of DB.

• You can have 5 read replicas for both MySQL and PostgreSQL.
• You can have read replicas in different regions but for MySQL only.
• Read replica’s can be built off Multi-AZ’s databases.
• You can have read replica’s of read replica’s , however only for MySQL and this will further increase latency.
• You can use read replicas for generating reports. By this you won’t put load on the primary DB.

RDS supports automated backups.
But keep these things in mind.

• There is a performance hit if Multi-AZ is not enabled.
• If you delete an instance then all automated backups are deleted, however manual db snapshots will not be deleted.
• All snapshots are stored on S3.
• When you do a restore , you can change the engine type(e.g. SQL standard to SQL enterprise) provided you have enough storage space.

Hope the above snapshot give you a decent understanding of RDS. If you want to try some handson check this tutorial .
This series is created to give you a quick snapshot of AWS technologies.  You can check about other AWS services in this series over here .