為MySQL設定安全的遠端連線

標籤：mysql安全   ssh連接埠轉寄   mysql遠端連線加密   遠端連線加密   

需求：開發、測試部門需要對準生產和生產資料庫進行遠端存取。

有一陣子，是直MySQL層面接授予最小許可權，並限制只允許從公司出口ip訪問；iptables層面也是針對資料庫連接埠只允許公司出口ip訪問。

但是這個樣子終究覺得不夠放心。後來研究了下MySQL的登入過程（wireshark抓包和參考官方文檔），雖然說MySQL實現的登入機制（這裡有機會單獨說）基本不存在泄露密碼的風險（理論上存在被破解的可能，但是也會相當費勁），但是登入之後的SQL語句和執行結果的傳輸卻都是明文傳輸的。
這對於營運來說是不可容忍的，必須搞加密。

一開始打算通過MySQL原生的ssl來實現：
大致研究了下：
首先檢查伺服器是否支援ssl，

mysql> show global variables like ‘%ssl%‘;      +---------------+----------+| Variable_name | Value    |+---------------+----------+| have_openssl  | DISABLED || have_ssl      | DISABLED |## have_openssl is alias of have_ssl

YES if mysqld supports SSL connections, NO if not. DISABLED indicates that the server was compiled with SSL support, but was not started with the appropriate --ssl-xxx options

To use SSL connections between the MySQL server and client programs, your system must support either OpenSSL or yaSSL:

? MySQL Enterprise Edition binary distributions are compiled using OpenSSL. It is not possible to use yaSSL with MySQL Enterprise Edition.
? MySQL Community Edition binary distributions are compiled using yaSSL.
? MySQL Community Edition source distributions can be compiled using either OpenSSL or yaSSL

To determine whether your server was compiled using OpenSSL, test the existence of any of those variables. For example, this statement returns a row if OpenSSL was used and an empty result if yaSSL was used:

SHOW STATUS LIKE ‘Rsa_public_key‘;

如何配置:
參見https://dev.mysql.com/doc/refman/5.6/en/using-secure-connections.html
參見https://dev.mysql.com/doc/refman/5.6/en/creating-ssl-files-using-openssl.html

綜合考慮，基於mysql（5.7之前）原生支援的ssl配置安全連線，需要手動在server和client端產生對應的認證，公開金鑰、私密金鑰等檔案：一是服務端配置步驟較為繁瑣；二是用戶端的配置也要增加使用者認證及私密金鑰等檔案，還得算上指導相關修改配置的時間，感覺有些小題大做了。
5.7的ssl配置及效能損失參考

之前就知道ssh能實現連接埠轉寄，正好藉此需求深入研究一番

最終解決方案：
通過ssh的連接埠轉寄，簡單說就是:

1. 讓一台本地server和遠程server間建立一個長ssh串連(ssh隧道),該隧道openssh有保活機制，預設300s會發送noop保持存活(抓包觀察到)
2. 同時openssh會在本地(內網)server監聽一個連接埠，目標是該本地IP:port的流量通過ssh隧道經由遠程server轉寄到對應的目標server的內網IP:port
3. 這樣，資料庫只需要給遠程帳號授權該中間server的內網ip即可。即在公網上的傳輸是經過ssh隧道保證了安全

配置步驟

1. [[email protected] ~]$ cat .ssh/config ControlPath ~/.ssh/master-%[email protected]%h:%pControlMaster auto

   上述配置詳細資料，參考man ssh_config

2. [[email protected] ~]$ ssh -MNf -L 172.18.200.23:33062:192.168.1.2:3306 [email protected][[email protected] ~]$ ssh -MNf -L 172.18.200.23:33065:192.168.1.5:3306 [email protected] 

#實驗過程中發現針對一個“轉送主機”實現兩個“本地連接埠轉寄”，會有如下報錯，但是經驗證不影響使用
ControlSocket /home/kai/.ssh/[email protected]:22 already exists, disabling multiplexin

如此，相關需求人員的MySQL用戶端軟體就只需要更改下對應庫的ip:port，其他都不需改變，以對使用者最小的改變實現了安全性的需求。[**關於SSH ControlMaster and ControlPath:**](https://ldpreload.com/blog/ssh-control)  [參考1](https://www.reddit.com/r/git/comments/3h7hbm/can_one_maintain_a_constant_ssh_connection_to_a/#content)> You want SSH‘s ControlMaster feature. I wrote a blog post about using it with git a little while back. Briefly, you set a ControlPath so all your SSH processes know how to find each other, and then you make one connection as a "master" that stays around forever. Every other SSH will check for an existing, shared connection before making a new one, which lets them skip authentication.>You may also want to use -o ServerAliveInterval=30 or something on the master, which sends a keep-alive packet over the SSH connection every 30 seconds. Depending on your network and server configuration, you may need this to prevent the connection from timing out.---[全域參考1](http://www.ruanyifeng.com/blog/2011/12/ssh_port_forwarding.html)  [全域參考2](http://blogs.perl.org/users/smylers/2011/08/ssh-productivity-tips.html)  [全域參考3 OpenSSH/Cookbook/Multiplexing](https://en.wikibooks.org/wiki/OpenSSH/Cookbook/Multiplexing)

為MySQL設定安全的遠端連線