39

UPDATE2

Using WireShark I found out the problem string (I hope I did):

28 | 9.582638 | 192.168.18.128 | 192.168.18.129 | MySQL Response Error 1043

And the error is (according to docs):

Error: 1043 SQLSTATE: 08S01 (ER_HANDSHAKE_ERROR)
Message: Bad handshake

Here are the screenshots of WireShark in two cases:

Connection from Windows 8 (Success):

enter image description here

Connection from CentOS (Fail):

enter image description here

Why does this happen?

UPDATE

One interesting notice:
I have successfully connected with Master DB using Windows 8 (192.168.18.1) by modifying ssluser setting on Master for 192.168.18.1 host - made a change: from REQUIRE SSL to REQUIRE X509. However this doesn't work in our case with slave-to-master connection.

I have faced with SSL replication problem in CentOS-6.3. I am using OpenSSL to create both clients and server certificates and both clients and server certificates are signed by the same CA.

Server IP: 192.168.18.128
Slave  IP: 192.168.18.129
MySQL version 5.1.66 SSL

All certificates I receive using "Setting Up SSL Certificates and Keys for MySQL" section of MySQL help pages.

Server's my.cnf file:

[mysqld]
ssl-key=/etc/mysql/certs/server-key.pem
ssl-cert=/etc/mysql/certs/server-cert.pem
ssl-ca=/etc/mysql/certs/ca-cert.pem

Client's my.cnf file:

[client]
ssl-ca=/etc/mysql/ssl/ca-cert.pem
ssl-key=/etc/mysql/ssl/client-key.pem
ssl-cert=/etc/mysql/ssl/client-cert.pem

On Master I setup slave user with SSL like this:

CREATE USER 'ssluser'@'192.168.18.129' IDENTIFIED BY 'sslpass';
GRANT REPLICATION SLAVE ON *.* TO 'ssluser'@'192.168.18.129' REQUIRE SSL;

To update Slave I am using the following command (according to show master status command):

SLAVE STOP;
CHANGE MASTER TO \
             MASTER_HOST='192.168.18.128',                      \
             MASTER_USER='sslreplicant',                        \
             MASTER_PASSWORD='db.sslreplicantprimary',          \
             MASTER_LOG_FILE='mysql-bin.000026',                \
             MASTER_LOG_POS=106,                                \
             MASTER_SSL=1,                                      \
             MASTER_SSL_CA='/etc/mysql/certs/ca-cert.pem',      \
             MASTER_SSL_CAPATH='/etc/mysql/certs/',             \
             MASTER_SSL_CERT='/etc/mysql/certs/client-cert.pem',\
             MASTER_SSL_KEY='/etc/mysql/certs/client-key.pem';
SLAVE START;

Replication itself works fine:

mysql> SHOW VARIABLES LIKE '%ssl%';

have_openssl  = YES
have_ssl      = YES
ssl_ca        = /etc/mysql/certs/ca-cert.pem
ssl_capath    =
ssl_cert      = /etc/mysql/certs/server-cert.pem
ssl_cipher    =
ssl_key       = /etc/mysql/certs/server-key.pem

This is both - on Master and on Slave.

But when I manually check the connection from Slave to Master I receive an error.

Here are the options I tried so far (the same result from everyone):

[gahcep@localhost  ~]$ mysql -u ssluser -h 192.168.18.128 -p

[gahcep@localhost  ~]$ mysql --ssl --ssl-ca=/etc/mysql/certs/ca-cert.pem \
                             -u ssluser -h 192.168.18.128 -p

[gahcep@localhost ~]$ mysql --ssl-ca=/etc/mysql/certs/ca-cert.pem \
                            --ssl-cert=/etc/mysql/certs/client-cert.pem \
                            --ssl-key=/etc/mysql/certs/client-key.pem \
                            -u ssluser -h 192.168.18.128 -p

Enter password:
ERROR 2026 (HY000): SSL connection error

Steps to Reproduce:

  1. setup/create both clients and server certs signed by same ca.
  2. setup both my.cnf files on clients and servers as mentioned in this thread
  3. create ssluser on master for slave
  4. mysql -u ssluser -h 192.168.18.128 -p

Please, note, I indeed used different Common Names for all certificates: for CA, clien and server.

ADDITIONAL INFORMATION

Verification results:

[gahcep@localhost ~]$ sudo openssl verify -purpose sslclient \
           -CAfile /etc/mysql/certs/ca-cert.pem /etc/mysql/certs/client-cert.pem
/etc/mysql/certs/client-cert.pem: OK

[gahcep@localhost ~]$ sudo openssl verify -purpose sslserver \
           -CAfile /etc/mysql/certs/ca-cert.pem /etc/mysql/certs/server-cert.pem
/etc/mysql/certs/server-cert.pem: OK

Sertificates information:

CA:

[gahcep@localhost ~]$ sudo openssl x509 -noout -subject -issuer -dates \
           -serial -hash -fingerprint -in /etc/mysql/certs/ca-cert.pem
