Mysql SSL connection specific problem with using certificates

Question

ISSUE: I cant establish connection between remote MySql server and my local computer when i try to use certificates.

• Mysql Version: Ver 14.14 Distrib 5.7.31, for Linux (x86_64) using EditLine wrapper
• buntu 18.04.3 LTS

I have created new Mysql instance on new Ubuntu server. i have created User By script:

CREATE USER 'sammy'@'MY_COMPUTER_IP' IDENTIFIED WITH mysql_native_password BY 'password';

When i type SHOW GLOBAL VARIABLES LIKE '%ssl%'; I see:

+---------------+--------------------------------+
| Variable_name | Value                          |
+---------------+--------------------------------+
| have_openssl  | YES                            |
| have_ssl      | YES                            |
| ssl_ca        | /var/lib/mysql/ca.pem          |
| ssl_capath    |                                |
| ssl_cert      | /var/lib/mysql/server-cert.pem |
| ssl_cipher    |                                |
| ssl_crl       |                                |
| ssl_crlpath   |                                |
| ssl_key       | /var/lib/mysql/server-key.pem  |
+---------------+--------------------------------+

I can see also there are (by default) certificates located in /var/lib/mysql/:

root@xxxxxx:/var/log/mysql# sudo find /var/lib/mysql -name '*.pem' -ls
   258224      4 -rw-------   1 mysql    mysql        1676 Aug 12 15:34 /var/lib/mysql/server-key.pem
   258226      4 -rw-------   1 mysql    mysql        1680 Aug 12 15:34 /var/lib/mysql/client-key.pem
   258225      4 -rw-------   1 mysql    mysql        1112 Aug 12 15:34 /var/lib/mysql/server-cert.pem
   258227      4 -rw-------   1 mysql    mysql        1112 Aug 12 15:34 /var/lib/mysql/client-cert.pem
   258223      4 -rw-------   1 mysql    mysql        2224 Aug 12 17:01 /var/lib/mysql/ca.pem
   258523      4 -rw-------   1 mysql    mysql         452 Aug 12 15:34 /var/lib/mysql/public_key.pem
   258495      4 -rw-------   1 mysql    mysql        1680 Aug 12 15:34 /var/lib/mysql/private_key.pem
   258222      4 -rw-------   1 mysql    mysql        1676 Aug 12 15:34 /var/lib/mysql/ca-key.pem

its how my /etc/mysql/my.cnf file looks like:

!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/
[mysqld]
require_secure_transport = ON
bind-address = thie_is_myserver_address_ip
        
ssl-ca=/var/lib/mysql/ca.pem
ssl-cert=/var/lib/mysql/server-cert.pem
ssl-key=/var/lib/mysql/server-key.pem

after changes I run:

sudo systemctl restart mysql

I have copied following certificates to my local computer:

 - /var/lib/mysql/ca.pem
 - /var/lib/mysql/client-cert.pem
 - /var/lib/mysql/client-key.pem

I tried to log in by mysqlWorkbench by using login, password and I have added those 3 certificates into SSL Tab With no luck...

Now i receiver Error:

SSL connection error: error:14094418:SSL routines:ssl3_read_bytes:tlsv1 alert unknown ca

Error logs (/var/log/mysql/error.log):

2020-08-12T20:22:48.398042Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2020-08-12T20:22:48.400817Z 0 [Note] /usr/sbin/mysqld (mysqld 5.7.31-0ubuntu0.18.04.1) starting as process 6428 ...
2020-08-12T20:22:48.407237Z 0 [Note] InnoDB: PUNCH HOLE support available
2020-08-12T20:22:48.407288Z 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2020-08-12T20:22:48.407302Z 0 [Note] InnoDB: Uses event mutexes
2020-08-12T20:22:48.407312Z 0 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
2020-08-12T20:22:48.407322Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2020-08-12T20:22:48.407332Z 0 [Note] InnoDB: Using Linux native AIO
2020-08-12T20:22:48.407814Z 0 [Note] InnoDB: Number of pools: 1
2020-08-12T20:22:48.407982Z 0 [Note] InnoDB: Using CPU crc32 instructions
2020-08-12T20:22:48.412789Z 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2020-08-12T20:22:48.426101Z 0 [Note] InnoDB: Completed initialization of buffer pool
2020-08-12T20:22:48.429598Z 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2020-08-12T20:22:48.442195Z 0 [Note] InnoDB: Highest supported file format is Barracuda.
2020-08-12T20:22:48.457283Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2020-08-12T20:22:48.457488Z 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2020-08-12T20:22:48.497583Z 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
2020-08-12T20:22:48.499136Z 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active.
2020-08-12T20:22:48.499162Z 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active.
2020-08-12T20:22:48.499694Z 0 [Note] InnoDB: Waiting for purge to start
2020-08-12T20:22:48.550043Z 0 [Note] InnoDB: 5.7.31 started; log sequence number 2721125
2020-08-12T20:22:48.550897Z 0 [Note] Plugin 'FEDERATED' is disabled.
2020-08-12T20:22:48.556599Z 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool
2020-08-12T20:22:48.559994Z 0 [Note] InnoDB: Buffer pool(s) load completed at 200812 20:22:48
2020-08-12T20:22:48.569386Z 0 [Note] Skipping generation of SSL certificates as options related to SSL are specified.
2020-08-12T20:22:48.570843Z 0 [Note] Skipping generation of RSA key pair as key files are present in data directory.
2020-08-12T20:22:48.571010Z 0 [Note] Server hostname (bind-address): 'xx.xxx.xx.xx'; port: 3306
2020-08-12T20:22:48.571043Z 0 [Note]   - 'xxx.xxx.xxx.xxx' resolves to 'xx.xx.xx.xx';
2020-08-12T20:22:48.571103Z 0 [Note] Server socket created on IP: 'xx.xx.xx.xx'.
2020-08-12T20:22:48.588243Z 0 [Note] Event Scheduler: Loaded 0 events
2020-08-12T20:22:48.588702Z 0 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.7.31-0ubuntu0.18.04.1'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  (Ubuntu)
2020-08-12T20:23:23.485022Z 2 [Note] Bad handshake
2020-08-12T20:24:27.578481Z 3 [Note] Bad handshake

When I try to connect locally to the remote server by add config for client (in /etc/mysql/my.cnf file):

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

And I reset mysql and I try to login loccally i receive:

root@xxxxxx:/var/log/mysql# mysql -u root -p
Enter password:
ERROR 2026 (HY000): SSL connection error: error:00000001:lib(0):func(0):reason(1) 

When i remove cert paths from my.cnf file it works corectlly withopu certificates. But i have to run this with sertificates.

What im I doing wrong? Thank you in advice!

Answer 1

Actually I forgot about this command

CREATE USER 'mysql_user'@'your_mysql_client_IP' IDENTIFIED BY 'password' REQUIRE X509;

I recommend those 2 easy tutorials to setup your mysql db with SSL/TLS certificates:

1. How to install & config
2. How to config certificates

Currently in point 1. fresh install already contains generated certificates so you dont have to generate new one. If you really want to generate new certificate or you want to refresh old one (default certificate has 356 the term of validity) you can use this command:

sudo mysql_ssl_rsa_setup --uid=mysql

or you can generate it manually by this tutorial:

1. How to Enable SSL and Remote Connections for MySQL

And put new certificates into this folder:

/var/lib/mysql/