Confirm the mysql Server environment with the following command:

MariaDB [(none)]> Show variables like ‘% % SSL;

If I show,

+---------------+----------+
| Variable_name | Value    |
+---------------+----------+
| have_openssl  | DISABLED |
| have_ssl      | DISABLED |
| ssl_ca        |          |
| ssl_capath    |          |
| ssl_cert      |          |
| ssl_cipher    |          |
| ssl_key       |          |
+---------------+----------+
7 rows in set (0.01 sec)

indicates that Mysql Server does not support SSL.

Complete the configuration by modifying my.CNF
vim /etc/my.cnf

After adding SSL under [mySQld], save.
service mariadb stop
service mariadb start
Restart the mysql service

At this point, open the database again to query SSL status:

MariaDB [(none)]> show variables like '%ssl%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| have_openssl  | YES   |
| have_ssl      | YES   |
| ssl_ca        |       |
| ssl_capath    |       |
| ssl_cert      |       |
| ssl_cipher    |       |
| ssl_key       |       |
+---------------+-------+
7 rows in set (0.00 sec)

found that mysql already supports SSL

# Use OpenSSL to create certificates and private keys
First confirm the OpensSL installation

[root@mcu web]# openssl version
OpenSSL 1.0.1e-fips 11 Feb 2013
[root@mcu web]# 

Create a./ CERT directory for the generated certificates and private keys

[root@mcu mcu]# mkdir ./cert
[root@mcu mcu]# cd ./cert/

# create CA private key and CA certificate
Then, let’s first generate a CA private key:
openssl genrsa 2048 > ca-key.pem

Once we have a CA private key, we can then use this private key to generate a new digital certificate:
openssl req -sha1 -new -x509 -nodes -days 3650 -key ca-key.pem > ca-cert.pem
When executing this command, you will need to fill in some questions, just fill in whatever you want.

After executing the above command, we have a CA private key and a CA certificate.

# create server-side RSA private key and digital certificate
Next, we need to create the private key on the server side and a certificate request file with the following command:
openssl req -sha1 -newkey rsa:2048 -days 3650 -nodes -keyout server-key.pem > server-req.pem
This command will generate a new private key (server-key.pem), and will be used to generate a certificate request file (server-req.pem).
this command also needs to answer a few questions, just fill in. Note, however, that the term A challenge password needs to be empty.

Next, we need to convert the generated private key into RSA private key file format:
openssl rsa -in server-key.pem -out server-key.pem

As a final step, we need to use the original generated CA certificate to generate a server-side digital certificate:
openssl x509 -sha1 -req -in server-req.pem -days 3650 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > server-cert.pem

The command above creates a digital certificate file on the server side.

# Create the client’s RSA private key and digital certificate
Similar to the command executed on the server side, we also need to generate a private key and certificate request file for the client. The command is as follows. Challenge Password is left blank:
openssl req -sha1 -newkey rsa:2048 -days 3650 -nodes -keyout client-key.pem > client-req.pem

Similarly, we need to convert the generated private key to the RSA private key file format:
openssl rsa -in client-key.pem -out client-key.pem


Finally, we also need to create a digital certificate for the client:
openssl x509 -sha1 -req -in client-req.pem -days 3650 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > client-cert.pem


# SSL configuration
In the previous step, we have generated 8 files, which are respectively:
Ca cert. Pem: ca certificate for generating digital certificates on the server/client side.
ca-key. Pem: ca private key for generating digital certificates on the server side.
server-key.
client-key.pem: client RSA private key
client-req.pem: client certificate request file, used to generate client digital certificate.
client cert. Pem: client digital certificate.
Next we need to configure the server side and the client side respectively.

# server side configuration
The server side needs to use three files, namely: 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 field:

?

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 the Bind-Address so that the MySQL service can receive clients from all IP addresses, that is:
bind-address = *
When configured, we need to restart the MySQL service to enable configuration.
As a final step, we add an account that requires SSL to log in to verify that our SSL configuration is working:

copy code
The code is as follows:

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

When configured, use root to log into MySQL and execute the show Variables like ‘% SSL %’ statement with the following output:

MariaDB [(none)]> show variables like '%ssl%'
    -> ;
+---------------+----------------------------+
| Variable_name | Value                      |
+---------------+----------------------------+
| have_openssl  | YES                        |
| have_ssl      | YES                        |
| ssl_ca        | /etc/mysql/ca-cert.pem     |
| ssl_capath    |                            |
| ssl_cert      | /etc/mysql/server-cert.pem |
| ssl_cipher    |                            |
| ssl_key       | /etc/mysql/server-key.pem  |
+---------------+----------------------------+
7 rows in set (0.00 sec)

# client configuration
Client configuration is relatively simple. First we need to copy ca-CERt.pem, Client-cert.pem, and client-key.pem to the client host. Then we can execute the following command to use SSL to connect to MySQL service:
mysql --ssl-ca=/path/to/ca-cert.pem --ssl-key=/path/to/client-key.pem --ssl-key=/path/to/ client-key.pem-h host_name-u ssl_test-p
in addition to the above command line configuration for SSL, we can also use the configuration file. Add the following contents to the etc/my.cnf file:

[client]
default-character-set=utf8
ssl-ca=/path/to/ca-cert.pem
ssl-cert=/path/to/client-cert.pem
ssl-key=/path/to/client-key.pem

if the following error occurs

ERROR 2026 (HY000): SSL connection error: SSL_CTX_set_default_verify_paths failed

indicates that the SSL authentication file cannot be accessed. Place the CA file in an accessible directory.

If the following error occurs

ERROR 2026 (HY000): SSL connection error: error:14090086:SSL routines:SSL3_GET_SERVER_CERTIFICATE:certificate verify failed

maybe the Common Name field in the cert file the certificate of the server and the certificate of the client are identical. This field cannot be consistent

If the following error occurs

ERROR 1045 (28000): Access denied for user 'ssl_test'@'10.35.8.182' (using password: YES)

requires the following options to be added when connecting

--ssl-cipher=AES128-SHA

When the connection is successful, we execute the following instructions

MariaDB [(none)]> \s
--------------
mysql  Ver 15.1 Distrib 5.5.41-MariaDB, for Linux (x86_64) using readline 5.1

Connection id:          13
Current database:
Current user:           [email protected]
SSL:                    Cipher in use is AES128-SHA
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server:                 MariaDB
Server version:         5.5.44-MariaDB MariaDB Server
Protocol version:       10
Connection:             10.35.11.196 via TCP/IP
Server characterset:    utf8
Db     characterset:    utf8
Client characterset:    utf8
Conn.  characterset:    utf8
TCP port:               3306
Uptime:                 1 hour 6 min 31 sec

Threads: 6  Questions: 3135  Slow queries: 0  Opens: 13  Flush tables: 2  Open tables: 39  Queries per second avg: 0.785
--------------

MariaDB [(none)]> 

?If the output contains information such as SSL: Cipher in use is HE-RSA-AES256-SHA, then SSL has been used to connect.