Secure MySQL with Self-Signed SSL Certificate on Ubuntu 24.04
SSL (Secure Sockets Layer, a tool that scrambles data to keep it private) encryption for MySQL on Ubuntu 24.04 stops people from reading your database traffic while it moves across a network. A self-signed SSL certificate acts as a digital lock for your server by creating a secure tunnel for all queries and data.
MySQL 8.0 and newer versions route connections through these encrypted keys once you set up a Certificate Authority (a trusted entity that signs digital certificates) to sign them. You can secure your database connections in a few steps without needing a paid certificate from an outside authority.
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
Setting up a secure MySQL SSL certificate Ubuntu deployment starts by checking your default database folder for existing key and certificate files. Ubuntu 24.04 creates these files automatically when you install the database server. Run sudo bash ls -al /var/lib/mysql/*.pem in your terminal to view the generated files and confirm they are ready for use.
Review the existing certificate files using the command below after getting MySQL installed.
sudo bash
ls -al /var/lib/mysql/*.pem
Review the generated certificate files from the output below.
-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
Validating whether the database allows SSL connections requires executing a specific SQL query. First, authenticate with the database server.
Log on to the MySQL database.
sudo mysql
Execute the following SQL statement to list the SSL tables.
show variables like '%ssl%';
Expect output matching the example 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)
Checking certificate expiration dates involves running the command below.
show status like 'Ssl_server_not%';
Matching output appears below.
Creating a new MySQL user, such as 'jdoe', requires enforcing SSL by default. Executing `CREATE USER jdoe IDENTIFIED BY 'type_your_password_here' require ssl;` forces the account to use encrypted links for every session, securing data transfer from the start.
Provisioning a new account named jdoe involves running the SQL statement below with a unique password.
CREATE USER jdoe IDENTIFIED BY 'type_your_password_here' require ssl;
Substitute jdoe with the actual account name.
Auditing database accounts that require SSL demands running the statement below.
select user,host,ssl_type,plugin from mysql.user;
Expect output resembling the example 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 |
+------------------+-----------+----------+-----------------------+
Enforcing SSL requirements on legacy database accounts takes a single SQL execution.
alter user 'root'@'localhost' require ssl;
Connect to MySQL using SSL
Connecting to your database with a MySQL SSL certificate Ubuntu setup requires passing specific flags in your terminal command to force encryption. Type mysql -u jdoe -p --protocol=tcp and replace jdoe with your actual database username to open a secure connection. If you use graphical database tools, you must also turn on the SSL option in their connection settings.
Successful connections rely on enabling SSL within database administration software. MySQL Workbench version 8.0, for instance, requires turning on this setting to communicate with a server using a self-signed certificate.
Configuration finishes here.
Key Takeaways:
Deploying a self-signed SSL certificate with MySQL on Ubuntu 24.04 substantially improves database connection security. Review these core concepts:
- 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.
Applying these practices builds a protected environment for managing 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 […]