Re: Can't connect to MySQL without SSL

6 Aug 2026 · *key*

      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/con...
https://docs.redhat.com/en/documentation/red_hat_enterprise_linux/10/html/co...
https://docs.faveohelpdesk.com/docs/installation/providers/enterprise/redhat...
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 <dovecot@dovecot.org>
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 -- dovecot@dovecot.org
To unsubscribe send an email to dovecot-leave@dovecot.org
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
[1]https://docs.redhat.com/en/documentation/red_hat_enterprise_linux/10/pdf/con...
[2]https://docs.redhat.com/en/documentation/red_hat_enterprise_linux/10/html/configuring_and_using_database_servers/using-mysql#configuring-mysql
[3]https://docs.faveohelpdesk.com/docs/installation/providers/enterprise/redhat...
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/[4]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
<[5]dovecot@dovecot.org> 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 -- [6]dovecot@dovecot.org
 To unsubscribe send an email to [7]dovecot-leave@dovecot.org
References
Visible links

https://docs.redhat.com/en/documentation/red_hat_enterprise_linux/10/pdf/con...
https://docs.redhat.com/en/documentation/red_hat_enterprise_linux/10/html/co...
https://docs.faveohelpdesk.com/docs/installation/providers/enterprise/redhat...
http://vuw.ac.nz/admjonesst1/mariadb-ssl.tar.gz
mailto:dovecot@dovecot.org
mailto:dovecot@dovecot.org
mailto:dovecot-leave@dovecot.org

Re: Can't connect to MySQL without SSL

Thing

Generate CA private key

Generate CA certificate

Client key

Client CSR

Sign it

Generate CA certificate

Client CSR

Sign it