How to enable SSL on MYSQL?

Verify SSL status:

MASTER >show variables like "%ssl%";
+---------------+-----------------------------------+
| Variable_name | Value                             |
+---------------+-----------------------------------+
| have_openssl  | DISABLED                          |
| have_ssl      | DISABLED                          |
| ssl_ca        |                                   |
| ssl_capath    |                                   |
| ssl_cert      |                                   |
| ssl_cipher    |                                   |
| ssl_key       |                                   |
+---------------+-----------------------------------+

Disabled means --> mysql has ssl support but it's not enabled.
NO means --> don't have ssl support.



Create SSL Certificate:

cd /home/mysql/certs
openssl genrsa -out ca-key.pem 2048;
openssl req -new -x509 -nodes -days 3 -key ca-key.pem -out ca-cert.pem;
openssl req -newkey rsa:2048 -days 3 -nodes -keyout server-key.pem -out server-req.pem;
openssl x509 -req -in server-req.pem -days 3 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 -out server-cert.pem;
openssl req -newkey rsa:2048 -days 3 -nodes -keyout client-key.pem -out client-req.pem;
openssl x509 -req -in client-req.pem -days 3 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 -out client-cert.pem;


Add configuration:

Add below configuration in /etc/my.cnf


[mysqld]

ssl
ssl-cipher=DHE-RSA-AES256-SHA
ssl-ca=/home/mysql/certs/ca-cert.pem
ssl-cert=/home/mysql/certs/server-cert.pem
ssl-key=/home/mysql/certs/server-key.pem


[client]
ssl-ca=/home/mysql/certs/ca-cert.pem
ssl-cert=/home/mysql/certs/client-cert.pem
ssl-key=/home/mysql/certs/client-key.pem



Restart mysql:

sudo /etc/init.d/mysql restart

Test SSL :

MASTER >show variables like "%ssl%";
+---------------+-----------------------------------+
| Variable_name | Value                             |
+---------------+-----------------------------------+
| have_openssl  | YES                               |
| have_ssl      | YES                               |
| ssl_ca        | /home/mysql/certs/ca-cert.pem     |
| ssl_capath    |                                   |
| ssl_cert      | /home/mysql/certs/server-cert.pem |
| ssl_cipher    | DHE-RSA-AES256-SHA                |
| ssl_key       | /home/mysql/certs/server-key.pem  |
+---------------+-----------------------------------+


How to check ssl certificate validation?

$ openssl x509 -noout -in server-cert.pem -dates
notBefore=Mar 12 15:36:27 2013 GMT
notAfter=Mar 15 15:36:27 2013 GMT
 
$ openssl x509 -noout -in client-cert.pem  -dates
notBefore=Mar 12 15:36:52 2013 GMT
notAfter=Mar 15 15:36:52 2013 GMT



ERROR 2026 (HY000): SSL connection error: ASN: after date in the past

ssl certificate might be expired




Enable Auditing Mysql:

MASTER >show variables like '%audit%';
Empty set (0.00 sec)
MASTER >install plugin audit_log soname 'audit_log.so';
Query OK, 0 rows affected (0.23 sec)
MASTER >show variables like '%audit%';
+--------------------------+--------------+
| Variable_name            | Value        |
+--------------------------+--------------+
| audit_log_buffer_size    | 1048576      |
| audit_log_file           | audit.log    |
| audit_log_flush          | OFF          |
| audit_log_policy         | ALL          |
| audit_log_rotate_on_size | 0            |
| audit_log_strategy       | ASYNCHRONOUS |
+--------------------------+--------------+
6 rows in set (0.00 sec)


Mysql Monitor Agent Starting Issue:

Verify Agent Logfile.

2013-07-10 12:55:14: (critical) [unix:/var/lib/mysql/mysql.sock] the hostid from `mysql`.inventory doesn't match our agent's host-id (ssh:{9d:d3:97:9f:1f:3e:cc:c4:cf:a6:63:00:9f:08:2a:04} != ssh:{80:c0:99:c4:fc:6d:d4:eb:ed:70:80:07:cb:df:3b:ec}). Shutting down
2013-07-10 12:55:14: (critical) agent_mysqld.c:1349: agent_mysqld.c:1127: operation canceled as we are shutting down
2013-07-10 12:55:14: (critical) last message repeated 37 times
2013-07-10 12:55:14: (critical) job_collect_mysql.c:428: [unix:/var/lib/mysql/mysql.sock] executing 'SHOW /*!50000 ENGINE */ INNODB STATUS' failed:  (0)
2013-07-10 12:55:14: (critical) agent_mysqld.c:1349: agent_mysqld.c:1127: operation canceled as we are shutting down

Workaround :

Truncate truncate mysql.inventory table then start agent