Secure binlog connection with SSL

[kpuputti]

First of all, thanks for a great library! We are using the library in Clojure for replicating a MySQL instance to a separate database.

However, we have not been able to make a secure connection for streaming the binlog. We have managed to connect using SSL for normal jdbc SQL queries, so the setup and certificates are supposedly correct.

We get this error:

Unhandled com.github.shyiko.mysql.binlog.network.AuthenticationException
Access denied for user '...'@'...' (using password: YES)

When we try to set a custom socket factory to the BinLogClient that returns a new javax.net.ssl.SSLSocket using the javax.net.ssl.SSLSocketFactory.getDefault() factory, we get:

Caused by javax.net.ssl.SSLException
   Unrecognized SSL message, plaintext connection?

              InputRecord.java:  710  sun.security.ssl.InputRecord/handleUnknownRecord
              InputRecord.java:  527  sun.security.ssl.InputRecord/read
            SSLSocketImpl.java:  973  sun.security.ssl.SSLSocketImpl/readRecord
            SSLSocketImpl.java: 1375  sun.security.ssl.SSLSocketImpl/performInitialHandshake
            SSLSocketImpl.java:  928  sun.security.ssl.SSLSocketImpl/readDataRecord
           AppInputStream.java:  105  sun.security.ssl.AppInputStream/read
BufferedSocketInputStream.java:   51  com.github.shyiko.mysql.binlog.io.BufferedSocketInputStream/read
     ByteArrayInputStream.java:  202  com.github.shyiko.mysql.binlog.io.ByteArrayInputStream/readWithinBlockBoundaries
     ByteArrayInputStream.java:  175  com.github.shyiko.mysql.binlog.io.ByteArrayInputStream/peek
          BinaryLogClient.java:  332  com.github.shyiko.mysql.binlog.BinaryLogClient/connect
          BinaryLogClient.java:  520  com.github.shyiko.mysql.binlog.BinaryLogClient$4/run
                   Thread.java:  745  java.lang.Thread/run

Any pointers how to get a working SSL connection for binlog streaming? We are using the 0.2.4 version of the library.

Thanks!

[shyiko]

Hi. Unfortunately it's not implemented. At the very least we're missing SSL Request Packet (more details here) (followed by SSL exchange) + AuthenticateCommand & ClientCapabilities.SSL.
If you have some spare time and would like to give it at shot - I would be happy to merge such changes in. If not - sooner or later, I'll implemented it myself.

[kpuputti]

Thanks for the response. We'll see what we can do.

[mvelliste]

I am finding this library super-useful too, thank you for creating it. But I am running into this very same SSL issue. I don't suppose there has been any progress on this?

[shyiko]

@mvelliste I'm afraid not. We're using IPsec so it never became an issue for us.

[shyiko]

Tested on MySQL 5.6.31, running Java 8 (1.8.77).

CREATE USER 'username'@'%' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON *.* TO 'username'@'%';
GRANT USAGE ON *.* TO 'username'@'%' REQUIRE SSL;
FLUSH PRIVILEGES;

(readme.md updated with usage instructions)

[thomas-woodruff]

@kpuputti

We have managed to connect using SSL for normal jdbc SQL queries

Hi there. How did you manage to connect to MySQL using SSL? I can't seem to find any documentation on this anywhere...

Thanks,
Tom

[shyiko]

@thomas-woodruff see https://dev.mysql.com/doc/connector-j/5.1/en/connector-j-reference-using-ssl.html and http://razorsql.com/articles/mysql_ssl_jdbc.html