0

I have created an RDS database and have used mysqldump to send all my local data to that remote database using the following commands:

$ mysqldump -u root fishDB > localDB.sql
$ mysql -h fishinstance.xxxxxxxxx.us-east-1.rds.amazonaws.com -u mola -p fishDB < localDB.sql

Since AWS usage is costly and the database is about 1Gb (but only 5mb is new) and growing, I would like to setup a daily update and insert revised or new data instead of doing a data dump every time. I've read about possible solutions from Accessing to a remote mysql and Using Trigger to update table. Although both solution discussions are very compelling, after checking my local database, I discover that I do not have FEDERATED. I do have MyISAM.

I've also looked at Amazon RDS documentations and found it to be very limiting and can't figure out how to setup this procedure. Could you help me with setting up this update? I really appreciate your help.

Community
  • 1
vt2424253
  • 103
  • 4
  • Why not use native MySQL replication? – EEAA Apr 19 '14 at 08:26
  • Why do you think this will reduce costs? You'll be paying for the RDS whenever it's running, even if you aren't making queries. – ceejayoz Apr 19 '14 at 18:18
  • It seems you have a very clear idea how to keep cost down to a manageable level. The AWS support team advised me to split my webapp into EC2 instance and RDS instance both running inside a VPC. They told me to build the MySQL database in the RDS and the easiest way to get my database up and running was to do a mysqldump. But now that everything is working, I need to update my RDS database. Thanks for pointing me to the replication idea. Should I start another question re replication setup, or can you help me with that setup, here? If it's off top, I'll start another question. Thanks! – vt2424253 Apr 19 '14 at 22:39

1 Answers1

0

You would want to use replication for this. If you look inside RDS's Parameter groups, you will see where to set the servers. It is a bit more involved then simply flicking a switch, but that is definitely your best bet. This will keep them in constant sync and as updates happen each server will receive them depending on the configuration (master/master or master slave, etc.). You don't have to do a dump every day, and this will likely lower your I/Os which you also get billed for. I believe binary logging also has to be on if you don't already have it.

David Eisen
  • 643
  • 5
  • 21