Overview
This article explains how to tunnel a MySQL connection through SSH. This is useful when you want to execute MySQL commands or develop applications remotely from your server.
Requirements
Before you start, you'll need:
- SSH accessSSH access
- SSH client
- MySQL internal hostname: internal-db.s00000.gridserver.com. Be sure to replace 00000 with your site number.
- IP address for your VPS
Instructions
Configure the tunnel
First, let's set up a local tunnel on a port. This ensures that local commands will be passed through to the remote server.
NOTE:
These instructions are for Terminal on Mac OS X. For Windows, we recommend PuTTY and the instructions to forward port 3306.
Run the following command in Terminal:
-
ssh -L 3306:internal-db.s00000.gridserver.com:3306 example.com@example.comBe sure to replace internal-db.s00000.gridserver.com with your own database server name, and example.com@example.com with your SSH login. You should replace example.com with your primary domain. You can find more database information within your Server Guide.
-
ssh -L 3306:xxx.xxx.xxx.xxx:3306 username@example.comYou will need to replace xxx.xxx.xxx.xxx with your own IP address or server name, and username@example.com with your own login.
Keep the SSH session running while you work. This will keep the tunnel open.
Explanation of the command:
- ssh - You are using SSH to forward the port.
- -L - This option lets you specify a local port that will be forwarded to your remote host and remote port.
- 3306: - Local port 3306 will be forwarded over your SSH connection. 3306 is standard for MySQL.
- The IP address or hostname of the remote MySQL server.
- internal-db.s00000.gridserver.com: For a Grid, you should use your database server name. You can find this in your Server Guide in the Database section.
- xxx.xxx.xxx.xxx: Use your IP on your VPS server.
- :3306 - Remote port 3306 will have your local port mapped onto it. 3306 is standard for MySQL.
- username@example.comexample.com@example.com is your standard SSH login, incorporating your username and server. See Connecting via SSH to your serverConnecting via SSH to your server for details.
Connect to MySQL
Now any MySQL commands that you run locally will be tunneled through SSH to your remote MySQL server. This includes commands in scripts and applications that you may have developed and any other programs that execute locally.
Here's a quick test command that you can run in a new tab in Terminal. Remember: Don't close your tunnel session. Make sure you replace the server name and username with your own information. The server name is listed after -h, and the username is listed after -u.
-
mysql -h internal-db.s000000.gridserver.com -u db00000 -p
Please see How do I create a database? and Database users on the Grid for help with your settings.
-
mysql -h 127.0.0.1 -u admin -p`cat /etc/psa/.psa.shadow`NOTE:
Don't change the IP address. -h 127.0.0.1 is specifying a local MySQL connection, which is what you want. See How do I create a database? for assistance with your username and password.
NOTE:
The 'admin' user's password is a hashed version of the admin password used to login to the Plesk control panel. This hashed version can be found in /etc/psa/.psa.shadow
- Enter your password at the prompt. You will now be connected to the remote MySQL server through the encrypted tunnel opened in the previous section.
Comments