Data Loader Blog

5-Oct-2021

How to use SSL Encryption for Connecting to MySQL

If we are moving data to and from MySQL database using public network then anybody on the network can see the data and thus compromising the data security.  With an unencrypted connection between the MySQL client and the server, someone with network access can monitor all your traffic and check the data sent or received between the client and server.

When information needs to be moved securely over a network, an unencrypted connection cannot be accepted. MySQL uses encryption to make all types of data unreadable.

 MySQL supports encrypted connections between clients and servers using the Transport Layer Security (TLS) protocol. TLS is sometimes referred to as Secure Sockets Layer (SSL)

Here is how you can configure MySQL Server for SSL encryption

Server Side

We can start the MySQL server with the --ssl option which specifies that the server permits but does not require encrypted connections. This option means MySQL can accept both encrypted as well as unencrypted connections. This option is enabled by default whenever you start MySQL, so it need not be specified explicitly.

To make it mandatory that clients connect using encrypted connections, we need to enable the require_secure_transport system variable.

To specify the certificate and key files the MySQL server uses when permitting clients to establish encrypted connections, we need to define these server variables in MySQL configuration file (my.cnf) on the server

• ssl_ca: The path name of the Certificate Authority (CA) certificate file. (ssl_capath is similar but specifies the path name of a directory of CA certificate files.)
• ssl_cert: The path name of the server public key certificate file.
• ssl_key: The path name of the server private key file.

For example, to enable the server for encrypted connections, start it with these lines in the my.cnf file, changing the file names as necessary:

Client Side 

Coming to client side, if you are using Data Loader for exporting or importing data into MySQL database. Then you can use SSL encryption by specifying the following settings in MySQL Source or Target screen.

SSL Preferred

If you want to connect to MySQL with encryption if the MySQL server supports it, then you can specify the following settings

This settings will use encryption if MySQL server supports it or will not use encryption if MySQL server doesn't support it. That is, connection will get established either this way or that way.

SSL Required

If you want your connection to MySQL to mandatory encrypted then you can specify the SSL - Required settings as shown below.

If you specify this settings then Data Loader will connect to MySQL with compulsory encryption, if MySQL Server doesn't support encryption then the connection will not established.

Using file based

To use file based SSL certificate please put the path of the certificate in the Server textbox preceded by a semicolon ";" as shown below

Like for example your SSL certificate file is "C:\yourfolder\client.pfx" and your server address is "localhost" then put the following in Server textbox in Data Loader.

localhost;CertificateFile=C:\yourfolder\client.pfx;CertificatePassword=pass;