6

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?

Tim
  • 31,888
  • 7
  • 52
  • 78
James
  • 133
  • 2
  • 8
  • Can you not just define your indexes and have the target database build new indexes? I'm not a DBA... – Tim Oct 10 '18 at 19:04
  • That's what I'd like to do yes, but I can't see how to do it using Amazon DMS, it HAS to be an option somewhere I would have thought – James Oct 11 '18 at 09:10
  • I'm not sure I understand the problem. When data is inserted into a database indexes are created automatically aren't they? If not have you search for a command to rebuild indexes, [like this](https://dev.mysql.com/doc/refman/5.7/en/rebuilding-tables.html)? – Tim Oct 11 '18 at 19:22
  • It doesn't no. Only the primary keys and a couple more if you're lucky. I was trying to avoid doing it manually with mysqldump due to the scale. Found a solution though, I'll answer the question. – James Oct 19 '18 at 10:40

2 Answers2

6

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.

James
  • 133
  • 2
  • 8
1

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.

Community
  • 1
Nik
  • 113
  • 5