I&#39;m trying to create a trigger on a table in my Amazon RDS database, and I can&#39;t seem to make it happen.

I tried to create a trigger on a table in the mysql client I use (Navicat), and got the error that I needed the SUPER privilege to do so. After some searching, I found that you could `SET GLOBAL log_bin_trust_function_creators = 1` to get around this. I tried that using these instructions: http://getasysadmin.com/2011/06/amazon-rds-super-privileges/ (and then restarting the DB server for good measure), but no luck.

I also tried creating the trigger and setting the variable via the mysql commmand line to make sure Navicat wasn&#39;t adding anything unwanted to my sql commands, but that failed, too. It also seems from searching that there&#39;s no way to grant yourself the SUPER privilege.

So ... is creating a trigger possible in RDS?


Can I CREATE TRIGGER in an rds DB?

I am using AWS RDS for MySQL. When it comes to backup, I understand that Amazon provides two types of backup - automated backup and database (DB) snapshot. The difference is explained [here][1]. However, I am still confused: should I stick to automated backup only or both automated and manual (db snapshots)?

What do you think guys? What&#39;s the setup of your own? I heard from others that automated backup is not reliable due to some unrecoverable database when the DB instance is crashed so the DB snapshots are the way to rescue you. If I am to do daily DB snapshots as similar settings to automated backup, I am gonna pay much bunch of bucks.

Hope anyone could enlighten me or advise me the right set up.

  [1]: http://aws.amazon.com/rds/faqs/#23

Should I stick only to AWS RDS Automated Backup or DB Snapshots?

I have a few questions in regards to upgrading the RDS instance.

1. What is the downtime when upgrading the instance from let&#39;s say small to large. Is the downtime relatively similar when you go and change any instance type(small, large, xlarge) or are there determining factors such as database size that alter the timing.
2. Can anyone share a technique of how to upgrade the instance type avoiding the downtime using RDS? Is that even possible in RDS. It doesn&#39;t have to be in great detail just some cliff-notes/big-picture stuff.
3. Is there down time when you allocate more disk space?

AWS RDS instance upgrade down time

So I try to import sql file into rds (1G MEM, 1 CPU). The sql file is like 1.4G

mysql -h xxxx.rds.amazonaws.com -u user -ppass --max-allowed-packet=33554432 db &lt; db.sql

It got stuck at:

    ERROR 1227 (42000) at line 374: Access denied; you need (at least one of) the SUPER privilege(s) for this operation

The actual sql content is:

    /*!50003 CREATE*/ /*!50017 DEFINER=`another_user`@`1.2.3.4`*/ /*!50003 TRIGGER `change_log_BINS` BEFORE INSERT ON `change_log` FOR EACH ROW
    IF (NEW.created_at IS NULL OR NEW.created_at = &#39;00-00-00 00:00:00&#39; OR NEW.created_at = &#39;&#39;) THEN
            SET NEW.created_at = NOW();
    END IF */;;


`another_user` is not existed in rds, so I do:

    GRANT ALL PRIVILEGES ON db.* TO another_user@&#39;localhost&#39;;

Still no luck.

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

I realize a couple of basic differences between the two, i.e.

1. EC2 is going to be cheaper

2. RDS I wouldn&#39;t have to do maintenance

Other than those two, are there any advantages to running my database from RDS as opposed to a separate EC2 server acting as a MySQL server. Assuming similar instance sizes, are both going to run into the same limitations in terms of being able to handle a load?

To give you a little bit more info about my use, I&#39;ve got a database, nothing too big or anything (biggest table 1 million rows), just high SELECT volume.

What are the respective advantages/limitations of Amazon RDS vs. EC2 with MySQL?

I&#39;m attempting to copy my mysql database from an Amazon EC2 to an RDS:

I successfully did a `mysqldump` of my database into my root folder using this:

    root@ip-xx-xx-xx-xx:~# mysqldump my_database -u my_username -p &gt; my_database.sql

Then I tried to transfer this .sql file to my new RDS database:

    root@ip-xx-xx-xx-xx:~# mysql my_database -u my_username -p -h  
    my_new_database.xxxxxxxxx.us-east-1.rds.amazonaws.com &lt; my_database.sql

Unfortunately, I get following error message:

    You do not have the SUPER privilege and binary logging is enabled 
    (you *might* want to use  the less safe log_bin_trust_function_creators variable)

I tried to `GRANT SUPER..` in a variety of ways but I&#39;m getting errors when I try to do that too. Typing `mysql &gt; FLUSH privileges;` doesn&#39;t work either. 

I&#39;m a mysql beginner so sorry for such an easy question. Thoughts?

MySQL/Amazon RDS error: &quot;you do not have SUPER privileges...&quot;

I have created a snapshot of my instance and made some unwanted changes in DB.

Now I want to restore my instance from this snapshot.

When I try to do it - it creates me one more instance, additionally to the one I have.

I specify &quot;DB Instance Identifier&quot; and after that I get two instances with the same ID.

