This page describes software needed to talk to the MySQL database. For workshops, the aim is to set this up before the workshop starts to save time getting started.

We will be using SSH (“secure shell”) through an intermediate gateway host to talk to the database server. The reason for this is that the database server is configured to accept connections only from a list of allowed IP addresses, and your laptop likely has a different IP address every time you start a new connection to a different network. Thus, we use a gateway host with a fixed IP address that is on the allowed list.

We will be using SSH “key-pair” authentication, which requires that you generate a code key on your machine and send it to me to be installed on the gateway host. Once the key pair is installed on both machines they can make a secure connection without the need to enter a password each time. Thus, the connection is machine-specific…a key pair installed on your laptop will not also work on your desktop.

Note: the SSH key pair should only be installed on a machine that only you have access to. Do not install SSH keys on shared machines.

Note: these instructions are incomplete because it seems like a bad idea to publicly post detailed connection parameters. You will need to contact Greg to complete your setup.

The first thing is a MySQL client. I am using Sequel Pro (http://www.sequelpro.com) on my Mac. I suggest starting with this one. For Linux systems, I am not sure what to use…this may require some research.

Note: in the fairly near future it will probably be necessary to upgrade the database server from MySQL 5.7 to 8.something. Sequel Pro does not work with MySQL 8. Possible replacements are MySQL Workbench or the free version of TablePlus (http://tableplus.com). Also, there are lots of (hopefully better) options that are not free.

If you have a Mac or Linux machine, SSH is already installed (Mac) or most likely already installed (Linux), so you just have to generate the key pair.

If you are using a Mac (the procedure is basically the same on all Linuxes, although file locations may differ), open a terminal window and type the following at the prompt:

ssh-keygen

Hit return to accept the default file location. Then hit return again to store it without a passphrase (usually the only reason you would need a passphrase would be if you were using a shared computer). It will spit back some gibberish indicating that the key has been generated.

Now type the following:

cat ~/.ssh/id_rsa.pub

This will spit back a long code string with 'ssh-rsa' at the beginning and the name of your computer at the end. Copy the entire string, paste it into an email, and send it to Greg.

Now that we've dealt with SSH keys, open Sequel Pro to connect to the database. A window with connection details will open. Click the “+” icon at lower left to create a new named connection. Give it a sensible name (e.g., ICED_remote). Select the 'SSH' tab. The connection details should look like the following:

You will need to ask an administrator (= Greg) for (i) the correct IP address to enter in the 'MySQL Host“ field and (ii) the correct host name for the “SSH Host” field. The “Username” and “Password” fields are for your login on the ICE-D MySQL server. Again, talk to an administrator about getting this set up if you haven't already. The Databases field can be left empty. The “SSH User” field pertains to your username on the SSH gateway machine, which is “iced.” Leave “SSH password” blank – no SSH password is required because a key pair has been installed on your machine and the gateway server. Hit 'Save changes' to save the connection parameters for future use.

Now try either 'Connect' or 'Test connection' to see if it works.

For Windows, the MySQL client, HeidiSQL (http://www.heidisql.com), works well.

Windows users will typically have to install an SSH client. The most common one of these is PuTTY (https://www.putty.org). Installing PuTTY will also install an SSH key generator called PuTTY Key Generator or PuTTYgen for short.

Open PuTTYgen and use the default settings to generate a key. This will involve making random movements with your mouse. This should have generated both a “public” and a “private” ssh key. The public key should be printed in the box near the top of the window. Copy this and email it to Greg. Save the private key in the following directory:

C:\Users\[user]\.ssh

This is not a standard directory (i.e., you probably shouldn't try to make it yourself), but it should either already exist or it might be auto-generated when you use PuTTY.

Now that we've dealt with SSH keys, open HeidiSQL to connect to the database. The Session Manager window will open, which is where you'll input the connection parameters. First, make a new Session and give it a sensible name (e.g., ICED_remote). Use the template below for the Settings tab:

Ask Greg for the correct IP address to put in the 'Hostname/IP' field. The User name and Password here are for your ICE-D MySQL account. Talk to Greg about getting this set up if you haven't already. The Databases field can be left empty.

Use the following template for the SSH tunnel tab:

Set the plink.exe location to wherever plink.exe is installed locally on your machine. It will probably be very similar to the example above. Ask an administrator (generally = Greg) for the correct host name to put in the 'SSH host+port' field. “Username” here pertains to your username on the SSH gateway machine, which is “iced.” Leave the Password blank..it is not needed for key pair authentication. Set the Private key file to the location of the private SSH key that you generated using PuTTYgen.

Okay, now try opening the connection. In theory, everything should work.