MySQL RDS主从库能否配置不同数据保留策略？求可行替代方案

Absolutely, you can set up separate data retention rules between your transactional RDS primary and reporting replica—since we can’t disable session-level binary logs on RDS like we would on EC2, here are practical, production-ready workarounds with scheduling options:

Option 1: Filter Replication to Ignore Primary’s Old Data Purges #

This is the most direct approach: let the primary clean up 6-month-old data, but configure the replica to skip those deletion commands entirely.

• Step 1: Standardize your purge process on the primary
  Create a dedicated stored procedure for cleaning old data, and tag all purge-related DELETE/DROP statements with a unique, identifiable comment. For example:

  DELIMITER //
  CREATE PROCEDURE purge_old_transaction_data()
  BEGIN
    -- Delete transactions older than 6 months
    DELETE FROM transactions 
    WHERE created_at < DATE_SUB(NOW(), INTERVAL 6 MONTH)
    /* RDS_REPLICA_IGNORE_PURGE */; -- Unique tag for replication filtering
  END //
  DELIMITER ;
  

• Step 2: Configure replication filtering on the replica
  Use MySQL’s replication pattern matching to ignore statements with your unique tag. On RDS, you can set this via a custom parameter group:

  1. Create a new parameter group for your replica (or modify the existing one)
  2. Set the replicate_ignore_pattern parameter to '/* RDS_REPLICA_IGNORE_PURGE */'
  3. Apply the parameter group to the replica (a restart may be required for changes to take effect)

  Alternatively, you can run this dynamic command on the replica (requires super privileges, granted via RDS’s rds_superuser role):

  CHANGE REPLICATION FILTER REPLICATE_IGNORE_PATTERN = ('/* RDS_REPLICA_IGNORE_PURGE */');
  

Option 2: Partitioned Tables + Detached Historical Partitions #

If you use partitioned tables (highly recommended for time-series transaction data), you can split data retention between primary and replica by managing partitions:

• Primary setup:
  Create your transaction table with monthly partitions, and schedule a monthly task to drop partitions older than 6 months. For example:

  ALTER TABLE transactions DROP PARTITION p_202306; -- Drop June 2023 data after January 2024
  

• Replica setup:
  Before the primary drops a partition, detach that partition from the replica’s table to keep it as a standalone historical table:

  ALTER TABLE transactions DETACH PARTITION p_202306 INTO TABLE transactions_hist_202306;
  

  This preserves the historical data on the replica while letting new partitions sync normally from the primary. You can then combine these historical tables into a single archive table for easier reporting if needed.

Option 3: Snapshot-Based Historical Data Backup #

For scenarios where replication filtering or partitions aren’t feasible, use RDS snapshots to preserve historical data on the replica:

1. Before the primary’s monthly purge, take a manual or automated snapshot of the primary instance.
2. Restore the snapshot to a temporary RDS instance.
3. Export the historical data (6–24 months old) from the temporary instance and import it into an archive table on your reporting replica.
4. Terminate the temporary instance to avoid extra costs.

Scheduling the Operations #

All these workflows can be automated using:

• RDS Event Scheduler: Enable it via your primary/replica parameter groups (event_scheduler = ON), then create scheduled events to run your purge procedures or partition management tasks. Example for the primary:

  CREATE EVENT monthly_purge
  ON SCHEDULE EVERY 1 MONTH
  STARTS '2024-01-01 02:00:00' -- Run during low-traffic hours
  DO CALL purge_old_transaction_data();
  

• AWS EventBridge + Lambda: Use EventBridge to trigger Lambda functions that run RDS API commands (like snapshot creation) or execute SQL via the RDS Data API for more complex, cross-service workflows.

Key Notes #

• Always test replication filtering and partition changes in a staging environment first to avoid data inconsistencies.
• For partitioned tables, ensure your partition scheme aligns with your retention policy (monthly partitions work well for 6–24 month windows).
• When using snapshots, schedule exports/imports during off-peak hours to minimize performance impact on your reporting replica.

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