CODEWITHSUNDEEP
mysqlssl
dot
dot

Reputation: 15660

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

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.

Upvotes: 15

Views: 42732

Answers (3)

Julien
Julien

Reputation: 3065

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

Upvotes: 8

StationaryTraveller
StationaryTraveller

Reputation: 1469

Adding '--skip-ssl' doesn't solve the problem, It's just a quick workaround. (And causes a possible security issue)

I solved it by simply changing the machine date to the current date.

It happened to me after a machine restart, and the clock was out sync. (Jumped to 2010 instead of 2017)

Upvotes: 11

dot
dot

Reputation: 15660

I found this option

 mysql --user=root --password=test testdb --skip-ssl

And it also works with the mysqldump command.

Upvotes: -3

Related Questions