wiki:DatabaseBasedAnalysis/Connection

Credentials

The database factdata is hosted at ihp-pc45.ethz.ch. The use is fact with the usual password.

For details on how to use these credentials see the man page of the mysql-client mysql ("man mysql"), the help page of the FACT++ tool rootifysql ("rootifysql --help") or the documentation of the tool of your choice.

A connection from everywhere is allowed, if the connection is encrypted. Connections from ihp-pc45 might be unencrypted.

The following two examples should give you a valid connection (however, the port 3306 must be open to connect to ihp-pc45 on your side):

The first option is through the mysql-client:

mysql -C -h ihp-pc45.ethz.ch -u fact -p factdata

To enforce encryption, --ssl (oder clients) or --ssl-mode=REQUIRED can be used. If you have problems with the connection, you can also try --protocol=TCP.

If you access the database from outside of ETH, it is wise to enable compression with the -C option. Inside ETH (in particular on ihp-pc45), enabling -C is certainly a performance drawback and should be avoided.

The second option is through a FACT++ tool:

rootifysql --uri fact:password@ihp-pc45.ethz.ch/factdata

It is wise to put the credentials into a configuration file (its default name is rootifysql.rc) and make it readable only for you ("chmod go-r rootifysql.rc) to avoid that the credentials are visible in the processlist (top, htop, ps aux, etc.). The shortest configuration file would look like this:

uri=fact:password@ihp-pc45.ethz.ch/factdata

Note that the mysql client libraries at ISDC are too old and do not allow for encrypted connections. Thus no connection from ISDC is possible without tunnel. How to tunnel your connection is explained in the following. Note that it requires an account on ihp-pc45 (which I think should not be generally available). Thus this is mainly meant as a solution for automatic processes running at ISDC, for example, to update the database.

Forward Tunnel

If you are logged in at ISDC as 'user' and you have an account 'ethz' at ihp-pc45, you can use a tunnel. To setup a tunnel use

ISDC> ssh -x -C -n -N -q -L 10000:localhost:3306 ethz@ihp-pc45.ethz.ch

(It is wise to enable compression of the connection with the -C option)

Note that after log-in this process seems to stall (nothing happens anymore). This is correct. The tunnel is open. It will forward the local port 10000 from the ISDC machine to the port 3306 on a machine which is accessible as 'locahost' from ihp-pc45.

The mysql call would now look like

> mysql -h 127.0.0.1 -P 10000 -u fact -p factdata

Note that you need to use the IP address instead of localhost, otherwise the mysql client tries to use a socket connection (which will fail). You could also use --protocol=TCP.

As the mysql connection now comes via the loopback interface and not via the external IP, the connection of the mysql client is allowed to be unencrypted.

Backward Tunnel

Assume that you are already logged into ihp-pc45.ethz.ch and want to execute a mysql at ISDC accessing ihp-pc45, a backward tunnel can be used:

ihp-pc45> ssh -x -C -n -N -q -R 10000:localhost:3306 user@isdc-nx.isdc.unige.ch

(It is wise to enable compression of the connection with the -C option)

This command will log you into isdc-nx and (in parallel) create a tunnel from port 10000 at isdc-nx to port 3306 of a machine which is called 'localhost' from where you started the ssh connection (ihp-pc45).

You can now do

ISDC> mysql -h localhost -P 10000 -u fact -p factdata

Note that you need to use the IP address instead of localhost, otherwise the mysql client tries to use a socket connection (which will fail). You could also use --protocol=TCP.

As the mysql connection now comes via the loopback interface and not via the external IP, the connection of the mysql client is allowed to be unencrypted.

Last modified 7 months ago Last modified on Aug 4, 2018, 6:20:27 PM