MySQL 5.7 have_ssl 的SSL加密方法

MySQL 5.7 have_ssl 的SSL加密方法

MySQL 5.7.6或以上版本

(1)创建证书开启SSL验证

–安装openssl

yum install -y openssl

openssl version

OpenSSL 1.0.1e-fips 11 Feb 2013

–安装证书

/usr/local/mysql/bin/mysql_ssl_rsa_setup --datadir=/data/mysql/mysql3306/data

–修改权限

chown -R mysql:mysql /data/mysql/mysql3306/data

pwd

/data/mysql/mysql3306/data

[root@VM_45_133_centos Wed Jun 28 10:51:22 data]# ll

total 1024072

-rw-r----- 1 mysql mysql 56 Jun 19 17:56 auto.cnf

-rw------- 1 root root 1679 Jun 28 10:48 ca-key.pem

-rw-r–r-- 1 root root 1074 Jun 28 10:48 ca.pem

-rw-r–r-- 1 root root 1078 Jun 28 10:48 client-cert.pem

-rw------- 1 root root 1679 Jun 28 10:48 client-key.pem

-rw-r----- 1 mysql mysql 672 Jun 28 10:47 ib_buffer_pool

-rw-r----- 1 mysql mysql 1048576000 Jun 28 10:47 ibdata1

drwxr-x— 2 mysql mysql 4096 Jun 19 17:57 mysql

drwxr-x— 2 mysql mysql 4096 Jun 19 17:57 performance_schema

-rw------- 1 root root 1679 Jun 28 10:48 private_key.pem

-rw-r–r-- 1 root root 451 Jun 28 10:48 public_key.pem

drwxr-x— 2 mysql mysql 4096 Jun 23 10:48 school

-rw-r–r-- 1 root root 1078 Jun 28 10:48 server-cert.pem

-rw------- 1 root root 1675 Jun 28 10:48 server-key.pem

drwxr-x— 2 mysql mysql 12288 Jun 19 17:57 sys

-rw-r----- 1 mysql mysql 418 Jun 20 14:14 VM_45_133_centos.log

客户端连接需要的证书，当然不用证书也是可以的

-rw-r–r-- 1 root root 1074 Jun 28 10:48 ca.pem

-rw-r–r-- 1 root root 1078 Jun 28 10:48 client-cert.pem

-rw------- 1 root root 1679 Jun 28 10:48 client-key.pem

-rw------- 1 root root 1679 Jun 28 10:48 private_key.pem

服务器上的证书

-rw-r–r-- 1 root root 1074 Jun 28 10:48 ca.pem

-rw------- 1 root root 1679 Jun 28 10:48 ca-key.pem

-rw-r–r-- 1 root root 451 Jun 28 10:48 public_key.pem

-rw-r–r-- 1 root root 1078 Jun 28 10:48 server-cert.pem

-rw------- 1 root root 1675 Jun 28 10:48 server-key.pem

–修改my.cnf

#########SSL#############

ssl-ca = /data/mysql/mysql3306/data/ca.pem

ssl-cert = /data/mysql/mysql3306/data/server-cert.pem

ssl-key = /data/mysql/mysql3306/data/server-key.pem

(2)重启mysql

/etc/init.d/mysql stop

/etc/init.d/mysql start

–查看ssl参数状态，查看have_ssl，为YES，这表示已经开始支持SSL了

show global variables like ‘%ssl%’;

±--------------±-------------------------------------------+

| Variable_name | Value |

±--------------±-------------------------------------------+

| have_openssl | YES |

| have_ssl | YES |

| ssl_ca | /data/mysql/mysql3306/data/ca.pem |

| ssl_capath | |

| ssl_cert | /data/mysql/mysql3306/data/server-cert.pem |

| ssl_cipher | |

| ssl_crl | |

| ssl_crlpath | |

| ssl_key | /data/mysql/mysql3306/data/server-key.pem |

±--------------±-------------------------------------------+

show global status like ‘%ssl%’;

±-------------------------------±-------------------------+

| Variable_name | Value |

±-------------------------------±-------------------------+

| Com_show_processlist | 0 |

| Ssl_accept_renegotiates | 0 |

| Ssl_accepts | 0 |

| Ssl_callback_cache_hits | 0 |

| Ssl_cipher | |

| Ssl_cipher_list | |

| Ssl_client_connects | 0 |

| Ssl_connect_renegotiates | 0 |

| Ssl_ctx_verify_depth | 0 |

