Secure MySQL with Self-Signed SSL Certificate on Ubuntu 24.04
Securing MySQL with a self-signed SSL certificate on Ubuntu 24.04 creates encrypted connections for your database.
A self-signed SSL certificate is like a digital lock that scrambles data so only you and your database can read it. This stops others from seeing or changing information as it travels between your server and applications.
This process involves creating your own trusted certificate issuer, called a Certificate Authority (CA). You then use this CA to sign your server’s certificate. Finally, you set up MySQL 8.0 or newer to use these encrypted connections, making your database much safer.
Generate self-signed SSL certificate files and configure MySQL to use them. Check existing certificates with `sudo bash ls -al /var/lib/mysql/*.pem` and verify SSL status by logging into MySQL and running `show variables like ‘📂%ssl%’;`.
Configure MySQL SSL connection
Your MySQL setup on Ubuntu 24.04 can use SSL, and the needed self-signed certificate files are in the MySQL data directory. To check if you have the necessary MySQL SSL certificate files for your Ubuntu setup, run the command `sudo bash ls -al /var/lib/mysql/*.pem`.
Run the command below to list MySQL self-signed SSL certificate files with MySQL installed.
sudo bash
ls -al /var/lib/mysql/*.pem
Here’s a list of MySQL certificate files.
-rw------- 1 mysql mysql 1705 Feb 21 11:20 /var/lib/mysql/ca-key.pem
-rw-r--r-- 1 mysql mysql 1112 Feb 21 11:20 /var/lib/mysql/ca.pem
-rw-r--r-- 1 mysql mysql 1112 Feb 21 11:20 /var/lib/mysql/client-cert.pem
-rw------- 1 mysql mysql 1705 Feb 21 11:20 /var/lib/mysql/client-key.pem
-rw------- 1 mysql mysql 1705 Feb 21 11:20 /var/lib/mysql/private_key.pem
-rw-r--r-- 1 mysql mysql 452 Feb 21 11:20 /var/lib/mysql/public_key.pem
-rw-r--r-- 1 mysql mysql 1112 Feb 21 11:20 /var/lib/mysql/server-cert.pem
-rw------- 1 mysql mysql 1705 Feb 21 11:20 /var/lib/mysql/server-key.pem
MySQL database is also configured to allow SSL connection. You can validate that by running the SQL statement below.
First, log on to the MySQL database.
sudo mysql
Then, run the SQL statement to list the SSL tables.
show variables like '%ssl%';
The result should be similar to the one below.
+-------------------------------------+-----------------+
| Variable_name | Value |
+-------------------------------------+-----------------+
| admin_ssl_ca | |
| admin_ssl_capath | |
| admin_ssl_cert | |
| admin_ssl_cipher | |
| admin_ssl_crl | |
| admin_ssl_crlpath | |
| admin_ssl_key | |
| have_openssl | YES |
| have_ssl | YES |
| mysqlx_ssl_ca | |
| mysqlx_ssl_capath | |
| mysqlx_ssl_cert | |
| mysqlx_ssl_cipher | |
| mysqlx_ssl_crl | |
| mysqlx_ssl_crlpath | |
| mysqlx_ssl_key | |
| performance_schema_show_processlist | OFF |
| ssl_ca | ca.pem |
| ssl_capath | |
| ssl_cert | server-cert.pem |
| ssl_cipher | |
| ssl_crl | |
| ssl_crlpath | |
| ssl_fips_mode | OFF |
| ssl_key | server-key.pem |
| ssl_session_cache_mode | ON |
| ssl_session_cache_timeout | 300 |
+-------------------------------------+-----------------+
27 rows in set (0.00 sec)
You can also see how long the certificates are valid by running the command below.
show status like 'Ssl_server_not%';
It should output similar lines as below.
MySQL user creation on Ubuntu requires SSL by default. When you create a new MySQL user, like ‘jdoe’, use the SQL command `CREATE USER jdoe IDENTIFIED BY ‘type_your_password_here’ require ssl;`. This command forces the ‘jdoe’ user to use SSL for all connections, securing data transfer from the start.
CREATE USER jdoe IDENTIFIED BY 'type_your_password_here' require ssl;
Replace jdoe with the name of the account you want to create.
Running the statement below validates all database accounts that must use SSL when connecting.
select user,host,ssl_type,plugin from mysql.user;
Your output should look similar to the one below.
+------------------+-----------+----------+-----------------------+
| user | host | ssl_type | plugin |
+------------------+-----------+----------+-----------------------+
| jdoe | % | ANY | caching_sha2_password |
| debian-sys-maint | localhost | | caching_sha2_password |
| mysql.infoschema | localhost | | caching_sha2_password |
| mysql.session | localhost | | caching_sha2_password |
| mysql.sys | localhost | | caching_sha2_password |
| root | localhost | | auth_socket |
+------------------+-----------+----------+-----------------------+
alter user 'root'@'localhost' require ssl;
Connect to MySQL using SSL
Connecting to your MySQL database using SSL on Ubuntu is quite manageable, particularly after setting up a user that requires it. To ensure your connection is secure, use the command `mysql -u jdoe -p –protocol=tcp`, replacing ‘jdoe’ with your username. For database tools, remember to enable SSL in their connection settings.
Database tools require SSL to be enabled for connections to succeed. For example, MySQL Workbench version 8.0 needs this setting turned on to connect to a MySQL server using a self-signed SSL certificate.
That should do it!
Key Takeaways:
Using a self-signed SSL certificate with MySQL on Ubuntu 24.04 significantly enhances the security of your database connections. Here are the key takeaways:
- Improved Security: Self-signed SSL certificates encrypt data in transit, protecting sensitive information from potential interception.
- Easy Configuration: MySQL automatically configures SSL settings upon installation, simplifying the process of enabling secure connections.
- User Restrictions: You can enforce that all users connect securely using SSL, minimizing vulnerability.
- Access Validation: Commands to check SSL configurations to ensure the setup is correct and functional.
- Database Tool Integration: Ensure your database tools are set to use SSL for successful connections.
Following these guidelines can create a more secure environment for managing your MySQL databases.
Was this guide helpful?
About the Author
Richard
Tech Writer, IT Professional
Richard, a writer for Geek Rewind, is a tech enthusiast who loves breaking down complex IT topics into simple, easy-to-understand ideas. With years of hands-on experience in system administration and enterprise IT operations, he’s developed a knack for offering practical tips and solutions. Richard aims to make technology more accessible and actionable. He's deeply committed to the Geek Rewind community, always ready to answer questions and engage in discussions.
[…] MySQL, when you install MariaDB on Ubuntu, it doesn’t automatically create a self-signed […]