How to Set Up and Secure MySQL SSL Connections

Securing MySQL connections between the client and the server is essential, especially when you use remote or cloud-hosted databases. In such applications, you often connect to databases over a public or shared network where data may be compromised.

In a non-encrypted connection, when MySQL communicates in plain text, all data, including sensitive information such as user credentials and database content, may be exposed to outside threats. One of the most serious risks is the high possibility of man-in-the-middle attacks, when data is tampered with in transit without either party's knowledge.

By setting up SSL/TLS encryption, you make sure that the data communicated between your MySQL server and client is protected from outside attacks, thus maintaining security and integrity of your digital assets.

MySQL encryption via SSL adds a reliable layer of data protection and trust, which is critical for anyone working with data: developers, database administrators, data analysts, or data architects. It creates a secure data management environment in more than one way:

• Data protection in transit
• Protected database access
• Safety of user credentials
• Confidentiality of analytics
• Regulatory compliance

In this article, we provide step-by-step instructions on setting up, enforcing, troubleshooting, and verifying SSL in MySQL.

What is SSL in MySQL and why it matters

SSL (Secure Sockets Layer) is a protocol that encrypts data exchanged between a MySQL server and client. Modern systems use TLS (Transport Layer Security), a more advanced security protocol, however, SSL is still widely used, too.

SSL/TLS encryption algorithms ensure that data in transit is protected from alteration or loss and that the identity of parties is verified. In SSL/TLS encryption, authentication is performed by certificates issued by a Certificate Authority. Such certificates serve as proof of identity. An SSL certificate contains the certificate owner's public key that is used to encrypt data. It can then be decrypted with a corresponding secret key.

This way, data is only accessible to certificate owners, preventing access by unauthorized parties.

Preparing for SSL setup in MySQL

When preparing to enable SSL encryption in MySQL, check the following:

1. Your server's MySQL version. SSL encryption is supported in MySQL version 5.7 and higher. Depending on your environment, you can choose one of the following methods of checking your MySQL server version:
   • If you are using the command line interface, run the following command:

     mysql -V
     

   • In dbForge Studio for MySQL, you can find the current version by executing the following query:

     SELECT VERSION();
     

2. Admin access to your MySQL server configuration files: my.cnf or my.ini
3. OpenSSL version. To check if OpenSSL is installed and find its version, run the following command in your CLI:

   openssl version
   

   If OpenSSL is installed, the response contains its version number. If the command is not recognized, download and install OpenSSL.

Generating certificates for MySQL server and MySQL client

To enable SSL encryption for your MySQL server and MySQL client, you need to generate SSL certificate and key files and specify their location in the SSL configuration.

Use OpenSSL CLI to generate the required files as follows:

1. Navigate to the directory where OpenSSL is installed and generate the key file (ca-key.pem) that you need to generate the authority certificate:

   openssl genrsa 1024 > ca-key.pem
   

   • openssl genrsa - invokes the OpenSSL command that generates an RSA private key
   • 1024 - specifies the key length in bits
   • > ca-key.pem - redirects the output to the ca-key.pem file where the private key will be stored
2. Use the key file to generate the authority certificate (ca-cert.pem):

   openssl req -new -x509 -nodes -days 1000 -key ca-key.pem -config myssl.cnf > ca-cert.pem
   

   • openssl req - invokes the OpenSSL command that creates and processes certificate requests
   • -new - specifies that a new certificate request should be created
   • -x509 - indicates that the output should be a self-signed X.509 certificate instead of a certificate request
   • -nodes - ensures the private key is not encrypted (no DES encryption), meaning no passphrase is required
   • -days 1000 - sets the validity period of the certificate to 1000 days
   • -key ca-key.pem - specifies the private key file (ca-key.pem) to be used for signing the certificate
   • -config myssl.cnf - references the configuration file (myssl.cnf) that defines certificate details, such as distinguished name (DN) fields
   • > ca-cert.pem - redirects the output to the ca-cert.pem file where the generated CA certificate will be stored
