Server Side configuration
- Check if the MySQL Supports encryption already:
mysql> show global variables like ‘%ssl%’;
+—————+———-+
| Variable_name | Value |
+—————+———-+
| have_openssl | DISABLED |
| have_ssl | DISABLED |
| ssl_ca | |
| ssl_capath | |
| ssl_cert | |
| ssl_cipher | |
| ssl_crl | |
| ssl_crlpath | |
| ssl_key | |
+—————+———-+
9 rows in set (0.00 sec)
- Create the SSL certificates and keys(Fill the details when prompted). Create in the data directory, preferably :
# Create CA certificate
openssl genrsa 2048 > ca-key.pem
openssl req -new -x509 -nodes -days 3600 -key ca-key.pem -out ca.pem
# Create server certificate, remove passphrase, and sign it
# server-cert.pem = public key, server-key.pem = private key
openssl req -newkey rsa:2048 -days 3600 \
-nodes -keyout server-key.pem -out server-req.pem
openssl rsa -in server-key.pem -out server-key.pem
openssl x509 -req -in server-req.pem -days 3600 \
-CA ca.pem -CAkey ca-key.pem -set_serial 01 -out server-cert.pem
# Create client certificate, remove passphrase, and sign it
# client-cert.pem = public key, client-key.pem = private key
openssl req -newkey rsa:2048 -days 3600 \
-nodes -keyout client-key.pem -out client-req.pem
openssl rsa -in client-key.pem -out client-key.pem
openssl x509 -req -in client-req.pem -days 3600 \
-CA ca.pem -CAkey ca-key.pem -set_serial 01 -out client-cert.pem
- Add the below lines to the config file – my.cnf and restart mysql :
[mysqld]
ssl-ca=ca.pem
ssl-cert=server-cert.pem
ssl-key=server-key.pem
- Check now whether mysql is ssl enabled:
mysql> show global variables like ‘%ssl%’;
+—————+—————–+
| Variable_name | Value |
+—————+—————–+
| have_openssl | YES |
| have_ssl | YES |
| ssl_ca | ca.pem |
| ssl_capath | |
| ssl_cert | server-cert.pem |
| ssl_cipher | |
| ssl_crl | |
| ssl_crlpath | |
| ssl_key | server-key.pem |
+—————+—————–+
9 rows in set (0.00 sec)
Client configuration
- Connect to mysql normally and check[Over TCP/IP]:
If enabled at server side, clients by default make use of SSL Connections. However, un-encrypted connections too possible by mentioning –ssl=0 while connecting.
- So, to mandate SSL usage, create user with REQUIRE SSL option:
mysql> grant all on *.* to require_ssl_user identified by ‘****’ REQUIRE SSL;
Query OK, 0 rows affected, 1 warning (0.00 sec)
- To make use of SSL, connect mentioning the ssl certificate path:
# mysql –ssl-ca=/var/lib/mysql/ca.pem
mysql> \s
————–
mysql Ver 14.14 Distrib 5.7.22, for Linux (x86_64) using EditLine wrapper
Connection id: 4
Current database:
Current user: root@localhost
SSL: Cipher in use is DHE-RSA-AES256-SHA
- To make the client to supply the certificate and key files, create user with REQUIRE X509 option:
mysql> grant all on *.* to requirex509_ssl_user identified by ‘****’ REQUIRE X509;
Query OK, 0 rows affected, 1 warning (0.00 sec)
- For users with REQUIRE X509 to connect , client key and certificate to be supplied:
# mysql -ussl_user_x509 -p’Root123#’ –ssl-ca=/var/lib/mysql/ca.pem –ssl-cert=/var/lib/mysql/client-cert.pem –ssl-key=/var/lib/mysql/client-key.pem
mysql> \s
————–
mysql Ver 14.14 Distrib 5.7.22, for Linux (x86_64) using EditLine wrapper
Connection id: 8
Current database:
Current user: ssl_user_x509@localhost
SSL: Cipher in use is DHE-RSA-AES256-SHA
- Copy the cert files to client server to enable encryption.
Databasically.. 