Using the backup and restore feature of the MySQL Administrator Tool does seem to work against Amazon RDS instances. I’ve done a basic test importing a very small database containing a handful of tables (including a blob fields), a trigger, and indexes. However I came across the following error initially.
“Error while executing this query:DROP TRIGGER /*!50030 IF EXISTS */ `your\trigger_name`;_
The server has returned this 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)_
Started going down the SUPER privilege route as most people would I would imagine. I discovered very quickly that you cannot change user privileges on Amazon RDS instances. Looking at Amazons recommendations for importing data I found they recommend switching off automated backup and binary logging, during import procedure, to improve import performance and reduce storage requirements - full details at
Amazon RDS Customer Data Import Guide for MySQL
It worked like a charm!
HOWTO: Disbale Automatic Backup and Binary Logging in Amazon RDS
C:\>rds-modify-db-instance <your\DbInstanceIdentifier> —backup-retention-period 0 —apply-immediately_
This will apply the change immediately meaning the database will be unavailable while it applies and reboots the instance. This took about 5min on an empty small instance.
C:\>rds-modify-db-instance _<your\DbInstanceIdentifier> --backup-retention-period 0
This will apply the change during the next schedules maintenance window
To check if the change has been applied.
Don’t forget to switch on automatic backup after the import procedure is complete by changing the backup retention period to a value greater than 0.
You need the Amazon RDS Command Line Toolkit installed for the above command to work.