MySQL Bugs: #116312: 8.0 replica cannot establish TLS connection with 5.7 replication source

Bug #116312	8.0 replica cannot establish TLS connection with 5.7 replication source
Submitted:	7 Oct 2024 16:51	Modified:	2 Nov 2024 20:46
Reporter:	Pierre C. Dussault	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S1 (Critical)
Version:	8.0.39	OS:	Red Hat (Almalinux 8)
Assigned to:	MySQL Verification Team	CPU Architecture:	x86
Tags:	encryption, replication

Description:
When trying to establish TLS encrypted replication between a replication source (in AWS RDS) on version 5.7.44 and a replica (on-premise) on version 8.0.39, I keep getting an error:
###
Error connecting to source '<REDACTED>@<REDACTED>:<REDACTED>'. This was attempt 1/86400, with a delay of 60 seconds between attempts. Message: SSL connection error: error:14094418:SSL routines:ssl3_read_bytes:tlsv1 alert unknown ca
###

The ca-certificate file (global-bundle.pem) used to validate the AWS RDS server certificate was downloaded, and then changed to ownership mysql:mysql with r--r--r-- permissions, and placed in a custom /aws-ca-certificates/ directory, which has ownership mysql:mysql and permissions r-xr-xr-x. All other certs and keys generated when installing the MySQL Server package on the VM are left to their defaults. I have attempted to connect to the AWS RDS source by using the CLI client of the same replica VM with the same CA certificate file (global-bundle.pem) to validate the server certificate, and it works. Meaning, the mysql CLI client worked while the replication process raised an error while attempting an equivalent connection. My my.cnf does not have a [client] block, so all defaults are used for the client. My [mysqld] block contains:
###
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
default_authentication_plugin = mysql_native_password
log_error_verbosity = 2
log_raw = OFF
relay_log=database-vm01-relay-bin
innodb_buffer_pool_size = 12G
innodb_buffer_pool_instances = 8
innodb_flush_method = O_DIRECT
innodb_log_file_size = 128M
innodb_log_files_in_group = 2
tls_version = TLSv1.2,TLSv1.3
ssl_cipher = ECDHE-ECDSA-AES128-GCM-SHA256:ECDHE-ECDSA-AES256-GCM-SHA384:ECDHE-RSA-AES128-GCM-SHA256:ECDHE-RSA-AES256-GCM-SHA384:DHE-RSA-AES128-GCM-SHA256:DHE-RSA-AES256-GCM-SHA384
tls_ciphersuites = TLS_AES_128_GCM_SHA256:TLS_AES_256_GCM_SHA384
plugin-load-add=connection_control.so
connection-control=FORCE_PLUS_PERMANENT
connection-control-failed-login-attempts=FORCE_PLUS_PERMANENT
connection_control_failed_connections_threshold=5
connection_control_min_connection_delay=60000
connection_control_max_connection_delay=1920000
allow_suspicious_udfs = OFF
skip_grant_tables = OFF
ssl_ca=/var/lib/mysql/ca.pem
ssl_cert=/var/lib/mysql/server-cert.pem
ssl_key=/var/lib/mysql/server-key.pem
server_id=22
replicate_do_db=<REDACTED>
###

How to repeat:
The command used to configure the replication (that is raising an error) was:
-- START
CHANGE MASTER TO
    MASTER_HOST='<REDACTED>',
    MASTER_USER='<REDACTED>',
    MASTER_PASSWORD='<REDACTED>',
    MASTER_LOG_FILE='mysql-bin-changelog.144869',
    MASTER_LOG_POS=521,
    MASTER_SSL=1,
    MASTER_SSL_CA='/aws-ca-certificates/global-bundle.pem',
    MASTER_SSL_CERT='/var/lib/mysql/client-cert.pem',
    MASTER_SSL_KEY='/var/lib/mysql/client-key.pem',
    MASTER_SSL_VERIFY_SERVER_CERT=1;
-- END

The command used to connect to the source with the CLI client (that works successfully) was:
### START
mysql -h "<REDACTED>" -u <REDACTED> -p --ssl-ca="/aws-ca-certificates/global-bundle.pem" --ssl-mode=VERIFY_CA
### END

... which provided the connection:
###
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 289389
Server version: 5.7.44
[...]
mysql>
###

I think 'Not a bug'. The CLI client connection is not encrypted. SHOW VARIABLES LIKE '%ssl_cipher%' gives an empty result even though the connection succeeds. Not sure why

Ok this is odd. After connecting as previously mentioned with the mysql CLI client, if I enter:
---
SHOW VARIABLES LIKE '%ssl_cipher%';
---

I get an empty result (as mentioned earlier):
---
| Variable_name | Value |
+---------------+-------+
| ssl_cipher    |       |
---

However, if I use the following command, taken directly from the AWS RDS documentation (https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/ssl-certificate-rotation-mysql.html):
---
SELECT id, user, host, connection_type 
       FROM performance_schema.threads pst 
       INNER JOIN information_schema.processlist isp 
       ON pst.processlist_id = isp.id;
---

... in order to verify which users are connected via SSL/TLS, my current connection is output as using SSL/TLS:
---
id    | user         | host                                          | connection_type |
+-------+----------+-------------------------------------------------+-----------------+
| 12967 | <REDACTED> | <REDACTED>                                    | SSL/TLS         |

---

... so, I am not even sure if I am using a TLS connection or not. How to tell?

On the server side in the AWS RDS instance, when the replica tries to connect, it logs "Bad handshake".

Hi,

I cannot reproduce this with our binaries. Maybe the problem is with RDS?

Likely so.

I ended up using an EC2 instance to create an SSH port forwarding tunnel to encrypt network traffic between the new database and the RDS instance, without using the replication's built-in encryption mecanism.