如何在 Linux 上为 MySQL 服务器和客户端设置 SSL

有志者，事竟成！如果你在学习数据库，那么本文《如何在 Linux 上为 MySQL 服务器和客户端设置 SSL》，就很适合你！文章讲解的知识点主要包括，若是你对本文感兴趣，或者是想搞懂其中某个知识点，就请你继续往下看吧~

在本教程中，我将介绍如何使用 SSH 连接进行加密，建立与 MySQL 服务器的安全连接，从而使数据库中的数据安全，黑客无法窃取数据。 SSL用于验证SSL证书的方式，可以防范网络钓鱼攻击。这还将向您展示如何在 MySQL 服务器上启用 SSL。

启用 SSL 支持

连接到 MySQL 服务器并检查 MySQL 服务器的 SSL 状态

# mysql -u root -p
mysql> show variables like '%ssl%';
Output:
+---------------+----------+
| Variable_name | Value    |
+---------------+----------+
| have_openssl | DISABLED  |
| have_ssl     |  DISABLED |
| ssl_ca       |           |
| ssl_capath   |           |
| ssl_cert     |           |
| ssl_cipher   |           |
| ssl_key      |           |
+---------------+----------+
7 rows in set (0.00 sec)
mysql> \q
Bye

为 MySQL 生成 SSL 证书

创建用于存储证书文件的目录

# mkdir /etc/certificates
# cd /etc/certificates

生成服务器证书

