mysql 使用加密连接ssl

ssl 作为安全链接。对于mysql 数据库的运行。和维护。都有一定的保护作用。

开启ssl功能：

在[mysqld]下面添加ssl 则会开启ssl 功能。

没开启的状态：

(root@localhost) [(none)]> show variables like '%ssl%'

+---------------+----------+

| Variable_name | Value |

+---------------+----------+

| have_openssl | DISABLED |

| have_ssl | DISABLED |

| ssl_ca | |

| ssl_capath | |

| ssl_cert | |

| ssl_cipher | |

| ssl_crl | |

| ssl_crlpath | |

| ssl_key | |

+---------------+----------+

9 rows in set (0.00 sec)

(root@localhost) [(none)]> SHOW VARIABLES LIKE 'have_ssl';

+---------------+----------+

| Variable_name | Value |

+---------------+----------+

| have_ssl | DISABLED |

+---------------+----------+

1 row in set (0.01 sec)

客户端

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| Ssl_cipher | |

+---------------+-------+

1 row in set (0.00 sec)

开启后的状态

(root@localhost) [(none)]> show variables like '%SSL%'

+---------------+-----------------------------------------+

| Variable_name | Value |

+---------------+-----------------------------------------+

| have_openssl | YES |

| have_ssl | YES |

| ssl_ca | /var/lib/mysql/newcerts/ca-cert.pem |

| ssl_capath | |

| ssl_cert | /var/lib/mysql/newcerts/server-cert.pem |

| ssl_cipher | |

| ssl_crl | |

| ssl_crlpath | |

| ssl_key | /var/lib/mysql/newcerts/server-key.pem |

+---------------+-----------------------------------------+

(root@localhost) [(none)]> SHOW VARIABLES LIKE 'have_ssl';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| have_ssl | YES |

+---------------+-------+

1 row in set (0.00 sec)

查看当前是否是加密连接：

(root@localhost) [(none)]> \s

--------------

mysql Ver 14.14 Distrib 5.6.21, for Linux (x86_64) using EditLine wrapper

Connection id: 7

Current database:

Current user: root@localhost

SSL: Not in use

Current pager: stdout

Using outfile: ''

Using delimiter: ;

Server version: 5.6.21-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial)

Protocol version: 10

Connection: Localhost via UNIX socket

Server characterset: latin1

Db characterset: latin1

Client characterset: utf8

Conn. characterset: utf8

UNIX socket: /var/lib/mysql/mysql.sock

Uptime: 30 sec

Threads: 5 Questions: 774 Slow queries: 0 Opens: 208 Flush tables: 1 Open tables: 201 Queries per second avg: 25.800

--------------

(root@localhost) [(none)]>

mysql Ver 14.14 Distrib 5.6.21, for Win64 (x86_64)

Connection id: 79

Current database:

SSL: Cipher in use is DHE-RSA-AES256-SHA

Using delimiter: ;

Server version: 5.6.21-enterprise-commercial-advanced MySQL Enterprise S

erver - Advanced Edition (Commercial)

Protocol version: 10

Connection: 192.168.154.190 via TCP/IP

Server characterset: latin1

Db characterset: latin1

Client characterset: gbk

Conn. characterset: gbk

TCP port: 3306

Uptime: 1 min 6 sec

Threads: 6 Questions: 1689 Slow queries: 0 Opens: 251 Flush tables: 1 Open

tables: 244 Queries per second avg: 25.590

--------------

SSL: Cipher in use is DHE-RSA-AES256-SHA 这个就说明是加密连接。

配置ssl 加密连接步骤：

1、安装openssl

2、开启mysql 的ssl

在my.cnf 中的mysqld加入ssl选项。

2、生成秘钥

# Create clean environment

shell> rm -rf newcerts

shell> mkdir newcerts && cd newcerts

# Create CA certificate

shell> openssl genrsa 2048 > ca-key.pem

-key ca-key.pem -out ca-cert.pem

# Create server certificate, remove passphrase, and sign it

# server-cert.pem = public key, server-key.pem = private key

shell> openssl req -newkey rsa:2048 -days 3600 \

-nodes -keyout server-key.pem -out server-req.pem

shell> openssl rsa -in server-key.pem -out server-key.pem

shell> openssl x509 -req -in server-req.pem -days 3600 \

-CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 -out server-cert.pem

# Create client certificate, remove passphrase, and sign it

# client-cert.pem = public key, client-key.pem = private key

shell> openssl req -newkey rsa:2048 -days 3600 \

-nodes -keyout client-key.pem -out client-req.pem

shell> openssl rsa -in client-key.pem -out client-key.pem

shell> openssl x509 -req -in client-req.pem -days 3600 \

-CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 -out client-cert.pem

After generating the certificates, verify them:

shell> openssl verify -CAfile ca-cert.pem server-cert.pem client-cert.pem

server-cert.pem: OK

client-cert.pem: OK

3、生成好的秘钥分别拷贝到客户端上。

F:\mysql-advanced-5.6.21-winx64\cet 譬如放到这个windows 的目录下。

4、配置服务端

#ssl

ssl

ssl-ca=/var/lib/mysql/newcerts/ca-cert.pem

ssl-cert=/var/lib/mysql/newcerts/server-cert.pem

ssl-key=/var/lib/mysql/newcerts/server-key.pem

在 my.cnf 下的 [mysqld] 下添加。重启服务器。

添加ssl用户：

这个用户必须用ssl 方式连接。

5、配置客户端

配置my.ini

在[mysql]下配置。

[mysql]

ssl-ca=F:\mysql-advanced-5.6.21-winx64\cet\ca-cert.pem

ssl-cert=F:\mysql-advanced-5.6.21-winx64\cet\client-cert.pem

ssl-key=F:\mysql-advanced-5.6.21-winx64\cet\client-key.pem

配置好以后。

使用建立好的账户登录：

6、登录以后就可以看到。

mysql Ver 14.14 Distrib 5.6.21, for Win64 (x86_64)

Connection id: 1564

Current database:

SSL: Cipher in use is DHE-RSA-AES256-SHA

Using delimiter: ;

Server version: 5.6.21-enterprise-commercial-advanced MySQL Enterprise S

erver - Advanced Edition (Commercial)

Protocol version: 10

Connection: 192.168.154.190 via TCP/IP

Server characterset: latin1

Db characterset: latin1

Client characterset: gbk

Conn. characterset: gbk

TCP port: 3306

Uptime: 17 min 11 sec

Threads: 6 Questions: 40891 Slow queries: 0 Opens: 344 Flush tables: 1 Open

tables: 337 Queries per second avg: 39.661

--------------

到此配置成功。

注意：秘钥的权限一定要对：一般为mysql:mysql