关于AWS DMS双向数据复制的技术咨询（已实现单向同步）

Great to hear your unidirectional SQL Server → MySQL replication is running smoothly! Extending this to bidirectional sync is totally feasible, but it requires careful planning to avoid replication loops and resolve data conflicts between the two heterogeneous databases. Here's a step-by-step approach tailored to your setup:

Key Pre-Requisites & Critical Considerations #

First, let's cover the non-negotiables to avoid headaches later:

• AWS DMS Version: Ensure your replication instance is running a recent DMS version (v3.4.5+ is a safe bet) that supports bidirectional replication for both SQL Server and MySQL.
• Schema Compatibility: SQL Server and MySQL have subtle data type differences (e.g., BIT vs BOOLEAN, NVARCHAR vs VARCHAR, identity vs auto-increment columns). Double-check that your table schemas on both sides are compatible—use AWS Schema Conversion Tool (SCT) if you need to adjust types or constraints.
• Conflict Resolution Rules: Decide upfront how to handle conflicting changes (e.g., same row updated on both sides at the same time). Common strategies include:
  • Last-Write-Wins: Use a timestamp column (like last_updated) to prioritize the most recent change.
  • Source Priority: Designate one database as authoritative for specific tables (e.g., SQL Server handles user data, MySQL handles analytics).
• Loop Prevention: Changes made by DMS on one database must not be replicated back to the source—this will create infinite loops. We'll fix this with task filters.

Step-by-Step Implementation #

1. Prepare MySQL RDS to Act as a Replication Source #

Your current setup uses SQL Server as the source, so now we need to configure MySQL to feed changes back to SQL Server:

• Enable Binary Logging: Modify your MySQL RDS parameter group to set:
  • log_bin = 1
  • binlog_format = ROW
  • binlog_row_image = FULL
    Restart the MySQL instance if required.
• Create a DMS Replication User: Run these commands on MySQL:

  CREATE USER 'dms_mysql_source'@'%' IDENTIFIED BY 'SecurePass123!';
  GRANT SELECT, RELOAD, SHOW DATABASES, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'dms_mysql_source'@'%';
  FLUSH PRIVILEGES;
  

• Update Security Groups: Ensure your MySQL RDS security group allows inbound traffic from your DMS replication instance.

2. Prepare SQL Server RDS to Act as a Replication Target #

While SQL Server is already a source, we need to set up a user for DMS to write changes from MySQL:

• Create a DMS Target User: Run these commands on SQL Server:

  CREATE LOGIN dms_sql_target WITH PASSWORD = 'SecurePass123!';
  CREATE USER dms_sql_target FOR LOGIN dms_sql_target;
  GRANT SELECT, INSERT, UPDATE, DELETE ON [YourDatabase].[YourSchema].[YourTables] TO dms_sql_target;
  

• Update Security Groups: Ensure your SQL Server RDS security group allows inbound traffic from the DMS replication instance.

3. Set Up the Reverse DMS Replication Task (MySQL → SQL Server) #

Don't modify your existing SQL → MySQL task—create a separate task for the reverse direction:

• Source Endpoint: Create a new DMS source endpoint pointing to your MySQL RDS, using the dms_mysql_source user you created.
• Target Endpoint: Use your existing SQL Server endpoint (or create a new one) with the dms_sql_target user.
• Configure the Task:
  • Choose Migrate existing data and replicate ongoing changes as the task type.
  • Table Mappings: Map the same tables as your existing task, but in reverse. Verify data type conversions (DMS handles most, but check edge cases like DATE/DATETIME time zone differences).
  • Prevent Loops: In the task's advanced settings, add exclude_transactions_from_users and specify the DMS user that writes to MySQL (from your original task). This ensures changes made by DMS on MySQL aren't sent back to SQL Server.
  • Conflict Resolution: In the task settings, define your strategy. For example, if using last-write-wins, set the task to use the last_updated column to resolve conflicts.

4. Test & Validate #

• Basic Sync Test: Update a row in SQL Server, confirm it syncs to MySQL. Then update the same row in MySQL, confirm it syncs back to SQL Server.
• Conflict Test: Intentionally create a conflict (e.g., update the same row in both databases within a few seconds) and verify your conflict resolution rule works as expected.
• Monitor Tasks: Use the DMS console to check for errors, latency, and task status. Keep an eye on the "Replication latency" metric to ensure sync is performing well.

5. Advanced Tips #

• Schema Changes: Any schema updates (e.g., adding a column) must be applied to both databases first, then update your DMS tasks to include the new schema elements.
• Isolate Replication Streams: For large workloads, consider using a second DMS replication instance for the reverse task. This improves performance and makes troubleshooting easier.
• High Availability: Configure your DMS replication instance as multi-AZ, and set up task recovery options to minimize downtime if something goes wrong.

Content of the question来源于stack exchange，提问作者Aman Middha