| Ssl_ctx_verify_mode | 0 |

| Ssl_default_timeout | 0 |

| Ssl_finished_accepts | 0 |

| Ssl_finished_connects | 0 |

| Ssl_server_not_after | Jun 26 02:48:05 2027 GMT |

| Ssl_server_not_before | Jun 28 02:48:05 2017 GMT |

| Ssl_session_cache_hits | 0 |

| Ssl_session_cache_misses | 0 |

| Ssl_session_cache_mode | Unknown |

| Ssl_session_cache_overflows | 0 |

| Ssl_session_cache_size | 0 |

| Ssl_session_cache_timeouts | 0 |

| Ssl_sessions_reused | 0 |

| Ssl_used_session_cache_entries | 0 |

| Ssl_verify_depth | 0 |

| Ssl_verify_mode | 0 |

| Ssl_version | |

±-------------------------------±-------------------------+

查看SSL的加密方式

show global variables like ‘tls_version’;

±--------------±--------------+

| Variable_name | Value |

±--------------±--------------+

| tls_version | TLSv1,TLSv1.1 |

±--------------±--------------+

(3)配置SSL用户

取消ssl验证

grant all privileges on . to abcssl@‘%’ identified by ‘123456’ require none;

alter user abcssl@‘%’ require none;

–强制ssl验证，即使设置了强制ssl，在登录时候使用–ssl-mode=disable依然可以避开ssl验证

grant all privileges on . to abcssl@‘%’ identified by ‘123465’ require ssl;

alter user abcssl@‘%’ require ssl;

查看是否开启强制用户使用SSL

select user,host,ssl_type,ssl_cipher from mysql.user;

±----------±----------±---------±-----------+

| user | host | ssl_type | ssl_cipher |

±----------±----------±---------±-----------+

| root | % | | |

| mysql.sys | localhost | | |

| abcssl | % | ANY | |

±----------±----------±---------±-----------+

(4)连接数据库的时候，带上SSL

不指定客户端证书方式

5.6

–ssl、–disable-ssl、–skip-ssl：在mysql5.7是将被废弃的选项，将来版本不再支持，建议使用–ssl-mode选项，

/usr/local/mysql/bin/mysql -uroot -p -h127.0.0.1 --ssl 默认为1

/usr/local/mysql/bin/mysql -uroot -p -h127.0.0.1 --ssl=0

/usr/local/mysql/bin/mysql -uroot -p -h127.0.0.1 --ssl=1 默认为1

/usr/local/mysql/bin/mysql -uroot -p -h127.0.0.1 --disable-ssl

/usr/local/mysql/bin/mysql -uroot -p -h127.0.0.1 --skip-ssl

5.7

/usr/local/mysql/bin/mysql -uroot -p -h127.0.0.1 --ssl-mode=disable

/usr/local/mysql/bin/mysql -uroot -p -h127.0.0.1 --ssl-mode=required 默认required

从另一台机器连接过去也可以ssl加密，表明不需要安装客户端证书的

/usr/local/mysql/bin/mysql -uroot -p -h10.105.45.133 --ssl-mode=required

指定客户端证书方式，5.6的方式，5.7也可以用

/usr/local/mysql/bin/mysql --ssl-ca=/data/mysql/mysql3306/data/ca.pem \

–ssl-cert=/data/mysql/mysql3306/data/client-cert.pem \

–ssl-key=/data/mysql/mysql3306/data/client-key.pem \

-uroot -p -h127.0.0.1

(5)连接验证连接是否用了ssl

\s == status

---

/usr/local/mysql/bin/mysql Ver 14.14 Distrib 5.7.18, for linux-glibc2.5 (x86_64) using EditLine wrapper 客户端版本

Connection id: 69

Current database:

Current user: root@127.0.0.1

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

Current pager: stdout

Using outfile: ‘’

Using delimiter: ;

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

Protocol version: 10

Connection: 127.0.0.1 via TCP/IP

Server characterset: utf8mb4

Db characterset: utf8mb4

Client characterset: utf8

Conn. characterset: utf8

TCP port: 3306

Uptime: 28 min 14 sec

Threads: 2 Questions: 1755 Slow queries: 0 Opens: 114 Flush tables: 1 Open tables: 102 Queries per second avg: 1.036

---

JDBC客户端的解决方法

连接字符串url中加入ssl=true或false：

url=jdbc:mysql://127.0.0.1:3306/framework?characterEncoding=utf8&useSSL=true