MySQL-通过SSL加密连接数据库详解

 

默认创建的用户,既可以通过帐号密码登录,也可以通过--SSL来登录,还可以通过密钥来登录

 

查看当前数据库是否启用SSL: [5.7之后默认启动,5.6需要手动启动]

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

    -> ;

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

| Variable_name | Value          |

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

| have_openssl  | YES            |

| have_ssl      | YES            |

| ssl_ca        | ca.pem         |

| ssl_capath    |                |

| ssl_cert      | server-cert.pem |

| ssl_cipher    |                |

| ssl_crl       |                |

| ssl_crlpath   |                |

| ssl_key       |server-key.pem  |

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

9 rows in set (0.01 sec)

 

二进制包安装过程:

shell> groupadd mysql

shell> useradd -r -g mysql mysql

shell> cd /usr/local

shell> tar zxvf /path/to/mysql-VERSION-OS.tar.gz

shell> ln -s full-path-to-mysql-VERSION-OS mysql

shell> cd mysql

shell> mkdir mysql-files

shell> chmod 770 mysql-files

shell> chown -R mysql .

shell> chgrp -R mysql .

shell> bin/mysql_install_db --user=mysql   # Before MySQL 5.7.6

shell> bin/mysqld --initialize --user=mysql # MySQL5.7.6 and up

shell> bin/mysql_ssl_rsa_setup             # MySQL 5.7.6 and up

shell> chown -R root .

shell> chown -R mysql data mysql-files

shell> bin/mysqld_safe --user=mysql &

# Next command is optional

shell> cp support-files/mysql.server/etc/init.d/mysql.server

 

安装完SSL之后产生的秘钥文件:

[root@node232 data]# pwd

/usr/local/mysql/data

[root@node232 data]# ll *.pem

-rw-------. 1 mysql root 1675 6月  14 23:07 ca-key.pem  #证书验证中心

-rw-r--r--. 1 mysql root 1070 6月  14 23:07 ca.pem    #证书验证中心

-rw-r--r--. 1 mysql root 1078 6月  14 23:07 client-cert.pem    #客户端证书

-rw-------. 1 mysql root 1679 6月  14 23:07 client-key.pem    #客户端密钥

-rw-------. 1 mysql root 1679 6月  14 23:07 private_key.pem

-rw-r--r--. 1 mysql root  451 6月  14 23:07 public_key.pem

-rw-r--r--. 1 mysql root 1078 6月  14 23:07 server-cert.pem #服务器端证书

-rw-------. 1 mysql root 1679 6月  14 23:07 server-key.pem    #服务器端密钥

 

客户端通过SSL来连接数据库:

[root@node232 data]# mysql -h 172.16.1.232 -uroot-p'Rscpass123.'   --ssl

mysql: [Warning] Using a password on the command lineinterface can be insecure.

Welcome to the MySQL monitor.  Commands end with ;or \g.

Your MySQL connection id is 9

Server version: 5.7.9 MySQL Community Server (GPL)

 

Copyright (c) 2000, 2015, Oracle and/or its affiliates.All rights reserved.

 

Oracle is a registered trademark of Oracle Corporationand/or its

affiliates. Other names may be trademarks of theirrespective

owners.

 

Type 'help;' or '\h'for help. Type '\c' to clear the current input statement.

 

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

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

mysql  Ver 14.14 Distrib 5.7.9, for linux-glibc2.5(x86_64) using  EditLine wrapper

 

Connection id:               9

Current database:       

Current user:               root@node232

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

Current pager:               stdout

Using outfile:               ''

Using delimiter:        ;

Server version:               5.7.9 MySQL Community Server (GPL)

Protocol version:        10

Connection:               172.16.1.232 via TCP/IP

Server characterset:        latin1

Db     characterset:       latin1

Client characterset:        utf8

Conn.  characterset:        utf8

TCP port:               3306

Uptime:                       34 min 1 sec

 

Threads: 2  Questions: 37  Slow queries:0  Opens: 115  Flush tables: 1  Open tables: 108  Queriesper second avg: 0.018

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

 

 

再次使用root账户登录,并指定秘钥文件

[root@node232 data]# mysql  -h172.16.1.232 -uroot -p'Rscpass123.'--ssl-cert=/usr/local/mysql/data/client-cert.pem --ssl-key=/usr/local/mysql/data/client-key.pem

mysql: [Warning] Usinga password on the command line interface can be insecure.

Welcome to the MySQLmonitor.  Commands end with ; or \g.

Your MySQL connectionid is 12

Server version: 5.7.9MySQL Community Server (GPL)

 

Copyright (c) 2000,2015, Oracle and/or its affiliates. All rights reserved.

 

Oracle is a registeredtrademark of Oracle Corporation and/or its

affiliates. Other namesmay be trademarks of their respective

owners.

 

Type'help;' or '\h' for help. Type'\c' to clear the current input statement.

 

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

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

mysql  Ver 14.14Distrib 5.7.9, for linux-glibc2.5 (x86_64) using  EditLine wrapper

 

Connection id:               12

Current database:      

Current user:               root@node232

