Re: Yet another SSL-does-not-work-with-MySQL user...
Manuel:
I don't know if you've already solved your problem, since it's been a couple of weeks, but I thought I would reply, since I just got it working myself after a lot of grief.
I'm certainly no expert, and I pretty much stumbled on a solution.
First, since I don't understand SSL in any deep way (and don't want to), let me tell you that I used the certificates, keys, etc. from the mysql-test/std-data subdirectory of a MySQL source tree. In that directory, they have certs and keys for the server side and a separate set for the client side. I simply assumed that it needed to be this way and went on from there.
In the [mysqld] section of my.ini (I was testing this on Windows; haven't tried it on Linux yet), I added these lines:
ssl
ssl-ca="G:/SSL/cacert.pem"
ssl-cert="G:/SSL/server-cert.pem"
ssl-key="G:/SSL/server-key.pem"
Then, in the [client] section of my.ini, I added these lines:
ssl-ca="G:/SSL/cacert.pem"
ssl-cert="G:/SSL/client-cert.pem"
ssl-key="G:/SSL/client-key.pem"
At the risk of belaboring the obvious, note that I used the MySQL-supplied files named "client-*" on the client side, and the files named "server-*" on the server side. It might work with the same files on both sides; I haven't tested that yet.
Note also that the "ssl" variable in the [mysqld] section has no value. I've also seen it specified like this: ssl=1. All I know is that it worked for me this way.
Anyway, that's all it takes to enable SSL connections (which you can check by querying the "have-ssl" system variable), but it does not *require* SSL connections. To do that, I created a user "fred" with the REQUIRE SSL option as follows:
* grant all privileges on *.* to 'fred'@'localhost' identified by 'schwartz' with grant option;
* grant all privileges on *.* to 'fred'@'%' identified by 'schwartz' with grant option;
* grant all privileges on *.* to 'fred'@'192.168.100.9' identified by 'schwartz' with grant option;
That last grant (to the MySQL server IP) shouldn't be necessary, since you already did one with tht '%', but I've had troubles when I didn't do that.
Following that, I was able to connect with these variants:
* mysql -ufred -pschwartz
* mysql -ufred -pschwartz --ssl
* mysql -ufred -pschwartz --ssl --ssl-ca=G:/SSL/cacert.pem --ssl-cert=G:/SSL/client-cert.pem --ssl-key=G:/SSL/client-key.pem --ssl-verify-server-cert
The first two variants work ONLY because I specified the certs and keys to be used in the [client] section of my.ini. To test that, I commented out the SSL-related lines in that section, and then only the last variant worked.
Hope this helps,
-- Charlie