janaka.co.uk

Amazon RDS - Importing a database using the MySQL Administrator tool

07 April 2010

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.


Personal blog by Janaka Abeywardhana. Thoughts on topics other than Software Engineering and Product Management. All of that is over at [janaka.dev](janaka.dev) by Janaka Abeywardhana. On Github, Twitter, and Instagram

© 2000-2021. Built with Gatsby. Deployed on the Distributed Web using IPFS and Fleek