MySQL SSL disabled on Windows

Question

I want to enable an SSL connection to my database. I've created SSL certificates and so on, however SSL remains disabled:

mysql> show variables like '%ssl%';
+---------------+------------------------------------+
| Variable_name | Value                              |
+---------------+------------------------------------+
| have_openssl  | DISABLED                           |
| have_ssl      | DISABLED                           |
| ssl_ca        | C:/Users/Krzysztof/ca-cert.pem     |
| ssl_capath    |                                    |
| ssl_cert      | C:/Users/Krzysztof/server-cert.pem |
| ssl_cipher    |                                    |
| ssl_key       | C:/Users/Krzysztof/server-key.pem  |
+---------------+------------------------------------+

What am I missing?

I'm using Windows 7.

Answer 1

You need to check to make sure the user indeed has the ssl field loaded.

Run this query;

SELECT A.* FROM
(SELECT user,host FROM mysql.user WHERE ssl_type IS NOT NULL) A
INNER JOIN
(SELECT user,host FROM mysql.user WHERE ssl_cipher IS NOT NULL) B
USING (user,host);

If nothing comes back, then look the user using

SELECT * FROM
(SELECT user,host FROM mysql.user WHERE ssl_type IS NOT NULL) A
UNION
(SELECT user,host FROM mysql.user WHERE ssl_cipher IS NOT NULL);

If nothing comes back from this second query, that means no user in mysql.user has ssl_type and ssl_cipher filled in properly.

Another thing you can investigate is this:

Did you add this to my.ini ?

[mysql]
ssl

If you didn't, please do so and run this

C:\> net stop mysql
C:\> net start mysql

Answer 2

Did you start your mysql server with the --ssl option?

See the documentation of mysql for more info.

Answer 3

Thanx @RolandoMySQLDBA and @dgw for help! ;) in meantime i created certificates again, validated them, then i re-installed all MySQL products on computer, added new user with this:

GRANT ALL PRIVILEGES ON *.* TO 'ssluser'@'localhost' IDENTIFIED BY 'ssluser' REQUIRE SSL;

and after loging like this:

mysql -ussluser -pssluser -P3306 --ssl-key=

it works :D. But i got another problem - how to connect to MySQL using SSL, via servlet? Without SSL code that worked looked like this:

package test;

import java.io.*;
import java.sql.*;
import javax.servlet.*;
import javax.servlet.http.*;
public class Registration extends HttpServlet{
/**
     * 
     */
    private static final long serialVersionUID = 1L;
public void init(ServletConfig config) throws ServletException{
super.init(config);
}
public void doPost(HttpServletRequest req, HttpServletResponse res) 
throws ServletException, IOException{
String connectionURL = "jdbc:mysql://localhost:3306/jsp";
Connection connection=null;
ResultSet rs;
res.setContentType("text/html");
PrintWriter out = res.getWriter();
String firstNamed = req.getParameter("firstName");
String lastNamed = req.getParameter("lastName");
String emaild = req.getParameter("email");
String userNamed = req.getParameter("userName");
String password1d = req.getParameter("password1");
String zipd = req.getParameter("zip");
String auto = "user";
try {
Class.forName("org.gjt.mm.mysql.Driver");
connection = DriverManager.getConnection(connectionURL, "root", "root"); 
String sql = "insert into userprofile values (?,?,?,?,?,?,?)";
PreparedStatement pst = connection.prepareStatement(sql);
pst.setString(1, firstNamed);
pst.setString(2, lastNamed);
pst.setString(3, emaild);
pst.setString(4,userNamed);
pst.setString(5, password1d);
pst.setString(6, zipd);
pst.setString(7, auto);

int numRowsChanged = pst.executeUpdate();
out.println(" Welcome : ");
out.println(" '"+userNamed+"'");
pst.close();
}
catch(ClassNotFoundException e){
out.println("Couldn't load database driver: " + e.getMessage());
}
catch(SQLException e){
out.println("SQLException caught: " + e.getMessage());
}
catch (Exception e){
out.println(e);
}
finally {

try {
if (connection != null) connection.close();
}
catch (SQLException ignored){
out.println(ignored);
}
}
}
}

just, how correct it? i quess that i need to modify connectionURL somehow but i dont know how :(