MySQL

The School provides a MySQL database server. It may be used for student projects.

MySQL

Documentation can be viewed at http://dev.mysql.com/doc/.

Graphical User Interface

The recommended graphical interface to MySQL is MySQLworkbench.

MySQL Workbench (Windows)

is available on CMD systems from the CS Portable Apps.

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.

  1. Launch PuttyGen from the CS Portable toolkit

  1. 1024 bits is no longer considered long enough to be secure. 2048 bits are recommended.
  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.

  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.

  1. Every time you connect, launch Pagent from the CS portable toolkit

  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:

  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)

     bcd .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.