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.
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
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.
Run the following command in Terminal:
ssh -L 3306:internal-db.s00000.gridserver.com:3306 email@example.com
Be sure to replace internal-db.s00000.gridserver.com with your own database server name, and firstname.lastname@example.org 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 email@example.com
You will need to replace xxx.xxx.xxx.xxx with your own IP address or server name, and firstname.lastname@example.org 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.
- email@example.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
mysql -h 127.0.0.1 -u admin -p`cat /etc/psa/.psa.shadow`
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.
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.