I have a MySQL RDS instance ("production") from which I need to copy incremental data to another MySQL RDS instance ("reporting"). “Incremental data” being the new rows in certain tables. Currently I accomplish this via a PHP script which queries from “production” based on the last update time and writes on CSV files to disk and then I use mysqlimport.exe to import the files into “reporting”.
My current method takes ~80 seconds. Is this the most robust and efficient way to do this? I’ve found other tools for “one off” transfers, but nothing in terms of an “enterprise incremental database replication” tool.
Our current process is not a “continuous” replication because of batch operations on the reporting side. This may be negotiable though. Would easing that constraint open up tools that would make this easy?
Relevant related articles/questions: Amazon’s article on importing data seems to mainly deal with the batch (initial load) case. http://aws.amazon.com/articles/2933
This can be done with a stored proc when moving from one DB to another on the same instance (the problem with my case is they are different instances). See: Keeping MySQL staging database on same server updated from production
I can't put both DBs on the same instance because our instances are under quite a bit of load.