SSL:                       Cipher inuse is DHE-RSA-AES256-SHA

Current pager:               stdout

Using outfile:               ''

Using delimiter:       ;

Server version:               5.7.9 MySQL Community Server(GPL)

Protocol version:       10

Connection:               172.16.1.232 via TCP/IP

Servercharacterset:        latin1

Db    characterset:        latin1

Clientcharacterset:        utf8

Conn. characterset:        utf8

TCP port:               3306

Uptime:                       40 min 49sec

 

Threads: 2 Questions: 78  Slow queries: 0  Opens: 116  Flush tables:1  Open tables: 109  Queries per second avg: 0.031

 

说明:默认创建的用户,即可以通过用户名密码登录,也可以通过--ssl的方式登录,还可以通过指定密钥文件的方式登录

 

下面创建一个用户,只能通过密钥文件才能登录数据库:

 

创建一个用户,只能通过SSL才能登录数据库:

(root@DB)[(none)]>create user 'ssl'@'%' identified by'Rscpass123.'

    -> ;

Query OK, 0 rowsaffected (0.00 sec)

 

(root@DB)[(none)]> grant all on *.* to'ssl'@'%' require x509;

Query OK, 0 rowsaffected, 1 warning (0.00 sec)

 

[root@node232 ~]#mysql  -ussl -p'Rscpass123.' -h 172.16.1.232--ssl-cert=/usr/local/mysql/data/client-cert.pem--ssl-key=/usr/local/mysql/data/client-key.pem

mysql: [Warning] Usinga password on the command line interface can be insecure.

Welcome to the MySQLmonitor.  Commands end with ; or \g.

Your MySQL connectionid is 30

Server version: 5.7.9MySQL Community Server (GPL)

 

Copyright (c) 2000,2015, Oracle and/or its affiliates. All rights reserved.

 

Oracle is a registeredtrademark of Oracle Corporation and/or its

affiliates. Other namesmay be trademarks of their respective

owners.

 

Type'help;' or '\h' for help. Type'\c' to clear the current input statement.

 

(ssl@DB)[(none)]> \s

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

mysql  Ver 14.14Distrib 5.7.9, for linux-glibc2.5 (x86_64) using  EditLine wrapper

 

Connection id:               30

Current database:      

Current user:               ssl@node232

SSL:                       Cipher inuse is DHE-RSA-AES256-SHA

Current pager:               stdout

Using outfile:               ''

Using delimiter:       ;

Server version:               5.7.9 MySQL Community Server(GPL)

Protocol version:       10

Connection:               172.16.1.232 via TCP/IP

Servercharacterset:        latin1

Db    characterset:        latin1

Clientcharacterset:        utf8

Conn. characterset:        utf8

TCP port:               3306

Uptime:                       58 min 3sec

 

Threads: 2 Questions: 130  Slow queries: 0  Opens: 124  Flush tables:1  Open tables: 117  Queries per second avg: 0.037

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

如果指定用户只能通过密钥才能登录,则下面的登录会失败:

[root@node232 ~]#mysql  -ussl -p'Rscpass123.' -h 172.16.1.232 --ssl

mysql: [Warning] Usinga password on the command line interface can be insecure.

ERROR 1045 (28000):Access denied for user 'ssl'@'node232'(using password: YES)

[root@node232 ~]#mysql  -ussl -p'Rscpass123.' -h 172.16.1.232

mysql: [Warning] Usinga password on the command line interface can be insecure.

ERROR 1045 (28000):Access denied for user 'ssl'@'node232'(using password: YES)

 多实例环境单独安装SSL

多实例环境:3307

数据目录:/usr/local/mysql3307/data

 

[root@node232 ~]# mysql_ssl_rsa_setup--datadir=/usr/local/mysql3307/data --user=mysql

Generating a 2048 bit RSA private key

.................................+++

..........................................................................................................................+++

writing new private key to 'ca-key.pem'

-----

Generating a 2048 bit RSA private key

.............................................+++

..........................+++

writing new private key to'server-key.pem'

-----

Generating a 2048 bit RSA private key

.+++

........................+++

writing new private key to'client-key.pem'

 

查看生成的SSL证书文件:

[root@node232 data]# pwd

/usr/local/mysql3307/data

[root@node232 data]# ll *.pem

-rw-------. 1 root root 1679 6月  17 16:22 ca-key.pem

-rw-r--r--. 1 root root 1070 6月  17 16:22 ca.pem

-rw-r--r--. 1 root root 1078 6月  17 16:22 client-cert.pem

-rw-------. 1 root root 1675 6月  17 16:22 client-key.pem

-rw-------. 1 root root 1675 6月  17 16:22 private_key.pem

-rw-r--r--. 1 root root  451 6月  17 16:22 public_key.pem

-rw-r--r--. 1 root root 1078 6月  17 16:22 server-cert.pem

-rw-------. 1 root root 1679 6月  17 16:22 server-key.pem

[root@node232 data]# chown mysql.mysql ./*.pem

[root@node232 data]# ll *.pem

-rw-------. 1 mysql mysql 1679 6月  17 16:22 ca-key.pem

