Mysql – 2 different thesql servers on localhost

MySQL

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 🙂

Best Answer

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!

Related Topic