Secure MySQL with Self-Signed SSL Certificate on Ubuntu 24.04
You secure MySQL with a self-signed SSL certificate on Ubuntu 24.04 by generating and configuring certificate files to enable encrypted connections.
A self-signed SSL certificate encrypts all data transferred between your MySQL server and client applications, actively preventing unauthorized access like eavesdropping or man-in-the-middle attacks.
This guide shows you how to create your own Certificate Authority (CA) and use it to sign your server’s SSL certificate. You will then configure your MySQL 8.0 or later installation to require these encrypted connections, significantly boosting your database’s security.
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
When you install the MySQL database server, it automatically creates and configures SSL settings in the database. Users are not required to use SSL to connect.
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.
+-----------------------+--------------------------+
| Variable_name | Value |
+-----------------------+--------------------------+
| Ssl_server_not_after | Feb 19 17:20:43 2035 GMT |
| Ssl_server_not_before | Feb 21 17:20:43 2025 GMT |
+-----------------------+--------------------------+
Force users to connect with SSL
Now that you know SSL is configured, you can force users to always use it when connecting to the database.
To create a new user, like one named jdoe, run the SQL statement below and enter a new password.
CREATE USER jdoe IDENTIFIED BY 'type_your_password_here' require ssl;
Replace jdoe with the name of the account you want to create.
By running the statement below, you can validate all the 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 |
+------------------+-----------+----------+-----------------------+
Execute the SQL statement below to force existing database accounts to use SSL.
alter user 'root'@'localhost' require ssl;
Connect to MySQL using SSL
Now that users must use SSL to connect to MySQL, they must run the command below to access the MySQL database from the local host.
mysql -u jdoe -p --protocol=tcp
If they’re using a database tool, they must enable SSL for the connection to succeed.
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 […]