CODEWITHSUNDEEP
mysqlsshmysql-workbenchputty
TrevDred
TrevDred

Reputation: 109

Connecting to Remote MySQL DB over ssh with workbench

I'm trying to connect to a remote DB with MySQL Workbench over SSH. The remote DB I'm trying to connect to has firewall which is private key protected (I have coverted this to OPEN SHH). I keep getting an error when I try to connect, I'm just a little confused on what is the information of the DB I should be using. I consistently see people use localhost and the DB changed intermittadely at the same point in different questions. My set up is currently that I have to connect to the server of the firewall(check) now there are multiple VMs running I wish to connect to e.g. 192.1.91.0. I have also a port set up to this on putty( I don't know if this is relevant) on localhost 5000. When I try to connect using 192.1.91.0 port 3306 or localhost 5000. I get the same error. Can anyone help. Attached is an image of the information I put into workbenchenter image description here

Upvotes: 1

Views: 3578

Answers (3)

Farooq Butt
Farooq Butt

Reputation: 201

To connect workbench with a private database you will need a 'jump host' also called 'bastion host' which can be any EC2 instance in a public subne in same VPC as database.

Follow Below Steps:

  1. Open the security group attached to the database, and add new rule as below:-

Type:MYSQL/Aurora, Protocol:TCP, PortRange:3306,
Source:securitygroupofEC2 (you can all security group by entering 'sg-')

  1. Open the security group attached to the EC2, and make port 22 is open. If not, add a new rule as below:-

Type:SSH, Protocol:TCP, PortRange:22, Source:MY IP

  1. Open Workbench, Click New connection
- Standard TCP/IP over SSH
 - SSH Hostname : < your EC2 Public IP >  #34.3.3.1
 - SSH Username : < your username > #common ones are : ubuntu, ec2-user, admin
 - SSH KeyFile: < attach your EC2 .pem file>

 - MYSQL Hostname: <database endpoint name> #mydb.tbgvsblc6.eu-west-1.rds.amazonaws.com

 - MYSQL Port: 3306
 - Username : <database username>
 - Password: <database password>

Click 'test connection' and boom done!!

Upvotes: 1

Mike Lischke
Mike Lischke

Reputation: 53502

I think there are a few misunderstandings here. A firewall is not protected by a keypair. An SSH connection is. The firewall only filters network traffic using specific rules (e.g. only let it pass for enabled network ports). When you use an SSH connection you have to use the MySQL address as seen from the remote SSH connection end. That means if the MySQL server runs on the same machine as the SSH server (which is what you connect to when you use an SSH tunnel) then the address is localhost (or the IPv4/IPv6 loopback address). See my video about connection creation and troubleshooting on Youtube for more details.

Upvotes: 2

Demigod
Demigod

Reputation: 116

If the MySQL Server is configured to accept remote connections, you can use the servers IP address, but then you don't need to connect over ssh. Once you choose connect over ssh, the workbench creates a portforwarding using the ssh credentials to the specified server. So you have to use localhost as MySQL hostname. Due to the portforwarding, the request will be forwarded to your remote machine. In short: As far as I understand your question, you have to use localhost.

Upvotes: 0

Related Questions