Generating Certificates for MySQL Server and SSL Client

Last modified: June 26, 2023

To create SSL connection the following three files are required:

• Client key - used along with the client certificate to encrypt and decrypt data during a connection
• Client certificate
• Authority certificate - used to verify identity of a client and a server

You need to specify their location in my.ini file of the MySQL server and on the Security tab of the Database Connection Properties dialog box.

1. Download OpenSSL. This command line tool is used for creation and management of private keys, public keys, and parameters.

2. Open the command prompt by using Start -> Run -> cmd and type the following to go to OpenSSL install directory (for example, it is D:\OpenSSL):

   d:

   cd \openssl

3. Generate a key file used for authority certificate generation by typing:

   openssl genrsa 1024 > ca-key.pem

   (This string will create ca-key.pem file)

4. Generate the authority certificate by typing the following:

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

   (This string will create ca-cert.pem file.)

5. Generate a key file used for server certificate generation by typing the following:

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

   (This string will create server-key.pem file.)

6. Generate the server certificate file by typing the following:

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

   (This string will create server-cert.pem file.)

7. Generate a key file used for client certificate generation by typing the following:

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

   (This string will create client-key.pem file.)

8. Generate the client certificate file by typing the following:

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

   (This string will create client-cert.pem file.)

9. Relocate the generated files on your machine if required, and go to the installation directory of the MySQL Server. Open the my.ini file and after [mysqld] line specify the location of the generated files - ca-cert.pem, server-cert.pem, and server-key.pem by typing:

   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”

10. Restart the MySQL Server and check if it supports SSL by opening a new SQL document in dbForge Fusion for MySQL - (on the top menu, select Fusion, click New SQL) and typing the following:

    SHOW VARIABLES LIKE ‘have_openssl’

If the server returns YES, you can set up SSL client. If the response is NO or something does not work in the existing configuration, please visit https://dev.mysql.com/doc/ for instructions on how to start up a required server from scratch.