Copying over indexes with AWS DMS

I need to use AWS DMS to copy one MySQL RDS instance to the other. I've been tinkering about have set it all up and I've run a successful migration. The issue is it doesn't create indexes.

I understand the reason behind this, obviously it's much quicker to copy without but I have too many tables over a number of schemas to add them back manually. Is there any way to get DMS to copy the indexes over after replication? Or an alternative solution?

It turns out DMS will only ever create primary keys and perhaps a couple more indexes if you're lucky.

There are of course ways to get around this using mysql commands but the AWS way to acheive a DB migration I've found is to first copy the schemas from one DB to another using the AWS schema conversion tool, then use AWS DMS to copy your data over.

I had the same issue - only PK were migrated by AWS DMS. Secondary indexes, Auto-increment counters, users and grants (everything which is not part of the data) had to be migrated separately.

The solution:

1. Dump db structure from the source RDS:

    mysqldump -u <username> -P <port> -h <host_source> --no-data -p --all-databases > schemas.sql
   

2. Import the structure on the target RDS:

    mysql -u <username> -P <port> -h <host_target> -p < schemas.sql
   

3. Specify in the Data Migration task "Do nothing" with tables on the target

Note:

1. For migrating large databases it is better to switch to bigger RDS instance type. Experience showed the migration will be stable when all RDS instances are in the same region (AZ) and have the same/similar instance type i.e. source, target and replica instances.

2. And the most important is to switch RDS Storage Provisioned IOPS with some custom high values (depends on the db data size).

Reference to the doc

Missing Foreign Keys and Secondary Indexes

AWS DMS creates tables, primary keys, and in some cases unique indexes, but it doesn't create any other objects that are not required to efficiently migrate the data from the source. For example, it doesn't create secondary indexes, non-primary key constraints, or data defaults.

To migrate secondary objects from your database, use the database's native tools if you are migrating to the same database engine as your source database. Use the Schema Conversion Tool if you are migrating to a different database engine than that used by your source database to migrate secondary objects.

PS It is not efficient to import data into tables with indexes, the trade off depends on each case.