无需修改数据库时，如何检测表行新增并记录跨库日志

Since you can't make any changes to DatabaseA (no triggers, stored procedures, or schema tweaks allowed), here are practical, event-driven approaches to detect new rows in your target table and log the associated user info to DatabaseB:

1. Use Built-In or Third-Party Change Data Capture (CDC) #

Most modern databases support Change Data Capture natively, which tracks row-level changes (INSERT, UPDATE, DELETE) without requiring modifications to the source database. Here's how it works:

• Enable CDC on DatabaseA for your target table (this is usually a database-level configuration, not schema modification).
• CDC will capture all INSERT events for the table and store them in a dedicated change table or stream.
• Set up a consumer (like a custom script, or an open-source CDC tool) to read these change events, extract the user information from the new rows, and write it directly to DatabaseB's log table.
• Example: For MySQL, you can use binlog-based CDC tools; for SQL Server, leverage its built-in CDC feature; for PostgreSQL, use logical replication to stream changes.

2. Polling with a Tracking Column #

If your target table in DatabaseA has a reliable tracking column (like an auto-incrementing id, or a created_at timestamp), a scheduled polling job is a simple, low-fuss option:

• Create a persistent store (could be a small table in DatabaseB, or a config file) to track the last processed id or timestamp.
• Write a script (Python, Bash, etc.) or use a job scheduler (Airflow, cron, or your database's native scheduler) to run at regular intervals:

  -- Example query to fetch new rows in DatabaseA
  SELECT user_id, user_name, created_at 
  FROM target_table 
  WHERE id > :last_processed_id;
  

• Insert the fetched user data into DatabaseB's log table, then update the tracking value to the latest id/timestamp from the new rows.
• Pro tip: Add a unique constraint on the log table in DatabaseB to avoid duplicate entries if the job runs into retries.

3. Parse Database Transaction Logs #

All databases write transaction logs (like MySQL's binlog, PostgreSQL's WAL, SQL Server's transaction log) to ensure data durability. You can parse these logs to extract INSERT events:

• Gain access to the database's transaction log files (you'll need appropriate server permissions for this).
• Use a log-parsing tool or custom script to scan the logs for INSERT operations on your target table.
• Extract the relevant user data from the log entries and write it to DatabaseB.
• Note: This approach is database-specific and requires careful handling of log rotation and format changes.

4. Intercept Writes at the Middleware Layer #

If all writes to DatabaseA's target table go through a shared middleware (like an API server, application service, or ORM layer), you can add event-driven logic here:

• Modify the middleware to trigger an event immediately after a successful INSERT to DatabaseA.
• The event handler will take the user information from the inserted row and write it to DatabaseB's log table.
• This is the most real-time option if you control the middleware, and it avoids any direct interaction with DatabaseA's internals.

Key Considerations #

• Real-Time vs. Latency: CDC and log parsing offer near-real-time detection, while polling introduces some latency (adjust the interval based on your needs).
• Data Consistency: Implement retry mechanisms and dead-letter queues for cases where writing to DatabaseB fails, to avoid losing log entries.
• Performance Impact: Polling can add load to DatabaseA if the interval is too short; CDC and log parsing are low-impact since they don't query the main table directly.

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