meta data for this page
  •  

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revisionPrevious revision
Next revision		Previous revision
pluginto:mysql_database [2021/09/08 01:26] – gregbalcopluginto:mysql_database [2021/11/03 16:12] (current) – gregbalco
Line 1: Line 1:
-==== Software setup for connecting to the MySQL database ====+===== Software setup for connecting to the MySQL database =====
  
 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.  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. 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. +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.  
  
-=== Mac (or Linux) ===+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.  
 + 
 +==== Mac (or Linux) ====
  
 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.  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 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.
Line 31: Line 39:
 Now try either 'Connect' or 'Test connection' to see if it works.  Now try either 'Connect' or 'Test connection' to see if it works. 
  
-=== Windows ===+==== Windows ====
  
 For Windows, the MySQL client, HeidiSQL ([[http://www.heidisql.com]]), works well.  For Windows, the MySQL client, HeidiSQL ([[http://www.heidisql.com]]), works well. 
Line 44: Line 52:
  
 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: 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:
 +
 +{{:pluginto:heidi1.png?400|}}
 +
 +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:
 +
 +{{:pluginto:heidi2.png?400|}}
 +
 +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.