MySQL Server Cannot Verify SSL Certificate

Question

I'm trying to configure a mysql server for secure remote connections over TCP/IP using SSL. The server mysql runs on has a domain name associated with it which i have requested a certificate for from Letsencrypt. I have copied all the letsencrypt certificate files to /var/lib/mysql and changed user and group ownership to mysql so all the files are readable by mysql. I set up the SSl configuration as follows:

ssl_ca=chain.pem
ssl_cert=cert.pem
ssl_key=privkey.pem
require_secure_transport=1

My problem now is that mysql keeps saying it cannot verify the SSL certificate because it cannot get issuer certificate. The full error message:

[Warning] [MY-013414] [Server] Server SSL certificate doesn't verify: unable to get issuer certificate

OS: Ubuntu 22.04.1 LTS

MySQL Version: 8.0.31-0ubuntu0.22.04.1 (version 8, basically)

I've tried using the following configuration and got the same result

ssl_ca=fullchain.pem
ssl_cert=fullchain.pem
ssl_key=privkey.pem
require_secure_transport=1

And

ssl_ca=fullchain.pem
ssl_cert=cert.pem
ssl_key=privkey.pem
require_secure_transport=1

I need help with getting rid of the error hilighted above. MySQL seems to work fine with its own self signed certificate files though, only showing a warning that the ca certificate is self signed when i use that.

Answer 1

I fixed it by copying the Root CA certificate in the chain.pem file to a separate file and installed it as part of the trusted root CAs on the OS by following the instruction here: https://ubuntu.com/server/docs/security-trust-store. Then i replaced the line ssl_ca=fullchain.pem/ssl_ca=chain.pem with ssl_capath=/etc/ssl/certs.