So my question: Is there any way to restore snapshot to existing instance?

Because in other case - new instance is created with differrent endpoint (hostname) and I need to change my configs to access database. Or there is a better way to manage such cases?

Amazon RDS: Restore snapshot to existing instance

I am an Amazon RDS customer and am experiencing daily amazon RDS write latency spikes, corresponding roughly to the backup window. I will also see spikes at the end of a snapshot (case in point: running a snapshot takes appx 1 hour, and in the final 5 minutes, write latency spikes). I am running a multi-AZ m1.large deployment. 

Is there anyone on Stack who can explain how Amazon RDS backup is *actually* working?  I&#39;ve read the Amazon RDS docs, and as far as I can tell, Amazon RDS is not behaving according to spec. Specifically, these backup/snapshot operations should be hitting my replica, and therefore not causing any downtime/performance hit, or so I thought. 

I can distill my problem into six questions:

* What is technically happening during a snapshot and a backup, and how are they different? (If you answer this question, please tell me if you are able to empirically confirm your answer, or are simply quoting me documentation).
* Is a spike in write latency to be expected during the backup window on a multi-AZ deployment?
* Is a spike in write latency to be expected at the end of a snapshot on a multi-AZ deployment?
* Would my write latency spike be even higher if I was not multi-AZ ?
* Architecturally, would I be able to avoid these write latency spikes if I rolled my own database running on two m1.large EC2 instances? 
* Are there any configurations I can use that would avoid these write latency spikes while still hosting my DB with RDS, or am I effectively at the mercy of Amazon? 

Bonus Question: where and how do you host your mysql database?

I can say that I have been generally happy with RDS except for these daily write latency issues. I love the built-in database monitoring and it was fairly simple to setup and get going. 

Thanks!

![amazon RDS write latency][1]


  [1]: http://i.stack.imgur.com/YgyYB.png

How does Amazon RDS backup/snapshot actually work?

AWS documentation has this [page][1] that talks about importing and exporting data from MySQL server, but it&#39;s mostly about import. The only thing I see in their documentation is a way to export 5.6 data using replication, which is documented [here][2]. I was wondering if there is a simpler way to export data using mysqldump and load in local database. The database that I want to export is not huge, may be 1GB, so size is not a issue.

  [1]: http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/MySQL.Procedural.Importing.html
  [2]: http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/MySQL.Procedural.Exporting.NonRDSRepl.html

How to export database from Amazon RDS MySQL instance to local instance?

I&#39;ve searched the web and browsed the RDS docs, yet I can&#39;t seem to find an open connection limit. 

For what it&#39;s worth, I&#39;m planning on using the new Postgres flavor of RDS, but I suppose an answer from the mySQL side of the house would be acceptable as well.

Thanks!

Amazon RDS (postgres) connection limit?

I recently downgraded my EC2 instance.  I can no longer connect to RDS.  I think it might be that the internal IP is different and now the logins are attached to that specific IP.  I haven&#39;t been able to figure it out.  I would like to be able to get a backup from the snapshot.  Is there a way to download it through AWS?

download RDS snapshot

I have a MySQL database running in [Amazon RDS][1], and I want to know how to export an entire table to CSV format.

I currently use MySQL server on Windows to query the Amazon database, but when I try to run an export I get an error, probably because there&#39;s no dedicated file server for amazon RDS. Is there a solution to this?

  [1]: https://en.wikipedia.org/wiki/Amazon_Relational_Database_Service






Exporting a table from Amazon RDS into a CSV file

The EC2 instance/live web can connect just fine to the RDS database. But when I want to debug the code in my local machine, I can&#39;t connect to the database and got this error:

