Mysql – update and insert MySQL DB on AWS RDS with local MySQL DB

amazon ec2amazon-web-servicesMySQLmysql-replication

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.

Best Answer

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.

Related Topic