多MySQL数据库合并至单库及双向同步技术方案咨询

Hey there, let's break down your three key questions based on the distributed offline/online setup you're running with 10 local databases.

1. Should you merge the 10 sub-databases into one? #

Short answer: It depends entirely on your offline requirements. Let's weigh the tradeoffs clearly:

Pros of merging: #

• Simplified central management: No need to handle 10 separate sync targets; you only interact with one unified central database.
• Reduced data redundancy: Duplicate schema and overlapping data (if any) gets consolidated, cutting down on storage waste.
• Easier cross-location reporting: Querying aggregated data from one source is far simpler than joining results from 10 separate databases.

Cons of merging: #

• Breaks offline independence: If you merge, local apps would need to rely on a cached copy of the central database for offline use (adding sync complexity) — you can't have fully independent local operations anymore.
• Single point of risk: A failure in the merged central database could impact all 10 locations, even those operating offline if they depend on a synced copy.
• Performance overhead: A single database with 10x the data volume might have slower query times, especially if local apps need low-latency access to their own data.
• Compliance/regulatory issues: Some regions require local data to be stored physically in the area — merging could violate these rules.

Final call: If your offline mode requires local apps to operate fully independently (no reliance on central data copies), don't merge. Merging only makes sense if offline use is rare, local apps can work with a cached central copy, or you have no regulatory barriers to consolidating data.

2. Is merging a reasonable solution? #

It’s reasonable only in specific scenarios:

• ✅ Reasonable if: Offline use is temporary, local apps don’t need to process large volumes of data offline, and you prioritize centralized management over local autonomy.
• ❌ Not reasonable if: Each location needs to handle significant offline workloads, has strict data residency rules, or requires low-latency access to local data.

In most hybrid offline/online setups like yours (10 distributed offline apps), keeping separate sub-databases is the more practical choice — it preserves local independence while allowing for centralized sync when online.

3. How to sync central database changes to corresponding sub-databases? #

To implement this, you’ll need a bidirectional, idempotent sync mechanism that handles both local-to-central uploads and central-to-local downloads. Here’s a step-by-step approach:

Step 1: Implement Change Data Capture (CDC) #

Every database (central + 10 sub-databases) needs to track all data changes (inserts, updates, deletes). You can do this in two ways:

• Database-native CDC: Use built-in tools like MySQL's binlog, PostgreSQL's WAL (Write-Ahead Log), or SQL Server's Change Tracking. These capture low-level database changes without modifying your app code.
• App-layer logging: Add a change_log table to every database. Every time your app modifies data, it writes a record to this table with:
  • Unique change ID
  • Operation type (insert/update/delete)
  • Data record ID
  • Timestamp
  • Source identifier (e.g., "sub_db_1" or "central")
  • User/process that made the change

Step 2: Define Sync Rules #

• Central-to-sub sync: When the central app makes a change, tag the change with the target sub-database(s) (e.g., "sync to sub_db_3" or "sync to all"). Your sync service will pick up these tagged changes and push them to the corresponding online sub-databases.
• Offline catch-up: When a sub-database comes online, it first uploads its local change_log entries to the central database (resolving any conflicts first), then pulls all central changes tagged for it that were made while it was offline.

Step 3: Handle Conflicts #

Since offline sub-databases and the central database might modify the same record, define clear conflict resolution rules upfront:

• Last-write wins: The change with the latest timestamp takes precedence.
• Central authority: Central database changes always override local changes (ideal for scenarios where central admin updates are non-negotiable).
• Manual resolution: For critical data, flag conflicts and let admins review and decide.

Step 4: Ensure Idempotency #

Each change entry must have a unique ID. When a sub-database receives a change, it checks if it's already processed that ID — if yes, it skips it. This prevents duplicate changes from being applied if sync retries happen.

Step 5: Add Consistency Checks #

Periodically run a validation job to compare key data between the central database and each sub-database. For example:

• Calculate a hash of a subset of records (e.g., daily transactions) and compare values.
• For smaller datasets, run a full row-by-row comparison to catch missed syncs.

内容的提问来源于stack exchange，提问作者Thanga Pandiyan