Secure MySQL with Self-Signed SSL Certificate on Ubuntu 24.04

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, so you do not have to generate anything yourself. The files live in /var/lib/mysql and end in .pem.

That folder is readable only by the mysql user and root, so a normal user cannot list it. Run the listing command with administrator rights, as shown in the code block below, 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 ls -al /var/lib/mysql/*.pem

Review the generated certificate files from the output below. You should see a CA certificate and key, a server certificate and key, and a client certificate and key. Typical names are ca.pem, server-cert.pem, server-key.pem, client-cert.pem and client-key.pem. If the list is empty, the server has not created them yet. Restart the MySQL service once and list the folder again.

-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

Having the files on disk does not prove MySQL is using them. 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. Enter the password for your MySQL account when asked. When login works, the prompt changes to mysql>.

sudo mysql

Execute the following SQL statement to list the SSL tables and variables. End the statement with a semicolon, or MySQL will wait for more input.

show variables like '%ssl%'; 

Expect output matching the example below. The values to look for are the SSL availability flags, which should read YES, and the certificate and key paths, which should point to the .pem files you just listed. If they are empty or read DISABLED, the server is not using SSL yet.

+-------------------------------------+-----------------+
| 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. Do this now rather than when connections start failing. A certificate that has expired will break clients that verify it.

show status like 'Ssl_server_not%';

Matching output appears below. Read the notAfter date. The certificates MySQL generates automatically are normally valid for years, but the date in your output is the one that counts.

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. If that user tries to connect without encryption, the server refuses the login.

Provisioning a new account named jdoe involves running the SQL statement below with a unique password. Use a strong password, because the one in the example is only a placeholder. You must be logged in to MySQL with an account that is allowed to create users.

CREATE USER jdoe IDENTIFIED BY 'type_your_password_here' require ssl; 

Substitute jdoe with the actual account name. MySQL answers with a short confirmation (a Query OK message) when the account is created.

Auditing database accounts that require SSL demands running the statement below. It shows which accounts are locked to encrypted connections and which are not.

select user,host,ssl_type,plugin from mysql.user;

Expect output resembling the example below. Your new account should show SSL in its requirement column. Accounts that show nothing there can still connect without encryption.

+------------------+-----------+----------+-----------------------+
| 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. Be careful with this on a live server. Any application that still connects to that account without SSL will be refused as soon as the change is made. Test with one account first and confirm the application can connect over SSL before you change the rest. To undo it for an account, change its requirement back to none.

alter user 'root'@'localhost' require ssl;

Connect to MySQL using SSL

Connecting to your database with a MySQL SSL certificate Ubuntu setup means using a network (TCP) connection, which is where encryption applies. Type mysql -u jdoe -p --protocol=tcp and replace jdoe with your actual database username to open the connection. Enter the password when asked.

A local socket connection never goes over the network, so it does not get the same treatment. That is why the command above asks for TCP. A user created with the SSL requirement will connect over TCP only if the client negotiates encryption, which the standard mysql client does when the server offers it.

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. Open the connection's settings, go to its SSL options, and set it to use SSL. If the login fails with an SSL error, check that the account requires SSL and that the option is on in the tool.

Keep in mind that a self-signed certificate encrypts the traffic, but clients cannot check it against a public authority. For stricter checking, copy the server's CA certificate to the client and point the client at it.

Configuration finishes here. MySQL now serves encrypted connections, and the accounts you changed refuse anything else.

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. Check the certificate expiry date from time to time, and test any new application account over SSL before you rely on it.