Intercepting the Plaintext Table

To create a client that can use its credentials to log in remotely to the MySQL server and access tables, enter:

$ mysql -u user -p -h <SSLsrvr_IPaddress>

In the meantime, initiate a tshark session at your server or client to sniff the plaintext data:

$ tshark -i any > mysql_plaintext.pcap

Hitting Ctrl+C stops the capture process and opens the mysql_plaintext.pcap file in Wireshark to retrieve the plaintext (Figure 1).

Figure 1: PCAP file in Wireshark.

Configuring the MySQL Server

To enable SSL support, you need to create the required SSL files and keys manually. MySQL requires private keys and X509 certificates signed by a certificate authority (CA) to ensure secure encrypted communication. Similarly, the MySQL server needs private keys and X509 certificates from every client that wants to connect to the server over SSL. Table 1 lists the files you need to prepare manually.

Creating SSL Files with OpenSSL

The OpenSSL command-line tool will help you prepare and generate the required SSL files. This handy tool uses the OpenSSL library to perform various tasks, like generating X509 request certificates, providing private keys, verifying and signing X509 certificates, and so on.

Before generating SSL files, create a directory in which to place keys and certificates for encryption in transit:

$ mkdir /var/lib/mysql/new_certs && cd /var/lib/mysql/new_certs

Next, generate the RSA 2048-bit private key to create the CA X509 certificate that signs the server and client X509 certificates:

$ openssl genrsa 2048 > ca-key.pem
$ openssl req -new -x509 -nodes -days 3500 -key ca-key.pem -out ca.pem

The openssl command generates the server's private key and certificate signing request. Once obtained, you need to remove the passphrase and sign server-req.pem with the CA key and certificate to obtain the final X509 certificate for the server:

$ openssl genrsa 2048 > server-key.pem
$ openssl req -new -key server-key.pem -out server-req.pem
$ openssl x509 -req -in server-req.pem -days 3600 -CA ca.pem -CAkey ca-key.pem -set_serial 01 -out server-cert.pem

MySQL configuration for SSL only requires server-key.pem, server-cert.pem, and the CA certificate.

Similarly, you need to generate the private key (cert-key.pem) and a self-signed X509 certificate for the MySQL client:

$ openssl genrsa 2048 > client-key.pem
$ openssl req -new -key client-key.pem -out client-req.pem
$ openssl x509 -req -in client-req.pem -days 3600 -CA ca.pem -CAkey ca-key.pem -set_serial 01 -out client-cert.pem

The openssl verify command lets you verify that OpenSSL has generated the correct certificates:

$ openssl verify -CAfile ca.pem server-cert.pem client-cert.pem
server-cert.pem: OK
client-cert.pem: OK

The OK value indicates that the X509 certificate was signed correctly.