3. Generate the key file (server-key.pem) that you need to generate the server certificate:

   openssl req -newkey rsa:1024 -days 1000 -nodes -keyout server-key.pem -config myssl.cnf > server-req.pem
   

   • openssl req - invokes the OpenSSL command that creates and processes key requests
   • -newkey rsa:1024 - specifies that a new RSA private key with a size of 1024 bits should be created
   • -days 1000 - sets the validity period of the key to 1000 days
   • -nodes - ensures the private key is not encrypted (no DES encryption), meaning no passphrase is required
   • -keyout server-key.pem - saves the generated private key to the server-key.pem file
   • -config myssl.cnf - references the configuration file (myssl.cnf) that defines Certificate Signing Request (CSR) details
   • > server-req.pem - redirects the CSR output to the server-req.pem file that is sent to the CA for signing
4. Use the server key file and authority certificate to generate the server certificate (server-cert.pem):

   openssl x509 -req -in server-req.pem -days 1000 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > server-cert.pem
   

   • openssl x509 - invokes the X.509 certificate management command
   • -req - specifies that the command should be processed as a certificate request instead of a self-signed certificate
   • -in server-req.pem - specifies the input CSR file (server-req.pem) that should be signed
   • -days 1000 - sets the validity period of the issued certificate to 1000 days
   • -CA ca-cert.pem - specifies the CA certificate (ca-cert.pem) used to sign the new certificate
   • -CAkey ca-key.pem - specifies the CA private key (ca-key.pem) corresponding to the CA certificate
   • -set_serial 01 - sets the serial number of the new certificate (unique for each certificate issued by the CA)
   • > server-cert.pem - redirects signed certificate output to the server-cert.pem file
5. Generate the client key file (client-key.pem) that you need to generate the client certificate:

   openssl req -newkey rsa:1024 -days 1000 -nodes -keyout client-key.pem -config myssl.cnf > client-req.pem
   

   • openssl req - invokes the OpenSSL command that creates and processes key requests
   • -newkey rsa:1024 - specifies that a new RSA private key with a size of 1024 bits should be created
   • -days 1000 - sets the validity period of the key to 1000 days
   • -nodes - ensures the private key is not encrypted (no DES encryption), meaning no passphrase is required
   • -keyout client-key.pem - saves the generated private key to the client-key.pem file
   • -config myssl.cnf - references the configuration file (myssl.cnf) that defines Certificate Signing Request (CSR) details
   • > client-req.pem - redirects the CSR output to the client-req.pem file that is sent to the CA for signing
6. Use the keys and certificates that you generated to generate the client certificate (client-cert.pem):

   openssl x509 -req -in client-req.pem -days 1000 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > client-cert.pem
   

   • openssl x509 - invokes the X.509 certificate management command
   • -req - specifies that the command should be processed as a certificate request instead of a self-signed certificate
   • -in client-req.pem - specifies the input CSR file (client-req.pem) that should be signed
   • -days 1000 - sets the validity period of the issued certificate to 1000 days
   • -CA ca-cert.pem - specifies the CA certificate (ca-cert.pem) used to sign the new certificate
   • -CAkey ca-key.pem - specifies the CA private key (ca-key.pem) corresponding to the CA certificate
   • -set_serial 01 - sets the serial number of the new certificate (unique for each certificate issued by the CA)
   • > client-cert.pem - redirects signed certificate output to the client-cert.pem file
7. Specify the locations of your ca-cert.pem, server-cert.pem, and server-key.pem files in the my.ini file of your MySQL server by typing the following after the [mysqld] line (use the actual locations of your SSL files):

   ssl
   	
   ssl-ca="D:/SSL Certificates/ca-cert.pem"
   	
   ssl-cert="D:/SSL Certificates/Server/server-cert.pem"
   	
   ssl-key="D:/SSL Certificates/Server/server-key.pem"
   

