???????????????????????????????????????????????????????????????????????????????????????? mysql 5.7——–ssl連接最佳實戰(zhàn)
1. 背景
? ?*?在生產環(huán)境下，安全總是無法忽視的問題，數據庫安全則是重中之重，因為所有的數據都存放在數據庫中
? ?*?當使用非加密方式連接mysql數據庫時，在網絡中傳輸的所有信息都是明文的，可以被網絡中所有人截取，敏感信息可能被泄露。在傳送敏感信息（如密碼）時，可以采用SSL連接的方式。
? ? ?* ?版本小于5.7.6時按照 MySQL 5.6 SSL配置的方式進行。
?
2. MySQL 連接方式
?
? ?*?socket連接
?
? ?*?TCP非SSL連接
?
? ?*?SSL安全連接
?
? ? ? ? * SSL + 密碼連接 [version > MySQL 5.7.5]
?
?* SSL + 密碼 + 密鑰連接
?
3. SSL 簡介
?
? *?SSL指的是SSL/TLS，其是一種為了在計算機網絡進行安全通信的加密協(xié)議。假設用戶的傳輸不是通過SSL的方式，那么其在網絡中以明文的方式進行傳輸，而這給別有用心的人帶來了可乘之機。所以，現在很多網站其實默認已經開啟了SSL功能，比如Facebook、Twtter、YouTube、淘寶等。
4. 環(huán)境 [ 關閉SeLinux ]
*?system 環(huán)境
[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.7安裝前面篇章已做詳細介紹 ]
?have_openssl 與?have_ssl 值都為DISABLED表示ssl未開啟
[root@MySQL?~]#?mysql?-p’123′
mysql:?[Warning]?Using?a?password?on?the?command?line?interface?can?be?insecure.
?
Welcome?to?the?MySQL?monitor.??Commands?end?with?;?or?g.
Your?MySQL?connection?id?is?6
Server?version:?5.7.18?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.7.18????|
+———–+
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.02?sec)
?
mysql>?show?variables?like?‘port’;
+—————+——-+
|?Variable_name?|?Value?|
+—————+——-+
|?port??????????|?3306??|
+—————+——-+
1?row?in?set?(0.01?sec)
?
mysql>?show?variables?like?‘datadir’;
+—————+——————-+
|?Variable_name?|?Value?????????????|
+—————+——————-+
|?datadir???????|?/data/mysql_data/?|
+—————+——————-+
1?row?in?set?(0.01?sec)
5. SSL配置
? ?* ?利用自帶工具生成SSL相關文件
?
[root@MySQL?~]#?/usr/local/mysql/bin/mysql_ssl_rsa_setup?–datadir=/data/mysql_data
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@MySQL?~]#?ls?-l?/data/mysql_data/*.pem
-rw——-?1?root?root?1679?Jun?24?20:54?/data/mysql_data/ca-key.pem
-rw-r–r–?1?root?root?1074?Jun?24?20:54?/data/mysql_data/ca.pem
-rw-r–r–?1?root?root?1078?Jun?24?20:54?/data/mysql_data/client-cert.pem
-rw——-?1?root?root?1675?Jun?24?20:54?/data/mysql_data/client-key.pem
-rw——-?1?root?root?1675?Jun?24?20:54?/data/mysql_data/private_key.pem
-rw-r–r–?1?root?root??451?Jun?24?20:54?/data/mysql_data/public_key.pem
-rw-r–r–?1?root?root?1078?Jun?24?20:54?/data/mysql_data/server-cert.pem
-rw——-?1?root?root?1675?Jun?24?20:54?/data/mysql_data/server-key.pem
* 重啟 MySQL 服務
[root@MySQL?~]#?/etc/init.d/mysqld?restart
Shutting?down?MySQL..?SUCCESS!?
Starting?MySQL.?SUCCESS!
? * 連接MySQL 查看SSL開啟狀態(tài)
? ? ?have_openssl 與?have_ssl 值都為YES表示ssl開啟成功
?
mysql>?show?variables?like?‘have%ssl%’;
+—————+——-+
|?Variable_name?|?Value?|
+—————+——-+
|?have_openssl??|?YES???|
|?have_ssl??????|?YES???|
+—————+——-+
2?rows?in?set?(0.03?sec)
6. SSL + 密碼連接測試
? ? * 創(chuàng)建用戶并指定 SSL 連接 [?MySQL 5.7后推薦使用create user 方式創(chuàng)建用戶 ]
?
mysql>?create?user?‘ssl_test’@’%’?identified?by?‘123’?require?SSL;
Query?OK,?0?rows?affected?(0.00?sec)
* 通過密碼連接測試 [ 默認采用SSL連接，需要指定不使用SSL連接 ]
[root@MySQL?~]#?mysql?-h?192.168.60.129?-ussl_test?-p’123′?–ssl=0
mysql:?[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 + 密碼 連接測試
? ? ? ?SSL:?Cipher in use is DHE-RSA-AES256-SHA 表示通過SSL連接
?
[root@MySQL?~]#?mysql?-h?192.168.60.129?-ussl_test?-p’123′??–ssl
mysql:?[Warning]?Using?a?password?on?the?command?line?interface?can?be?insecure.
WARNING:?–ssl?is?deprecated?and?will?be?removed?in?a?future?version.?Use?–ssl-mode?instead.
Welcome?to?the?MySQL?monitor.??Commands?end?with?;?or?g.
Your?MySQL?connection?id?is?12
Server?version:?5.7.18?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.7.18,?for?linux-glibc2.5?(x86_64)?using??EditLine?wrapper
?
Connection?id:???? 12
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.7.18?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:???????? 7?min?34?sec
?
Threads:?1??Questions:?29??Slow?queries:?0??Opens:?112??Flush?tables:?1??Open?tables:?105??Queries?per?second?avg:?0.063
————–
?
7. SSL + 密碼 + 密鑰連接
?
? ? * 創(chuàng)建用戶并指定 X509 [ SSL+密鑰 ] 連接 [?MySQL 5.7后推薦使用create user 方式創(chuàng)建用戶?]
?
mysql>?create?user?‘X509_test’@’%’?identified?by?‘123’?require?X509;
Query?OK,?0?rows?affected?(0.00?sec)
* 通過密碼連接測試
[root@MySQL?~]#?mysql?-h?192.168.60.129?-uX509_test?-p’123′?–ssl=0
mysql:?[Warning]?Using?a?password?on?the?command?line?interface?can?be?insecure.
ERROR?1045?(28000):?Access?denied?for?user?‘X509_test’@’192.168.60.129’?(using?password:?YES)
* 通過 SSL +密碼 連接測試
[root@MySQL?~]#?mysql?-h?192.168.60.129?-uX509_test?-p’123′?–ssl
mysql:?[Warning]?Using?a?password?on?the?command?line?interface?can?be?insecure.
ERROR?1045?(28000):?Access?denied?for?user?‘X509_test’@’192.168.60.129’?(using?password:?YES)
? * 通過 SSL + 密碼＋密鑰連接測試
??SSL:?Cipher in use is DHE-RSA-AES256-SHA 表示通過SSL連接
?
[root@MySQL?~]#?mysql?-h?192.168.60.129?-uX509_test?-p’123′?–ssl-cert=/data/mysql_data/client-cert.pem?–ssl-key=/data/mysql_data/client-key.pem?
mysql:?[Warning]?Using?a?password?on?the?command?line?interface?can?be?insecure.
Welcome?to?the?MySQL?monitor.??Commands?end?with?;?or?g.
Your?MySQL?connection?id?is?21
Server?version:?5.7.18?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.7.18,?for?linux-glibc2.5?(x86_64)?using??EditLine?wrapper
?
Connection?id:???? 21
Current?database:?
Current?user:????? X509_test@192.168.60.129
SSL:??????????? Cipher?in?use?is?DHE-RSA-AES256-SHA
Current?pager:???? stdout
Using?outfile:???? ”
Using?delimiter:?? ;
Server?version:??????? 5.7.18?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:???????? 18?min?27?sec
?
Threads:?1??Questions:?40??Slow?queries:?0??Opens:?118??Flush?tables:?1??Open?tables:?111??Queries?per?second?avg:?0.036
————–
? 版權聲明
文章版權歸作者所有，未經允許請勿轉載。
THE END
