SSL connection error: error:0A000086:SSL routines::certificate verify failed

[Boisseau Nicolas]

Hello,

I'm dealing with a problem that's taking up far too much of my time. I must have spent around 30 hours on the subject and I'm starting to overdose.

Let me explain what I'm dealing with: I want to use MySQL to replicate 2 databases. Everything was working fine, I was able to replicate correctly.

But then I got a message telling me that it's not safe to let the password show through, so I need to use the SSL/TLS protocol to make things more secure
and not have the password in clear text on the network (even though I'm on a LAN).
To do this, I used the .pem files created automatically during installation with MySQL, i.e. my ca.pem - client-cert.pem - client-key.pem files that I sent to my 2nd server (the slave). Once the rights had been given, as well as the correct group, I initialised the connection between my slave and my master. except that I came up against the following error:

‘Last_IO_Error: Error connecting to source ‘replication@192.168.1.10:3306’. This was attempt 16162/86400, with a delay of 20 seconds between attempts. Message: SSL connection error: error:0A000086:SSL routines::certificate verify failed’

I've spent countless hours trying to find out why this error is appearing, and I can't figure it out. I've looked everywhere (maybe not enough?) but I just don't get it, and I'd welcome any help.

I'd also like to point out that I originally thought it was the fault of the certificate generated by MySQL that wasn't working, but I finally regenerated all the certificates with openssl to see if that was the cause.
to see if it was coming from there, but apparently not, I have the same problem. (I generated the ca.pem - ca-key.pem - client-cert.pem - client-key.pem - server-cert.pem - server-key.pem.
I also signed the certificates with the ca.pem and normally I have my certificates set to CA=True and not false on my client and server, but that doesn't seem to have a
change. I should also point out that I have changed the following parameter: SOURCE_SSL_VERIFY_SERVER_CERT=1 to ‘0’ and the connection works again, which I suppose is logical because of course
I also tested a command line connection with the certificates, but I don't think this proves anything about the validity of my certificates ?
mysql -h 192.168.1.10 -u replication -p --ssl-ca=ca.pem --ssl-cert=client-cert.pem --ssl-key=client-key.pem / This command works and I connect.

Here are all my configurations for the first server, the master (the one with the server certificates)

1/ Configuration my.cnf :

[mysqld]
log-bin=master1
server-id=1
binlog-format=mixed
relay-log=relay-log-master1
bind-address=0.0.0.0
ssl-ca=ca.pem
ssl-cert=server-cert.pem
ssl-key=server-key.pem
require_secure_transport=ON

2/ MySQL parameters :
mysql> SHOW VARIABLES LIKE '%ssl%';
+-------------------------------------+-----------------+
| Variable_name | Value |
+-------------------------------------+-----------------+
| admin_ssl_ca | |
| admin_ssl_capath | |
| admin_ssl_cert | |
| admin_ssl_cipher | |
| admin_ssl_crl | |
| admin_ssl_crlpath | |
| admin_ssl_key | |
| have_openssl | YES |
| have_ssl | YES |
| mysqlx_ssl_ca | |
| mysqlx_ssl_capath | |
| mysqlx_ssl_cert | |
| mysqlx_ssl_cipher | |
| mysqlx_ssl_crl | |
| mysqlx_ssl_crlpath | |
| mysqlx_ssl_key | |
| performance_schema_show_processlist | OFF |
| ssl_ca | ca.pem |
| ssl_capath | |
| ssl_cert | server-cert.pem |
| ssl_cipher | |
| ssl_crl | |
| ssl_crlpath | |
| ssl_fips_mode | OFF |
| ssl_key | server-key.pem |
| ssl_session_cache_mode | ON |
| ssl_session_cache_timeout | 300 |
+-------------------------------------+-----------------+

3/File in /var/lib/MySQL :

-rw------- 1 mysql mysql 1,7K mars 28 14:14 server-key.pem
-rw-r--r-- 1 mysql mysql 1,4K mars 28 14:14 server-cert.pem
-rw------- 1 mysql mysql 1,7K mars 28 14:14 client-key.pem
-rw-r--r-- 1 mysql mysql 1,3K mars 28 14:14 ca.pem
-rw------- 1 mysql mysql 1,7K mars 28 14:14 ca-key.pem
-rw-r--r-- 1 mysql mysql 1,4K mars 28 14:14 client-cert.pem

openssl verify -CAfile /var/lib/mysql/ca.pem /var/lib/mysql/server-cert.pem
/var/lib/mysql/server-cert.pem: OK



Slave side (customer) :

[mysqld]
log-bin=slave1
server-id=2
binlog-format=mixed
relay-log=relay-log-slave1

2/ Configuration file in /var/lib/MySQL :
-rw-r--r-- 1 mysql mysql 1,3K mars 28 14:46 ca.pem
-rw------- 1 mysql mysql 1,7K mars 28 14:46 client-key.pem
-rw-r--r-- 1 mysql mysql 1,4K mars 28 14:46 client-cert.pem

3/ Replica configuration :
CHANGE REPLICATION SOURCE TO
SOURCE_HOST='192.168.1.10',
SOURCE_USER='replication',
SOURCE_PASSWORD='password',
SOURCE_PORT=3306,
SOURCE_LOG_FILE='master1.000011',
SOURCE_LOG_POS=157,
SOURCE_CONNECT_RETRY=20,
SOURCE_SSL_CA='/var/lib/mysql/ca.pem',
SOURCE_SSL_CERT='/var/lib/mysql/client-cert.pem',
SOURCE_SSL_KEY='/var/lib/mysql/client-key.pem',
SOURCE_SSL_VERIFY_SERVER_CERT=1,
GET_SOURCE_PUBLIC_KEY=1,
SOURCE_SSL=1;