MySQL Bugs: #113777: Replication with SSL certificates fails after upgrade to MySQL 8

Bug #113777	Replication with SSL certificates fails after upgrade to MySQL 8
Submitted:	26 Jan 2024 14:24	Modified:	2 Feb 2024 15:52
Reporter:	IGG t	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Security: Encryption	Severity:	S3 (Non-critical)
Version:	8.0.36	OS:	Windows
Assigned to:	MySQL Verification Team	CPU Architecture:	Any
Tags:	replication, SSL

Description:
Previously with MySQL 5.7 I used replication with SSL certificates and keys. 
I have now upgraded to MySQL 8 and it no longer appears to work.

I have only tested it on Windows so far.

How to repeat:
1) Generate a set of certificates as per the documentation: https://dev.mysql.com/doc/refman/8.0/en/creating-ssl-files-using-openssl.html

2) set up two clean installs of MySQL 5.7.44

3) place all the certificates on each of these servers in c:/mysql/ssl/

4) on the "master" add entries to the my.ini file:

[mysqld]
ssl-ca   = C:/MySQL/ssl/ca.pem
ssl-cert = C:/MySQL/ssl/server-cert.pem
ssl-key  = C:/MySQL/ssl/server-key.pem

restart mysql.

5) on the "slave" add entries as follows:
[client]
ssl-cert=c:/mysql/ssl/client-cert.pem
ssl-key=c:/mysql/ssl/client-key.pem
 
restart mysql

6) on the "master" set up a user:
CREATE USER 'ssl_test'@'%' IDENTIFIED WITH 'mysql_native_password' BY '********' REQUIRE ISSUER '<issuer details here>' PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK;
GRANT REPLICATION SLAVE ON *.* TO 'ssl_test'@'%';

7) on the "slave" change master and start replication:
CHANGE master TO
master_HOST =  '<host>',
master_PORT = 3306,
master_USER = 'ssl_test',
master_PASSWORD = '********',
master_auto_position = 1,
master_SSL=1,
master_SSL_CA='C:/MySQL/ssl/ca.pem', 
master_SSL_CERT='C:/MySQL/ssl/client-cert.pem', 
master_SSL_KEY='C:/MySQL/ssl/client-key.pem'; 

start slave;

Replication should connect fine.

Now repeat the process using two MySQL 8.0.36 databases. Set up the same user, and use the same certificates / keys.

Run the change replication source statement:

CHANGE replication source TO
source_HOST =  '<host>',
source_PORT = 3306,
source_USER = 'ssl_test',
source_PASSWORD = '********',
source_auto_position = 1,
source_SSL=1,
source_SSL_CA='ca.pem', 
source_SSL_CAPATH='C:/MySQL/ssl', 
source_SSL_CERT='C:/MySQL/ssl/client-cert.pem', 
source_SSL_KEY='C:/MySQL/ssl/client-key.pem';

When you try and start replication it just sits there "connecting" and gives the error 

ERROR] [MY-010584] [Repl] Replica I/O for channel '': Error connecting to source 'ssl_test@<host>:3306'. This was attempt 2/86400, with a delay of 60 seconds between attempts. Message: SSL connection error: error:0A000086:SSL routines::certificate verify failed, Error_code: MY-002026

Suggested fix:
Replication using SSL Certificates should continue to work, after upgrading to MySQL 8.

Hi Mr. t,

Thank you for your bug report.

However, it is not a bug ......

For 8.0, you have not defined your certifications correctly.

This is what's wrong":

master_SSL_CERT='C:/MySQL/ssl/client-cert.pem', 
master_SSL_KEY='C:/MySQL/ssl/client-key.pem'; 

Please, follow  these instructions: 

https://dev.mysql.com/doc/refman/8.0/en/replication-encrypted-connections.html.

For two servers you need 2 sets of certs.

Also, you do not  need the [client] , that you are using.

Next, please note that  mysql_ssl_rsa_setup will soon be deprecated.

Difference is that, unlike 5.7,  8.0 is now doing more to validate that the correct certs are used everywhere.

And you have not done that .

Not a bug.

Not a Bug.

Thanks, however you say I have not defined my certifications correctly and to follow the instructions. But from what I can see the instruction simply says:

    ssl_ca: The path name of the Certificate Authority (CA) certificate file. (ssl_capath is similar but specifies the path name of a directory of CA certificate files.)

    ssl_cert: The path name of the server public key certificate file. This certificate can be sent to the client and authenticated against the CA certificate that it has.

    ssl_key: The path name of the server private key file.

Which from what I can see, is exactly what I have:

    source_SSL_CA='ca.pem', 
    source_SSL_CAPATH='C:/MySQL/ssl', 
    source_SSL_CERT='C:/MySQL/ssl/client-cert.pem', 
    source_SSL_KEY='C:/MySQL/ssl/client-key.pem';