Set up a secure remote connection for MySQL

Requirements:Development and testing departments are required to access the production and production databases remotely.

For a while, the straight MySQL level is granted minimum permissions and restricts access only from the company's export IP; the iptables level is also for the database port only allows the company to export IP access.

But this kind of appearance after all felt not reassuring. Later on the MySQL login process (Wireshark and reference official documents), although the MySQL implementation of the login mechanism (here is the opportunity to say separately) there is no risk of leaking passwords (theoretically cracked possible, but it will be quite laborious), However, the transfer of SQL statements and execution results after login is transmitted in plaintext.
This is not tolerated for operations and must be encrypted.

The first attempt was to implement the MySQL native SSL:
The following is a general study:
First check whether the server supports 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 is compiled with SSL support, but is 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 C5>yassl:

? MySQL Enterprise Edition binary distributions is compiled using OpenSSL. It is not a possible to use YASSL with MySQL Enterprise Edition.
? MySQL Community Edition binary distributions is compiled using YASSL.
? MySQL Community Edition Source distributions can be compiled using either OpenSSL or Yassl

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

SHOW STATUS LIKE ‘Rsa_public_key‘;

How to configure:
See https://dev.mysql.com/doc/refman/5.6/en/using-secure-connections.html
See https://dev.mysql.com/doc/refman/5.6/en/creating-ssl-files-using-openssl.html

Comprehensive consideration, based on MySQL (5.7 ago) natively supported SSL configuration secure connection, you need to manually generate the corresponding certificate, public key, private key files on server and client side: First, the service side configuration step is more cumbersome, and the client configuration also to increase the user certificate and private key files, It's also a bit of a fuss to figure out the time to guide the configuration changes.
5.7 SSL configuration and performance loss reference

Previously knew that SSH can implement port forwarding, just take this requirement in-depth research

Final Solution:
Via SSH port forwarding, simply say:

1. Have a local server and a remote server to establish a 长ssh连接(ssh隧道) , the tunnel openssh has a keepalive mechanism, the default 300s will send NoOp to remain alive (catch packet observed)
2. At the same time OpenSSH will 本地(内网)server listen to a port, the target is the 本地IP:port traffic by ssh隧道 远程server forwarding to the corresponding目标server的内网IP:port
3. In this way, the database only needs to authorize the remote account for the 中间server intranet IP. That is, the transmission on the public network is secured through the SSH tunnel.

Configuration steps

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

   The above configuration details, reference man ssh_config

#实验过程中发现针对一个 "Relay host" to implement two "local port forwarding", there will be the following error, but the experience does not affect the use of
Controlsocket/home/kai/.ssh/[email protected]:22 already exists, disabling multiplexin

Thus, the requirements of the MySQL client software needs to change the ip:port of the corresponding library, the others do not need to change, in order to minimize user changes to achieve security requirements. [* * About SSH controlmaster and controlpath:**]  (Https://ldpreload.com/blog/ssh-control) [Reference 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 the using it with git a little when back. Briefly, you set a controlpath so all your SSH processes know how to find each other, and then do one connection as A "master" that stays around forever. Every other SSH would check for a 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 T He SSH connection every seconds. Depending on your network and server configuration, if you are need this to prevent the connection from timing out.---[global reference 1]  (http://www.ruanyifeng.com/blog/2011/12/ssh_port_forwarding.html) [Global Reference2] (http://blogs.perl.org/users/smylers/2011/08/ssh-productivity-tips.html) [global Reference 3 openssh/cookbook/multiplexing] (https://en.wikibooks.org/wiki/OpenSSH/Cookbook/Multiplexing)

To set up a secure remote connection for MySQL