{"id":67111,"date":"2026-03-19T13:34:54","date_gmt":"2026-03-19T10:34:54","guid":{"rendered":"https:\/\/cloudspinx.com\/?p=67111"},"modified":"2026-03-24T11:57:55","modified_gmt":"2026-03-24T08:57:55","slug":"secure-mysql-tls-ssl-certificates","status":"publish","type":"post","link":"https:\/\/computingforgeeks.com\/secure-mysql-tls-ssl-certificates\/","title":{"rendered":"Secure MySQL 8.4 LTS with TLS\/SSL Certificates on Ubuntu 24.04 \/ Rocky Linux 10"},"content":{"rendered":"\n<p>MySQL 8.4 LTS supports TLS\/SSL encryption for all client-server communication, protecting data in transit from eavesdropping and man-in-the-middle attacks. By default, MySQL allows unencrypted local connections, but any production deployment accepting remote connections must enforce TLS encryption with properly signed certificates.<\/p>\n\n\n\n<p>This guide walks through generating a custom Certificate Authority (CA), server certificates, and client certificates with OpenSSL, then configuring MySQL 8.4 LTS to require TLS on both Ubuntu 24.04 and Rocky Linux 10. We also cover per-user TLS enforcement, client-side configuration, connection verification, and certificate rotation procedures.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Prerequisites<\/h2>\n\n\n\n<ul class=\"wp-block-list\">\n<li>A server running Ubuntu 24.04 LTS or Rocky Linux 10 with root or sudo access<\/li>\n\n\n\n<li>MySQL 8.4 LTS installed and running &#8211; see our guides for <a href=\"https:\/\/computingforgeeks.com\/how-to-install-mysql-8-on-ubuntu\/\" target=\"_blank\" rel=\"noreferrer noopener\">installing MySQL on Ubuntu<\/a> or <a href=\"https:\/\/computingforgeeks.com\/how-to-install-mysql-8-0-on-rhel-8\/\" target=\"_blank\" rel=\"noreferrer noopener\">installing MySQL on RHEL\/Rocky Linux<\/a><\/li>\n\n\n\n<li>OpenSSL installed (included by default on both distributions)<\/li>\n\n\n\n<li>A client machine for testing TLS connections<\/li>\n\n\n\n<li>Port 3306\/TCP open between server and client<\/li>\n<\/ul>\n\n\n\n<h2 class=\"wp-block-heading\">Step 1: Check Current MySQL TLS Status<\/h2>\n\n\n\n<p>Before generating certificates, check whether MySQL already has TLS configured. MySQL 8.4 auto-generates certificates on first startup, but these self-signed certificates are not suitable for production use where you need to verify server identity.<\/p>\n\n\n\n<p>Log into MySQL and check the TLS status.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>sudo mysql -u root -p -e \"SHOW VARIABLES LIKE '%ssl%';\"<\/code><\/pre>\n\n\n\n<p>Expected output on a default installation:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>+-------------------------------------+-----------------+\n| Variable_name                       | Value           |\n+-------------------------------------+-----------------+\n| admin_ssl_ca                        |                 |\n| admin_ssl_capath                    |                 |\n| admin_ssl_cert                      |                 |\n| admin_ssl_cipher                    |                 |\n| admin_ssl_crl                       |                 |\n| admin_ssl_crlpath                   |                 |\n| admin_ssl_key                       |                 |\n| have_openssl                        | YES             |\n| have_ssl                            | YES             |\n| mysqlx_ssl_ca                       |                 |\n| mysqlx_ssl_cert                     |                 |\n| mysqlx_ssl_key                      |                 |\n| ssl_ca                              | ca.pem          |\n| ssl_capath                          |                 |\n| ssl_cert                            | server-cert.pem |\n| ssl_cipher                          |                 |\n| ssl_crl                             |                 |\n| ssl_crlpath                         |                 |\n| ssl_fips_mode                       | OFF             |\n| ssl_key                             | server-key.pem  |\n+-------------------------------------+-----------------+<\/code><\/pre>\n\n\n\n<p>The auto-generated certificates (<code>ca.pem<\/code>, <code>server-cert.pem<\/code>, <code>server-key.pem<\/code>) live in the MySQL data directory. We will replace these with our own CA-signed certificates for proper trust chain verification.<\/p>\n\n\n\n<p>Also verify the current connection encryption status.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>sudo mysql -u root -p -e \"SHOW STATUS LIKE 'Ssl_cipher';\"<\/code><\/pre>\n\n\n\n<p>If the <code>Ssl_cipher<\/code> value is empty, the current session is not encrypted.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Step 2: Create a Directory for TLS Certificates<\/h2>\n\n\n\n<p>Create a dedicated directory to store all MySQL TLS certificates and keys. Keep this separate from the MySQL data directory for cleaner management.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>sudo mkdir -p \/etc\/mysql\/ssl\nsudo chmod 750 \/etc\/mysql\/ssl<\/code><\/pre>\n\n\n\n<p>On Rocky Linux 10, the MySQL configuration directory is <code>\/etc\/my.cnf.d\/<\/code>, but we still create the SSL directory under <code>\/etc\/mysql\/ssl<\/code> for consistency across both distributions.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>## Rocky Linux 10\n$ sudo mkdir -p \/etc\/mysql\/ssl\n$ sudo chmod 750 \/etc\/mysql\/ssl<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">Step 3: Generate the Certificate Authority (CA)<\/h2>\n\n\n\n<p>The CA certificate signs both the server and client certificates. Anyone who trusts this CA will trust any certificate it signs. In production, you may use an existing internal CA. For this guide, we generate a self-signed CA.<\/p>\n\n\n\n<p>Generate a 4096-bit RSA private key for the CA.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>sudo openssl genrsa 4096 | sudo tee \/etc\/mysql\/ssl\/ca-key.pem > \/dev\/null<\/code><\/pre>\n\n\n\n<p>Now generate the CA certificate, valid for 3650 days (10 years). Adjust the subject fields to match your organization.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>$ sudo openssl req -new -x509 -nodes -days 3650 \\\n  -key \/etc\/mysql\/ssl\/ca-key.pem \\\n  -out \/etc\/mysql\/ssl\/ca-cert.pem \\\n  -subj \"\/C=US\/ST=California\/L=SanFrancisco\/O=MyOrg\/OU=DBA\/CN=MySQL-CA\"<\/code><\/pre>\n\n\n\n<p>Verify the CA certificate was created successfully.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>sudo openssl x509 -in \/etc\/mysql\/ssl\/ca-cert.pem -noout -subject -dates<\/code><\/pre>\n\n\n\n<p>You should see output like this:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>subject=C=US, ST=California, L=SanFrancisco, O=MyOrg, OU=DBA, CN=MySQL-CA\nnotBefore=Mar 19 10:00:00 2026 GMT\nnotAfter=Mar 17 10:00:00 2036 GMT<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">Step 4: Generate MySQL Server TLS Certificate<\/h2>\n\n\n\n<p>The server certificate identifies the MySQL server to connecting clients. The Common Name (CN) should match the server hostname or IP address that clients use to connect.<\/p>\n\n\n\n<p>Generate the server private key.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>sudo openssl genrsa 4096 | sudo tee \/etc\/mysql\/ssl\/server-key.pem > \/dev\/null<\/code><\/pre>\n\n\n\n<p>Create a Certificate Signing Request (CSR) for the server. Replace the CN with your actual server hostname or FQDN.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>$ sudo openssl req -new \\\n  -key \/etc\/mysql\/ssl\/server-key.pem \\\n  -out \/etc\/mysql\/ssl\/server-req.pem \\\n  -subj \"\/C=US\/ST=California\/L=SanFrancisco\/O=MyOrg\/OU=DBA\/CN=db-server.example.com\"<\/code><\/pre>\n\n\n\n<p>Sign the server CSR with the CA certificate. Set the validity to 365 days because server certificates should be rotated annually.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>$ sudo openssl x509 -req -days 365 \\\n  -in \/etc\/mysql\/ssl\/server-req.pem \\\n  -CA \/etc\/mysql\/ssl\/ca-cert.pem \\\n  -CAkey \/etc\/mysql\/ssl\/ca-key.pem \\\n  -CAcreateserial \\\n  -out \/etc\/mysql\/ssl\/server-cert.pem<\/code><\/pre>\n\n\n\n<p>Verify the server certificate against the CA.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>sudo openssl verify -CAfile \/etc\/mysql\/ssl\/ca-cert.pem \/etc\/mysql\/ssl\/server-cert.pem<\/code><\/pre>\n\n\n\n<p>Expected output:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>\/etc\/mysql\/ssl\/server-cert.pem: OK<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">Step 5: Generate MySQL Client TLS Certificate<\/h2>\n\n\n\n<p>Client certificates allow the MySQL server to verify the identity of connecting clients. This enables mutual TLS (mTLS) authentication, where both sides prove their identity.<\/p>\n\n\n\n<p>Generate the client private key.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>sudo openssl genrsa 4096 | sudo tee \/etc\/mysql\/ssl\/client-key.pem > \/dev\/null<\/code><\/pre>\n\n\n\n<p>Create the client CSR. Use a different CN than the server certificate.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>$ sudo openssl req -new \\\n  -key \/etc\/mysql\/ssl\/client-key.pem \\\n  -out \/etc\/mysql\/ssl\/client-req.pem \\\n  -subj \"\/C=US\/ST=California\/L=SanFrancisco\/O=MyOrg\/OU=DBA\/CN=mysql-client\"<\/code><\/pre>\n\n\n\n<p>Sign the client CSR with the same CA.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>$ sudo openssl x509 -req -days 365 \\\n  -in \/etc\/mysql\/ssl\/client-req.pem \\\n  -CA \/etc\/mysql\/ssl\/ca-cert.pem \\\n  -CAkey \/etc\/mysql\/ssl\/ca-key.pem \\\n  -CAcreateserial \\\n  -out \/etc\/mysql\/ssl\/client-cert.pem<\/code><\/pre>\n\n\n\n<p>Verify the client certificate.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>sudo openssl verify -CAfile \/etc\/mysql\/ssl\/ca-cert.pem \/etc\/mysql\/ssl\/client-cert.pem<\/code><\/pre>\n\n\n\n<p>Output confirming the certificate chain is valid:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>\/etc\/mysql\/ssl\/client-cert.pem: OK<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">Step 6: Set File Permissions on SSL Certificates<\/h2>\n\n\n\n<p>Private keys must be readable only by the MySQL user. Incorrect permissions will prevent MySQL from starting or loading the certificates.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>sudo chown -R mysql:mysql \/etc\/mysql\/ssl\/\nsudo chmod 600 \/etc\/mysql\/ssl\/*-key.pem\nsudo chmod 644 \/etc\/mysql\/ssl\/*-cert.pem \/etc\/mysql\/ssl\/ca-cert.pem<\/code><\/pre>\n\n\n\n<p>Verify the permissions are set correctly.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>ls -la \/etc\/mysql\/ssl\/<\/code><\/pre>\n\n\n\n<p>Expected output:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>total 40\ndrwxr-x--- 2 mysql mysql 4096 Mar 19 10:05 .\n-rw-r--r-- 1 mysql mysql 2033 Mar 19 10:02 ca-cert.pem\n-rw------- 1 mysql mysql 3243 Mar 19 10:01 ca-key.pem\n-rw-r--r-- 1 mysql mysql 1931 Mar 19 10:04 client-cert.pem\n-rw------- 1 mysql mysql 3243 Mar 19 10:04 client-key.pem\n-rw-r--r-- 1 mysql mysql 1931 Mar 19 10:03 server-cert.pem\n-rw------- 1 mysql mysql 3243 Mar 19 10:02 server-key.pem<\/code><\/pre>\n\n\n\n<p>On Rocky Linux 10 with SELinux enabled, restore the SELinux context so MySQL can read the files.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>## Rocky Linux 10 only\n$ sudo restorecon -Rv \/etc\/mysql\/ssl\/<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">Step 7: Configure MySQL Server for TLS Encryption<\/h2>\n\n\n\n<p>Edit the MySQL configuration to point to the custom certificates. The configuration file location differs between distributions.<\/p>\n\n\n\n<p>On Ubuntu 24.04, edit the MySQL configuration file.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>sudo vim \/etc\/mysql\/mysql.conf.d\/mysqld.cnf<\/code><\/pre>\n\n\n\n<p>Add these lines under the <code>[mysqld]<\/code> section:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>[mysqld]\n# TLS\/SSL Configuration\nssl_ca = \/etc\/mysql\/ssl\/ca-cert.pem\nssl_cert = \/etc\/mysql\/ssl\/server-cert.pem\nssl_key = \/etc\/mysql\/ssl\/server-key.pem\n\n# Require TLS for all connections (optional but recommended)\nrequire_secure_transport = ON\n\n# Minimum TLS version - enforce TLSv1.2 or higher\ntls_version = TLSv1.2,TLSv1.3<\/code><\/pre>\n\n\n\n<p>On Rocky Linux 10, the configuration file is different.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>sudo vim \/etc\/my.cnf.d\/mysql-server.cnf<\/code><\/pre>\n\n\n\n<p>Add the same TLS settings under the <code>[mysqld]<\/code> section:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>[mysqld]\n# TLS\/SSL Configuration\nssl_ca = \/etc\/mysql\/ssl\/ca-cert.pem\nssl_cert = \/etc\/mysql\/ssl\/server-cert.pem\nssl_key = \/etc\/mysql\/ssl\/server-key.pem\n\n# Require TLS for all connections\nrequire_secure_transport = ON\n\n# Minimum TLS version\ntls_version = TLSv1.2,TLSv1.3<\/code><\/pre>\n\n\n\n<p>The <code>require_secure_transport<\/code> setting forces all connections to use TLS. If you set this to ON, unencrypted connections will be rejected. Local connections over Unix socket are still allowed because they do not traverse the network.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Step 8: Restart MySQL and Verify TLS is Active<\/h2>\n\n\n\n<p>Restart the MySQL service to load the new certificates.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>sudo systemctl restart mysql    # Ubuntu 24.04\nsudo systemctl restart mysqld   # Rocky Linux 10<\/code><\/pre>\n\n\n\n<p>Check that MySQL started successfully.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>sudo systemctl status mysql    # Ubuntu 24.04\nsudo systemctl status mysqld   # Rocky Linux 10<\/code><\/pre>\n\n\n\n<p>If MySQL fails to start, check the error log for certificate-related issues.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>sudo tail -50 \/var\/log\/mysql\/error.log      # Ubuntu 24.04\nsudo tail -50 \/var\/log\/mysqld.log           # Rocky Linux 10<\/code><\/pre>\n\n\n\n<p>Common causes of startup failure include incorrect file permissions on key files, mismatched CA and server certificates, or SELinux denials on Rocky Linux.<\/p>\n\n\n\n<p>Log into MySQL and verify the TLS configuration is loaded.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>sudo mysql -u root -p -e \"SHOW VARIABLES LIKE '%ssl%';\"<\/code><\/pre>\n\n\n\n<p>The <code>ssl_ca<\/code>, <code>ssl_cert<\/code>, and <code>ssl_key<\/code> values should now point to your custom certificate paths under <code>\/etc\/mysql\/ssl\/<\/code>.<\/p>\n\n\n\n<p>Verify that TLS is active for the current session.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>sudo mysql -u root -p -e \"SHOW STATUS LIKE 'Ssl%';\" | head -20<\/code><\/pre>\n\n\n\n<p>Key values to look for:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>+-----------------------------+-------------------------------+\n| Variable_name               | Value                         |\n+-----------------------------+-------------------------------+\n| Ssl_cipher                  | TLS_AES_256_GCM_SHA384        |\n| Ssl_cipher_list             | ...                           |\n| Ssl_server_not_after        | Mar 19 10:03:00 2027 GMT      |\n| Ssl_server_not_before       | Mar 19 10:03:00 2026 GMT      |\n| Ssl_version                 | TLSv1.3                       |\n+-----------------------------+-------------------------------+<\/code><\/pre>\n\n\n\n<p>A non-empty <code>Ssl_cipher<\/code> confirms the connection is encrypted. The <code>Ssl_version<\/code> shows which TLS protocol version is in use.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Step 9: Require TLS for Specific MySQL Users<\/h2>\n\n\n\n<p>Instead of enforcing TLS globally with <code>require_secure_transport<\/code>, you can require TLS on a per-user basis. This is useful when some local applications connect over Unix socket (no TLS needed) while remote users must encrypt their connections.<\/p>\n\n\n\n<p>Create a new user that must connect over TLS.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>sudo mysql -u root -p<\/code><\/pre>\n\n\n\n<p>Run these SQL statements to create the user with TLS requirement:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>CREATE USER 'app_user'@'10.0.1.%' IDENTIFIED BY 'StrongP@ssw0rd!' REQUIRE SSL;\nGRANT ALL PRIVILEGES ON app_db.* TO 'app_user'@'10.0.1.%';\nFLUSH PRIVILEGES;<\/code><\/pre>\n\n\n\n<p>The <code>REQUIRE SSL<\/code> clause forces this user to connect with TLS. You can be even more restrictive with <code>REQUIRE X509<\/code>, which demands a valid client certificate.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>CREATE USER 'secure_user'@'10.0.1.%' IDENTIFIED BY 'StrongP@ssw0rd!' REQUIRE X509;\nGRANT ALL PRIVILEGES ON secure_db.* TO 'secure_user'@'10.0.1.%';\nFLUSH PRIVILEGES;<\/code><\/pre>\n\n\n\n<p>For the strictest control, require a specific certificate issuer and subject.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>CREATE USER 'strict_user'@'10.0.1.%' IDENTIFIED BY 'StrongP@ssw0rd!'\n  REQUIRE SUBJECT '\/C=US\/ST=California\/L=SanFrancisco\/O=MyOrg\/OU=DBA\/CN=mysql-client'\n  AND ISSUER '\/C=US\/ST=California\/L=SanFrancisco\/O=MyOrg\/OU=DBA\/CN=MySQL-CA';\nGRANT ALL PRIVILEGES ON strict_db.* TO 'strict_user'@'10.0.1.%';\nFLUSH PRIVILEGES;<\/code><\/pre>\n\n\n\n<p>To modify an existing user to require TLS, use ALTER USER.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>ALTER USER 'existing_user'@'%' REQUIRE SSL;\nFLUSH PRIVILEGES;<\/code><\/pre>\n\n\n\n<p>Verify the TLS requirements for all users.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT user, host, ssl_type FROM mysql.user WHERE ssl_type != '';<\/code><\/pre>\n\n\n\n<p>Expected output:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>+-------------+-----------+----------+\n| user        | host      | ssl_type |\n+-------------+-----------+----------+\n| app_user    | 10.0.1.%  | ANY      |\n| secure_user | 10.0.1.%  | X509     |\n| strict_user | 10.0.1.%  | SPECIFIED|\n+-------------+-----------+----------+<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">Step 10: Configure MySQL Client for TLS Connections<\/h2>\n\n\n\n<p>On the client machine, you need the CA certificate (and optionally the client certificate and key for X509 authentication). Copy the required files from the server to the client.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>scp root@10.0.1.10:\/etc\/mysql\/ssl\/ca-cert.pem \/etc\/mysql\/ssl\/\nscp root@10.0.1.10:\/etc\/mysql\/ssl\/client-cert.pem \/etc\/mysql\/ssl\/\nscp root@10.0.1.10:\/etc\/mysql\/ssl\/client-key.pem \/etc\/mysql\/ssl\/<\/code><\/pre>\n\n\n\n<p>Set proper permissions on the client side.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>sudo chmod 600 \/etc\/mysql\/ssl\/client-key.pem\nsudo chmod 644 \/etc\/mysql\/ssl\/ca-cert.pem \/etc\/mysql\/ssl\/client-cert.pem<\/code><\/pre>\n\n\n\n<p>Test a TLS connection from the client by specifying the certificates on the command line.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>$ mysql -u app_user -p -h 10.0.1.10 \\\n  --ssl-ca=\/etc\/mysql\/ssl\/ca-cert.pem \\\n  --ssl-cert=\/etc\/mysql\/ssl\/client-cert.pem \\\n  --ssl-key=\/etc\/mysql\/ssl\/client-key.pem<\/code><\/pre>\n\n\n\n<p>To avoid typing certificate paths every time, configure them in the MySQL client configuration file. On Ubuntu 24.04, edit <code>\/etc\/mysql\/mysql.conf.d\/mysql.cnf<\/code>. On Rocky Linux 10, edit <code>\/etc\/my.cnf.d\/client.cnf<\/code>. You can also use <code>~\/.my.cnf<\/code> for per-user settings.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>[client]\nssl-ca = \/etc\/mysql\/ssl\/ca-cert.pem\nssl-cert = \/etc\/mysql\/ssl\/client-cert.pem\nssl-key = \/etc\/mysql\/ssl\/client-key.pem\nssl-mode = VERIFY_IDENTITY<\/code><\/pre>\n\n\n\n<p>The <code>ssl-mode<\/code> options control how strictly the client validates the server certificate:<\/p>\n\n\n\n<figure class=\"wp-block-table\"><table class=\"has-fixed-layout\"><thead><tr><th>ssl-mode<\/th><th>Description<\/th><\/tr><\/thead><tbody><tr><td>DISABLED<\/td><td>No TLS encryption<\/td><\/tr><tr><td>PREFERRED<\/td><td>Use TLS if available, fall back to unencrypted (default)<\/td><\/tr><tr><td>REQUIRED<\/td><td>Require TLS but do not verify server certificate<\/td><\/tr><tr><td>VERIFY_CA<\/td><td>Require TLS and verify the server certificate against the CA<\/td><\/tr><tr><td>VERIFY_IDENTITY<\/td><td>Require TLS, verify CA, and check server hostname matches the certificate CN<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<p>For production, always use <code>VERIFY_CA<\/code> or <code>VERIFY_IDENTITY<\/code>. The <code>REQUIRED<\/code> mode encrypts traffic but does not protect against man-in-the-middle attacks.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Step 11: Verify Encrypted MySQL Connections<\/h2>\n\n\n\n<p>After connecting with TLS, run these queries to confirm the connection is encrypted.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>$ mysql -u app_user -p -h 10.0.1.10 \\\n  --ssl-ca=\/etc\/mysql\/ssl\/ca-cert.pem \\\n  --ssl-cert=\/etc\/mysql\/ssl\/client-cert.pem \\\n  --ssl-key=\/etc\/mysql\/ssl\/client-key.pem \\\n  -e \"\\s\"<\/code><\/pre>\n\n\n\n<p>Look for the SSL line in the output:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SSL:                    Cipher in use is TLS_AES_256_GCM_SHA384<\/code><\/pre>\n\n\n\n<p>For more detail, query the session SSL status variables.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SHOW SESSION STATUS LIKE 'Ssl%';<\/code><\/pre>\n\n\n\n<p>Key fields to verify:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>+-----------------------------+-------------------------------+\n| Variable_name               | Value                         |\n+-----------------------------+-------------------------------+\n| Ssl_cipher                  | TLS_AES_256_GCM_SHA384        |\n| Ssl_version                 | TLSv1.3                       |\n| Ssl_verify_mode             | 5                             |\n| Ssl_server_not_after        | Mar 19 10:03:00 2027 GMT      |\n+-----------------------------+-------------------------------+<\/code><\/pre>\n\n\n\n<p>You can also check all active connections and their encryption status from the server side. If you are running <a href=\"https:\/\/computingforgeeks.com\/monitoring-mysql-mariadb-with-prometheus-in-five-minutes\/\" target=\"_blank\" rel=\"noreferrer noopener\">MySQL monitoring with Prometheus<\/a>, track TLS connection metrics over time.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT user, host, connection_type, ssl_version, ssl_cipher\nFROM performance_schema.threads t\nJOIN performance_schema.session_connect_attrs s USING(processlist_id)\nWHERE t.type = 'FOREGROUND' LIMIT 10;<\/code><\/pre>\n\n\n\n<p>Alternatively, use the simpler processlist query.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT * FROM performance_schema.session_status\nWHERE variable_name IN ('Ssl_cipher', 'Ssl_version');<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">Step 12: Open Firewall Port for Remote MySQL Connections<\/h2>\n\n\n\n<p>If connecting from remote clients, open port 3306\/TCP in the firewall. On Ubuntu 24.04 with UFW:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>sudo ufw allow from 10.0.1.0\/24 to any port 3306 proto tcp\nsudo ufw reload\nsudo ufw status | grep 3306<\/code><\/pre>\n\n\n\n<p>On Rocky Linux 10 with <a href=\"https:\/\/computingforgeeks.com\/getting-started-with-firewalld-rhel-centos\/\" target=\"_blank\" rel=\"noreferrer noopener\">firewalld<\/a>:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>sudo firewall-cmd --permanent --add-service=mysql\nsudo firewall-cmd --reload\nsudo firewall-cmd --list-services | grep mysql<\/code><\/pre>\n\n\n\n<p>For tighter security, restrict MySQL access to specific source IPs instead of opening it to all networks.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>sudo firewall-cmd --permanent --add-rich-rule='rule family=\"ipv4\" source address=\"10.0.1.0\/24\" port protocol=\"tcp\" port=\"3306\" accept'\nsudo firewall-cmd --reload<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">Step 13: Certificate Rotation Without Downtime<\/h2>\n\n\n\n<p>TLS certificates expire and must be rotated before expiration. MySQL 8.4 supports reloading certificates without restarting the server, which means zero downtime during rotation.<\/p>\n\n\n\n<p>First, check the current certificate expiry dates.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>sudo openssl x509 -in \/etc\/mysql\/ssl\/server-cert.pem -noout -enddate<\/code><\/pre>\n\n\n\n<p>Output shows when the certificate expires:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>notAfter=Mar 19 10:03:00 2027 GMT<\/code><\/pre>\n\n\n\n<p>Generate new server certificates using the same CA (follow Step 4 again). Place the new certificates in a staging directory first.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>$ sudo mkdir -p \/etc\/mysql\/ssl\/new\n$ sudo openssl genrsa 4096 | sudo tee \/etc\/mysql\/ssl\/new\/server-key.pem > \/dev\/null\n$ sudo openssl req -new \\\n  -key \/etc\/mysql\/ssl\/new\/server-key.pem \\\n  -out \/etc\/mysql\/ssl\/new\/server-req.pem \\\n  -subj \"\/C=US\/ST=California\/L=SanFrancisco\/O=MyOrg\/OU=DBA\/CN=db-server.example.com\"\n$ sudo openssl x509 -req -days 365 \\\n  -in \/etc\/mysql\/ssl\/new\/server-req.pem \\\n  -CA \/etc\/mysql\/ssl\/ca-cert.pem \\\n  -CAkey \/etc\/mysql\/ssl\/ca-key.pem \\\n  -CAcreateserial \\\n  -out \/etc\/mysql\/ssl\/new\/server-cert.pem<\/code><\/pre>\n\n\n\n<p>Verify the new certificate before deploying it.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>sudo openssl verify -CAfile \/etc\/mysql\/ssl\/ca-cert.pem \/etc\/mysql\/ssl\/new\/server-cert.pem<\/code><\/pre>\n\n\n\n<p>Swap the certificates in place.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>sudo cp \/etc\/mysql\/ssl\/server-cert.pem \/etc\/mysql\/ssl\/server-cert.pem.bak\nsudo cp \/etc\/mysql\/ssl\/server-key.pem \/etc\/mysql\/ssl\/server-key.pem.bak\nsudo cp \/etc\/mysql\/ssl\/new\/server-cert.pem \/etc\/mysql\/ssl\/server-cert.pem\nsudo cp \/etc\/mysql\/ssl\/new\/server-key.pem \/etc\/mysql\/ssl\/server-key.pem\nsudo chown mysql:mysql \/etc\/mysql\/ssl\/server-cert.pem \/etc\/mysql\/ssl\/server-key.pem\nsudo chmod 600 \/etc\/mysql\/ssl\/server-key.pem<\/code><\/pre>\n\n\n\n<p>Tell MySQL to reload the certificates without restarting the service. This is the key command for zero-downtime rotation.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>sudo mysql -u root -p -e \"ALTER INSTANCE RELOAD TLS;\"<\/code><\/pre>\n\n\n\n<p>Verify the new certificate is loaded.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>sudo mysql -u root -p -e \"SHOW STATUS LIKE 'Ssl_server_not_after';\"<\/code><\/pre>\n\n\n\n<p>The expiry date should reflect the new certificate. Existing connections continue using the old certificate until they reconnect. New connections use the updated certificate immediately.<\/p>\n\n\n\n<p>Clean up the staging directory after successful rotation.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>sudo rm -rf \/etc\/mysql\/ssl\/new<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">Step 14: Automate Certificate Expiry Monitoring<\/h2>\n\n\n\n<p>Set up a simple cron job to alert you before certificates expire. This script checks the server certificate and sends a warning 30 days before expiry.<\/p>\n\n\n\n<p>Create the monitoring script.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>echo '#!\/bin\/bash\nCERT=\"\/etc\/mysql\/ssl\/server-cert.pem\"\nDAYS_WARNING=30\nEXPIRY=$(openssl x509 -in \"$CERT\" -noout -enddate | cut -d= -f2)\nEXPIRY_EPOCH=$(date -d \"$EXPIRY\" +%s)\nNOW_EPOCH=$(date +%s)\nDAYS_LEFT=$(( (EXPIRY_EPOCH - NOW_EPOCH) \/ 86400 ))\n\nif [ \"$DAYS_LEFT\" -lt \"$DAYS_WARNING\" ]; then\n    echo \"WARNING: MySQL TLS certificate expires in $DAYS_LEFT days ($EXPIRY)\" | \\\n    mail -s \"MySQL TLS Certificate Expiry Warning\" admin@example.com\nfi' | sudo tee \/usr\/local\/bin\/check-mysql-cert.sh > \/dev\/null\nsudo chmod +x \/usr\/local\/bin\/check-mysql-cert.sh<\/code><\/pre>\n\n\n\n<p>Add it to the root crontab to run daily.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>echo \"0 8 * * * \/usr\/local\/bin\/check-mysql-cert.sh\" | sudo tee -a \/var\/spool\/cron\/crontabs\/root > \/dev\/null<\/code><\/pre>\n\n\n\n<p>On Rocky Linux 10, the cron path is different.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>echo \"0 8 * * * \/usr\/local\/bin\/check-mysql-cert.sh\" | sudo tee -a \/var\/spool\/cron\/root > \/dev\/null<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">Troubleshooting MySQL TLS Issues<\/h2>\n\n\n\n<p>Here are common problems you may encounter when setting up MySQL TLS and their solutions.<\/p>\n\n\n\n<p><strong>ERROR 2026 (HY000): SSL connection error: error:0A000086<\/strong> &#8211; This usually means the CA certificate on the client does not match the one that signed the server certificate. Verify with:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>openssl verify -CAfile \/etc\/mysql\/ssl\/ca-cert.pem \/etc\/mysql\/ssl\/server-cert.pem<\/code><\/pre>\n\n\n\n<p><strong>MySQL fails to start after adding TLS config<\/strong> &#8211; Check the error log for specifics. The most common cause is wrong file permissions on the private key.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>sudo ls -la \/etc\/mysql\/ssl\/server-key.pem\nsudo namei -l \/etc\/mysql\/ssl\/<\/code><\/pre>\n\n\n\n<p><strong>SELinux denials on Rocky Linux 10<\/strong> &#8211; Check for AVC denials and fix the context.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>sudo ausearch -m AVC -ts recent | grep mysql\nsudo restorecon -Rv \/etc\/mysql\/ssl\/<\/code><\/pre>\n\n\n\n<p><strong>Client connects but shows no encryption<\/strong> &#8211; The client defaults to <code>ssl-mode=PREFERRED<\/code>, which may silently fall back. Force TLS by setting <code>--ssl-mode=REQUIRED<\/code> on the command line or in the client config.<\/p>\n\n\n\n<p><strong>Certificate chain issues<\/strong> &#8211; If using intermediate CAs, concatenate the intermediate and root CA certificates into a single file for the <code>ssl_ca<\/code> parameter.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Conclusion<\/h2>\n\n\n\n<p>MySQL 8.4 LTS is now secured with custom TLS certificates on Ubuntu 24.04 and Rocky Linux 10. All client-server traffic is encrypted, and per-user TLS requirements give fine-grained control over who must present valid certificates. The <code>ALTER INSTANCE RELOAD TLS<\/code> command makes certificate rotation a zero-downtime operation.<\/p>\n\n\n\n<p>For production hardening, combine TLS with strong authentication plugins, regular certificate rotation, automated expiry monitoring, and <a href=\"https:\/\/computingforgeeks.com\/backup-mysql-databases-amazon-s3\/\" target=\"_blank\" rel=\"noreferrer noopener\">database backups to S3<\/a> or another off-site location. If you are running <a href=\"https:\/\/computingforgeeks.com\/configure-mysql-8-master-slave-replication-on-ubuntu\/\" target=\"_blank\" rel=\"noreferrer noopener\">MySQL replication<\/a>, enable TLS on the replication channel as well to encrypt data between primary and replica servers.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Related Guides<\/h2>\n\n\n\n<ul class=\"wp-block-list\">\n<li><a href=\"https:\/\/computingforgeeks.com\/how-to-install-mysql-8-on-ubuntu\/\" target=\"_blank\" rel=\"noreferrer noopener\">How To Install MySQL 8.0 on Ubuntu 24.04\/22.04\/20.04<\/a><\/li>\n\n\n\n<li><a href=\"https:\/\/computingforgeeks.com\/how-to-install-mysql-8-on-fedora\/\" target=\"_blank\" rel=\"noreferrer noopener\">How To Install MySQL 8.4\/8.0 on Fedora 43\/42\/41\/40<\/a><\/li>\n\n\n\n<li><a href=\"https:\/\/computingforgeeks.com\/install-and-configure-prometheus-mysql-exporter-on-ubuntu-centos\/\" target=\"_blank\" rel=\"noreferrer noopener\">Configure Prometheus MySQL Exporter on Ubuntu \/ CentOS<\/a><\/li>\n\n\n\n<li><a href=\"https:\/\/computingforgeeks.com\/easiest-way-install-letsencrypt-linux\/\" target=\"_blank\" rel=\"noreferrer noopener\">How To Generate Let&#8217;s Encrypt SSL Certificates on Linux<\/a><\/li>\n\n\n\n<li><a href=\"https:\/\/computingforgeeks.com\/how-to-install-mysql-8-0-on-debian\/\" target=\"_blank\" rel=\"noreferrer noopener\">How To Install MySQL 8.0 on Debian 12\/11\/10<\/a><\/li>\n<\/ul>\n\n","protected":false},"excerpt":{"rendered":"<p>MySQL 8.4 LTS supports TLS\/SSL encryption for all client-server communication, protecting data in transit from eavesdropping and man-in-the-middle attacks. By default, MySQL allows unencrypted local connections, but any production deployment accepting remote connections must enforce TLS encryption with properly signed certificates. This guide walks through generating a custom Certificate Authority (CA), server certificates, and client &#8230; <a title=\"Secure MySQL 8.4 LTS with TLS\/SSL Certificates on Ubuntu 24.04 \/ Rocky Linux 10\" class=\"read-more\" href=\"https:\/\/computingforgeeks.com\/secure-mysql-tls-ssl-certificates\/\" aria-label=\"Read more about Secure MySQL 8.4 LTS with TLS\/SSL Certificates on Ubuntu 24.04 \/ Rocky Linux 10\">Read more<\/a><\/p>\n","protected":false},"author":3,"featured_media":162932,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[461,299,75,81],"tags":[324,282,323,35904,2254],"cfg_series":[],"class_list":["post-67111","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-databases","category-how-to","category-security","category-ubuntu","tag-databases","tag-linux","tag-mysql","tag-rocky-linux","tag-ubuntu"],"_links":{"self":[{"href":"https:\/\/computingforgeeks.com\/wp-json\/wp\/v2\/posts\/67111","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/computingforgeeks.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/computingforgeeks.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/computingforgeeks.com\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/computingforgeeks.com\/wp-json\/wp\/v2\/comments?post=67111"}],"version-history":[{"count":2,"href":"https:\/\/computingforgeeks.com\/wp-json\/wp\/v2\/posts\/67111\/revisions"}],"predecessor-version":[{"id":164116,"href":"https:\/\/computingforgeeks.com\/wp-json\/wp\/v2\/posts\/67111\/revisions\/164116"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/computingforgeeks.com\/wp-json\/wp\/v2\/media\/162932"}],"wp:attachment":[{"href":"https:\/\/computingforgeeks.com\/wp-json\/wp\/v2\/media?parent=67111"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/computingforgeeks.com\/wp-json\/wp\/v2\/categories?post=67111"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/computingforgeeks.com\/wp-json\/wp\/v2\/tags?post=67111"},{"taxonomy":"cfg_series","embeddable":true,"href":"https:\/\/computingforgeeks.com\/wp-json\/wp\/v2\/cfg_series?post=67111"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}