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:
...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)?
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.