MySQL connections fail when using SSL after upgrading to MySQL 8

Question

I have a database running 8.0.36 (recently upgraded from 5.7). On the source Database I create three users:

One wthout SSL, One with REQUIRE SSL, and One with REQUIRE ISSUER.

CREATE USER 'ssl_test1'@'%' IDENTIFIED WITH 'mysql_native_password' BY '<password>';
GRANT REPLICATION SLAVE ON *.* TO 'ssl_test1'@'%';

CREATE USER 'ssl_test2'@'%' IDENTIFIED WITH 'mysql_native_password' BY '<password>' REQUIRE SSL;
GRANT REPLICATION SLAVE ON *.* TO 'ssl_test2'@'%';

CREATE USER 'ssl_test3'@'%' IDENTIFIED WITH 'mysql_native_password' BY '<password>' REQUIRE ISSUER '. . . . ';
GRANT REPLICATION SLAVE ON *.* TO 'ssl_test3'@'%';

I have now set up a test database (clean install of MySQL 8.0.36).

On this replica server I place a copy of all the PEM files copied from the source (these files were all created as per https://dev.mysql.com/doc/refman/8.0/en/creating-ssl-files-using-openssl.html).

I then try to start replication. First of all without SSL

CHANGE replication source TO
SOURCE_HOST =  '1.2.3.4',
SOURCE_PORT = 3306,
SOURCE_USER = 'ssl_test1',
SOURCE_PASSWORD = '***********',
SOURCE_AUTO_POSITION = 1,
source_SSL=0;

This works fine.

Next, I try to add SSL:

CHANGE replication source TO
SOURCE_HOST =  '1.2.3.4',
SOURCE_PORT = 3306,
SOURCE_USER = 'ssl_test2',
SOURCE_PASSWORD = '***********',
SOURCE_AUTO_POSITION = 1,
source_SSL=1;

But, the database just sits there "connecting". The Last_IO_Error says Error connecting to source '[email protected]:3306'. This was attempt 1/86400, with a delay of 60 seconds between attempts. Message: SSL connection error: SSL_CTX_set_default_verify_paths failed

Finally, I try the option with certificates:

CHANGE replication source TO
SOURCE_HOST =  '1.2.3.4',
SOURCE_PORT = 3306,
SOURCE_USER = 'ssl_test3',
SOURCE_PASSWORD = '***********',
SOURCE_AUTO_POSITION = 1,
SOURCE_SSL=1,
SOURCE_SSL_CA='ca-cert.pem', 
SOURCE_SSL_CAPATH='C:/MySQL/ssl/', 
SOURCE_SSL_CERT='client-cert.pem', 
SOURCE_SSL_KEY='client-key.pem';

But it just complains it can't connect.

I'm trying to work out of this is something I am doing wrong (all the examples above worked fine under 5.7), or if there is Bug in MySQL 8.0.36?

I've tried using old certificates and creating new ones. I've tried it with databases that were upgraded from 5.7 and ones that were always MySQL 8.0 And every time I get the same result.

Answer 1

ssl_capath no longer works in 8.0, I had the same problem; You now have to specify the PEM files with an absolute path, I think that's stupid too