How Can I Migrate to a New Database Without Performing a Re-Sync?

Question

How can I configure my connection to sync from a new database without triggering a re-sync?

Environment

• Connector: MySQL
• Update method: Binary log

Answer

When you update your database in Fivetran, we may lose track of the MySQL binary log (binlog) position. When this happens, we perform a re-sync to capture the correct position and resume incremental syncs. These re-syncs are free. For more information, see our Re-syncs pricing documentation

If you want to avoid a re-sync and manually adjust the binlog position, contact our support team to coordinate the migration and update process. The support team will confirm the steps you must take, though you'll likely need to do the following:

1. Pause all writes on your current MySQL database.

2. In Fivetran, run a final sync and wait for it to complete.

3. Pause the connection.

   i. Go to your MySQL connection page.

   ii. In the top right corner, set the ENABLED toggle to OFF.

4. Migrate to the new database.

5. In Fivetran, update your connection to sync data from the new database.

   i. Go to your MySQL connection page.

   ii. Select the Setup tab.

   iii. Click Edit connection.

   iv. Update the necessary fields.

   v. Click Save & Test.

6. Run the following query on your new database.

   SHOW BINARY LOGS;
   

7. From the query output, share the first available binary log filename with our support team. They'll use it to update the binlog position and resume your connection.

8. Run a sync, then verify that the data has synced to your destination.

WARNING: Manually adjusting the binlog position may result in data discrepancies. We strongly recommend using the default approach (a free re-sync) to capture the new binlog position.