subject= /C=RU/L=Vladivostok/O=Default Company Ltd/CN=PriSec
issuer= /C=RU/L=Vladivostok/O=Default Company Ltd/CN=PriSec
notBefore=Jan  4 06:27:46 2013 GMT
notAfter=Nov 13 06:27:46 2022 GMT
serial=B45D177E85F99578
c2c5b88b
SHA1 Fingerprint=5B:07:AA:39:28:24:CE:1A:CF:35:FA:14:36:23:65:8F:84:61:B0:1C

Client Certificate:

[gahcep@localhost ~]$ sudo openssl x509 -noout -subject -issuer -dates \
           -serial -hash -fingerprint -in /etc/mysql/certs/client-cert.pem
subject= /C=RU/L=Vladivostok/O=Default Company Ltd/CN=Secondary
issuer= /C=RU/L=Vladivostok/O=Default Company Ltd/CN=PriSec
notBefore=Jan  4 06:29:07 2013 GMT
notAfter=Nov 13 06:29:07 2022 GMT
serial=01
6df9551f
SHA1 Fingerprint=F5:9F:4A:14:E8:96:26:BC:71:79:43:5E:18:BA:B2:24:BE:76:17:52

Server Certificate:

[gahcep@localhost ~]$ sudo openssl x509 -noout -subject -issuer -dates \
            -serial -hash -fingerprint -in /etc/mysql/certs/server-cert.pem
subject= /C=RU/L=Vladivostok/O=Default Company Ltd/CN=Primary
issuer= /C=RU/L=Vladivostok/O=Default Company Ltd/CN=PriSec
notBefore=Jan  4 06:28:25 2013 GMT
notAfter=Nov 13 06:28:25 2022 GMT
serial=01
64626d57
SHA1 Fingerprint=39:9E:7A:9E:60:9A:58:68:81:2F:90:A5:9B:BF:E8:26:C5:9D:3C:AB

Directories Permissions:

On Master:

[gahcep@localhost ~]$ ls -la /etc/mysql/certs/
drwx------. 3 mysql  mysql  4096 Jan  3 23:49 .
drwx------. 3 mysql  mysql  4096 Jan  3 07:34 ..
-rw-rw-r--. 1 gahcep gahcep 1261 Jan  3 22:27 ca-cert.pem
-rw-rw-r--. 1 gahcep gahcep 1675 Jan  3 22:27 ca-key.pem
-rw-rw-r--. 1 gahcep gahcep 1135 Jan  3 22:28 server-cert.pem
-rw-rw-r--. 1 gahcep gahcep 1679 Jan  3 22:28 server-key.pem
-rw-rw-r--. 1 gahcep gahcep  976 Jan  3 22:28 server-req.pem

On Slave:

[gahcep@localhost ~]$ ls -la /etc/mysql/certs/
drwx------. 3 mysql mysql 4096 Jan  3 22:57 .
drwx------. 3 mysql mysql 4096 Jan  3 07:50 ..
-rw-r--r--. 1 root  root  1261 Jan  3 22:56 ca-cert.pem
-rw-r--r--. 1 root  root  1139 Jan  3 22:57 client-cert.pem
-rw-r--r--. 1 root  root  1675 Jan  3 22:57 client-key.pem

If someone can suggest the solution, I would really appreciate this!

Improve this question
1
  • Comments are not for extended discussion; this conversation has been moved to chat.
    – Paul White ♦
    Oct 7, 2016 at 10:47

3 Answers 3

Reset to default
2

Try making certificate files owned by mysql user and not readable by others.

You can try also with a fixed cipher:

mysql ... --ssl-cipher=AES128-SHA

And for the change master:

CHANGE MASTER TO ... MASTER_SSL_CIPHER='AES128-SHA'

Improve this answer
1

Possible solutions:

How to tell whether MySQL Server uses yaSSL or OpenSSL

That shows a workaround for the lack of a suitable global status value. The idea is to check for the Rsa_public_key status variable:

mysql> show status like '%rsa%';
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| Rsa_public_key |       |
+----------------+-------+
1 row in set (0.00 sec)

If OpenSSL is in use, that variable will exist; otherwise it is yaSSL.

Other possibility:

MySQL and SSL connection failing ERROR 2026 (HY000) (Stack Overflow)

Improve this answer
0
1

If you tried everything, but SSL is not working, and at the same time you're running mysqld in chroot, then the cause for the errors like:

ERROR 2026 (HY000): SSL connection error: error:00000001:lib(0):func(0):reason(1)

or

ERROR 2026 (HY000): SSL connection error: protocol version mismatch

could be that you forgot to create dev/random and dev/urandom devices in the chroot environment (and openssl lib can't obtain entropy - it opens these devices after chroot). You can fix it this way (replace /srv/mysqld with your chroot dir and mysqld with the user mysqld is running under):

sudo install -d -o mysqld -g mysqld -m 500 /srv/mysqld/dev
sudo mknod -m 444 /srv/mysqld/dev/random c 1 8
sudo mknod -m 444 /srv/mysqld/dev/urandom c 1 9
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.