1

I have a production DB (AWS RDS MySQL instance) that my app interacts with and that collects all sorts of data.

I have been asked to create nightly snapshot of this production DB and then use that snapshot to create a new DB instance (restored from the snapshot) that will be used as an analytics DB and several analytical tools will connect to it and run analyses/reports against it.

For reasons outside the scope of this question, configuring the production DB with a read replica is not possible, but yes, that would solve the problem I have. Which is...

If every night I am:

  1. deleting the existing analytics DB
  2. creating a snapshot of the production DB
  3. creating a new analytics DB
  4. restoring the new analytics DB with the latest snapshot

...then every night the analytical tools will need their connection info to be updated so that they can connect to the new instance, and this will get cumbersome. The username and password and database name will always be the same (each night), but the host information will change since it will always be a new RDS instance.

Again, if there was a dedicated read replica feeding the analytics DB, there would be no problem here. I would provide my analytical tools with the read replica's connection string info one time and I'd be all set. But again, the read replica solution is not possible for reasons outside of my control.

So I ask: is there any Route53, ELB or other "AWS magic" I can use to give my analytical tools a set host name "proxy" that somehow points to the new analytics DB (even though its being re-created each and every night)?

hotmeatballsoup
  • 385
  • 6
  • 58
  • 136
  • 1
    Is this process automated, or you are doing all this manually? Also, your analytical tool runs inside a VPC, on an ec2 instance? – Marcin Mar 06 '21 at 03:15
  • Hi @Marcin, yes, these tools run on EC2 instances inside the same VPC as the analytics DB – hotmeatballsoup Mar 06 '21 at 03:21

1 Answers1

1

I think one way to overcome your issue would involve creating private hosted zones (PHZ) in Route53 associated with your VPC. Using the PHZ you could create an alias record for your proxy, e.g. proxy.private. So you could use proxy.private in-place of a default url created by the RDS proxy.

Off course, since you create new proxy every day, the record in the PHZ would have to be updated, so that proxy.private points to the new proxy url. The updated of the record in PHZ would depending on how you are setting up the new proxy everyday (using CloudForamtion, fully manual, lambda funcion, ...).

But the most general way, would be to setup EventBridge (EB) rule for API events (CloudTrail trail required) which would listen for API events associated with creation of the proxy (CreateDBProxy). The EB rule could trigger a lambda function in a response to CreateDBProxy event which would wait for the proxy to be available and update the record automatically.

Marcin
  • 215,873
  • 14
  • 235
  • 294
  • Thanks @Marcin (+1) this is a very compelling answer. Let me provide a bit more detail here that might refine your answer some more. I will be doing all of this work from inside a Java Lambda, using the Java SDK obviously (so yes, this is **automated**). So I could set up the alias `proxy.private` manually inside Route53, one time. But then I'd need to update the `proxy.private` alias from inside the lambda. To your knowledge is this possible to do with the AWS SDK (Java, Python or otherwise)? Thanks again! – hotmeatballsoup Mar 06 '21 at 04:15
  • 1
    @hotmeatballsoup Yes, I don't see the issue with making such a lambda functions. Lambda would use [AWS SDK](https://aws.amazon.com/tools/) for java, python or any other language is supported by lambda, to interact with route53 and the rds proxy. The question is how do you trigger the function, and this depends on how the proxy is created. – Marcin Mar 06 '21 at 04:19
  • 1
    Thanks again, I'll wait until tomorrow just to see if there are any other ideas, but I like this one and will very likely mark it as the accepted answer in the morning. – hotmeatballsoup Mar 06 '21 at 04:24
  • @hotmeatballsoup Yes, no problem :-) – Marcin Mar 06 '21 at 04:25
  • BTW @Marcin, I [took a crack at doing this myself](https://stackoverflow.com/questions/66502691/aws-java-sdk-updating-a-route53-zone-record-with-new-rds-instance-info) but am running into issues with finding the IP address of the new DB instance, and am unable to update the record. If you are familiar with the Route53 and could shed some light on this, I'd happily give you the green check for that question as well! Thanks again! – hotmeatballsoup Mar 06 '21 at 15:21