This is the second part of an evaluation into connecting to MySQL remotely and securely. There are some helpful links in my first post referencing speed over SSL versus ssh tunneling. TLDR; tunneling is far more efficient then MySQL over SSL. Part 1: Examples of Remote MySQL SSL Connections Using PHP: MySQLi and PDO
The intent of this post is to jog my memory, share my process, and offer some helpful code snippets and resources.
THE QUOTATION MARKS IN THIS DOCUMENT WILL BREAK THESE COMMANDS ON THE COMMAND LINE
This post assumes you have an understanding of ssh-keygen and setting up passwordless SSH access to your MySQL remote server. See here for a good post on getting this setup: Digital Ocean
This post also assumes you have autossh setup on the server that will have remote client access to the MySQL server. Here is a good post on how to install autossh: http://ask.xmodulo.com/install-autossh-linux.html
I would also recommend creating a script to run the command at boot time. This would prevent a loss of connection in the event of a server reboot.
MY USE CASE
I am using this as a means to query my database from another server. This database connection is not used for production, and I would recommend against using it in this way. The connection tunnel cannot accommodate the typical production needs of a database connection. Proximity between the servers has a large effect on the latency when querying too.
THE IMPLEMENTATION
Here is a layout for these who like to visualize what the setup.
FIRST – SSH TUNNEL SETUP
# Setup the ssh tunnel w/o autossh
ssh -N <server name from ~/.ssh/config > -L 6666:localhost:3306
# Example using the connection (on Mac using MySQL shell client)
mysqlsh -u<user> -p "<password>" -P 6666 -h127.0.0.1 --sql --nw
SECOND – AUTOSSH TUNNEL SETUP
# DEBUGGING - YOU SHOULD SEE autossh sending a request every ServerAliveInterval seconds.
AUTOSSH_DEBUG=1 autossh -M 0 -N <server name from ~/.ssh/config > -o "ServerAliveInterval 10" -o "ServerAliveCountMax 3" -L 6666:localhost:3306 -vvv
# For testing without debugging information
autossh -M 0 -N <server name from ~/.ssh/config > -o "ServerAliveInterval 10" -o "ServerAliveCountMax 3" -L 6666:localhost:3306
# Once the connection is validated add the -f flag to have it run in the background.
autossh -f -M 0 -N <server name from ~/.ssh/config > -o "ServerAliveInterval 10" -o "ServerAliveCountMax 3" -L 6666:localhost:3306
# Enable compression using the -C flag.
autossh -f -M 0 -N <server name from ~/.ssh/config > -o "ServerAliveInterval 10" -o "ServerAliveCountMax 3" -C -L 6666:localhost:3306
# Limit to ipv4 or ipv6 using -4 or -6 respectively.
autossh -f -M 0 -N <server name from ~/.ssh/config > -o "ServerAliveInterval 10" -o "ServerAliveCountMax 3" -4 -L 6666:localhost:3306
# Killing the autossh process
ps aux | grep ssh
ps aux | grep autossh
Kill the ssh process that autossh created. Autossh will recognize that you have killed the process and end its process too.
# Testing on server
autossh -M 0 -N <server name from ~/.ssh/config > -o "ServerAliveInterval 10” -o "ServerAliveCountMax 3” -L 6666:localhost:3306
– Uses mysql shell on Mac
mysqlsh -u<mysql user> -p -h 127.0.0.1 -P 6666
Sample PHP Connection
<?php
$link = mysqli_connect("localhost:<tunnel port 6666>", "my_user", "my_password", "my_db");
if (!$link) {
echo "Error: Unable to connect to MySQL." . PHP_EOL;
echo "Debugging errno: " . mysqli_connect_errno() . PHP_EOL;
echo "Debugging error: " . mysqli_connect_error() . PHP_EOL;
exit;
}
echo "Success: A proper connection to MySQL was made! The my_db database is great." . PHP_EOL;
echo "Host information: " . mysqli_get_host_info($link) . PHP_EOL;
mysqli_close($link);
Comments and Information from the Man Page:
ServerAliveCountMax
Sets the number of server alive messages (see below) which may be
sent without ssh(1) receiving any messages back from the server.
If this threshold is reached while server alive messages are
being sent, ssh will disconnect from the server, terminating the
session. It is important to note that the use of server alive
messages is very different from TCPKeepAlive (below). The server
alive messages are sent through the encrypted channel and there‐
fore will not be spoofable. The TCP keepalive option enabled by
TCPKeepAlive is spoofable. The server alive mechanism is valu‐
able when the client or server depend on knowing when a connec‐
tion has become inactive.
The default value is 3. If, for example, ServerAliveInterval
(see below) is set to 15 and ServerAliveCountMax is left at the
default, if the server becomes unresponsive, ssh will disconnect
after approximately 45 seconds. This option applies to protocol
version 2 only.
ServerAliveInterval
Sets a timeout interval in seconds after which if no data has
been received from the server, ssh(1) will send a message through
the encrypted channel to request a response from the server. The
default is 0, indicating that these messages will not be sent to
the server. This option applies to protocol version 2 only.
