Secure MySQL with Self-Signed SSL Certificate on Ubuntu 24.04
Securing MySQL with a self-signed SSL certificate on Ubuntu 24.04 scrambles the data that travels between your database and apps so nobody can steal it. A self-signed SSL certificate acts as a digital lock for your server.
You create a digital notary called a Certificate Authority (CA) to sign your keys and build trust. MySQL 8.0 or newer then routes your database traffic over these safe, encrypted connections.
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
Ubuntu 24.04 stores the necessary MySQL self-signed certificate files inside the default database directory. Running `sudo bash ls -al /var/lib/mysql/*.pem` checks for existing certificate files on the system.
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 via SSL on Ubuntu takes only a moment once user restrictions are in place. Run `mysql -u jdoe -p --protocol=tcp` to force an encrypted link, replacing 'jdoe' with the actual username. External database management tools require enabling SSL inside their specific connection preferences.
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 […]