Miscellaneous

Miscellaneous
(last updated: Jul 26, 2009) — print

Navicat/MySQL through SSH tunnel

Assuming that you do not have direct access to a MySQL DBMS on a remote server, D, Navicat has the ability to access the DBMS on D, so long as:

you have a secure shell access to some server, S
you have MySQL connection access from server S to the MySQL server D

The technique is called SSH Tunnelling, which is something that the SSH server, S, must support (it does so with default OpenSSH configurations).

Simplest situation: you have SSH access to the server with the MySQL DBMS

In our general terms above, the situation I'm describing is when D and S are the same server, which we'll refer to as:

SERVER

You have a login on this server which we'll refer to as

SERVER_LOGIN

The first step you must do is get access to the server through a public key. We will assume you have a decent terminal shell to use, such as a Linux shell or Cygwin on Windows.

Create a public key/private key pair. Run the command:
```
$ ssh-keygen -t dsa
```
This will prompt for a passphrase and a place to store the public key (for which you will take the default). The easiest thing to do is to hit "Enter" three times. If you want a more secure public key access, you have to introduce a passphrase when prompted for it.
View the results by typing:
```
$ ls ~/.ssh
$ cat ~/.ssh/id_dsa                    the private key
$ cat ~/.ssh/id_dsa.pub                the public key
```

Send the public key file to SERVER via scp:

$ scp ~/.ssh/id_dsa.pub SERVER_LOGIN@SERVER:          (note the terminating ":")

Log into SERVER:
```
$ ssh -l SERVER_LOGIN SERVER
```
This should be the last time you'll need the login password.
From your SERVER shell, in your home directory, check that the .ssh/ directory exists:
```
[SERVER]$ ls .ssh
```
If not, create it:
```
[SERVER]$ mkdir .ssh
```
Then concatenate the contents of id_dsa.pub (sent from your client machine) onto the file ~/.ssh/authorized_keys2:
```
[SERVER]$ cat id_dsa.pub >> .ssh/authorized_keys2
[SERVER]$ rm id_dsa.pub
[SERVER]$ exit
```
Test that the public key works:
```
$ ssh -l SERVER_LOGIN SERVER
```
If your public key passphrase is empty, you'll go right in, otherwise provide the passphrase.

The second step is to create a connection with the appropriate information in Navicat. Let's make some further naming assumptions:

MySQL DBMS login:    MYSQL_LOGIN
MySQL DBMS password: MYSQL_PASS

Press the Connection button to start making a new connection.

Select the SSH tab. Enter these field values:
```
 Use SSH Tunnel
Host name/IP address:  SERVER
Port:                  22
User name:             SERVER_LOGIN
Authentication Method: 
Private key:           ./ssh/id_dsa       (see below)
Passphrase:            -- whatever you chose, leave empty if none --
                        Save Passphrase
```
The selection of the Private key may be a bit tricky because ".ssh" is a hidden directory. Click the on the right to bring up the file selection dialog. Navigate to the directory containing .ssh — you may be right where it is, but it's not showing! — and then type .ssh into the File name field. This should open up the .ssh directory and make id_dsa accessible.

Select the General tab. Enter these field values:

Connection Name:       SERVER-MYSQL_LOGIN              (or whatever you like)
Host name/IP address:  localhost                       (not SERVER)
Port:                  3306
User name:             MYSQL_LOGIN
Password:              MYSQL_PASS

Click the Test Connection button to check that it's working.

Miscellaneous (last updated: Jul 26, 2009) — print

Navicat/MySQL through SSH tunnel

Simplest situation: you have SSH access to the server with the MySQL DBMS

Miscellaneous
(last updated: Jul 26, 2009) — print