# openssl genrsa 2048 > ca-key.pem
Generating RSA private key, 2048 bit long modulus
...................................................................................+++
..........+++
e is 65537 (0x10001)
# openssl req -newkey rsa:2048 -days 1000 -nodes -keyout server-key.pem > server-req.pem
Generating a 2048 bit RSA private key
..................+++
..............................................................................................+++
writing new private key to 'server-key.pem'
You are about to be asked to enter information that will be incorporated
into your certificate request.
What you are about to enter is what is called a Distinguished Name or a DN.
There are quite a few fields but you can leave some blank
For some fields there will be a default value,
If you enter '.', the field will be left blank.
-----
Country Name (2 letter code) [XX]:
State or Province Name (full name) []:
Locality Name (eg, city) [Default City]:
Organization Name (eg, company) [Default Company Ltd]:
Organizational Unit Name (eg, section) []:
Common Name (eg, your name or your server's hostname) []:
Email Address []:
Please enter the following 'extra' attributes
to be sent with your certificate request
A challenge password []:
An optional company name []:
# openssl x509 -req -in server-req.pem -days 1000 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 > server-cert.pem
Signature ok
subject=/C=XX/L=Default City/O=Default Company Ltd
Error opening CA Certificate ca-cert.pem
139991633303368:error:02001002:system library:fopen:No such file or directory:bss_file.c:398:fopen('ca-cert.pem','r')
139991633303368:error:20074002:BIO routines:FILE_CTRL:system lib:bss_file.c:400:
unable to load certificate
Generating client certificates

# openssl req -newkey rsa:2048 -days 1000 -nodes -keyout client-key.pem > client-req.pem
Generating a 2048 bit RSA private key
...............................................+++
.................+++
writing new private key to 'client-key.pem'
-----
You are about to be asked to enter information that will be incorporated
into your certificate request.
What you are about to enter is what is called a Distinguished Name or a DN.
There are quite a few fields but you can leave some blank
For some fields there will be a default value,
If you enter '.', the field will be left blank.
-----
Country Name (2 letter code) [XX]:
State or Province Name (full name) []:
Locality Name (eg, city) [Default City]:
Organization Name (eg, company) [Default Company Ltd]:
Organizational Unit Name (eg, section) []:
Common Name (eg, your name or your server's hostname) []:
Email Address []:
Please enter the following 'extra' attributes openssl x509 -req -in client-req.pem -days 1000 -CA ca-# cert.pem -CAkey ca-key.pem -set_serial 01 > client-cert.pem
Signature ok
subject=/C=XX/L=Default City/O=Default Company Ltd
Error opening CA Certificate ca-cert.pem
140327140685640:error:02001002:system library:fopen:No such file or directory:bss_file.c:398:fopen('ca-cert.pem','r')
140327140685640:error:20074002:BIO routines:FILE_CTRL:system lib:bss_file.c:400:
unable to load certificate to be sent with your certificate request
A challenge password []:
An optional company name []:

现在打开 my.cnf 文件并添加证书

# vi /etc/my.cnf
[mysqld]
ssl-ca=/etc/certificates/cacert.pem
ssl-cert=/etc/certificates/server-cert.pem
ssl-key=/etc/certificates/server-key.pem

重启MySQL服务器并检查证书状态

#service mysqld restart
#mysql -uroot -p
mysql>show variables like '%ssl%';
+---------------+-----------------------------------+
| Variable_name |        Value                      |
+---------------+-----------------------------------+
| have_openssl  |          YES                      |
| have_ssl      |          YES                      |
| ssl_ca        |/etc/certificates/cacert.pem       |
| ssl_capath    |                                   |
| ssl_cert      | /etc/certificates/server-cert.pem |
| ssl_cipher    |                                   |
| ssl_key       | /etc/certificates/server-key.pem  |
+---------------+-----------------------------------+
7 rows in set (0.00 sec)

创建具有 SSL 访问权限的用户

mysql> GRANT ALL PRIVILEGES ON *.* TO ‘ssl_user’@’%’ IDENTIFIED BY ‘password’ REQUIRE SSL;
mysql> FLUSH PRIVILEGES;

为 MySQL 客户端配置 SSL

从服务器端，我们需要将 client-cert.pem client-key.pem client-req.pem 从服务器复制到客户端。

# scp /etc/ certificates/client-cert.pem root@192.168.87.158:/etc/certificates
# scp /etc/ certificates/client-key.pem root@192.168.87.158:/etc/certificates
# scp /etc/ certificates/client-req.pem root@192.168.87.158:/etc/certificates

文件传输到客户端后，将连接到客户端并尝试使用 SSL 证书连接到 MySQL。

# mysql --ssl-ca=ca-cert.pem --ssl-cert=client-cert.pem --ssl-key=client-key.pem -h 192.168.87.156 -u ssluser -p
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.1.73 Source distribution
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> status
--------------
mysql Ver 14.14 Distrib 5.1.73, for redhat-linux-gnu (x86_64) using readline 5.1
Connection id: 3
Current database:
Current user: root@localhost
SSL: Clipher in use is DHE-RSA-AES256-SHA
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.1.73 Source distribution
Protocol version: 10
Connection: 192.168.87.158 via TCP/IP
Server characterset: latin1
Db characterset: latin1
Client characterset: latin1
Conn. characterset: latin1
UNIX socket: /var/lib/mysql/mysql.sock
Uptime: 11 min 13 sec
Threads: 1 Questions: 8 Slow queries: 0 Opens: 15 Flush tables: 1 Open tables: 8 Queries per second avg: 0.11
-------------

稍后，在 /etc/my.cnf 文件中添加设置，以便永久连接到 MySQL 服务器时，我们应该使用 SSL 进行连接。

# vi /etc/my.cnf
[client]
ssl-ca=/etc/certificates/ client-cert.pem
ssl-cert=/etc/certificates/client-cert.pem
ssl-key=/etc/certificates/client-key.pem

完成此配置和设置后，您现在可以使用 SSL 密钥从客户端连接到 MySQL 服务器，以保护数据不被窃取，同时也可以保护数据免受黑客攻击。

终于介绍完啦！小伙伴们，这篇关于《如何在 Linux 上为 MySQL 服务器和客户端设置 SSL》的介绍应该让你收获多多了吧！欢迎大家收藏或分享给更多需要学习的朋友吧~golang学习网公众号也会发布数据库相关知识，快来关注吧！