6

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!

Scott W
  • 165
  • 1
  • 1
  • 4
  • did you check that user with that host has privileges over that db? – jpganz18 Jun 19 '13 at 20:17
  • 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 – Scott W Jun 19 '13 at 20:24
  • 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. –  Jun 19 '13 at 22:40
  • 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 Jun 20 '13 at 20:10
  • 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. – Scott W Jun 20 '13 at 21:58
  • Based on the solution, this is a duplicate of: http://serverfault.com/questions/361771/mysql-over-ssh-tunnel – joe May 08 '15 at 04:10

1 Answers1

4

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 fakeuser@server.remote.com

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 root@10.10.0.31
root@10.10.0.31'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:~$
Sharad Chhetri
  • 129
  • 4
  • 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 fakeuser@192.168.100.93 – Scott W Jun 20 '13 at 20:09
  • ssh -L 33333:localhost:3306 fakeuser@server.remote.com did not work for me but ssh -L 33333:127.0.0.1:3306 fakeuser@server.remote.com did – Scott W Jun 20 '13 at 22:01
  • 1
    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. – Sharad Chhetri Jun 21 '13 at 04:02