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

Question

Background:

I'm trying to log in via command line to a mysql database set up by one of our admins. I see that they have ssl enabled because when I try to connect i get this message:

 mysql --user=root --password=test testdb
ERROR 2026 (HY000): SSL connection error: error:00000001:lib(0):func(0):reason(1)

What I've Checked So far:

I've checked the my.cnf file for the ssl settings:

[client]
#password       = your_password
port            = 3306
socket          = /var/run/mysqld/mysqld.sock
ssl-ca   = /etc/ssl/ca-self-cert.pem
ssl-cert = /etc/ssl/server-self-cert.pem
ssl-key  = /etc/ssl/server-self-key.pem

[mysqld]
...
server-id       = 100                                                      
relay-log = mysqld-relay-bin                                               
ssl-ca   = /etc/ssl/ca-self-cert.pem                                       
ssl-cert = /etc/ssl/server-self-cert.pem                              
ssl-key  = /etc/ssl/server-self-key.pem  

I tried changing the login command to look this this instead:

mysql --user=root --password=test testdb --protocol=TCP --ssl-ca=/etc/ssl/ca-self-cert.pem
ERROR 2003 (HY000): Can't connect to MySQL server on 'localhost' (111)

and also:

mysql --user=root --password=test testdb --protocol=TCP --ssl-ca=/etc/ssl/ca-self-cert.pem --host=10.123.123.123
ERROR 2026 (HY000): SSL connection error: error:00000001:lib(0):func(0):reason(1)

The value I've specified for host matches what is set up as the bind-address in my.cnf

I'm still google more to find other articles / posts. But so far, I haven't been able to find a solution.

Any suggestions would be appreciated.

ps. I do know that the database itself is ok because the web application that connects to it is working fine. I just need to be able to connect so I can do a dump of the database.

Answer 1

As already said, --skip-ssl (or --ssl-mode=DISABLED for MySQL 8.0) doesn't solve that problem, it only bypasses it.

The error occures when the SSL handshake encountered an error, MySQL lacks of details about that, and this is an opened bug : https://bugs.mysql.com/bug.php?id=75311

You should first check your certificates :

openssl verify -CAfile /etc/mysql/newcerts/ca-cert.pem /etc/mysql/newcerts/server-cert.pem /etc/mysql/newcerts/client-cert.pem

Then, you must follow MySQL requierements to generate proper keys and certificats , especially about Common Name values that must differ : https://dev.mysql.com/doc/refman/8.0/en/creating-ssl-files-using-openssl.html

Here is a way to generate those certificates, maybe not the best, but it works, according to Activ'Cloud support :

Server side :

openssl genrsa 2048 > /tmp/cert/mysqld-ca-key.pem
openssl req -sha1 -new -x509 -nodes -days 3650 -key /tmp/cert/mysqld-ca-key.pem -subj "/C=FR/ST=/L=/O=mysqld/CN=mysqld-CA" > /tmp/cert/mysqld-ca-cert.pem
openssl req -sha1 -newkey rsa:2048 -days 3650 -nodes -keyout /tmp/cert/mysqld-server-key.pem -subj "/C=FR/ST=/L=/O=mysqld/CN=mysqld-server" > /tmp/cert/mysqld-server-req.pem
openssl rsa -in /tmp/cert/mysqld-server-key.pem -out /tmp/cert/mysqld-server-key.pem
openssl x509 -sha1 -req -in /tmp/cert/mysqld-server-req.pem -days 3650 -CA /tmp/cert/mysqld-ca-cert.pem -CAkey /tmp/cert/mysqld-ca-key.pem -set_serial 01 > /tmp/cert/mysqld-server-cert.pem

Client side :

openssl req -sha1 -newkey rsa:2048 -days 3650 -nodes -keyout /tmp/client-cert/mysql-client-key.pem > /tmp/client-cert/mysql-client-req.pem -subj "/C=FR/ST=/L=/O=mysql-client/CN=mysql-client"
openssl rsa -in /tmp/client-cert/mysql-client-key.pem -out /tmp/client-cert/mysql-client-key.pem
openssl x509 -sha1 -req -in /tmp/client-cert/mysql-client-req.pem -days 3650 -CA /tmp/cert/mysqld-ca-cert.pem -CAkey /tmp/cert/mysqld-ca-key.pem -set_serial 01 > /tmp/client-cert/mysql-client-cert.pem                                                                                                                                                                                                    

Then copy the files generated for client and server sides in the right directories.

Lastly, you can try to force your cipherlist : https://dev.mysql.com/doc/refman/8.0/en/server-status-variables.html#statvar_Ssl_cipher_list