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.
