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

linuxmysqldatabase

在本教程中,我将解释如何使用 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

After this configuration and set up now you can be able to connect to MySQL server from the client side using the SSL key to protect the data from stealing data and which also secures the data from hackers.


相关文章