AWS DMS can not use SSL required mode with MySQL

0
votes

I tried to use AWS DMS to migrate MySQL innodb cluster on another cloud provide to AWS. I uploaded self signed CA.pem generated by MySQL it doesn't work.

AWSDatabaseMigrationService: CA Certificate validation error

DMS SSL mode doesn't have required option for MySQL. So I am stuck...

I am wondering what should I do in this case. There are two solutions I've thought of.

  1. replace the CA and all server certificate so DMS can work with it. Not sure if there's any potential risk for this.
  2. spin up another EC2 replication instance by myself. Then RDS can replicate the EC2 instance. (The reason I am not able to use auto positioning in RDS is none of the cluster node has all the binary log)

Most of the thread in AWS forum is not answered. I feel it's better to ask here.
Any suggestion or idea would be good. Thanks.

1
mysqlamazon-web-servicessslaws-dmsmysql-innodb-cluster

1 Answers

0
votes

So I eventually went with the option #1, and it works.

One biggest problem I encountered was after importing our CA and server certificate to MySQL it can't be connect with --ssl-ca specified. However with only --ssl it is fine.

In MySQLWorkbench SSL connection error: error:00000001:lib(0):func(0):reason(1) error message was totally useless. On DMS it said Error 2026 (SSL connection error: unable to get issuer certificate) connecting to MySQL server.

It turns out the problem was on OSX and DMS it doesn't trust AddTrust External CA Root or it doesn't have that root certificate in their keystore. So I had to manually add the root certificate into CA bundle file.

To make it more concrete here's the chain.
company certificate -> COMODO RSA Domain Validation Secure Server CA -> COMODO RSA Certification Authority (which is NOT the root certificate in OSX Keychain) -> AddTrust External CA Root

I don't know why OSX & DMS couldn't verify the root certificate by default. It took me two days to figure out the problem. Hopefully this information will be helpful to others having trouble with COMODO issued certificate.

UPDATE (2018/11/07):

DMS turned out will have missing record or unmatched record if it only migrate data to the existing schema. I have turned off foreign key check but the situation still exists.

I ended up went to #2 route.