title: Running a cross-regional query in Amazon RDS with blacklisted schemas
body: <p>I have a set of Amazon RDS instances spread across multiple regions. Each RDS instance has a similar set of tables prefixed in a predictable way.</p>
  <p>For example, I have the database for 50 universities in EU-West, and 50 universities in US-East, and one DB per university (100 databases across two regions). I want to get the total count of students across all databases. We have the connection configuration for individual databases listed in a DynamoDB table.</p>
  <p>Currently, if we want to run a MySQL query across our entire dataset, we would do it by the following steps:</p>
  <ol>
  <li>Get all the connection configurations from DynamoDB</li>
  <li>Check against a blacklist to filter out schema by wildcard</li>
  <li>Loop through each connection configuration in an application script (in this case: PHP)</li>
  <li>Submit the MySQL query to each database individually, writing a CSV of the results.</li>
  </ol>
  <p>This workflow is slow, as it is single-threaded, and it is difficult to make it fail gracefully. It has the pros of being somewhat flexible in terms of whitelisting, but requires a code change to allow any sort of non-rigid queries.</p>
  <p>I have looked into the following and have run into some problems:</p>
  <ul>
  <li>Amazon Athena, Aurora, Glue, and Redshift all do something similar, but seem to not have strong cross-regional capabilities.</li>
  <li>Federated tables in MySQL can work cross-region, but require more maintenance and configuration each time we would add a new database.</li>
  <li>For the size of our dataset replicating everything into an S3 bucket or a Datalake would be cost-prohibitive on the grounds of the data-transfer cost.</li>
  </ul>
  <p> Is there a workflow or technology which will allow me to overcome the shortcomings of the single-threaded, application driven method? </p>