Given this pattern of use, we can already point out one thing: no direct connection on port 3306 of the MySQL server is established with the client. All data from the client are sent over the SSH server port. To secure our MySQL server, we can already prohibit connections from outside.
We have seen how to prohibit any TCP / IP connection with the skip-networking option. However, it is not appropriate in the case of an SSH tunnel. Indeed, the SSH server must be able to redirect the data to the MySQL server, and for that there is no alternative to TCP / IP. It is therefore necessary to allow the TCP / IP local.
To do this we use the bind-address option which allows to limit the source of the connections. That is what can be added to the configuration file for MySQL:
After rebooting the server, only connections from 127.0.0.1 (localhost) will be accepted. And since we're a bit paranoid and especially perfectionists, we will also modify the privileges of the root user (or other name if you have changed, as has been previously advised) in MySQL:
bind-address = 127.0.0.1
After execution of these requests, the root user can log into MySQL as it is on the same machine.
Mysql.user DELETE FROM WHERE User = 'root' AND Host! = 'Localhost';
We do not see how to set up (and secure) a SSH server, or how to configure the client to enable a tunnel. I advise you, however, look at OpenSSH, which includes a server and SSH client. By default, the OpenSSH server (sshd) is configured to allow tunneling. To be sure, check that option PermitTunnel is "yes" in the configuration file for sshd (usually / etc / ssh / sshd_config)
Here is an example of connection tunnelée using the MySQL console client and the OpenSSH SSH client:
Through this tunnel, not only communication between the client and MySQL server will be encrypted, but in addition, identification will be strengthened since it will also log into SSH. For information, you can disable the identification if the SSH to enter two passwords you discomfort. However, remember to secure the system used for the SSH connection!
> Ssh-f-L 66306:127.0.0.1:3306 sleep mysql.mondomaine.com 10
firstname.lastname@example.org 's password: xxxxxxx
=> From here, we are connected to the server and our SSH tunnel is enabled on the local port 66306.
> Mysql-h 127.0.0.1-P 66306-u root-p
=> To connect to the MySQL server, we use localhost: 66306 not mysql.mondomaine.com: 3306
If the MySQL server machine also has the administration programs (mysql, mysqldump, ...), it is not necessary to set up port-forwarding. You can simply run the command directly on the shell. A small example:
> Mysql.mondomaine.com ssh / usr / local / mysql / bin / mysqldump-A> backup