5. Using kaivos.csc.fi directly from your local computer

5.1 Tunneling MySQL port with ssh or plink

The MySQL databases in kaivos.csc.fi can be directly accessed only from the computing servers of CSC. However, you can make the database visible to your own computer using your CSC user account and port forwarding through an ssh tunnel.

In linux and MacOSX machines an ssh tunnel from your local computer to kaivos.csc.fi via taito-shell.csc.fi can be done for example with the command:

ssh -l taito_username -L 3306:kaivos.csc.fi:3306 taito-shell.csc.fi -N

The –N option in the end of the connection command blocks the command shell after the connection is established. Once the connection is closed also the port forwarding becomes disabled. Note that the ssh command above uses your CSC user account and password, not the database user account.

In Windows machines you can use e.g. plink program to open the tunnel. Plink can only be used through the command prompt. Below is a sample command for using plink:

plink -L 3306:kaivos.csc.fi:3306 taito_username@taito-shell.csc.fi

 

 

5.2 Tunneling MySQL port with Scientist's User Interface

If you don't have ssh or plink program available in your windows computer you can use the GSI SSH console tool in the Scientist's User Interface to do the secure server port forwarding.

First log into the Scientist's User Interface service by clicking the link below

Once you have entered to the service, launch the SSH Console by clicking the icon in the Services Desktop or by selecting the tool from the Services Menu ( under the Computing Environment sub-menu). When the console is launched, it asks for your user account and the name of the server you want to connect. The password will be asked only later on. For example:

   Username: csc_username

   Remote Host: taito-shell.csc.fi

And then launch the SSH console.

When the Java based console program starts you may need to accept launching the process in you local machine and allow the process connect CSC. When the console is running it will ask you to choose the authentication method to be used. Select: password and press Proceed.

Figure 1: Selecting authentication for SSH Console.

After this a Password Authentication window opens. Type in you password and press OK.

Figure 2: Password authentication for SSH Console.

You can use the console to use the CSC servers, but it contains also other tools than just the command line client. You can use it for example for file transport or secure tunneling of server ports. In the case of port tunneling go to the "Tools" menu of the client window and select:

    Tools | Secure Tunneling

To open the MySQL tunnel, press the New Tunnel button in the Tunnel management window. To use Kaivos, you should define a tunnel with following settings

Name: Mysql
Tunnel type: Outgoing
Linstenig Port(local): 3306
Destination Host:  localhost
Destination Port: 3306

 

5.3 Remote MySQL connections

The commands in capter 5.1 and 5.2 define that the communication to port 3306 (the default port of MySQL) in you local computer is transported to the MySQL port of kaivos.csc.fi through taito-shell.csc.fi server. As long as the connection to taito-shell.csc.fi is active the database in kaivos.csc.fi can be accessed from your local computer using the same MySQL commands as described above for Taito (assuming that you have the MySQL client program installed in your local computer). The only difference compared to previous command examples is that the host section (-h) should now point to host 127.0.0.1 that refers to your local host, instead of kaivos.csc.fi.

So for example the syntax to open an interactive MySQL session would now be:

mysql -u db_user_account –p -h 127.0.0.1 --local database_name

And the syntax for mysqlimport:

mysqlimport -h 127.0.0.1 -u db_user_account --local --compress \
--password database_name input_file.table

In the same way, you can make your databases visible in your local computer using locally installed graphical MySQL interfaces, like MySQL Workbench. The only major limitation with the port forwarding is that normally ssh tunnels are not very stable. You may need to reopen the ssh connection every now and then and you should not trust that the tunnel remains usable for several hours.
For the security reasons we recommend that you always close the ssh connection when you stop using the database.

 

  Previous chapter     One level up     Next chapter