Wednesday, 7 April 2010

Amazon RDS - Importing a database using the MySQL Administrator tool

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)
MySQL Error."

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

C:\>rds-describe-db-instances --headers

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.

No comments: