CODEWITHSUNDEEP
mysqlsqlamazon-web-servicesetlamazon-aurora
Grant Bartel
Grant Bartel

Reputation: 363

Daily copy of AWS Aurora database to another AWS Aurora database

We have an AWS Aurora database sitting on an instance that holds all of our production data. I want to be able to perform analytics on that data without doing it in our production environment, so I want to copy the production data on a daily basis to another AWS Aurora database on a completely different instance. Within that "analytics" database, I'll build out all the needed views and stored procedures to aggregate whatever transformed data I need to store.

At first I thought of creating an Aurora replica, but of course that's read-only. I need to find a way to do this outside of the production environment and I feel it's an easy enough task to do, but I just can't find out how to do it. Maybe I haven't been able to ask the write questions, so I came here. How can I achieve this?

Upvotes: 2

Views: 1644

Answers (2)

Michael - sqlbot
Michael - sqlbot

Reputation: 179124

Replication is the correct (subjective, of course) solution, but you can't use a managed Aurora replica, which is to say you can't use an Aurora replica in the cluster.

That does not, however, mean you can't create your own asynchronous Aurora replica... which would be a second Aurora cluster, an independent master that is writable, but that uses the replication stream (the binary logs, also called "binlogs,") from the master cluster to keep its data in sync.

The one caveat: you must be extremely cautious not to write to any of the tables on the asynchronous cluster that are being replicated from the production master. Do that, of course, and replication breaks. The master cluster will be completely unaffected, but the replica cluster will stop replicating once inconsistent data is detected. But you can create additonal tables, views, and stored programs without issue.

Within an Aurora cluster, there is no need for replication in the traditional sense -- the replicas use the same backing store as the master (the "cluster volume.") Here, we're just replicating from cluster to cluster, identical to the way two ordinary MySQL servers would replicate (in one direction, only, of course).

The setup is essentially identical to the setup for replicating in and out of Aurora, to or from MySQL. Since this solution uses MySQL native replication, the steps are the same.

http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Aurora.Overview.Replication.MySQLReplication.html

Upvotes: 1

Naruto
Naruto

Reputation: 4329

This is simple AWS replication.

http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Aurora.Replication.CrossRegion.html

Also if you prefer to use mysql or any other RDBMS use

http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Aurora.Overview.Replication.MySQLReplication.html

It is similar to master slave replication with little difference in sharded data mainted in Aurora.

Upvotes: 1

Related Questions