-rw-r--r--. 1 mysql mysql 1070 6月  17 16:22 ca.pem

-rw-r--r--. 1 mysql mysql 1078 6月  17 16:22 client-cert.pem

-rw-------. 1 mysql mysql 1675 6月  17 16:22 client-key.pem

-rw-------. 1 mysql mysql 1675 6月  17 16:22 private_key.pem

-rw-r--r--. 1 mysql mysql  451 6月  17 16:22 public_key.pem

-rw-r--r--. 1 mysql mysql 1078 6月  17 16:22 server-cert.pem

-rw-------. 1 mysql mysql 1679 6月  17 16:22 server-key.pem

 

登录多实例环境:
[root@node232 data]# mysqld_multistart 3307

[root@node232 data]# mysqld_multi report

Reporting MySQL servers

MySQL server from group: mysqld3307 is running

 

[root@node232 data]# mysql -S/usr/local/mysql3307/mysql3307.sock --ssl

Welcome to the MySQL monitor.  Commands end with ;or \g.

Your MySQL connection id is 4

Server version: 5.7.9-log MySQL Community Server (GPL)

 

Copyright (c) 2000, 2015, Oracle and/or its affiliates.All rights reserved.

 

Oracle is a registered trademark of Oracle Corporationand/or its

affiliates. Other names may be trademarks of theirrespective

owners.

 

Type 'help;' or '\h'for help. Type '\c' to clear the current input statement.

 

You are enforcing ssl conection via unix socket. Pleaseconsider

switching ssl off as it does not make connection via unixsocket

any more secure.

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

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

mysql  Ver 14.14 Distrib 5.7.9, for linux-glibc2.5(x86_64) using  EditLine wrapper

 

Connection id:               4

Current database:       

Current user:               root@localhost

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

Current pager:               stdout

Using outfile:               ''

Using delimiter:        ;

Server version:               5.7.9-log MySQL Community Server (GPL)

Protocol version:        10

Connection:               Localhost via UNIX socket

Server characterset:        latin1

Db     characterset:       latin1

Client characterset:        utf8

Conn.  characterset:        utf8

UNIX socket:               /usr/local/mysql3307/mysql3307.sock

Uptime:                       1 min 3 sec

 

Threads: 1  Questions: 14  Slow queries:0  Opens: 108  Flush tables: 1  Open tables: 101  Queriesper second avg: 0.222

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

(root@DB)[(none)]> show variables like'%port%'

    -> ;

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

| Variable_name           | Value |

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

| innodb_support_xa        | ON   |

| large_files_support      | ON   |

| port                    | 3307  |

| report_host             |       |

| report_password         |       |

| report_port             | 3307  |

| report_user             |       |

| require_secure_transport | OFF   |

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

 

查看数据库是否开启的SSL:

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

    -> ;

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

| Variable_name | Value |

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

| have_openssl  | YES   |

| have_ssl      | YES   |

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

2 rows in set (0.01 sec)

 

创建一个用户,只有通过密钥才能登录数据库:

 

(root@DB)[(none)]> create user'ssl'@'%' identified by'Rscpass123.'

    -> ;

Query OK, 0 rows affected (0.05 sec)

 

(root@DB)[(none)]> grant all on *.* to'ssl'@'%' require x509;

Query OK, 0 rows affected, 1 warning (0.01 sec)

 

通过密钥登录数据库:

[root@node232 data]# mysql -ussl-p'Rscpass123.' -h172.16.1.232 -P 3307 --ssl-cert=/usr/local/mysql3307/data/client-cert.pem--ssl-key=/usr/local/mysql3307/data/client-key.pem

mysql: [Warning] Using a password on the command lineinterface can be insecure.

Welcome to the MySQL monitor.  Commands end with ;or \g.

Your MySQL connection id is 6

Server version: 5.7.9-log MySQL Community Server (GPL)

 

Copyright (c) 2000, 2015, Oracle and/or its affiliates.All rights reserved.

 

Oracle is a registered trademark of Oracle Corporationand/or its

affiliates. Other names may be trademarks of theirrespective

owners.

 

Type 'help;' or '\h'for help. Type '\c' to clear the current input statement.

 

(ssl@DB)[(none)]> \s

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

mysql  Ver 14.14 Distrib 5.7.9, for linux-glibc2.5(x86_64) using  EditLine wrapper

 

Connection id:               6

Current database:       

Current user:               ssl@node232

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

Current pager:               stdout

Using outfile:               ''

Using delimiter:        ;

Server version:               5.7.9-log MySQL Community Server (GPL)

Protocol version:        10

Connection:               172.16.1.232 via TCP/IP

Server characterset:        latin1

Db     characterset:       latin1

Client characterset:        utf8

Conn.  characterset:        utf8

TCP port:               3307

Uptime:                       6 min 31 sec

 

Threads: 1  Questions: 35  Slow queries:0  Opens: 116  Flush tables: 1  Open tables: 109  Queriesper second avg: 0.089

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


分割线
打赏
YWSOS.COM 平台代运维解决方案
 评论
 发表评论
姓   名:

Powered by AKCMS