Search code examples
mysqlamazon-web-servicesamazon-redshiftdms

Newly created tables are not being migrated to Redshift in dms

I have set up a DMS with RDS MYSQL as source endpoint and REDSHIFT as target endpoint with "full and CDC".

setup is working fine and even update, delete stmts are being replicated to Redshift. however when i create a new table in my source RDS MYSQL it's not being repliacted to targer Redshift.

please note- there isnt any primary key assosiated with the new table.

Solution

  • So this is because, whenever a new table is created the DMS user (mysql user) does not have access to read these new tables. You will have to explicitly grant permission to the user to read this new table-

    GRANT SELECT ON SCHEMA.TABLE_NAME TO dmsuser_readonly;

    Then add supplement logging to allow the user to access the logs for the table-

    ALTER TABLE SCHEMA.TABLE_NAME ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;

    PS: Allow all accesses to the dmsuser using the owner schema user. Let me know in case of any issues.