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: