1. 背景

? ?*?在生產(chǎn)環(huán)境下，安全總是無法忽視的問題，數(shù)據(jù)庫(kù)安全則是重中之重，因?yàn)樗械臄?shù)據(jù)都存放在數(shù)據(jù)庫(kù)中

? ?*?當(dāng)使用非加密方式連接MySQL數(shù)據(jù)庫(kù)時(shí)，在網(wǎng)絡(luò)中傳輸?shù)乃行畔⒍际敲魑牡?#xff0c;可以被網(wǎng)絡(luò)中所有人截取，敏感信息可能被泄露。在傳送敏感信息（如密碼）時(shí)，可以采用SSL連接的方式。

2. MySQL 連接方式

? ?*?socket連接

? ?*?TCP非SSL連接

? ?*?SSL安全連接

3. SSL 簡(jiǎn)介

? *?SSL指的是SSL/TLS，其是一種為了在計(jì)算機(jī)網(wǎng)絡(luò)進(jìn)行安全通信的加密協(xié)議。假設(shè)用戶的傳輸不是通過SSL的方式，那么其在網(wǎng)絡(luò)中以明文的方式進(jìn)行傳輸，而這給別有用心的人帶來了可乘之機(jī)。所以，現(xiàn)在很多網(wǎng)站其實(shí)默認(rèn)已經(jīng)開啟了SSL功能，比如Facebook、Twtter、YouTube、淘寶等。

4. 環(huán)境 [ 關(guān)閉SeLinux ]

? ?*?system 環(huán)境

1 2 3 4 5 6 7 8

[root@MySQL?~]#?cat?/etc/redhat-release? CentOS?release?6.9?(Final) [root@MySQL?~]#?uname?-r 2.6.32-696.3.2.el6.x86_64 [root@MySQL?~]#?getenforce? Disabled

? ?*?MySQL 環(huán)境?[?MySQL 5.6安裝前面篇章已做詳細(xì)介紹 ]

? ? ? ??have_openssl 與?have_ssl 值都為DISABLED表示ssl未開啟

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45

[root@MySQL?mysql]#?mysql Welcome?to?the?MySQL?monitor.??Commands?end?with?;?or?\g. Your?MySQL?connection?id?is?3 Server?version:?5.6.36?MySQL?Community?Server?(GPL) Copyright?(c)?2000,?2017,?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>?select?version(); +-----------+ |?version()?| +-----------+ |?5.6.36????| +-----------+ 1?row?in?set?(0.00?sec) mysql>?show?variables?like?'have%ssl%'; +---------------+----------+ |?Variable_name?|?Value????| +---------------+----------+ |?have_openssl??|?DISABLED?| |?have_ssl??????|?DISABLED?| +---------------+----------+ 2?rows?in?set?(0.00?sec) mysql>?show?variables?like?'port'; +---------------+-------+ |?Variable_name?|?Value?| +---------------+-------+ |?port??????????|?3306??| +---------------+-------+ 1?row?in?set?(0.00?sec) mysql>?show?variables?like?'datadir'; +---------------+-------------------+ |?Variable_name?|?Value?????????????| +---------------+-------------------+ |?datadir???????|?/data/mysql_data/?| +---------------+-------------------+ 1?row?in?set?(0.00?sec)

5. 通過openssl 制作生成 SSL 證書

? ?*?生成一個(gè) CA 私鑰

1 2 3 4 5

[root@MySQL?~]#?openssl?genrsa?2048?>?ca-key.pem Generating?RSA?private?key,?2048?bit?long?modulus .............................+++ ....................+++ e?is?65537?(0x10001)

? ?*?通過 CA 私鑰生成數(shù)字證書

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

[root@MySQL?~]#?openssl?req?-new?-x509?-nodes?-days?3600?\ >??????????-key?ca-key.pem?-out?ca.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?[]:

? ?*?創(chuàng)建 MySQL 服務(wù)器 私鑰和請(qǐng)求證書

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26

[root@MySQL?~]#?openssl?req?-newkey?rsa:2048?-days?3600?\ >??????????-nodes?-keyout?server-key.pem?-out?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?[]:

? ?*?將生成的私鑰轉(zhuǎn)換為 RSA 私鑰文件格式

1 2	[root@MySQL?~]#?openssl?rsa?-in?server-key.pem?-out?server-key.pem writing?RSA?key

? ?*?用CA 證書來生成一個(gè)服務(wù)器端的數(shù)字證書

1 2 3 4 5

[root@MySQL?~]#?openssl?x509?-req?-in?server-req.pem?-days?3600?\ >??????????-CA?ca.pem?-CAkey?ca-key.pem?-set_serial?01?-out?server-cert.pem Signature?ok subject=/C=XX/L=Default?City/O=Default?Company?Ltd Getting?CA?Private?Key

? ?*?創(chuàng)建客戶端的 RSA 私鑰和數(shù)字證書

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26

[root@MySQL?~]#?openssl?req?-newkey?rsa:2048?-days?3600?\ >??????????-nodes?-keyout?client-key.pem?-out?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 to?be?sent?with?your?certificate?request A?challenge?password?[]: An?optional?company?name?[]:

? ?*?將生成的私鑰轉(zhuǎn)換為 RSA 私鑰文件格式

1 2	[root@MySQL?~]#?openssl?rsa?-in?client-key.pem?-out?client-key.pem writing?RSA?key

? ?*?用CA 證書來生成一個(gè)客戶端的數(shù)字證書

1 2 3 4 5

[root@MySQL?~]#?openssl?x509?-req?-in?client-req.pem?-days?3600?\ >??????????-CA?ca.pem?-CAkey?ca-key.pem?-set_serial?01?-out?client-cert.pem Signature?ok subject=/C=XX/L=Default?City/O=Default?Company?Ltd Getting?CA?Private?Key

? ?*?查看所有生成的SSL文件

1 2 3 4 5 6 7 8 9

[root@MySQL?~]#?ls?-l?*.pem -rw-r--r--?1?root?root?1675?Jun?24?14:16?ca-key.pem -rw-r--r--?1?root?root?1220?Jun?24?14:19?ca.pem -rw-r--r--?1?root?root?1090?Jun?24?14:29?client-cert.pem -rw-r--r--?1?root?root?1679?Jun?24?14:28?client-key.pem -rw-r--r--?1?root?root??952?Jun?24?14:28?client-req.pem -rw-r--r--?1?root?root?1090?Jun?24?14:24?server-cert.pem -rw-r--r--?1?root?root?1679?Jun?24?14:23?server-key.pem -rw-r--r--?1?root?root??952?Jun?24?14:20?server-req.pem

6. MySQL 配置啟動(dòng) SSL

? ?*?復(fù)制 CA 證書和服務(wù)端SSL文件至MySQL 數(shù)據(jù)目錄

1 2 3 4 5

[root@MySQL?~]#?cp?ca.pem?server-*.pem?/data/mysql_data?-v `ca.pem'?->?`/data/mysql_data/ca.pem' `server-cert.pem'?->?`/data/mysql_data/server-cert.pem' `server-key.pem'?->?`/data/mysql_data/server-key.pem' `server-req.pem'?->?`/data/mysql_data/server-req.pem'

? ?*?修改 MySQL 數(shù)據(jù)目錄的CA 證書和服務(wù)端 SSL 文件所屬用戶與組

1 2 3 4 5

[root@MySQL?~]#?chown?-v?mysql.mysql?/data/mysql_data/{ca,server*}.pem changed?ownership?of?`/data/mysql_data/ca.pem'?to?mysql:mysql changed?ownership?of?`/data/mysql_data/server-cert.pem'?to?mysql:mysql changed?ownership?of?`/data/mysql_data/server-key.pem'?to?mysql:mysql changed?ownership?of?`/data/mysql_data/server-req.pem'?to?mysql:mysql

? ?*?配置 MySQL 服務(wù)的配置文件 [/etc/my.cnf]

1 2 3 4

[mysqld] ssl-ca=/data/mysql_data/ca.pem ssl-cert=/data/mysql_data/server-cert.pem ssl-key=/data/mysql_data/server-key.pem

? ?*?重啟MySQL服務(wù)

1 2 3

[root@MySQL?~]#?/etc/init.d/mysqld?restart Shutting?down?MySQL..?SUCCESS!? Starting?MySQL.?SUCCESS!

? ?*?登陸查看SSL開啟狀態(tài)

? ? ? ?have_openssl 與?have_ssl 值都為YES表示ssl開啟成功

1 2 3 4 5 6 7 8

mysql>?show?variables?like?'have%ssl%'; +---------------+-------+ |?Variable_name?|?Value?| +---------------+-------+ |?have_openssl??|?YES???| |?have_ssl??????|?YES???| +---------------+-------+ 2?rows?in?set?(0.01?sec)

6. SSL連接測(cè)試

? ?*?創(chuàng)建用戶并指定 SSL 連接

1 2	mysql>?grant?all?on?*.*?to?'ssl_test'@'%'?identified?by?'123'?require?SSL; Query?OK,?0?rows?affected?(0.00?sec)

? ?*?通過密碼連接測(cè)試

1 2 3

[root@MySQL?~]#?mysql?-h?192.168.60.129?-ussl_test?-p'123' Warning:?Using?a?password?on?the?command?line?interface?can?be?insecure. ERROR?1045?(28000):?Access?denied?for?user?'ssl_test'@'192.168.60.129'?(using?password:?YES)

? ?*?通過客戶端密鑰與證書SSL + 密碼連接測(cè)試

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35

[root@MySQL?~]#?mysql?-h?192.168.60.129?-ussl_test??--ssl-cert=client-cert.pem?--ssl-key=client-key.pem? Welcome?to?the?MySQL?monitor.??Commands?end?with?;?or?\g. Your?MySQL?connection?id?is?9 Server?version:?5.6.36?MySQL?Community?Server?(GPL) Copyright?(c)?2000,?2017,?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>?\s -------------- mysql??Ver?14.14?Distrib?5.6.36,?for?linux-glibc2.5?(x86_64)?using??EditLine?wrapper Connection?id:???? 20 Current?database:? Current?user:????? ssl_test@192.168.60.129 SSL:??????????? Cipher?in?use?is?DHE-RSA-AES256-SHA Current?pager:???? stdout Using?outfile:?????'' Using?delimiter:?? ; Server?version:??????? 5.6.36?MySQL?Community?Server?(GPL) Protocol?version:? 10 Connection:???? 192.168.60.129?via?TCP/IP Server?characterset:?? latin1 Db?????characterset:?? latin1 Client?characterset:?? utf8 Conn.??characterset:? utf8 TCP?port:????? 3306 Uptime:???????? 16?min?38?sec Threads:?1??Questions:?35??Slow?queries:?0??Opens:?67??Flush?tables:?1??Open?tables:?60??Queries?per?second?avg:?0.035

7. 總結(jié)

以需求驅(qū)動(dòng)技術(shù)，技術(shù)本身沒有優(yōu)略之分，只有業(yè)務(wù)之分。

? ? ? 本文轉(zhuǎn)自asd1123509133 51CTO博客，原文鏈接：http://blog.51cto.com/lisea/1942216，如需轉(zhuǎn)載請(qǐng)自行聯(lián)系原作者

總結(jié)

以上是生活随笔為你收集整理的MySQL 5.6--------SSL连接最佳实战的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。