Comments on: Setting up MySQL SSL and Secure Connections

By: Tim Child

Tim Child — Mon, 03 Dec 2018 19:43:54 +0000

The url for example 1
1. Generate SSL certificates according to the example 1. Use the different Common Name for server and client certificates.
is not https://dev.mysql.com/doc/refman/8.0/en/creating-ssl-files-using-openssl.html

By: Dominic

Dominic — Sun, 24 Sep 2017 16:05:00 +0000

This is just pure gold. No bullshit, everything you need.

By: tv

tv — Wed, 29 Mar 2017 23:01:02 +0000

It is possible to have differents certificates for each users (and also prevent a user to use someone else cert) ?
Thanks

By: Matthew Schumacher

Matthew Schumacher — Fri, 27 Jan 2017 16:59:21 +0000

on the [root@centos6 ~]# mysql -u ssluser -p -sss -e ‘\s’ | grep SSL
SSL: Cipher in use is DHE-RSA-AES256-SHA

is the -sss supposed to be -ssl ?

By: Anatoli

Anatoli — Mon, 12 Dec 2016 03:18:27 +0000

If you tried everything, but SSL is not working, and at the same time you’re running mysqld in chroot, then the cause for the errors like:

ERROR 2026 (HY000): SSL connection error: error:00000001:lib(0):func(0):reason(1)

ERROR 2026 (HY000): SSL connection error: protocol version mismatch

could be that you forgot to create dev/random and dev/urandom devices in the chroot environment (and openssl lib can’t obtain entropy – it opens these devices *after* chroot). You can do that this way (replace /srv/mysqld with your chroot dir and mysqld with the user mysqld is running under):

sudo install -d -o mysqld -g mysqld -m 500 /srv/mysqld/dev
sudo mknod -m 444 /srv/mysqld/dev/random c 1 8
sudo mknod -m 444 /srv/mysqld/dev/urandom c 1 9

By: Shane Bishop

Shane Bishop — Sat, 10 Sep 2016 19:25:44 +0000

In reply to sweetfa. A bit of an old post, but you would remove the passphrase like so: openssl rsa -in server-key.pem -out server-key.pem There is no way to have mysql enter a passphrase for a key (that I'm aware of), when running mysql as a service (and who runs it any other way?).

By: Bob McRae

Bob McRae — Wed, 27 Jul 2016 15:12:30 +0000

I am able to connect via SSL using mysql and the custom perconi.ini (Windows). However, I keep getting access denied using the same host, user, password, and .ini file. Any other things to check?

By: sweetfa

sweetfa — Tue, 10 May 2016 08:03:05 +0000

If your server key has a passphrase, how do you configure that, particularly when running mysql as a service

By: fred mcintyre

fred mcintyre — Mon, 05 Oct 2015 00:36:27 +0000

Thanks for the good info. One problem I had that took a while to figure out: In Example 1 (http://dev.mysql.com/doc/refman/5.5/en/creating-ssl-files-using-openssl.html) it uses ca.pem and your instructions use ca-cert.pem. I happily copied/pasted without noticing.

By: Shaun McPeck

Shaun McPeck — Sun, 04 Oct 2015 14:37:17 +0000

I lost a lot of time during my setup because of a crucial setting missing from the my.cnf file. The final winning move for me was:

ssl-cipher=AES256-SHA:DHE-DSS-AES256-SHA:DHE-RSA-AES256-SHA

By: Roman Vynar

Roman Vynar — Thu, 09 Jul 2015 20:36:39 +0000

Looks like it’s not possible. https://bugs.mysql.com/bug.php?id=23206

By: Emilio Macias

Emilio Macias — Thu, 09 Jul 2015 15:00:37 +0000

I would like to use it for federated tables. How i can do it.

By: Ajay Singh

Ajay Singh — Wed, 25 Mar 2015 03:23:38 +0000

i need to complete the project and topic is : ” How to secure a MySQL or MS SQL connected website ”
So can anybody help me like what should i need to write into this ?

By: Fabrizio Bartolomucci

Fabrizio Bartolomucci — Tue, 25 Nov 2014 21:18:41 +0000

In the process of moving a web server to another domain, I would like to grant the access to the files in the old location to the mysql DB on the new site so to minimize differences between them. Yet the latter app presently only allows access by ssl and I would need to both configure QuincyKit and the normal php scripts to accept that. Easier would be to temporarily also allow the regular sql access. How do I do it?

By: CHRIS

CHRIS — Wed, 10 Sep 2014 22:55:30 +0000

I believe in step #3, “CHANGE MASTER MASTER_SSL=1” should read “CHANGE MASTER TO MASTER_SSL=1”

By: Roman Vynar

Roman Vynar — Fri, 04 Jul 2014 09:32:55 +0000

Thanks QS. I had to put html backslash code \ instead.

By: QS

QS — Thu, 03 Jul 2014 17:26:02 +0000

Looks like your escape character got lost in translation:

[root@centos6 ~]# mysql -u ssluser -p -sss -e ‘s’ | grep SSL

should be

[root@centos6 ~]# mysql -u ssluser -p -sss -e ‘\s’ | grep SSL

By: Roman Vynar

Roman Vynar — Wed, 19 Feb 2014 14:35:57 +0000

Reinard, Miklos, thanks, I have made your notes.

By: Miklos Szel

Miklos Szel — Tue, 18 Feb 2014 16:00:50 +0000

Hi Roman,

First of all, good post, thanks!

I know that this is not the point of this whole article but maybe it would be better to change the:
GRANT USAGE ON *.* to ‘repl’@’%’ REQUIRE SSL;
to
GRANT REPLICATION SLAVE ON *.* to ‘repl’@’%’ REQUIRE SSL;
as we speak about a replication and this could be a bit confusing.

By: Reinard Dolleschel

Reinard Dolleschel — Tue, 28 Jan 2014 22:50:50 +0000

Thanks for the post. One note though: I think you should discourage people from using the -p[pass] option on the command line. It shows up in the history and in the process list with the password exposed in plain text and is a horrible idea from a security standpoint. Especially when you’re just testing, just use -p and wait for it to prompt you for the password.