Add support for SSL

[igroene]

I came across some use cases where strict compliance rules enforce that all connections to the db's need to use SSL. It would be nice if gh-ost supported that option.
It looks like go-mysql-driver already has support (go-sql-driver/mysql#89)

Also for reference: https://dev.mysql.com/doc/refman/5.7/en/using-encrypted-connections.html

[zmoazeni]

We have been looking into this as well. One option Percona mentioned to me was to define the SSL options in the my.cnf and set the user you use to require SSL.

I haven't had a chance to test this yet but it has been on my radar. You may also need to setup the existing replication user the same way.

For what it's worth, I've also been thinking about this in the context of orchestrator (failing over replication using SSL when that happens)

[igroene]

We have been looking into this as well. One option Percona mentioned to me was to define the SSL options in the my.cnf and set the user you use to require SSL.

That won't work for gh-ost as it doesn't use the standard mysql client to connect

[zmoazeni]

Ah that makes sense. That might only work for orchestrator issuing CHANGE MASTER TO... without supplying the SSL options. (Since it's mysqld connecting to mysqld). Bummer.

[shlomi-noach]

Well, orchestrator uses the exact same client to connect to the servers via SSL. It just supports SSL code-wise, as per https://github.com/github/orchestrator/blob/master/docs/ssl-and-tls.md , and we'd need to apply roughly the same to gh-ost.

[wesleyk]

Any update on this request @shlomi-noach? If no one has started on this, is there perhaps a sample PR from orchestrator that you could link here that we might use to add SSL support in?

Thanks!

[shlomi-noach]

@wesleyk I have no immediate plan to work on this. Relevant orchestrator PRs:

• TLS functions in go/db/db.go to go/db/tls.go openark/orchestrator#155
• Added ability to mix TLS and non-TLS connections to nodes. openark/orchestrator#242
• simplified TLS detection flow openark/orchestrator#307

[wesleyk]

Sounds good, thanks for the PRs!

[brandonbodnar-wk]

We experienced a recent need for SSL support while using the gh-ost tool on AWS RDS instances. I've submitted #705 as an initial pass of what I think is the minimum support we need for our use case. While it doesn't cover every ssl setting that users might want to select, it can provide at least a starting point to adding in some level of SSL/TLS support into gh-ost.

[RafeKettler]

@shlomi-noach I am also interested in SSL support. I tried @brandonbodnar-wk's patch last week, and had to make some further changes to get SSL to work in my use case. The specific issue I have found is it does not seem like SSL options passed to the CLI are properly used in all places we connect to MySQL; this was causing errors while gh-ost was trying to walk from the replica up to the master:

2019-02-13 00:26:57 INFO starting gh-ost
2019-02-13 00:26:57 INFO Migrating `rafetest_staging`.`foo`
2019-02-13 00:26:57 INFO connection validated on host2
2019-02-13 00:26:57 INFO User has ALL privileges
2019-02-13 00:26:57 INFO binary logs validated on host2
2019-02-13 00:26:57 INFO Restarting replication on host2 to make sure binlog settings apply to replication thread
2019-02-13 00:26:58 INFO Inspector initiated on host2, version 5.6.42-84.2-log
2019-02-13 00:26:58 INFO Table found. Engine=InnoDB
2019-02-13 00:26:58 INFO Estimated number of rows via EXPLAIN: 3
2019-02-13 00:26:58 INFO Recursively searching for replication master
2019-02-13 00:26:58 ERROR invalid value / unknown config name: host1
2019-02-13 00:26:58 INFO Tearing down inspector
2019-02-13 00:26:58 FATAL invalid value / unknown config name: host1

Full disclosure, I edited the output above to remove actual hostnames and replace them with host1 and host2. Error is coming from attempting to use a TLS config called "host1" when mysql.RegisterTLSConfig was never called to register a config "host1". I am working on tests and plan on opening the PR in the next week.

[brandonbodnar-wk]

Interesting @RafeKettler. I'll admit, I have not tested the patch for ssl support on mysql setups other than with AWS RDS, which I know acts differently than other setups in gh-ost.