&gt; OperationalError: (2003, &quot;Can&#39;t connect to MySQL server on &#39;aa9jliuygesv4w.c03i1
ck3o0us.us-east-1.rds.amazonaws.com&#39; (10060)&quot;)

I&#39;ve added `.pem` and `.ppk` keys to `.ssh` and I already configure EB CLI. I don&#39;t know what should I do anymore.

FYI: The app is in Django

AWS: can&#39;t connect to RDS database from my machine

I need to create a new MySQL user with limited permission on an existing Amazon RDS instance.  After encountering a couple error messages I was sort of able to do this using the official MySQL Administrator tool and the user now appears in the list.  However, I&#39;m unable to assign any schema privileges as all the users are greyed out.  I&#39;m logged in as the &quot;master user&quot; created when the instance was launched.  Not sure where to go from here.  I do have the RDS command line tools installed but wasn&#39;t able to track down anything there either. Ideas

Creating A New MySQL User In Amazon RDS Environment

Will I am able to switch (I mean upgrade or downgrade) Amazon RDS instance on need basis or do I have to create a new afresh and go through migration?

Are Amazon RDS instances upgradable?

From EC2 instance i-78a8df00, I&#39;m trying to connect to RDS instance mysql.************.us-east-1.rds.amazonaws.com. They are both in the U.S. East region. I added the security group of EC2 instance (sg-********) to the RDS security group, but that didn&#39;t help. It appears to be a firewall/DNS issue as it is timing out when running this command:

    ubuntu@ip-10-195-189-237:~$ mysql -h mysql.************.us-east-1.rds.amazonaws.com
&gt; ERROR 2003 (HY000): Can&#39;t connect to MySQL server on &#39;mysql.************.us-east-1.rds.amazonaws.com&#39; (110)

I can connect to RDS instance fine from my local machine using the same line as above. I tried various forum solutions but those don&#39;t help.

Can&#39;t connect to RDS instance from EC2 instance

As I understand it, RDS Provisioned IOPS is quite expensive compared to standard I/O rate.

In Tokyo region, P-IOPS rate is 0.15$/GB, 0.12$/IOP for standard deployment. ([Double the price for Multi-AZ deployment...](http://aws.amazon.com/rds/pricing/))

For P-IOPS, the minimum required storage is 100GB, IOP is 1000. 
Therefore, starting cost for P-IOPS is 135$ excluding instance pricing.

For my case, using P-IOPS costs about 100X more than using standard I/O rate.

This may be a very subjective question, but please give some opinion. 

In the most optimized database for RDS P-IOPS, would the performance be worth the price?

or

The [AWS site](http://aws.amazon.com/rds/#PIOPS) gives some insights on how P-IOPS can benefit the performance. Is there any actual benchmark?

# SELF ANSWER
In addition to the answer that zeroSkillz wrote, I did some more research. However, please note that I am not an expert on reading database benchmarks. Also, the benchmark and the answer was based on EBS.

According to [an article](http://iomelt.com/iomelt-aws-ebs-benchmark-december-2012/) written by &quot;Rodrigo Campos&quot;, the performance does actually improve significantly.

From 1000 IOPS to 2000 IOPS, the read/write(including random read/write) performance doubles. From what zeroSkillz said, the standard EBS block provices about 100 IOPS. Imagine the improvement on performance when 100 IOPS goes up to 1000 IOPS(which is the minimum IOPS for P-IOPS deployment).

### Conclusion
According to the benchmark, the performance/price seems reasonable. For performance critical situations, I guess some people or companies should choose P-IOPS even when they are charged 100X more.

However, if I were a financial consultant in a small or medium business, I would just scale-up(as in CPU, memory) on my RDS instances gradually until the performance/price matches P-IOPS.

AWS RDS Provisioned IOPS really worth it?

I have an Amazon RDS instance. Freeable Memory has been declining since setup over 1-2 weeks, starting from 15GB of memory down to about 250MB. As it has dropped this low in the last days, it has started to resemble a sawtooth pattern where Freeable Memory drops to this range (250 - 350MB) and then goes back up agin to 500 - 600MB in a sawtooth pattern. 

There has not been any notable decline in application quality. However, I am worried that the DB will run out of memory and crash. 

Is there a danger that the RDS instance will run out of memory? Is there some setting or parameter I should be looking at to determine if the instance is set up correctly? What is causing this sawtooth pattern? 

![Freeable Memory Declining][1]


  [1]: http://i.stack.imgur.com/ZX71y.jpg

Amazon RDS running out of freeable memory. Should I be worried?

I got a database server failure, says host is blocked because of many connection errors. It ask me to unblock with &#39;mysqladmin flush-hosts&#39;

how and where should I  run this command to our amazon rds database server?

thank you

how to run command &quot;mysqladmin flush-hosts&quot; on Amazon RDS database Server instance?

I am creating an RDS instance using CloudFormation using this:

&lt;!-- language-all: lang-json --&gt;

&lt;pre&gt;
&quot;Resources&quot;: {
        &quot;myDB&quot;: {
            &quot;Type&quot;: &quot;AWS::RDS::DBInstance&quot;,
            &quot;Properties&quot;: {
                &quot;AllocatedStorage&quot;: &quot;5&quot;,
                &quot;DBInstanceClass&quot;: &quot;db.m1.small&quot;,
                &quot;Engine&quot;: &quot;MySQL&quot;,
                &quot;EngineVersion&quot;: &quot;5.5&quot;,
                &quot;DBName&quot;: &quot;mydb&quot;,
                &quot;MasterUsername&quot;: {
                    &quot;Ref&quot;: &quot;DBUser&quot;
                },
                &quot;MasterUserPassword&quot;: {
                    &quot;Ref&quot;: &quot;DBPassword&quot;
                },
                &quot;DBParameterGroupName&quot;: {
                    &quot;Ref&quot;: &quot;myRDSParamGroup&quot;
                }
            }
        }
&lt;/pre&gt;

and it all works.  But I need to run initial SQL on the DB when its created, to setup my apps schema.  My current approach is to have the app self migrating, but I&#39;d like to do it in the CloudFormation definition.  Is this possible?

All Amazon Rds Solutions on Gang of Coders

Total of 20 Amazon Rds Solutions