Recently, SSL connection has been used in the project. Record that the environment is Windows 10 and Mysql version is 5.6.

See if SSL is supported

First, execute the following command on MySQL to query whether MySQL supports SSL:

mysql> SHOW VARIABLES LIKE 'have_ssl';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| have_ssl      | YES   |
+---------------+-------+
1 row in set (0.02 sec)

When have_ssl is YES, it means that MySQL service already supports SSL at this time. If DESABLE, then you need to enable SSL when starting MySQL service.

Creating SSL certificates and private keys using OpenSSL

• Download Win(xx)OpenSSL installation based on your operating system
• Create a new directory to store the generated certificates and private keys

//Generate a CA private key
openssl genrsa 2048 > cert/ca-key.pem
//Use the private key to generate a new digital certificate. When you execute this command, you will need to fill in some questions. Just fill in the blanks.
openssl req -sha1 -new -x509 -nodes -days 3650 -key ./cert/ca-key.pem > cert/ca-cert.pem
//Create a server-side RSA private key and digital certificate. This command generates a new private key (server-key.pem) and uses this new private key to generate a certificate request file (server-req.pem).
//This command also needs to answer a few questions, just fill them in. However, it should be noted that the A challenge password item needs to be empty.
openssl req -sha1 -newkey rsa:2048 -days 3650 -nodes -keyout cert/server-key.pem > cert/server-req.pem
//Converting the generated private key to RSA private key file format
openssl rsa -in cert/server-key.pem -out cert/server-key.pem
//Generate a server-side digital certificate using the originally generated CA certificate
openssl x509 -sha1 -req -in cert/server-req.pem -days 3650 -CA cert/ca-cert.pem -CAkey cert/ca-key.pem -set_serial 01 > cert/server-cert.pem
//Creating RSA Private Key and Digital Certificate for Client
openssl req -sha1 -newkey rsa:2048 -days 3650 -nodes -keyout cert/client-key.pem > cert/client-req.pem
//Converting the generated private key to RSA private key file format
openssl rsa -in cert/client-key.pem -out cert/client-key.pem
//Create a digital certificate for the client
openssl x509 -sha1 -req -in cert/client-req.pem -days 3650 -CA cert/ca-cert.pem -CAkey cert/ca-key.pem -set_serial 01 > cert/client-cert.pem

SSL Configuration

In the previous step, we have generated eight files, namely:

• ca-cert.pem: CA certificate, used to generate server/client digital certificates.
• ca-key.pem: CA private key used to generate server/client digital certificates.
• server-key.pem: RSA private key on the server side
• Ser-req.pem: Certificate request file on the server side for generating digital certificates on the server side.
• server-cert.pem: A digital certificate on the server side.
• client-key.pem: RSA private key of client
• client-req.pem: Client-side certificate request file for generating client-side digital certificates.
• client-cert.pem: Client's digital certificate.

Next we need to configure the server and the client, respectively.

• Server-side configuration
  The server side needs to use three files: CA certificate, RSA private key on the server side and digital certificate on the server side. We need to add the following contents in the [mysqld] configuration domain:

[mysqld]
ssl-ca=/etc/mysql/ca-cert.pem
ssl-cert=/etc/mysql/server-cert.pem
ssl-key=/etc/mysql/server-key.pem

Then we can change bind-address so that MySQL services can receive clients from all ip addresses, that is:

bind-address = *

When configured, we need to restart MySQL services
Finally, we add an account that needs to be logged in using SSL to verify that the SSL we configured is valid:

GRANT ALL PRIVILEGES ON *.* TO 'ssl_test'@'%' IDENTIFIED BY 'ssl_test' REQUIRE SSL;
FLUSH PRIVILEGES;

When configured, log in to MySQL using root

mysql --ssl-ca="D:/Program Files/OpenSSL-Win64/bin/cert/ca-cert.pem" --ssl-cert="D:/Program Files/OpenSSL-Win64/bin/cert/client-cert.pem" --ssl-key="D:/Program Files/OpenSSL-Win64/bin/cert/client-key.pem"  -u coisini -p

When the connection is successful, we execute the following instructions

\s

Executing the show variables like'% ssl%'statement will have the following output:

mysql> show variables like '%ssl%';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| have_openssl  | YES             |
| have_ssl      | YES             |
| ssl_ca        | ca.pem          |
| ssl_capath    |                 |
| ssl_cert      | server-cert.pem |
| ssl_cipher    |                 |
| ssl_crl       |                 |
| ssl_crlpath   |                 |
| ssl_key       | server-key.pem  |
+---------------+-----------------+
9 rows in set (0.01 sec)

JAVA configuration

• Use this command to generate the files required for java to connect using SSL:

keytool -importcert -alias MySQLCACert -file "D:\Program Files\OpenSSL-Win64\bin\cert\ca-cert.pem" -keystore truststore -storepass Password

• Configure the system environment variables with the generated files

Name: JAVA_OPTS 
Value: - Djavax.net.ssl.trustStore= "Local path to file generation in the previous step" - Djavax.net.ssl.trustStorePassword= "password"

• JDBC Configuration Connection

##jdbc.properties:
yxaq.dz=jdbc:mysql://127.0.0.1:3306/yxaqgl?verifyServerCertificate=true&useSSL=true&requireSSL=true