Re: Can't connect to MySQL without SSL

Sorry to wander in in the middle of this but, Is the mysql server set to
only take ssl?

In the database,

SHOW VARIABLES LIKE 'have_ssl';

or test with,

mysql -h <server> -u <user> -p --ssl-mode=DISABLED


Here are my notes for a mariadb ssl self cert but its very close for mysql,
(and a different app but again should help)


to turn of,

[mariadb]

#ssl=0

and restart mysql/mariadb


Notes on making a ssl setup.

Mariadb/Mysql SSL/TLS

Thursday, 4 June 2026

12:45 pm



Database TLS



https://docs.redhat.com/en/documentation/red_hat_enterprise_linux/10/pdf/configuring_and_using_database_servers/Red_Hat_Enterprise_Linux-10-Configuring_and_using_database_servers-en-US.pdf


https://docs.redhat.com/en/documentation/red_hat_enterprise_linux/10/html/configuring_and_using_database_servers/using-mysql#configuring-mysql



https://docs.faveohelpdesk.com/docs/installation/providers/enterprise/redhat_mysql_ssl/



Database files,

ssl directory (set via ansible)

mkdir -p /var/lib/mysql/ssl

Create a Certificate Authority (CA)

 cd /var/lib/mysql/ssl

# Generate CA private key

openssl genrsa 2048 > mariadb-ca-key.pem



# Generate CA certificate

openssl req -new -x509 -nodes -days 3650 \

  -key mariadb-ca-key.pem \

  -out mariadb-ca-cert.pem



Generate Server Key and Certificate

 Create server private key

 openssl genrsa 2048 > mariadb-server-key.pem



Create certificate signing request (CSR)



openssl req -new -key mariadb-server-key.pem -out mariadb-server-req.pem



We now have 4 files,



mariadb-ca-cert.pem

mariadb-ca-key.pem

mariadb-server-key.pem

mariadb-server-req.pem



openssl x509 -req -in mariadb-server-req.pem \

  -days 3650 \

  -CA mariadb-ca-cert.pem \

  -CAkey mariadb-ca-key.pem \

  -set_serial 01 \

  -out mariadb-server-cert.pem


 We now have 5 files,

 mariadb-ca-cert.pem

mariadb-ca-key.pem

mariadb-server-cert.pem

mariadb-server-key.pem

mariadb server-req.pem


Now do the sym links

ln -s  /var/lib/mysql/ssl/mariadb-ca-key.pem
/etc/pki/tls/private/mariadb-ca-key.pem
ln -s /var/lib/mysql/ssl/mariadb-server-key.pem
/etc/pki/tls/private/mariadb-server-key.pem

ln -s  /var/lib/mysql/ssl/mariadb-ca-cert.pem
/etc/pki/tls/certs/mariadb-ca-cert.pem

ln -s   /var/lib/mysql/ssl/mariadb-server-cert.pem
/etc/pki/tls/certs/mariadb-server-cert.pem



vi /etc/my.cnf.d/mariadb-server-tls.cnf



[mysqld]

ssl-ca=/path/to/ca.pem

ssl-cert=/path/to/server-cert.pem

ssl-key=/path/to/server-key.pem



[mariadb]

ssl_key = /var/lib/mysql/ssl/mariadb-server-key.pem

ssl_cert = /var/lib/mysql/ssl/mariadb-server-cert.pem

ssl_ca = /var/lib/mysql/ssl/mariadb-ca-key.pem

tls_version = TLSv1.3

#ssl=0



Verify the output hashes match,



openssl x509 -noout -modulus -in /etc/pki/tls/certs/mariadb-server-cert.pem
| openssl md5
openssl rsa -noout -modulus -in /etc/pki/tls/private/mariadb-server-key.pem
| openssl md5

Set ownership

 chown mysql: *





Restart the database,

 systemctl restart mysql

 Check ssl is working,



SHOW VARIABLES LIKE 'have_ssl';





Check TLS version



mysql -u root -p



SHOW GLOBAL VARIABLES LIKE 'tls_version';






Other checks,



SHOW VARIABLES LIKE 'have_openssl';

SHOW STATUS LIKE 'Ssl_cipher';







Client side



 cd /var/lib/mysql/ssl



# Client key

openssl genrsa 2048 > redcap-sandpit-client-key.pem



# Client CSR

openssl req -new -key redcap-sandpit-client-key.pem -out
redcap-sandpit-client-req.pem



# Sign it

openssl x509 -req -in redcap-sandpit-client-req.pem \

  -days 3650 \

  -CA /etc/pki/tls/certs/mariadb-ca-cert.pem \

  -CAkey  /etc/pki/tls/private/mariadb-ca-key.pem \

  -set_serial 02 \

  -out redcap-sandpit-client-cert.pem



Set permissions,



chmod 0600 *key*



mariadb-ca-cert.pem

mariadb-ca-key.pem

mariadb-server-cert.pem

mariadb-server-key.pem

mariadb-server-req.pem

redcap-sandpit-client-key.pem

redcap-sandpit-client-req.pem

redcap-sandpit-client-cert.pem



Tar up the client and ca certs,



tar zcvf mariadb-ssl.tar.gz redcap-sandpit-client-key.pem
mariadb-ca-key.pem redcap-sandpit-client-cert.pem



On the client,



Edit  /var/www/html/database.php and disable the 5 lines as shown,







Add these to /etc/redcap/db_conn.php



/ Enable SSL

$db_ssl = true;

$db_ssl_key    = '/etc/mysql/ssl/redcap-sandpit-client-key.pem';

$db_ssl_cert   = '/etc/mysql/ssl/redcap-sandpit-client-cert.pem';

$db_ssl_ca     = '/etc/mysql/ssl/mariadb-ca-key.pem';

$db_ssl_capath = null;

$db_ssl_cipher = null;





On the client,



(done in ansible incl selinux context) mkdir  -p /etc/mariadb/ssl/ ; cd
/etc/mariadb/ssl/



Copy and expand,



cp /home/vuw.ac.nz/admjonesst1/mariadb-ssl.tar.gz .



tar zxvf mariadb-ssl.tar.gz



Local test



 mysql -u sandpit -p   --ssl-ca=/etc/mariadb/ssl/mariadb-ca-cert.pem -h <ip
address>































On Thu, 6 Aug 2026 at 16:46, dovecot--- via dovecot <[email protected]>
wrote:

> @basti require_secure_transport is OFF, I had checked that.
>
> Hi Timo!
>
> Creating the mysql-client.cnf didn't change the behavior, it tries to
> connect even with this minimal configuration:
>
> /etc/dovecot/dovecot.conf:
> sql_driver = mysql
>
> mysql host.docker.internal {
>   option_file = /etc/dovecot/mysql-client.cnf
> }
>
> /etc/dovecot/mysql-client.cnf:
> [client]
> ssl-mode=DISABLED
>
> Still gets me an error:
> TLS/SSL error: self-signed certificate in certificate chain
>
> In the meantime I will try to make it work using SSL. But the last time I
> tried it somehow conflicted with the "ssl_server_cert_file" and
> "ssl_server_key_file" which point to the certbot/letsencrypt certificates
> while MySQL generates self-signed certificates.
>
> Thanks for your help so far!
> _______________________________________________
> dovecot mailing list -- [email protected]
> To unsubscribe send an email to [email protected]
>

_______________________________________________
dovecot mailing list -- [email protected]
To unsubscribe send an email to [email protected]