Mets Minor League Blog

Music theory, instruments and musicians

Mets Minor League Blog

Music theory, instruments and musicians

How to Create a Self-Signed SSL Certificate for MariaDB on Ubuntu 24.04

By metsPosted on Posted in Sport Gear

How to Create a Self-Signed SSL Certificate for MariaDB on Ubuntu 24.04

Implementing SSL encryption for your MariaDB server enhances security by encrypting data transmitted between clients and the server. When setting up MariaDB on Ubuntu 24.04, creating a self-signed SSL certificate is a practical way to enable encrypted connections, especially in development or internal environments where purchasing a certificate isn’t necessary. This guide walks you through the process of generating a self-signed SSL certificate and configuring MariaDB to use it, ensuring your data remains protected during transit.

What You’ll Need

  • Ubuntu 24.04 server with root or sudo privileges
  • MariaDB installed and running
  • OpenSSL installed (usually pre-installed on Ubuntu)
  • Basic knowledge of command-line operations

Step-by-Step

  1. Update your system packages: Ensure your server is up to date. 
    sudo apt update && sudo apt upgrade -y
  2. Install MariaDB if not already installed: (Skip if already installed) 
    sudo apt install mariadb-server -y
  3. Generate a private key and self-signed certificate: Use OpenSSL to create a new key and certificate. 
    sudo mkdir -p /etc/mysql/ssl
sudo openssl req -newkey rsa:2048 -nodes -keyout /etc/mysql/ssl/mariadb.key -x509 -days 3650 -out /etc/mysql/ssl/mariadb.crt

    During the process, you’ll be prompted to enter details such as country, state, organization, etc. Fill these out as needed. Ensure the key and certificate are stored securely.

  4. Secure the private key: Set proper permissions to restrict access. 
    sudo chmod 600 /etc/mysql/ssl/mariadb.key
  5. Configure MariaDB to use SSL: Edit the MariaDB configuration file. 
    sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf

    6. Add or modify the following lines under the [mysqld] section:

    [mysqld]
ssl-ca=/etc/mysql/ssl/mariadb.crt
ssl-cert=/etc/mysql/ssl/mariadb.crt
ssl-key=/etc/mysql/ssl/mariadb.key
  6. Restart MariaDB to apply changes: 
    sudo systemctl restart mariadb
  7. Verify SSL is enabled: Log into MariaDB and check SSL status. 
    mysql -u root -p -e "SHOW VARIABLES LIKE 'have_ssl';"

# Expected output:
# +--------------+--------+
# | Variable_name | Value |
# +--------------+--------+
# | have_ssl | YES |
# +--------------+--------+
  8. Test SSL connection: Connect with SSL to ensure it’s working. 
    mysql -u root -p --ssl-ca=/etc/mysql/ssl/mariadb.crt --ssl-cert=/etc/mysql/ssl/mariadb.crt --ssl-key=/etc/mysql/ssl/mariadb.key

Common Mistakes

  • Not setting correct permissions on the private key, which can prevent MariaDB from reading the SSL files.
  • Using mismatched or improperly configured SSL certificate files, leading to SSL errors.
  • Forgetting to restart MariaDB after editing the configuration file, so changes do not take effect.
  • Not verifying SSL status after setup, leaving connection insecure without realizing it.

Tips

  • Keep your SSL certificate and key files in a secure directory with restricted permissions.
  • Use a strong, unique password for your MariaDB root user and consider creating dedicated SSL users.
  • Regularly renew or replace self-signed certificates before they expire (every 10 years in this case).
  • Test SSL connections from different clients to ensure compatibility.

Troubleshooting

If SSL does not work as expected, follow these steps:

  • Check MariaDB logs: Review /var/log/mysql/error.log for SSL-related errors.
  • Verify file permissions: Ensure only root or MariaDB user can access SSL files. 
    sudo chmod 600 /etc/mysql/ssl/mariadb.key
  • Confirm configuration: Double-check the my.cnf configuration entries for typos or incorrect paths.
  • Test SSL status: Run the SHOW VARIABLES LIKE ‘have_ssl’; query again to confirm.
  • Restart MariaDB: After making changes, restart the service. 
    sudo systemctl restart mariadb
  • Use verbose client options: When connecting, include –ssl and related options to see detailed errors.

Frequently Asked Questions

1. Can I use a self-signed SSL certificate in production?

While self-signed certificates work for encryption, they are not trusted by clients by default. For production, consider obtaining a certificate from a trusted Certificate Authority (CA) to avoid trust issues.

2. How long does a self-signed certificate last?

By default, you can specify the validity period when generating the certificate. In this guide, we set it to 10 years (3650 days), but you can adjust this as needed.

3. Do I need to generate a new SSL certificate for each client?

No, the server’s SSL certificate is used to encrypt data between the server and clients. Clients can use the same CA certificate (or self-signed cert) to verify the server.

4. How do I enforce SSL connections in MariaDB?

You can configure MariaDB user accounts to require SSL by setting the SSL options in user privileges or by configuring the client to use SSL explicitly.

5. Is it necessary to restart MariaDB after configuring SSL?

Yes, changes to the configuration file require a restart of the MariaDB service to take effect.

6. How can I check if a client connection is using SSL?

Run a query like SHOW STATUS LIKE 'Ssl_cipher'; after connecting; if it returns a cipher, SSL is active.

By following these steps, you can secure your MariaDB server with a self-signed SSL certificate on Ubuntu 24.04, ensuring encrypted data transmission and improved security for your database environment.

Related

How to Create a Self-Signed SSL Certificate for MariaDB on Ubuntu 24.04
Scroll to top