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.

Solved: How to reset RDS master user password

In this post we will show you how to reset  the master user password of an RDS DB instance. The new password after reset will be effective immediately.

• Go to your AWS RDS console.
• Select the DB instance whose master password you want to change. Do note that the DB instance should be in available state and not in backing up or any other state.
• Once you have selected the instance, select Modify from Instance Actions dropdown.
• It will take you to a new page  :- Modify DB Instance: cloudvedasdb
• Scroll down to the Setting section and look for New master password.
• Enter the new password in the text box and at the bottom click on continue.
• Select if you want to change password immediately or during next maintenance as this will need reboot.
• Finally click on Modify DB instance.
• The new password will be effective depending on what option you chose above.

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 .