Continuously replicate AWS Aurora MySQL to Redshift

This answer assumes you already have Glue connections to both Aurora and Redshift set up and have created a crawler for the Aurora database with an include path similar to "schema/%" to fetch all tables in that schema.

Given that, when you create a Glue Job in the console, you select a single table from the Glue Data Catalog as the source. As the target, you choose to "Create tables in your data target" and select the Redshift connection previously created and specify the target table. Once that Glue Job is created, you can create a Trigger to run it on a scheduled basis.

Now you're set up for one table, but if you wanted to do this for every table in that schema, you would have to modify the Glue script and either hard-code the list of tables that you want to sync and loop through them in the code, or you could also fetch the list of tables from the Glue API using the boto3 client if you're using pyspark.

If you want to run this continuously, there are a couple things to mention:

1. For tables that have sequential primary keys, you can enable job bookmarks as mentioned here such that only new data is inserted into Redshift: https://docs.aws.amazon.com/glue/latest/dg/monitor-continuations.html
2. If that's not an option, you'll likely need to truncate the table on every run. You can do this by adding a "preactions" key to the connection options when writing the dynamic frame.

"preactions":"truncate table target_table;"

Without doing one of those, duplicate data will continue to get loaded into Redshift.

For ongoing replication, Database Migration Service may be an option here. I realize there's more infrastructure to set up, but it replicates data using the binary logs and can be more efficient as a result. It can also do this continuously if near-real-time replication is important to you.