I need help with mysql replication with ssl. I got a problem when i'm turn on ssl on my slave – error connecting to master 'replica@1.2.3.4:3306' - retry-time: 60 retries: 1
Here is my setup
Master
mysql> show variables like "%ssl%";
+---------------+--------------------------------------+
| Variable_name | Value |
+---------------+--------------------------------------+
| have_openssl | YES |
| have_ssl | YES |
| ssl_ca | /etc/mysql/ssl-mysql/ca-cert.pem |
| ssl_capath | |
| ssl_cert | /etc/mysql/ssl-mysql/server-cert.pem |
| ssl_cipher | |
| ssl_key | /etc/mysql/ssl-mysql/server-key.pem |
+---------------+--------------------------------------+
Certs created on Gentoo with OpenSSL 1.0.2d 2015-07-09
Here's the interesting part. if I connect from Ubuntu 12.04 client instance specifying a client key, I get the standard error:
$ mysql -h 1.2.3.4 --ssl-ca=/etc/mysql/ssl-mysql/ca-cert.pem --ssl-cert=/etc/mysql/ssl-mysql/client-cert.pem --ssl-key=/etc/mysql/ssl-mysql/client-key.pem -u root -p
Enter password:
ERROR 2026 (HY000): SSL connection error
In logs
SSL error: Unable to get certificate from '/etc/mysql/ssl-mysql/client-cert.pem'
[ERROR] Slave I/O: error connecting to master 'replica@1.2.3.4:3306' - retry-time: 60 retries: 1, Error_code:2026
However, if I only specify the CA certificate and do not specify a client key or certificate, I can connect correctly with SSL:
mysql -h 1.2.3.4 --ssl-ca=/etc/mysql/ssl-mysql/ca-cert.pem -u replica -p
mysql> \s
--------------
mysql Ver 14.14 Distrib 5.6.27, for Linux (x86_64) using EditLine wrapper
Connection id: 6873
Current database:
Current user: replica@1.2.3.5
SSL: Cipher in use is DHE-RSA-AES256-SHA
Best Answer
You are trying to connect to the remote host using the server file of the master.
The reason it is working with only the CA is presumably the SSL configuration on the slave has it's own certificate signed by the same CA.
What you seem to be trying to do is use client certificate authentication. If that is the case you need an additional client certificate that is recognised on the slave to be used as the certificate you use when trying to do a client connection.
IN simple terms, the server-cert is used to provide the SSL negotiation and is provided by the server to clients to allow the clients to encrypt their traffic in a way that the server can decrypt.
Each server would have its own server-cert that identifies its hostname and allows clients to connect and verify they are connecting to the expected server.
A client certificate is normally a separate certificate and is unrelated to the server certificate on either the master or the slave.