AWS RDS Read Replica: How can I avoid replication of few tables and columns

I am a using RDS MariaDB and have a requirement for having a slave DB which I can use for analytics. I am looking for AWS Read Replica but the issue is it doesn’t provide me provision for skipping replication of few tables and columns which hold some sensitive information and we don’t want to have the slave DB with that information.

Can I skip replication of few tables and columns with the AWS Read Replica? Or in AWS Read Replica can I write AWS Lambda which can delete this information from Replica DB?

Answer

AWS Database Migration Service would work for this use case:

  • You can use the AWS Database Migration Service to establish ongoing continuous replication between heterogeneous source and target databases.
  • AWS DMS supports MariaDB for both source and target databases.
  • AWS DMS supports per-table selection from the source database.
  • AWS DMS supports removing columns during transformation to the target database.

Per the FAQ:

Q. In addition to one-time data migration, can I use AWS Database Migration Service for continuous data replication?

Yes, you can use AWS Database Migration Service for both one-time data migration into RDS and EC2-based databases as well as for continuous data replication. AWS Database Migration Service will capture changes on the source database and apply them in a transactionally-consistent way to the target. Continuous replication can be done from your data center to the databases in AWS or in the reverse, replicating to a database in your datacenter from a database in AWS. Ongoing continuous replication can also be done between homogenous or heterogeneous databases. For ongoing replication it would be preferable to use Multi-AZ for high-availability.

If you aren’t familiar with AWS DMS, take a look at the official documentation ‘Getting Started‘ pages, and then continue below.


Create your target database with an identical schema as the source except for these few tables and columns that you do not want to populate.

Once you’ve associated your source and target databases with DMS, you’ll want to create a task to perform the migration, with specific table mappings to exclude tables and remove columns.

Excluding Tables

Create a selection rule with rule-action exclude, and specify which tables you want to exclude. Example from the documentation that migrates all tables except those with tablenames starting with DMS%:

{
    "rules": [
        {
            "rule-type": "selection",
            "rule-id": "1",
            "rule-name": "1",
            "object-locator": {
                "schema-name": "Test",
                "table-name": "%"
            },
            "rule-action": "include"
        },
        {
            "rule-type": "selection",
            "rule-id": "2",
            "rule-name": "2",
            "object-locator": {
                "schema-name": "Test",
                "table-name": "DMS%"
            },
            "rule-action": "exclude"
        }
    ]
}

Removing Columns

Create a transformation rule with rule-action remove and rule-target any specify the schema, table, and column names of the column that you want to remove. The following example, also from the documentation, removes all columns from the test.Actor table that begin with the characters col:

{
    "rules": [{
        "rule-type": "selection",
        "rule-id": "1",
        "rule-name": "1",
        "object-locator": {
            "schema-name": "test",
            "table-name": "%"
        },
        "rule-action": "include"
    }, {
        "rule-type": "transformation",
        "rule-id": "2",
        "rule-name": "2",
        "rule-action": "remove-column",
        "rule-target": "column",
        "object-locator": {
            "schema-name": "test",
            "table-name": "Actor",
            "column-name": "col%"
        }
    }]
 }

Further Reading

Leave a Reply

Your email address will not be published. Required fields are marked *