Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

How do I go about moving certain data (returned by a simple query) from one database in AWS' RDS to another?

An interruption of up to 20 mins is acceptable, although not preferred.

My current plan is to use Sequel Pro to perform an Export, and then Import it into the target database, but I'm wondering if there's a better way to do it.

BTW: Database is mostly InnoDB and some MyISAM tables.

share|improve this question
add comment

5 Answers

Can't you setup replication? It would be the safer/faster way to do it, but I am not sure you can replicate only certain queries (you can do it with specific databases, though)

share|improve this answer
    
AWS RDS doesn't allow custom replication unfortunately. –  iqq Jun 6 '11 at 9:59
add comment

Your question is far from clear but if I interpret it correctly the simplest method is to use a select into query, which inserts the results of a query into a table, which can then by copied to the target system using dump and load, or replicated as per coredump's suggestion.

share|improve this answer
    
This seems like the most viable way for me to move data so far –  iqq Jun 6 '11 at 9:59
add comment

If they are both MySQL databases then perhaps you could use the federated engine (which facilitates access to databases on remote servers) to update the remote db using a scheduled task to update as often as required.

share|improve this answer
1  
Except Amazon's RDS service unfortunately doesn't--at least at the moment--support the federated storage engine. –  denishaskin Aug 17 '12 at 20:20
    
It should be possible to use a non-RDS instance as stepping stone. Simply spin up an EC2 instance, configure MySQL, add the federated tables from both RDS instances, and you are set. –  sfussenegger Nov 15 '12 at 10:14
add comment

The only way I can think of to move data from one RDS server to another RDS is to mysqldump the table from a source MySQL RDS Instance. Then, load the table into the target MySQL RDS Instance.

WARNING : There may be charges for data transfer !!!

According to the AWS RDS Documentation under Data Transfer

The pricing below is based on data transferred “in” and “out” of Amazon RDS.

Region: (Pricing Varies Between Regions, Shown is North Virgina Pricing)

                            Pricing
Data Transfer IN
All data transfer in        $0.000 per GB
Data Transfer OUT***
First 1 GB / month          $0.000 per GB
Up to 10 TB / month         $0.120 per GB
Next 40 TB / month          $0.090 per GB
Next 100 TB / month         $0.070 per GB
Next 350 TB / month         $0.050 per GB
Next 524 TB / month         Contact Us
Next 4 PB / month           Contact Us
Greater than 5 PB / month   Contact Us

Footnotes

  • Data transferred between Amazon RDS and Amazon EC2 Instances in the same Availability Zone is free.
  • Data transferred between Availability Zones for replication of Multi-AZ deployments is free.
  • Amazon RDS DB Instances outside VPC: For data transferred between an Amazon EC2 instance and Amazon RDS DB Instance in different Availability Zones of the same Region, there is no Data Transfer charge for traffic in or out of the Amazon RDS DB Instance. You are only charged for the Data Transfer in or out of the Amazon EC2 instance, and standard Amazon EC2 Regional Data Transfer charges apply ($.01 per GB in/out).
  • Amazon RDS DB Instances inside VPC: For data transferred between an Amazon EC2 instance and Amazon RDS DB Instance in different Availability Zones of the same Region, Amazon EC2 Regional Data Transfer charges apply on both sides of transfer.
  • Data transferred between Amazon RDS and AWS services in different regions will be charged as Internet Data Transfer on both sides of the transfer.
  • ** As part of AWS’s Free Usage Tier, new AWS customers will receive free 15 GB of data transfer out each month aggregated across all AWS services for one year.
  • *** Rate tiers take into account your aggregate Data Transfer Out usage across Amazon EC2, Amazon S3, Amazon RDS, Amazon SimpleDB, Amazon SQS, Amazon SNS, AWS Storage Gateway, Amazon DynamoDB and Amazon VPC.
share|improve this answer
add comment

This thread is a little dated. AWS now offers the Data Pipeline product for this purpose. However I had the same need to move partial data on a regular basis from one DB to another (different RDS servers). The approach i took was to create a cron job hosted with Iron.io. The service also runs on AWS US East so there isn't much network latency. Connect to one server, grab your data and format it. Then connect to the other server and update. You can run jobs with Iron Worker as frequently as every 5 mins.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.