Advanced MySQL security tips (a complete guide)

Guard Duty

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.

Table 1

Keys and Certificates

File Function
ca-key.pem The private key used to generate an X509 certificate for the certificate authority.
ca.pem The X509 certificate containing certificate details and public key.
server-req.pem The server certificate signing request (CSR).
server-key.pem The private server key.
server-cert.pem A self-signed X509 certificate that contains server certificate metadata and the public key.
client-req.pem The client CSR.
client-key.pem The client private key
client-cert.pem A self-signed X509 client certificate.

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.

Buy this article as PDF

Express-Checkout as PDF
Price $2.95
(incl. VAT)

Buy ADMIN Magazine

Get it on Google Play

US / Canada

Get it on Google Play

UK / Australia

Related content

comments powered by Disqus
Subscribe to our ADMIN Newsletters
Subscribe to our Linux Newsletters
Find Linux and Open Source Jobs

Support Our Work

ADMIN content is made possible with support from readers like you. Please consider contributing when you've found an article to be beneficial.

Learn More”>


		<div class=