Replicating data from MySQL to Redshift?

  • 10Views
  • Last Post 4 weeks ago
0
votes
Jamie Biggar posted this 4 weeks ago

I have a MySQL database sitting in Amazon that I want to create an exact replica of in Redshift. The data will never change on Redshift, so a one way sync is totally fine.

 

I tried to set up a replication job, but I can't pick MySQL as the source. I can select MySQL as the source for an import job, but then have to set up all the tables and relationships manually. Is that a bug or a feature? If it has to be manual, do you have any tips and tricks for creating the correct tables and structures?

 

0
votes
Mariia Zaharova posted this 4 weeks ago

 Hi! It is impossible to use relational database as a Source in Replication packages. This is as-designed behaviour. You can take a look at the Import package. Import package allows loading data from CSV files, cloud application objects or relational database tables to another cloud application or relational database. Skyvia Import supports all DML operations (including Upsert), and thus can be used for performing massive data updates.

One of our users found this workaround for target tables creation:

1) Create OData endpoint with your MySQL tables: https://skyvia.com/resources/docs/index.html?how_to_configure_odata_endpoint.htm

2) Create OData connection with this endpoint: https://skyvia.com/resources/docs/index.html?odata_connections.htm

3) Use this OData connection as a Source in Replication package (without 'Incremental Updates' option, but with the 'Create Tables' one). In order to just create target tables and limit records being transferred via OData protocol you can add filter conditions for each task.

4) Then, you can use Import package with an Upsert operation and MySQL connection as a Source, Redshift connection as a Target.

 

Please let us know if this workaround suits you.

Close