Letsencrypt SSL for MySQL on Ubuntu 20.04

Please fill out the fields below so we can help you better. Note: you must provide your domain name to get help. Domain names for issued certificates are all made public in Certificate Transparency logs (e.g. https://crt.sh/?q=example.com), so withholding your domain name here does not increase secrecy, but only makes it harder for us to provide help.

My domain is: maraxai.de

I ran this command:

It produced this output:

My web server is (include version): VPN unmaged server

The operating system my web server runs on is (include version): Ubuntu 20.04

My hosting provider, if applicable, is:

I can login to a root shell on my machine (yes or no, or I don't know): yes

I'm using a control panel to manage my site (no, or provide the name and version of the control panel):no

The version of my client is (e.g. output of certbot --version or certbot-auto --version if you're using Certbot):0.40.0

I installed a Letsencrypt SSL certificate on my Ubuntu 20.4 VPN server and it works. Now, I am trying to configure mysql on this server for SSL. I read many relating posts that deal with the same issue and I spent many hours to fix it, without success.

These are the steps I did:

  • I copied the files cert.pem, chain.pem, fullchain.pem and privkey.pem into /var/lib/mysql.
    These are the same files that I used for the SSL configuration of my
    domain.

  • The configuration in mysql was not clear. I tried different combinations in [mysql] and [mysqld]. The one I expected to be correct, did not work: ssl_ca=/var/lib/mysql/cert.pem,ssl_cert=/var/lib/mysql/chain.pem,ssl_key=/var/lib/mysql/privkey.pem

    Only this configuration works without throwing an error. Note: fullchain.pem contains the certificates from cert.pem and chain.pem. In /etc/mysql/mysql.conf.d:

    [mysqld]
    
    ssl_cert=/var/lib/mysql/fullchain.pem
    
    ssl_key=/var/lib/mysql/privkey.pem
    

I can connect to mysql locally from my server and when checking the ssl attributes, I get:

(I list only the variables with values )

| Variable_name                       | Value                        |
+--------------------------------------+-----------------------------+
| have_openssl                        | YES                          |                       
| have_ssl                            | YES                          |
| performance_schema_show_processlist | OFF                          |
| ssl_cert                            | /var/lib/mysql/fullchain.pem |                           |
| ssl_fips_mode                       | OFF                          |
| ssl_key                             | /var/lib/mysql/privkey.pem 

When I run mysql > \s, I get:


Connection id:          32
Current database:
Current user:           mikeHome@localhost
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         8.0.26-0ubuntu0.20.04.3 (Ubuntu)
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    utf8mb4
Db     characterset:    utf8mb4
Client characterset:    latin1
Conn.  characterset:    latin1
UNIX socket:            /var/run/mysqld/mysqld.sock
Binary data as:         Hexadecimal

The line SSL: Not in use, shows that something is wrong.

When I run $ openssl s_client -connect maraxai.de:3306 -servername maraxai.de, I expect to get the same result as with $ openssl s_client -connect maraxai.de:443 -servername maraxai.de, i.e. the complete certificate chain with the successful handshake but instead, I get:

139990121219392:error:1408F10B:SSL routines:ssl3_get_record:wrong version number:../ssl/record/ssl3_record.c:331:
---
no peer certificate available
---
No client certificate CA names sent
---
SSL handshake has read 5 bytes and written 302 bytes
Verification: OK
---
New, (NONE), Cipher is (NONE)
Secure Renegotiation IS NOT supported
Compression: NONE
Expansion: NONE
No ALPN negotiated
Early data was not sent
Verify return code: 0 (ok)

Some posts suggested that the line SSL handshake has read 5 bytes and written 302 bytes suggests that the SSL handshake has been started but aborted since the server returns something that is not expected.

To further check I use openssl s_client -connect maraxai.de:3306 -servername maraxai.de -starttls mysql. The first section tells me about error:num=20:unable to get local issuer certificate and error:num=21:the server certificate is not verified for the server certificate (depth:0). Further, I see only one certificate (cert.pem). The intermediate certificates of chain.pem are not there but I use fullchain.pem which includes all three certificates (chain.pem and cert.pem).

To check if issuers and subjects of the certificates are set correctly, I run:

openssl crl2pkcs7 -nocrl -certfile fullchain.pem | openssl pkcs7 -print_certs -noout

Everything looks ok here:

subject=CN = maraxai.de
issuer=C = US, O = Let's Encrypt, CN = R3

subject=C = US, O = Let's Encrypt, CN = R3
issuer=C = US, O = Internet Security Research Group, CN = ISRG Root X1

subject=C = US, O = Internet Security Research Group, CN = ISRG Root X1
issuer=O = Digital Signature Trust Co., CN = DST Root CA X3
CONNECTED(00000003)
depth=0 CN = maraxai.de
verify error:num=20:unable to get local issuer certificate
verify return:1
depth=0 CN = maraxai.de
verify error:num=21:unable to verify the first certificate
verify return:1
---
Certificate chain
 0 s:CN = maraxai.de
   i:C = US, O = Let's Encrypt, CN = R3
---
Server certificate
-----BEGIN CERTIFICATE-----
//MIIF...
-----END CERTIFICATE-----
subject=CN = maraxai.de

issuer=C = US, O = Let's Encrypt, CN = R3

---
No client certificate CA names sent
Requested Signature Algorithms: ECDSA+SHA256:ECDSA+SHA384:ECDSA+SHA512:Ed25519:Ed448:RSA-PSS+SHA256:RSA-PSS+SHA384:RSA-PSS+SHA512:RSA-PSS+SHA256:RSA-PSS+SHA384:RSA-PSS+SHA512:RSA+SHA256:RSA+SHA384:RSA+SHA512:ECDSA+SHA224:RSA+SHA224
Shared Requested Signature Algorithms: ECDSA+SHA256:ECDSA+SHA384:ECDSA+SHA512:Ed25519:Ed448:RSA-PSS+SHA256:RSA-PSS+SHA384:RSA-PSS+SHA512:RSA-PSS+SHA256:RSA-PSS+SHA384:RSA-PSS+SHA512:RSA+SHA256:RSA+SHA384:RSA+SHA512
Peer signing digest: SHA256
Peer signature type: RSA-PSS
Server Temp Key: X25519, 253 bits
---
SSL handshake has read 2027 bytes and written 448 bytes
Verification error: unable to verify the first certificate
---
New, TLSv1.3, Cipher is TLS_AES_256_GCM_SHA384
Server public key is 2048 bit
Secure Renegotiation IS NOT supported
Compression: NONE
Expansion: NONE
No ALPN negotiated
Early data was not sent
Verify return code: 21 (unable to verify the first certificate)
---
---
Post-Handshake New Session Ticket arrived:
SSL-Session:
    Protocol  : TLSv1.3
    Cipher    : TLS_AES_256_GCM_SHA384
    Session-ID: B157BD91FF0A458D6A546C26CB5665C95CD88B99CE6C66A5D98783642C39EFA4
    Session-ID-ctx:
    Resumption PSK: CB807FC16CE11EB47FE7BDDD99C71A5AAF1AE5CDC600A127230E914AFC4AE1018A34F72F44741D2440EB4917D5DDD0D7
    PSK identity: None
    PSK identity hint: None
    SRP username: None
    TLS session ticket lifetime hint: 7200 (seconds)
    TLS session ticket:
    // ...0000 - 00e0


    Start Time: 1645286635
    Timeout   : 7200 (sec)
    Verify return code: 21 (unable to verify the first certificate)
    Extended master secret: no
    Max Early Data: 0
---
read R BLOCK
2▒▒#08S01Got timeout reading communication packetsread:errno=0

Also, for privkey.pem, I changed the key format to PKCS#1 to get the correct header -----BEGIN RSA PRIVATE KEY----- with the command $ openssl rsa -in privkey.pem -out privkey.pem.

I have no idea how to further investigate this issue. Any help would be greatly appreciated.

Are you sure? Because:

You're not connecting using IP/TCP/TLS, but using an UNIX socket. On localhost too. So a TLS (SSL) certificate isn't required.

Personally, I wouldn't be bothered with TLS. Heck, I believe it's recommended to disable listening to a network connection if not required, but use an UNIX socket only. For safety. That way you don't even require TLS for MySQL.

3 Likes

Eventually, I need a working SSL connection to MySQL. I run a NodeJS application that connects to MySQL on the SSL secured server. SSL needs to be working for this.

Sorry, mea culpa. When I connect to mysql externally, I get:

mysql  Ver 8.0.21 for Win64 on x86_64 (MySQL Community Server - GPL)

Connection id:          65
Current database:
Current user:           someuser@someIP
SSL:                    Cipher in use is TLS_AES_256_GCM_SHA384
Using delimiter:        ;
Server version:         8.0.26-0ubuntu0.20.04.3 (Ubuntu)
Protocol version:       10
Connection:             maraxai.de via TCP/IP
Server characterset:    utf8mb4
Db     characterset:    utf8mb4
Client characterset:    cp850
Conn.  characterset:    cp850
TCP port:               3306
Binary data as:         Hexadecimal

Cool, so it seems to work?

2 Likes

No, I can access MySQL externally via command line but when I connect to my server and check the certificates with 'openssl s_client -connect maraxai.de:3306 -servername maraxai.de' I get the described errors. Nothing has changed.

You were correct using -starttls mysql earlier.

2 Likes

It returns two verify errors, ' error:num=20:unable to get local issuer certificate' and 'error:num=21:unable to verify the first certificate'.
Also, it only shows the first certificate in the chain, the server certificate. The remaining two certificates are not listed.
The handshake is started but at the end of the 2nd handshake, there is a timeout listed: '2▒▒#08S01Got timeout reading communication packetsread:errno=0'

'

Please show the entire output.

2 Likes
CONNECTED(00000003)
depth=0 CN = maraxai.de
verify error:num=20:unable to get local issuer certificate
verify return:1
depth=0 CN = maraxai.de
verify error:num=21:unable to verify the first certificate
verify return:1
---
Certificate chain
 0 s:CN = maraxai.de
   i:C = US, O = Let's Encrypt, CN = R3
---
Server certificate
-----BEGIN CERTIFICATE-----
//MIIF....
-----END CERTIFICATE-----
subject=CN = maraxai.de

issuer=C = US, O = Let's Encrypt, CN = R3

---
No client certificate CA names sent
Requested Signature Algorithms: ECDSA+SHA256:ECDSA+SHA384:ECDSA+SHA512:Ed25519:Ed448:RSA-PSS+SHA256:RSA-PSS+SHA384:RSA-PSS+SHA512:RSA-PSS+SHA256:RSA-PSS+SHA384:RSA-PSS+SHA512:RSA+SHA256:RSA+SHA384:RSA+SHA512:ECDSA+SHA224:RSA+SHA224
Shared Requested Signature Algorithms: ECDSA+SHA256:ECDSA+SHA384:ECDSA+SHA512:Ed25519:Ed448:RSA-PSS+SHA256:RSA-PSS+SHA384:RSA-PSS+SHA512:RSA-PSS+SHA256:RSA-PSS+SHA384:RSA-PSS+SHA512:RSA+SHA256:RSA+SHA384:RSA+SHA512
Peer signing digest: SHA256
Peer signature type: RSA-PSS
Server Temp Key: X25519, 253 bits
---
SSL handshake has read 2027 bytes and written 448 bytes
Verification error: unable to verify the first certificate
---
New, TLSv1.3, Cipher is TLS_AES_256_GCM_SHA384
Server public key is 2048 bit
Secure Renegotiation IS NOT supported
Compression: NONE
Expansion: NONE
No ALPN negotiated
Early data was not sent
Verify return code: 21 (unable to verify the first certificate)
---
---
Post-Handshake New Session Ticket arrived:
SSL-Session:
    Protocol  : TLSv1.3
    Cipher    : TLS_AES_256_GCM_SHA384
    Session-ID: 27B947A4A2D6831B1BB16C8AACA8E53C2857CC35ABC4B10B99EDB9E9DB108075
    Session-ID-ctx:
    Resumption PSK: 7FB43FFC812388198A279A1C8C05634B977AE83D6A8A6FFD385065563DA50D211076F58D2DC4F247C772D6168EC151E5
    PSK identity: None
    PSK identity hint: None
    SRP username: None
    TLS session ticket lifetime hint: 7200 (seconds)
    TLS session ticket:
//0000-00e0

    Start Time: 1645351533
    Timeout   : 7200 (sec)
    Verify return code: 21 (unable to verify the first certificate)
    Extended master secret: no
    Max Early Data: 0
---
read R BLOCK
---
Post-Handshake New Session Ticket arrived:
SSL-Session:
    Protocol  : TLSv1.3
    Cipher    : TLS_AES_256_GCM_SHA384
    Session-ID: E1C005DCCFD2852788F297971B0CBA0843B8456E9582DDF20CB0868F6B97FD5B
    Session-ID-ctx:
    Resumption PSK: 162958E9EF18C661116A9837EC58D375F450F186D7435FED05A5132E6FB61DC00E9E6346B1E64916908FDAF7AA40AF2F
    PSK identity: None
    PSK identity hint: None
    SRP username: None
    TLS session ticket lifetime hint: 7200 (seconds)
    TLS session ticket:
//0000-00e0

    Start Time: 1645351533
    Timeout   : 7200 (sec)
    Verify return code: 21 (unable to verify the first certificate)
    Extended master secret: no
    Max Early Data: 0
---
read R BLOCK
2▒▒#08S01Got timeout reading communication packetsread:errno=0

And ssl_cert is still refering to fullchain.pem?

3 Likes

The names of the variables may be interesting too.
May be there is variable you should set, something like ssl_chain or similar? I am just purely guessing, I have no experience with MySQL.

2 Likes

yes, in /etc/mysql/mysql.conf.d/mysqld.cnf:

[mysqld]
ssl_cert=/var/lib/mysql/fullchain.pem
ssl_key=/var/lib/mysql/privkey.pem

Here are all ssl variables:

| admin_ssl_ca                        |                              |
| admin_ssl_capath                    |                              |
| admin_ssl_cert                      |                              |
| admin_ssl_cipher                    |                              |
| admin_ssl_crl                       |                              |
| admin_ssl_crlpath                   |                              |
| admin_ssl_key                       |                              |
| have_openssl                        | YES                          |
| have_ssl                            | YES                          |
| mysqlx_ssl_ca                       |                              |
| mysqlx_ssl_capath                   |                              |
| mysqlx_ssl_cert                     |                              |
| mysqlx_ssl_cipher                   |                              |
| mysqlx_ssl_crl                      |                              |
| mysqlx_ssl_crlpath                  |                              |
| mysqlx_ssl_key                      |                              |
| performance_schema_show_processlist | OFF                          |
| ssl_ca                              |                              |
| ssl_capath                          |                              |
| ssl_cert                            | /var/lib/mysql/fullchain.pem |
| ssl_cipher                          |                              |
| ssl_crl                             |                              |
| ssl_crlpath                         |                              |
| ssl_fips_mode                       | OFF                          |
| ssl_key                             | /var/lib/mysql/privkey.pem

It might be that MySQL isn't able to use a full chain file for the ssl_cert variable. Try setting ssl_ca to chain.pem and ssl_cert to cert.pem.

In your opening post you've used those variable/file combinations just the other way around.

2 Likes

Yes, you are right, this helped! Now, I get the complete chain with the handshake but there still is this timeout error at the end.
In nodejs, when calling the server.js file, I get the error

mike@server:/var/www/html/HSF/calculator/report$ sudo node server.js
This app listens port 3000.
error in connection:  [Error: 139715198224320:error:14094418:SSL routines:ssl3_read_bytes:tlsv1 alert unknown ca:../deps/openssl/openssl/ssl/record/rec_layer_s3.c:1546:SSL alert number 48
] {
  library: 'SSL routines',
  function: 'ssl3_read_bytes',
  reason: 'tlsv1 alert unknown ca',
  code: 'ERR_SSL_TLSV1_ALERT_UNKNOWN_CA',
  fatal: true
}
Connected to MySQL server

This is the complete return from openssl s_client -connect maraxai.de:3306 -servername maraxai.de -starttls mysql. It says: No client certificate CA names sent. Where do I have to go from here?

CONNECTED(00000003)
depth=2 C = US, O = Internet Security Research Group, CN = ISRG Root X1
verify return:1
depth=1 C = US, O = Let's Encrypt, CN = R3
verify return:1
depth=0 CN = maraxai.de
verify return:1
---
Certificate chain
 0 s:CN = maraxai.de
   i:C = US, O = Let's Encrypt, CN = R3
 1 s:C = US, O = Let's Encrypt, CN = R3
   i:C = US, O = Internet Security Research Group, CN = ISRG Root X1
 2 s:C = US, O = Internet Security Research Group, CN = ISRG Root X1
   i:O = Digital Signature Trust Co., CN = DST Root CA X3
---
Server certificate
-----BEGIN CERTIFICATE-----
//
cmjrgKxGe40q4sEfyw==
-----END CERTIFICATE-----
subject=CN = maraxai.de

issuer=C = US, O = Let's Encrypt, CN = R3

---
No client certificate CA names sent
Requested Signature Algorithms: ECDSA+SHA256:ECDSA+SHA384:ECDSA+SHA512:Ed25519:Ed448:RSA-PSS+SHA256:RSA-PSS+SHA384:RSA-PSS+SHA512:RSA-PSS+SHA256:RSA-PSS+SHA384:RSA-PSS+SHA512:RSA+SHA256:RSA+SHA384:RSA+SHA512:ECDSA+SHA224:RSA+SHA224
Shared Requested Signature Algorithms: ECDSA+SHA256:ECDSA+SHA384:ECDSA+SHA512:Ed25519:Ed448:RSA-PSS+SHA256:RSA-PSS+SHA384:RSA-PSS+SHA512:RSA-PSS+SHA256:RSA-PSS+SHA384:RSA-PSS+SHA512:RSA+SHA256:RSA+SHA384:RSA+SHA512
Peer signing digest: SHA256
Peer signature type: RSA-PSS
Server Temp Key: X25519, 253 bits
---
SSL handshake has read 4723 bytes and written 448 bytes
Verification: OK
---
New, TLSv1.3, Cipher is TLS_AES_256_GCM_SHA384
Server public key is 2048 bit
Secure Renegotiation IS NOT supported
Compression: NONE
Expansion: NONE
No ALPN negotiated
Early data was not sent
Verify return code: 0 (ok)
---
---
Post-Handshake New Session Ticket arrived:
SSL-Session:
    Protocol  : TLSv1.3
    Cipher    : TLS_AES_256_GCM_SHA384
    Session-ID: 47053FCED76FF59261FEF72CF0D97C2F91022648A6FDC6E90EAC4C9837E7CBC9
    Session-ID-ctx:
    Resumption PSK: 408A25D12B42C0494965F0D6ECE27F5EE759D2D391FCB86A9F59D554454D68729D84F3A6E24702C28032E964FCAFAC0A
    PSK identity: None
    PSK identity hint: None
    SRP username: None
    TLS session ticket lifetime hint: 7200 (seconds)
    TLS session ticket:
    //

    Start Time: 1645376931
    Timeout   : 7200 (sec)
    Verify return code: 0 (ok)
    Extended master secret: no
    Max Early Data: 0
---
read R BLOCK
---
Post-Handshake New Session Ticket arrived:
SSL-Session:
    Protocol  : TLSv1.3
    Cipher    : TLS_AES_256_GCM_SHA384
    Session-ID: 9A7369417224EBD918AA05F3925E800FDC5570A4822B8822F0BE84E60F1B102D
    Session-ID-ctx:
    Resumption PSK: D95A0E44EEA1E357729FE92FBC649521A7CE0551C50880D5FAF45DC49B406E59B6CBE1F46197CAE26511DED9A82600DC
    PSK identity: None
    PSK identity hint: None
    SRP username: None
    TLS session ticket lifetime hint: 7200 (seconds)
    TLS session ticket:
//

    Start Time: 1645376931
    Timeout   : 7200 (sec)
    Verify return code: 0 (ok)
    Extended master secret: no
    Max Early Data: 0
---
read R BLOCK
2▒▒#08S01Got timeout reading communication packetsread:errno=0


That's normal, you're not sending a client certificate. (They're only used in some enterprise systems to identify users)

From there you try to connect and see if the database itself works over TLS.

2 Likes

Yes, I can connect externally to MySQL and for SSL it states Cipher in use is TLS_AES_256_GCM_SHA384. Nevertheless, if I run my Nodejs server file, which establishes a connection to MySQL, I get the error from above.

That's not the error.

This is the error.

Your client server does not recognize Let's Encrypt as a valid issuer.

What's the difference between these two records? Is your mysql sending all three certs or just one? (Check the mysql documentation)

If I were you, I'd use postgresql.

1 Like