8. Restart the MySQL server. On Windows, click WIN + R, and type services.msc. In the list of services, find MySQL, right-click it, and select Restart from the context menu. On Linux, run the following command:

   sudo systemctl restart mysql
   

9. Verify that MySQL server now supports SSL by executing the following SQL query in dbForge Studio for MySQL or by running the same command in your CLI:

   SHOW VARIABLES LIKE 'have_openssl'
   

   If the response contains YES, you have successfully enabled SSL for your MySQL server.

Connecting to MySQL with SSL (client side)

Depending on your client-side configuration, you can choose from several methods of connecting to the MySQL server with SSL. Follow the instructions below to establish an encrypted connection.

Establishing an SSL-encrypted connection using CLI

If you prefer to use the command-line interface, run the following command:

mysql -u your_username -p --host=your_mysql_host --ssl-ca=ca-cert.pem --ssl-cert=client-cert.pem --ssl-key=client-key.pem

The -p parameter prompts for your MySQL password.

To enforce an SSL connection, add the following line to your command:

--ssl-mode=REQUIRED

Establishing an SSL-encrypted connection in dbForge Studio for MySQL

dbForge Studio for MySQL allows you to connect to the MySQL server with SSL in a user-friendly GUI as follows:

1. In the Database menu, select New Connection.
2. Navigate to the Security tab and check Use security protocol.
3. Select SSL and specify the locations of your client key, client certificate, and authority certificate files.
4. Optionally, choose one of the ciphers (data encryption algorithms). All ciphers are enabled by default, but you can choose the required one.
   
5. On the General tab, enter your MySQL login details:
   • Type: TCP/IP
   • Host: name or IP address of the MySQL server host
   • Port: TCP/IP port to be used to connect to the MySQL server
   • User: your user name
   • Password: your password
6. Choose the database to connect to.
7. Click Connect to establish a connection.
   

Establishing an SSL-encrypted connection using programming languages

Programming languages support inherent options of connecting to databases using SSL. Let's look at the most commonly used ones.

PHP

PHP offers two extensions that enable connections with MySQL:

• MySQLi used to connect to the MySQL DBMS
• PDO (PHP Data Objects) representing a common interface for connecting to various types of databases

1. Define SSL options:

   mysqli_ssl_set(connection, key, cert, ca, capath, cipher);
   

2. Connect to the MySQL server:

   mysqli_real_connect(connection, host, username, password, database, port, socket, MYSQLI_CLIENT_SSL);
   

   The MYSQLI_CLIENT_SSL flag specifies that the connection must use SSL encryption.

3. Check the connection:

   mysqli_connect_error();
   

   If NULL is returned, the connection is established.

1. Define SSL options:

   <?php
   $dsn = 'mysql:host=your_host;dbname=your_dbname;port=3306';
   $options = [
       PDO::MYSQL_ATTR_SSL_CA     => '/path/to/ca-cert.pem',
       PDO::MYSQL_ATTR_SSL_CERT   => '/path/to/client-cert.pem',
       PDO::MYSQL_ATTR_SSL_KEY    => '/path/to/client-key.pem',
       PDO::ATTR_ERRMODE          => PDO::ERRMODE_EXCEPTION
   ];
   

2. Establish the connection:

   $pdo = new \PDO($dsn, $user, $pass, $options);
   

Python

Python supports multiple packages for connecting to MySQL, the most common being PyMySQL and mysql-connector-python.

To connect to a MySQL server with SSL, run the following command:

connection = pymysql.connect(
    host='your_host',
    user='your_user',
    password='your_password',
    database='your_dbname',
    port=3306,
    ssl={
        'ca': '/path/to/ca-cert.pem',
        'cert': '/path/to/client-cert.pem',
        'key': '/path/to/client-key.pem'
    }
)

