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
-------------
