How to Secure MySQL/MariaDB with SSL Encryption

Securing MySQL or MariaDB with SSL encryption ensures data privacy by encrypting the communication between the database server and its clients. Here’s a guide to setting up SSL encryption for MySQL or MariaDB on Linux:

Step 1: Generate SSL Certificates #

1. Create a Directory for SSL Certificates:

sudo mkdir /etc/mysql/ssl
cd /etc/mysql/ssl

2. Generate the CA (Certificate Authority) Certificate:

openssl genpkey -algorithm RSA -out ca-key.pem
openssl req -new -x509 -key ca-key.pem -out ca-cert.pem -days 3650 \
-subj "/C=US/ST=State/L=City/O=Organization/OU=Department/CN=CA"

3. Generate the Server Certificate and Key:

openssl genpkey -algorithm RSA -out server-key.pem
openssl req -new -key server-key.pem -out server-req.pem \
  -subj "/C=US/ST=State/L=City/O=Organization/OU=Department/CN=Server"
openssl x509 -req -in server-req.pem -days 3650 -CA ca-cert.pem \
  -CAkey ca-key.pem -set_serial 01 -out server-cert.pem

4. Generate the Client Certificate and Key:

openssl genpkey -algorithm RSA -out client-key.pem
openssl req -new -key client-key.pem -out client-req.pem \
-subj "/C=US/ST=State/L=City/O=Organization/OU=Department/CN=Client"
openssl x509 -req -in client-req.pem -days 3650 -CA ca-cert.pem \
-CAkey ca-key.pem -set_serial 01 -out client-cert.pem

5. Set Permissions on the Key Files:

sudo chmod 600 *.pem

Step 2: Configure MySQL or MariaDB to Use SSL #

1. Locate the MySQL or MariaDB Configuration File:
   • Typically, this is located at /etc/mysql/my.cnf or /etc/my.cnf.
2. Add SSL Configuration to the [mysqld] Section:

[mysqld]
ssl-ca=/etc/mysql/ssl/ca-cert.pem
ssl-cert=/etc/mysql/ssl/server-cert.pem
ssl-key=/etc/mysql/ssl/server-key.pem

3. Restart MySQL or MariaDB:

sudo systemctl restart mysql

4. Verify SSL Configuration: Log in to the MySQL/MariaDB server and confirm SSL is enabled:

SHOW VARIABLES LIKE '%ssl%';

Check if have_ssl and have_openssl are both set to YES.

Step 3: Configure MySQL or MariaDB Users to Require SSL #

1. Enable SSL for a Specific User:

CREATE USER 'secure_user'@'%' IDENTIFIED BY 'secure_password';
GRANT ALL PRIVILEGES ON database_name.* TO 'secure_user'@'%' REQUIRE SSL;

2. Verify User SSL Requirement:

SHOW GRANTS FOR 'secure_user'@'%';

It should show REQUIRE SSL in the privileges.

Step 4: Configure MySQL Clients to Connect Using SSL #

1. Verify Client SSL Connection:
   • Run the following command from a client machine using the SSL certificates created earlier:

mysql -u secure_user -p --ssl-ca=/etc/mysql/ssl/ca-cert.pem \
--ssl-cert=/etc/mysql/ssl/client-cert.pem \
--ssl-key=/etc/mysql/ssl/client-key.pem

2. Confirm SSL Connection:

• Once logged in, verify the connection is encrypted:

SHOW SESSION STATUS LIKE 'Ssl_cipher';

• If the Ssl_cipher variable shows a cipher, the connection is encrypted with SSL.

Additional Tips #

• Enforce SSL: To ensure all connections use SSL, set require_secure_transport=ON in your MySQL configuration.
• Automate Certificate Renewal: Use tools like certbot to automate certificate renewal if using publicly trusted certificates.
• Use Strong Ciphers: Set strong SSL/TLS ciphers by adding tls_version and ssl_cipher options in your configuration file.

By following these steps, you can secure your MySQL or MariaDB server’s communication with clients, providing encryption to protect sensitive data.