14

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.

dot
  • 14,928
  • 41
  • 110
  • 218

3 Answers3

11

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)

StationaryTraveller
  • 1,449
  • 2
  • 19
  • 31
7

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

Julien
  • 2,747
  • 1
  • 12
  • 17
1

I found this option

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

And it also works with the mysqldump command.

dot
  • 14,928
  • 41
  • 110
  • 218
  • 14
    This does not solve the problem, it simply bypasses it. If you intend to use SSL, then use it. If not, then do not set it up in the servers. – siride Apr 24 '17 at 21:14
  • 2
    this answer is similar to a doctor suggesting to cut off the leg because the toe hurts. – Sar009 Mar 16 '21 at 09:06