To connect to a MySQL server with SSL, run the following command:

connection = mysql.connector.connect(
    host='your_host',
    user='your_user',
    password='your_password',
    database='your_dbname',
    port=3306,
    ssl_ca='/path/to/ca-cert.pem',
    ssl_cert='/path/to/client-cert.pem',
    ssl_key='/path/to/client-key.pem'
)

Java

Java supports JDBC (Java Database Connectivity), a platform-agnostic API for defining a connection between a client application and a database.

Before connecting to MySQL, make sure you have added a compatible MySQL JDBC driver, such as Connector/J 8.0+, to your project.

The code used to connect your Java project to MySQL must contain a JDBC URL for SSL that specifies your connection details and your key and certificate file locations:

String url = "jdbc:mysql://hostname:3306/dbname?ssl=true&sslmode=require&sslrootcert=server-ca.pem&sslcert=client-cert.pem&sslkey=client-key.pem";

To connect to the MySQL server, use this URL in the following code:

public class SSLConnectionExample {
    public static void main(String[] args) {
        String url = "jdbc:mysql://hostname:3306/dbname?ssl=true&sslmode=require&sslrootcert=server-ca.pem&sslcert=client-cert.pem&sslkey=client-key.pem";
        String user = "your_username";
        String password = "your_password";

        try (Connection conn = DriverManager.getConnection(url, user, password)) {
            System.out.println("SSL connection successful!");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

How to enforce SSL connections in MySQL

To ensure data exchange protection at all times, you can set up your MySQL connection to make SSL encryption mandatory. You can enforce SSL-encrypted connections at various levels: MySQL server, MySQL client, or database user:

• To enforce SSL on MySQL server, enable the require_secure_transport option in the my.cnf or my.ini file:

  require_secure_transport = ON
  

  With this configuration, all MySQL server clients will be required to use SSL/TLS.

• To configure client connections to require SSL regardless of whether the server requires it or not, set the --ssl-mode to REQUIRED, VERIFY_CA, or VERIFY_IDENTITY.
• To enforce an SSL connection on a per-user basis, include a REQUIRE SSL option in the CREATE USER statement:

  CREATE USER 'username' REQUIRE SSL;
  

  You can modify an existing user's access to require SSL with an ALTER USER statement:

  ALTER USER 'username' REQUIRE SSL;
  

These measures ensure SSL-encrypted connections at all times and prevent non-encrypted access attempts.

How dbForge Studio for MySQL makes SSL management easier

In this article, we described several ways of setting up and establishing an SSL connection to the MySQL server to ensure strong and reliable database security. dbForge Studio for MySQL stands out among them as a user-friendly database management environment with a GUI that allows easy and intuitive use of MySQL.

Using MySQL GUI tools by Devart, you can easily configure and set up SSL encryption for your database on a per-user basis. The Studio offers the Security Manager, where you can create and store user profiles specifying SSL as their required method of connection:

The connection management features of dbForge Studio for MySQL cover all the aspects of setting up a securely encrypted data exchange between a MySQL server and client applications. With multiple supported database connections, security concerns may become paramount, however, the built-in encryption capabilities enable data protection when connecting to any supported MySQL-compatible database. To database admins, the Studio offers options for creating and managing user roles and profiles, assigning them to database users to ensure and maintain the highest level of security. The security configuration approach implemented in dbForge Studio for MySQL streamlines user management, reduces errors, and enables auditing of the organization's access system.

Conclusion

Data security remains one of the priority concerns for any IT application, and SSL encryption is one of the reliable protection mechanisms that you can implement to safeguard your databases. There are many ways to enable SSL protocols in your MySQL environment, however, dbForge Studio for MySQL stands out with its user-friendly GUI that offers a comprehensive set of features for implementing SSL encryption. Take advantage of the 30-day free trial of the Enterprise edition of dbForge Studio for MySQL and evaluate its full database development and management potential.