Databases Page History


MySQL

The School provides a MySQL database server. It may be used for student projects. Documentation can be viewed at http://dev.mysql.com/doc/.

Graphical User Interface

The recommended graphical interface to MySQL is MySQLworkbench. This has a built in mechanism for opening a secure tunnel when performing database administration tasks from off-campus.

MySQL Workbench (Windows 10)

On managed windows 10 machines in the Urban Sciences Building, MySQL Workbench is already installed (start typing its name in the start menu to locate it). This is a portable installation in C:\CSPA17\MySQL-Workbench so can be taken home by copying the entire folder.

MySQL Workbench (Windows 7/8)

MySQL Workbench is also available on manged windows 7/8 systems elsewhere in the university from the CS Portable Apps collection.

Other systems

MySQLworkbench can be downloaded from http://www.mysql.com/downloads/workbench/. For Windows it's simplest to download the zip file that doesn't include an installer - just unzip then launch ''MySQLworkbench.exe''.

Off-campus access

MySQL Workbench allows a new connection to be defined which is tunnelled through linux.cs via SSH enabling you to access your database on homepages.cs when off campus - use the ''Standard TCP/IP over SSH'' Connection method, linux.cs.ncl.ac.uk as the SSH hostname and your campus id as SSH username. If you wish, you can generate an SSL key pair to authorize your login. This is preferable to using the password vault which stores your password using encryption of unknown quality. To generate a key pair, launch PuTTYgen from CS Portable Apps and follow the instructions to generate a new key pair. Use a strong passphrase that is not the same as your campus login password.

Search for PuttyGen in the start menu or launch it from the CS Portable toolkit

PuTTYgen

  1. 1024 bit RSA keys and all DSA keys are no longer safe and should not be used. It has been suggested that ECDSA is also insecure.
  2. Generate a key pair and expect to be asked to move the mouse to assist with randomness.
  3. The secure passphrase needs to be different from your campus password and easily memorable. Nobody can reset this.
  4. Save the private key in an area only you have access to. Your H: drive is adequate as this is an encrypted file.
  5. The public key in the correct form for Linux can be selected and copied by right-clicking here. Add this to .ssh/authorized_keys in your linux.cs.ncl.ac.uk home space. Keep it as a single long line.

Launch MySQL Workbench from the start menu. Define a new connection.

Connect

  1. Define a connection using your campus login name to connect to linux.cs.ncl.ac.uk
  2. Do not store your campus password in the vault but instead...
  3. ...save the location of the private key previously generated.
  4. Your database login is normally the same as your campus long name.
  5. The mysql password is a lower grade and can safely be stored in the vault.

Before you use MySQL Workbench ensure Pagent is running and load your saved key into memory

PagentAdd

  1. Right click on the Pagent icon to add a key
  2. Select the ppk file saved earlier
  3. Decrypt the in memory copy of the private key
  4. Opening the connection should now have all it needs to connect (and re-connect after a timeout).

Command Line Tools

The command line interfaces to SQL are:

mysql:: available on School Linux and Windows systems. It is run like this: {{{ mysql -h -u -p }}} and you will be prompted for your password.

JDBC

The JDBC driver is in {{{/usr/share/java/mysql-connector-java.jar}}} on Linux. You can connect from an on-campus host to a database as follows

import java.sql.*;

public class Connect
{
   public static void main (String[] args)
   {
       Connection conn = null;

       try
       {
           String userName = "user";
           String password = "password";
           String url = "jdbc:mysql://server.ncl.ac.uk/database";
           Class.forName ("com.mysql.jdbc.Driver").newInstance ();
           conn = DriverManager.getConnection (url, userName, password);
           System.out.println ("Database connection established");
       }
       catch (Exception e)
       {
           System.err.println ("Cannot connect to database server");
           System.err.println (e.getMessage ());
       }
       finally
       {
           if (conn != null)
           {
               try
               {
                   conn.close ();
                   System.out.println ("Database connection terminated");
               }
               catch (Exception e) { /* ignore close errors */ }
           }
       }
   }
}

Off campus

Direct MySQL connections are blocked from off campus.

  • Direct external SQL access will not usually be required if using a web access layer which is hosted on campus.

  • For development and individual work on a mobile device, the use of a local solution such as SQLite is recommended.

If neither of these options apply and there is need for shared external access with using JDBC in (for example) a mobile phone connecting over 4G a tunnel will also be required. An approach similar to the following should apply:

export

  1. Using PuttyGen generate a new key pair. Then Export the generated key in OpenSSH format:

  2. Connect to linux.cs.ncl.ac.uk using WinSCP. Open the .ssh folder and place the created public key here.

  3. From Putty enter the following commands (replace id_rsa.pub with the name of the public key)

    cd .ssh touch authorized_keys chmod 600 authorized_keys cat id_rsa.pub >> authorized_keys rm id_rsa.pub

  4. Within your java program use the .addIdentity method provided by Jsch, providing a link to the private key and the passphrase. You'll still need to specify a username to connect but this means a password is no longer embedded within the program.

session


Last edited by Chris Ritson