Generate certificates for MySQL server and SSL client
To establish an SSL connection, the following files are required:
- Client key and Client certificate are used to encrypt and decrypt data during connection
- Authority certificate is used to verify the identities of the client and the server
You need to specify their location in the my.ini file of your MySQL server and on the Security tab of the Database Connection Properties dialog.
1. Download OpenSSL. This command-line tool is used to create and manage private keys, public keys, and parameters.
2. Open the command prompt via Start > Run > cmd and type the path to the OpenSSL installation directory. For example, if your path is D:\OpenSSL, type the following:
d:
cd \openssl
3. Generate a key file that will be used to generate the Authority certificate using the following command:
openssl genrsa 1024 > ca-key.pem
This string will create a ca-key.pem file.
4. Generate the Authority certificate using the following command:
openssl req -new -x509 -nodes -days 1000 -key ca-key.pem -config myssl.cnf > ca-cert.pem
This string will create a ca-cert.pem file.
5. Generate a key file that will be used to generate a server certificate using the following command:
openssl req -newkey rsa:1024 -days 1000 -nodes -keyout server-key.pem -config myssl.cnf > server-req.pem
This string will create a server-key.pem file.
6. Generate the server certificate file using the following command:
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 a server-cert.pem file.
7. Generate a key file that will be used to generate a client certificate using the following command:
openssl req -newkey rsa:1024 -days 1000 -nodes -keyout client-key.pem -config myssl.cnf > client-req.pem
This string will create a client-key.pem file.
8. Generate a client certificate file using the following command:
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 a client-cert.pem file.
9. Move the generated files to your machine, if required, and go to the MySQL server installation directory. Open the my.ini file and, after the [mysqld] line, specify the location of the generated files ca-cert.pem, server-cert.pem, and server-key.pem using the following command:
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 your MySQL server and check whether it supports SSL by opening a new SQL document in Data Compare and running the following:
SHOW VARIABLES LIKE have_openssl
If the server returns YES, you can finish setting up your SSL client. If the response is NO or something does not work in the existing configuration, please refer to the MySQL documentation for instructions on setting up the required server from scratch.
Want to Find out More?
Overview
Request a demo
