'How to Create all tables of an RDS instance in a new instance in a new Region

So here is what I need. I have two RDS instances setup in a specific way (Security Groups, Naming convention, Subnet, encryption, etc). I need empty replicas of this database in a new region as the data needs to be stored locally for each region. Also each database is accessed by it's root user using a randomly generated password, but ideally they should not be the same password accross region. Final requirement is that we would like all this to be scriptable (Cloud Formation or a series of AWS CLI calls. We can obvsiously generate the random password ourselves and insert into the script at some point)

So one option is to:

  1. Create a read replica of the DB into a new region. (Setting everything appropiately)
  2. Promote to regular DB.
  3. Change it's master password.
  4. Truncate all tables.

But I would like to know if there is an option to simply create (from zero) each DB in the new region with the new password, setup and everything and then simply run a script (SQL script) that generates the empty tables. This seems like the most script-friendly solution. My problem is that I don't know how I can authomatically generate a SQL script that is simpy a bunch of create for the tables with the exact same structure as the databases already running. Is there a way to do this? Or is my first option better (from a how-easy-it-is-to-script point of view?



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source