MySQL & SSL

[CJZ]

I am writing an application that deals with my server's database (MySQL). I would like the connection to be encrypted. However, when I enable encryption in the settings, MySQL reports back that the server does not support SSL connections. The connection connects and works when connecting without SSL.

I am running Plesk Panel 10.3.1 and have an SSL certificate installed and set to also be shared. I am connecting to the domain that the certificate is certified for.

How can I setup MySQL to support the connection?

 

[breun]

Installing an SSL certificate in Plesk sets this up for HTTPS access in Apache. Using an SSL certificate for a MySQL server is a totally different thing and is can't be set up via Plesk.

http://dev.mysql.com/doc/refman/5.1/en/secure-connections.html

Are you trying to connect to the MySQL server on localhost?

 

[CJZ]

I'm trying to connect from an external source, the reason why I want SSL. I'm having a C# application connect to the server over Connector/Net plugin from MySQL.
After reading your response, I went on to try and set it up on my own.

mysql> SHOW VARIABLES LIKE 'have_ssl';
+------------------+--------------+
| Variable_name | Value         |
+------------------+--------------+
| have_ssl         | DISABLED   |
+------------------+--------------+

MySQL is set to support SSL.

/etc/my.conf:

[mysqld]
ssl-ca=$DIR/ca-cert.pem
ssl-cert=$DIR/server-cert.pem
ssl-key=$DIR/server-key.pem
[client]
ssl-ca=$DIR/ca-cert.pem
ssl-cert=$DIR/client-cert.pem
ssl-key=$DIR/client-key.pem

With these in the conf file, MySQL fails to start. I followed instructions on creating the certs.

Any suggestions? Thank you.

MySQL: Running 5.5 (latest package from Plesk)

 

[breun]

If MySQL fails to start, I'd check the log file to check for error messages.

 

[CJZ]

111104 11:18:32 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
111104 11:18:32 [Warning] option 'max_connections': unsigned value 18446744073709551615 adjusted to 100000
111104 11:18:32 [Note] Plugin 'FEDERATED' is disabled.
111104 11:18:32 InnoDB: The InnoDB memory heap is disabled
111104 11:18:32 InnoDB: Mutexes and rw_locks use GCC atomic builtins
111104 11:18:32 InnoDB: Compressed tables use zlib 1.2.3
111104 11:18:32 InnoDB: Using Linux native AIO
111104 11:18:32 InnoDB: Initializing buffer pool, size = 128.0M
111104 11:18:32 InnoDB: Completed initialization of buffer pool
InnoDB: Unable to lock ./ibdata1, error: 11
InnoDB: Check that you do not already have another mysqld process
InnoDB: using the same InnoDB data or log files.
111104 11:18:32  InnoDB: Retrying to lock the first data file
InnoDB: Unable to lock ./ibdata1, error: 11
InnoDB: Check that you do not already have another mysqld process
InnoDB: using the same InnoDB data or log files.
InnoDB: Unable to lock ./ibdata1, error: 11
InnoDB: Check that you do not already have another mysqld process
InnoDB: using the same InnoDB data or log files.

This only occurs when I have the ssl variables uncommented.

any suggestions?

 

[breun]

I suggest asking the MySQL community about this, it doesn't really ring any bells with me.

 

[paulieG]

Hi CJZ,

Does it work at all? Even for a very short time? That error usually means exactly what it says, that there are two mysqld processes in existence.

We see it occasionally when we contrive to restart MySQL manually at the same time as Watchdog decides to restart it...

If you have it working for a minute or two then it falls apart then I would suspect that Watchdog is interfering and because of the encrypted connection is deciding that MySQL is down and therefore needs starting, you can test for this by checking the watchdog log file : less /usr/local/psa/var/modules/watchdog/log/monit.log, it will tell you if Watchdog has found and tried to fix problems with MySQL.

A long shot, but it's a possibility,

Paul.