数据结构
网络
关系数据库管理系统 (RDBMS)
操作系统
Java
MS Excel
iOS
HTML
CSS
Android
Python
C语言编程
C++
C#
MongoDB
MySQL
Javascript
PHP
物理学
化学
生物学
数学
英语
经济学
心理学
社会学
服装设计
法学如何在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 [email protected]:/etc/certificates # scp /etc/ certificates/client-key.pem [email protected]:/etc/certificates # scp /etc/ certificates/client-req.pem [email protected]:/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服务器，以保护数据免遭窃取，并保护数据免受黑客攻击。
更新于：2020年1月20日
901 次浏览
广告