Understanding how Fivetran MySQL connectors affect MAR
Connector Sync Strategy and Options
- The first initial sync or “historical” sync will replicate the selected MySQL tables into your destination warehouse using SELECT statements.
- Subsequent incremental syncs utilise the MySQL binary logs to replicate changes.
What Contributes Towards MAR?
There are 5 primary actions that will contribute to MAR:
- A new row is added to an existing table
- A value is updated for an existing row in a table
- A value is deleted within a table
- A new column which has values is added to a table
- A manual or automatic resync is triggered
Managing MAR for this Connector
- If Fivetran cannot load data for longer than the binlog retention period (recommended 7 days) it will need to do a full database re-sync which means incurring MAR.
- Column blocking to reduce MAR is only applicable to tables that are without Primary keys in MySQL. This is because we create a synthetic primary key based on a commonly changing column and other unique keys. If the commonly changing column is blocked then this will reduce MAR.
- Certain DDL changes require a full table re-sync and will incur MAR:
- Creating, deleting, or changing the position of a column.
- Changing the column definition for ENUM type columns.
- It is recommended not to pause the connector for too long because then Fivetran will need to perform a full re-sync if the binlogs have run out.