For these instructions, you will need:
- plink on Windows, or ssh on Linux/Unix - to establish the tunnel - download here
- a MySQL client, we recommend MySQL GUI Tools, Toad for MySQL, or the MySQL command line client
- preferably a cable/ADSL connection - GUI admin tools are quite 'chatty' and access will be slow over dial-up
Download these tools before continuing.
Starting the tunnel
We provide access to MySQL over an SSH tunnel for security. You will need to start the tunnel before connecting with your client.
On Windows, the tunnel is established using plink. On Linux, the ssh tool is used. These are both command line tools.
You will need the following details from Metawerx:
- a metawerx username, which will be referred to as metausername below
- a metawerx password, which will be referred to as metapassword below, or an SSH key
- the SSH gateway name and port, ssh-port and ssh-gateway below
- a mysql server name and port, which will be referred to servername:serverport below
- for MySQL 4.x this will be mysql.metawerx.net:3306
- for MySQL 5.x this will be mysql5.metawerx.net:3506
You will also use a new username for the SSH connection. This will always be the same as your normal metawerx username, but with ssh_ on the front. eg: ssh_neale
WARNING: do not attempt to connect unless you know your SSH connection has been activated and have the correct details, or your IP may be blocked by our IDS and require unlocking.
To start the tunnel, execute the following command on Windows:
plink -C -v -l <metausername> -pw <metapassword> -L 127.0.0.1:3500:servername:serverport -P <ssh-port> <ssh-gateway>
or on Linux:
ssh -Cv -p <ssh-port> -L 127.0.0.1:3500:servername:serverport <metausername>@<ssh-gateway>
Example, if your username is cassie, password cassiepass, SSH gateway is ssh-meta1.metawerx.net, ssh-port is 22, server mysql5.metawerx.net:3506, and you are using Windows, use the following command:
plink -C -v -l cassie -pw cassiepass -L 127.0.0.1:3500:mysql5.metawerx.net:3506 -P 22 ssh-meta1.metawerx.net
This will open a tunnel between your localhost (127.0.0.1) port 3500, and our MySQL server port 3506.
- The first time you connect, or at other times we regenerate our key files, you will be asked to store the key in your cache. Answer "y" (yes) to continue.
- All data transmitted through the tunnel will be encrypted by SSH
- A prompt will appear, awaiting commands. Do not enter any commands - just leave the window open and minimize it.
- When you close this window, the tunnel will be closed, and your connection will be lost
- MySQL uses port 3306 by default - we are using port 3500 to avoid conflicts with any local version of MySQL already running on your machine
- You are now ready to start your MySQL client
- If you want to know how this works, or are creating your own tunnel, see the SSH Tunnel page for a full explanation
Using MySQL Administrator and MySQL Query Browser
- Ensure the tunnel is running, then start MySQL Administrator
- Enter the following details:
- Stored Connection: the name, anything you like, such as 'metawerx tunnel' or your login name
- Server Host: localhost (or 127.0.0.1, same thing)
- Port: 3500
- Username: your metawerx username
- Password: your metawerx password
- Click OK
- A menu appears on the left, and connection info appears on the right
- Click Catalogs - other menu options are for Server administration, or your local MySQL service
- Double click your database from the bottom left panel - your table information will appear on the right
- Table structures can be edited from here
- To run SQL queries, select MySQL Query Browser from the Tools menu - it will open in a new window
- Double click your database name from the Schemata panel on the right
- You can now enter SQL queries in the top panel - results appear in the lower panel
- Ensure the tunnel is running, then start Toad
- Enter the following details in the New Connection window:
- Display Name: the name, anything you like, such as 'metawerx tunnel' or your login name, you have to click the Edit icon next to this field to edit it
- Connection Type: TCP
- Host: localhost (or 127.0.0.1, same thing)
- Username: your metawerx username
- Password: your metawerx password
- Database: your database name (usually the same as your metawerx username)
- Connection Timeout: leave as is (eg: 15)
- Port: 3500
- Click Create
- A menu appears on the left, and columns info on the right
- Navigate to the Databases option. Depending on the layout you selected, Databases may appear on the Tabbed Menu, DropDown or as a Tree menu item
- Click the database you want to open, then Tables, then the Table you want to work with - table information will appear on the right
- Table structures can be edited from here
- To run SQL queries, select Group Execute from the View menu - an SQL query execution window will open
Using the MySQL command line client
- Open a Command Prompt on Windows, or a new shell on Linux
- If necessary, CD to the folder where the command line tools are located (the <mysql-install>/bin folder)
- Enter the following command (substitute your real username/password/databasename - your databasename will be the same as your username unless you have multiple databases)
mysql -P3500 -h127.0.0.1 -uUSERNAME -pPASSWORD DATABASENAME
- This will display something similar to the following:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 356918 to server version: 4.1.21-community-nt-log
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql>
- You are now ready to enter SQL commands, which must end with a semicolon. Try the following command to see the databases you have access to:
mysql> show databases;
Using JDBC
- To connect to the database from a Java application running on your local machine, you can connect to 127.0.0.1:3500, which is the IP and port your tunnel is listening on.
- An example JDBC URL is as follows:
jdbc:mysql://127.0.0.1:3500/YOUR_DATABASE_NAME?autoReconnect=true
See Also
--Neale Rudd
