Connecting to MYSQL over SSH tunnel

mysql ssh tunnel

11,196

When you do ssh tunnel it make a encrypted communication between your system to remote server and bind the remote opened port to your defined port.

ssh -L 33333:localhost:3306 [email protected]

here 3306 as you said is mysql port no.

use IPADDRESS instead of localhost i.e 127.0.0.1

Connection to 10.10.0.31 closed.
linux@tuxworld:~$ ssh -fNg -L 33333:localhost:3306 [email protected]
[email protected]'s password: 
linux@tuxworld:~$

See below eg which I did short while ago, mysql user is root and ssh user is also root. I opened a new terminal

linux@tuxworld:~$ mysql -u root -h 127.0.0.1 -P 33333
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.1.69 Source distribution

Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> exit
Bye
linux@tuxworld:~$

11,196

Author by

Scott W

Updated on September 18, 2022

Comments

Scott W over 1 year
I can ssh onto the remote server and login without a problem.

I'm trying to connect to a remote mysql db over a ssh tunnel and running into issues. I've created the tunnel successfully (verified by telnet). When I try to login with the command:
```
mysql --host=127.0.0.1 -P 3302 -u fakeuser -p
```
I get
```
Access denied for user 'fakeuser'@'192.168.100.93'
```
The issue is the @192.168.100.93. I believe I need that to be fakeuser@localhost. Since I'm connected through the SSH tunnel why isn't it being set to localhost? How do I force it to localhost? where is it getting the 192.168.100.93 from?

Note that I can't change any settings on the MySQL server.

Thanks for the help!
- jpganz18 almost 11 years
  
  did you check that user with that host has privileges over that db?
- Scott W almost 11 years
  
  it does not have those privileges - I thought that was the point of the SSH tunnel, so it appeared to MySQL that the connection was coming from localhost (of the MySQL server) as opposed to the outside world
- Admin almost 11 years
  
  How did you set up the tunnel? (What options are you using to ssh?). If you specified ssh -L3302:remotehost:3306, then it's going to forward that port to the IP that resolves to 'remotehost'. Try -L3302:localhost:3306 or -L3302:127.0.0.1:3306.
- Scott W almost 11 years
  
  I did -L3302:127.0.0.1:3306. My problem is that when I do mysql --host=127.0.0.1 -P 3302 -u fakeuser -p, MySQL logs me in as 'fakeuser'@'192.168.100.93', when only fakeuser@localhost has permission.
- Scott W almost 11 years
  
  Okay, solved it. Apparently when setting up the SSH tunnel, I used localhost, instead of 127.0.0.1, which caused some issues when logging into MySQL.
- Sam about 9 years
  
  Based on the solution, this is a duplicate of: serverfault.com/questions/361771/mysql-over-ssh-tunnel
Scott W almost 11 years

my problem is that when I do that, MySQL tries to log me in as 'fakeuser'@'192.168.100.93', when I want it to login me in as localhost. I specify -h 127.0.0.1, yet it still logs me in as [email protected]
Sharad Chhetri almost 11 years

I have already mentioned. "use IPADDRESS instead of localhost i.e 127.0.0.1" . I think I should have to elaborate more. The actual mean as use 127.0.0.1 .It seems your problem is solved.