Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

Im trying to build application, which uses 2 mysql servers. I set up port forwarding (thats the name to callit -i dont know) like that:

sudo ssh -g -L3306:foreignserver.ip:3306 me@foreignserver.ip

and i changed my own my.cnf port to 3360 and did

sudo service mysql restart

When i try to connect to the foreignserver - i fail.

mysql --host=localhost --port=3306 --user=userinforeignsystem --password=pwdthere

gives me error

ERROR 1045 (28000): Access denied for user 'userinforeignsystem'@'localhost' (using password: YES)

since i cant connect to the other server mysql database from outside, ssh/port forwarding is only way to develop for me. Database user privileges and usernames/passwords are doublechecked and correct. I could log in, using those credentials, in other server just fine.

Alan.

Edit: If i swap the username and password for my own mysql username and password and leave the port 3306, it somehow connects to my own computers mysql - i can select databases and see tables of my own mysql db not foreignserver.ip stuff. It looks like it either pays no aatention to port or something is wrong with port forwarding or something...

Edit2:

After some googling i found command

sudo netstat -plntu

and used it. When i removed lines not including ports 3360 and 3306 only those 2 were left:

tcp        0      0 0.0.0.0:3306            0.0.0.0:*               LISTEN      8145/mysqld     
tcp6       0      0 :::3306                 :::*                    LISTEN      8080/ssh  

Does that mean, that for some reason, my mysql is not running at 3360 despite the my.cnf?

Edit3: localhost/phpmyadmin variables also shows port 3306 in use.. guess thats where the problem lies...

Edit4: same time mysqladmin variables command shows port beeing 3360. Now im out of ideas.

edit5: doublechecked my.cnf and found out other places where port was set:

Proto Recv-Q Send-Q Local Address           Foreign Address         State       PID/Program name
tcp        0      0 0.0.0.0:3360            0.0.0.0:*               LISTEN      -               
tcp        0      0 0.0.0.0:3306            0.0.0.0:*               LISTEN      -               
tcp6       0      0 :::3306                 :::*                    LISTEN      -               

and now phpmyadmin shows port 3360 beeing used too. The mysql command from shell still fails to connect though.

EDIT6 - SOLVED. For some reason

mysql --host=localhost --port=3306 --user=userinforeignsystem --password=pwdthere

did not work, while

mysql --host=127.0.0.1 --port=3306 --user=userinforeignsystem --password=pwdthere

worked. If someone could explain that to me, then youd be teaching me something interesting and new :)

share|improve this question

1 Answer 1

up vote 2 down vote accepted

Looks like you're doing everything right, except that the user that you're trying to use doesn't have privileges for connecting from localhost.

The SSH tunnel will mask your IP using the endpoint, in this case it's the localhost machine, you have several ways to solve this.

  • Try to end the tunnel against another server that is in the same network, then you'll show up as connecting from that server

    sudo ssh -g -L3306:foreignserver.ip:3306 me@secondforeginserver.ip

  • Add permissions to the localhost user, adding permissions for '%' never grants permissions for localhost

Let me know how it goes!

share|improve this answer
    
I logged into 'foreignserver.ip' via ssh and i could log on mysql using exactly the same, copy-pasted, command i used in my own computer. And it worked fine there. –  Zayatzz Jan 20 '11 at 9:59
    
If i swap the username and password for my own mysql username and password and leave the port 3306, it somehow connects to my own computers mysql - i can select databases and see tables of my own mysql db not foreignserver.ip stuff. It looks like it either pays no aatention to port or something is wrong with port forwarding or something... –  Zayatzz Jan 20 '11 at 10:01
    
Everything right - except for running the ssh tunnel as root! –  symcbean Jan 20 '11 at 10:18
    
Yeah Zayatzz but permissions to access are per server, so diagnosing that you can access your localhost mysql server doesn't really mean that you'll be able to log into the remote one, as said it really looks like you don't have permissions to login from localhost –  lynxman Jan 20 '11 at 10:40
    
Well. Look at what i did to solve this - i did not change user permissions and user permissions remained all permissions for host %. –  Zayatzz Jan 20 '11 at 10:41

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.