Connecting to Database through a MySQL Client Using Port Forwarding
You will need the following data to connect:
- SERVER – the name of your domain
- SFTP_USER – user for SFTP
- SFTP_PASS – password for SFTP
- MYSQL_USER – user for MySQL
- MYSQL_PASS – password for MySQL
Tunneling will be done through the local port 3333. Before starting, make sure that the port is available. If not, use any other port.
ssh SFTP_USER@SERVER -L localhost:3333:127.0.0.1:3306 mysql -h 127.0.0.1 --port 3333 -u MYSQL_USER -pMYSQL_PASS
The use of port forwarding in Windows requires an external program, such as Heidi SQL. After starting Heidi SQL click on New in the bottom left corner:
- In Network type select MySQL (SSH tunnel)
- Hostname / IP leave 127.0.0.1
- User and password - set the login data for your database.
- Port -select 3306
Go to the second tab SSH tunnel. In the first box you need to enter the location of the plink.exe file on your computer. This file is is part of the tool Putty, so It can already be installed on your computer. If your are not sure, you can download the file via the link provided under this item. If you download the file, we recommend that you save it in the default directory on your computer, for example C:\Documents and Settings\User.
In this step, you need to save the RSA key to cache memory.
This is done as follows:
- In Windows, click Start -> Run. Type in cmd and click OK in the command line that will appear. (In Windows Vista, Windows 7 or higher, type in cmd directly in the search bar in the Start menu).
- Windows command line will appear. You should be able to see the path to the default directory, which should be C:\Documents and settings\User. You need to be in the folder where the plink.exe file is located (therefore it is convenient to save the plink.exe file in this directory. If the file is saved elsewhere, use the command “CD name of folder” to move between directories and “CD ..” to move up).
Once you are in the correct folder, enter the following command:
In the SFTP login field enter your username to the domain SFTP and the name of your domain after the at sign.
The computer should show the statement seen in the screenshot.
Enter Y after the question Store key in cache? and press Enter.
This procedure needs to be applied only the first time; the RSA key will stay stored in cache.
You may now close the command line and return to HeidiSQL settings:
- In the item SSH host + port enter the name of your domain including www
- In username and password enter your login to SFTP.
- In the last item Local port enter 3333. We recommend you to save the settings by clicking Save so that you do not have to fill in the data again in the future.
Click Open to connect to the database.