Replicating MySQL To MySQL With DMS Without Losing Secondary Indexes and Foreign Key Constraints

Easy Right?

We wouldn’t be in spring time without spring cleaning right? As part of this spring cleaning I decided to do some DB consolidation to make better economies of scale out of my RDS instances (especially since Multi-AZ instances aren’t exactly cheap). Having used AWS DMS (Database Migration Service) to replicate MySQL to Redshift data warehouse I figured it’d be a trivial left to stand up a new RDS instance and make it the replication target of several source MySQL instances I wanted to combine/condense and since there no conflicting table or db names it should be easy.

OK Maybe Not…

If this were the case this would be the end of this post…but wait there’s more! So data loaded and replicated just fine…comparing table max ids, row counts, and spot checking data everything looked great. Until I started checking indexes and foreign keys. It was only then that I realized DMS with target table preparation mode set to ‘drop and create’ will not create secondary indexes or FK constraints. Furthermore it doesn’t set auto-increment by default on any columns which would have spelled a major disaster in production.

A Solution at Last!

Exploring other options I attempted to do a mysqldump of just the table create DDL and applied this to my target successfully. Then in DMS I chose the target table preparation mode option ‘do nothing’..and well it didn’t do what I wanted…so I tried ‘truncate’, this just produced hundreds of table errors due to FK integrity violations and the fact that the table loads were not necessarily in the same order as their constraints and dependancies. It was at this point I finally arrived on a successful solution. In DMS there is an option on the target to disable FK checks and just load the data. This in tandem with truncate gave us exactly what we wanted.

TL:DR Instructions
  1. Dump your table DDL from your sources
mysqldump --no-data --skip-set-charset --single-transaction=TRUE -h{{db host}} -u{{dbuser}} -p {{database name}} > myddldump.sql

2. Import your table DDL to your target (the db you’re migrating to obviously use caution here if the target has existing tables or dbs that share a name)

mysql -h{{db host}} -u{{dbuser}} -p {{database name}} < myddldump.sql

3. In your DMS Endpoints, set the following “extra connection attributes” on your target

initstmt=SET FOREIGN_KEY_CHECKS=0

4. Modify your DMS task target table preparation mode to ‘truncate’

5. Start your task and profit!

Leave a Reply

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

seven + 18 =