SEARCH

How-To Geek

Access Your MySQL Server Remotely Over SSH

So you’ve got MySQL on your web server, but it’s only opened to local ports by default for security reasons. If you want to access your database from a client tool like the MySQL Query Browser, normally you’d have to open up access from your local IP address… but that’s not nearly as secure.

So instead, we’ll just use port-forwarding through an SSH tunnel, so your MySQL client thinks it’s connecting to your localhost machine, but it’s really connecting to the other server through the tunnel.

If you are using the command line ssh, the command would look like this. (You can do the same thing graphically in Putty or SecureCRT options if you need to)

ssh -L 3306:localhost:3306 geek@webserver.com

The syntax is ssh -L <localport>hostname<remoteport> <username>@<servername>. We’re using localhost as the hostname because we are directly accessing the remote mysql server through ssh. You could also use this technique to port-forward through one ssh server to another server.

If you already have mysql running on your local machine then you can use a different local port for the port-forwarding, and just set your client tools to access MySQL on a different port.

image

Once you’ve got the ssh tunnel going, you can open up MySQL Query Browser and enter in the details for your remote server, using localhost as the server host, and adjust the port to whatever you used.

Once you get used to this method, you’ll wonder why you ever used phpmyadmin or the command line version.

Lowell Heddings, better known online as the How-To Geek, spends all his free time bringing you fresh geekery on a daily basis. You can follow him on if you'd like.

  • Published 06/29/07

Comments (29)

  1. sazwqa

    Just remember if you already have MySQL installed on client machine then u need to change the localport to something else, since mysql will be using 3306, like:

    ssh -L 7777:localhost:3306 geek@webserver.com

  2. René

    Sorry, but doesn’t work here. I get the error

    Can’t connect to local MySQL server through socket ‘/var/lib/mysql/mysql.sock

    I have Mysql installed locally, but it is not running. I used

    ssh -L 3300:localhost:3306 user@domain.com

  3. Andre

    is your mysql server running?

  4. David

    can someone help, i want to use mysql query browser to connect remote server mysql 5.0 with port 3306. but it always show error 2003. if i set the server to localhost , it can run.

  5. John

    You mean remote server is over the internet. i also having this problem, can someone help.

  6. David

    I just solve this problem, it may cause by FireWall or Mysql Server Port Number wrong or Error. try to reset it.

  7. 0zSpitt

    i think this is what is missing when installing mythbuntu

  8. wqas

    I have same problem connecting MYSQL Sever using SSH tunneling but my scanrio is different. i have a linux server which has enables ssh but MYSQL server is another server on the LAN of SSH server.

    i.e
    MYSQL Client->SSH->SSH Server->(LAN)->MYSQL Sever

    Can some body advice me how to connect in this scanrio
    I have acess to my middle server using SSH but even i can connect to mySQL server using command line. but how can i connect using MYSQL GUI

    Waiting for reply
    Thanks

  9. René

    wqas,

    I don’t think that would work with port forwarding. If the remote SSH has free access to the LAN and when the MySQL is open to LAN requests, then it would be possible to access it this way:

    ssh -L 3300:mysqlserver.com:3306 user@sshserver.com

  10. Rene

    Could it be that MySQL Query browser is using the command line tool mysql? If I try with

    telnet localhost 3300

    I get the version number of the remote MySQL server. So the port forward and connection are OK.
    T
    hen I try:

    mysql –host=localhost –port=3300 –user=username –password=mypw database

    And I get the same:
    ERROR 2002 (HY000): Can’t connect to local MySQL server through socket ‘/var/lib/mysql/mysql.sock’ (2)

    But when I activate my local MySQL server I get:

    ERROR 1045 (28000): Access denied for user ‘username’@’localhost’ (using password: YES)

    Then I tried:
    mysql –host=127.0.0.1 –port=3300 –user=username –password=mypw database

    And it connected correctly!

    Conclusion: the mysql tool always connects through a socket when you use localhost and MySQL Query Browser does the same.

  11. kslat3r

    Rene:

    Thanks a lot! Solved my problem

  12. skyboysea

    If you use the following command you force mysql to use TCP instead of a socket and you don’t need to have mysqld running on the local computer.

    mysql –port=1234 –protocol=TCP -u username -p

  13. Billy

    I did everything the article mentioned *except*

    1. I changed “localhost” to “127.0.0.1” (thanks Rene)
    2. I avoided 3306 for my local port since I have mysql already running locally on that port (thanks Sazwqa)

    This approach worked for me.

  14. ted

    Hello I use the freeware ” myentunnel” http://nemesis2.qx.net/pages/MyEnTunnel : it uses Plink ( putty) to create forwarded link and well it’s GUI so it’s easy to use, save configuration, use private key file. For mysql all you have to do is to write : 3306:localhost:3306 in the tunnel section under “local” .So now I can connect to my remote server to connect my database server and everything between me and the server is encrypted. But there is also another way for those who dont want any trouble: to use my-en-tunnel ( well putty if you have time to waste) and a software like http://www.proxifier.com : it will automaticly redirect any application on any ports to the ssh tunnel. this is great because you dont even have to setup any ports: whatever it’s mail, web, ftp : it translate it to myentunnel which pass all this encrypted to your server and of course same thing the other way.

  15. mr bojangles

    this will fail to work (connection hangs or timeouts) when trying to connect to remote mysql if the iptables rules are not properly set.My mistake: forgetting to let the loopback port open.solution: iptables -I INPUT 1 -i lo -j ACCEPT

  16. max-k

    Hello,
    When i use this command : ssh -L 3307:localhost:3306 user@domain, i’m always connected to the remote.
    This is a problem for me because i want to make a lot of port-forwarding and don’t want to open a lot of consoles.
    Is it possible to run ssh port-forwardings in background ?

  17. pobzeb

    Just add a -N to the end of your ssh command to run it in the background.

    Ex:
    ssh -L 3307:localhost:3306 user@domain -N

  18. Aldo

    I always try to connect to MySQL Query Browser using a tunnel but it just connects to my local database… this is weird, anybody know why?? I mean all the others databases doesn’t appear.

  19. Sven

    If the hosts file is wrong there will be a timeout as well.
    Check that the server side /etc/hosts have the following line:
    127.0.0.1 localhost
    If there’s a preconfig for IPv6, like many VPS instances, there’s a line with “localhost” for IPv6. Remove the name localhost from the other lines so the only line containing “localhost” is the first one. Other concatenated versions like “localhost.domain” can still be there.

  20. harold

    i got a permision denied error

    Permission denied (publickey,gssapi-with-mic).

    what could be the problem?

    [root@admin1 .ssh]# ssh -L 3307:localhost:3306 root@rakis.com
    The authenticity of host ‘rakis.com (209.216.249.55)’ can’t be established.
    RSA key fingerprint is 61:50:dc:bf:6b:d2:e6:fb:2e:cb:4b:80:a4:29:0d:27.
    Are you sure you want to continue connecting (yes/no)? yes
    Warning: Permanently added ‘rakis.com,209.216.249.55′ (RSA) to the list of known hosts.
    Permission denied (publickey,gssapi-with-mic).
    [root@admin1 .ssh]# ssh -L 3307:localhost:3306 root@rakis.com
    Permission denied (publickey,gssapi-with-mic).

  21. Dom

    good tutorial mate although I had to change localhost port to 3308 guessing this is something to do with local mysql install even though i shut down mysql locally before I ran this. Thank you for posting

  22. vikram

    how can use mysql query browser through my ip “192.168. . ” instead of local host or 127.0.0.1 in ubuntu 9.04

    plz help me

  23. Nappy

    Thank you mr bojangles!
    iptables -I INPUT 1 -i lo -j ACCEPT
    That was a life saver and saved me a lot of time debugging.

  24. Tomer

    Good tutorial! Thanks!

  25. Kadimi

    Great post and very helpful to me, if only you didn’t say : “Once you get used to this method, you’ll wonder why you ever used phpmyadmin or the command line version.”

    Phpmyadmin is a great open source tool that have many features that maybe MySQL AB never heard of.

    The command line (mysql >) is simply fantastic and the fastest way to learn mysql (learn != have notions)

  26. Kat

    This is exactly what I needed. Thank you!

  27. Viktor

    i have made a tunnel with ssh -f -N -i /path/to/identity/file -L 127.0.0.1:3306:localhost:3306 user@server,
    i see the listener on port 3306 on localhost:
    tcp 0 0 127.0.0.1:3306 0.0.0.0:* LISTEN –
    when i’m trying to connect to mysql with command line client
    mysql –protocol=tcp -h 127.0.0.1 -u username -p -P 3306
    i’m getting error:
    channel 1: open failed: connect failed: Connection refused
    ERROR 2013 (HY000): Lost connection to MySQL server at ‘reading initial communication packet’, system error: 0

    From google i found, that one line need to be added to /etc/hosts.allow on server side
    mysqld: ALL
    To avoid errors like mentioned above.
    But i still getting that error.

    Does anybody know how to deal with this error?

  28. Viktor

    Oh, i’ve solved it:
    The problem was in my mysql server/ssh tunnel settings.
    Explanation: Mysql server was listening on 192.168.10.10 interface, not on 127.0.0.1,
    so i have recreated the tunnel with
    ssh -f -N -i /path/to/identity/file -L 127.0.0.1:3306:192.168.10.10:3306 user@server
    then i was able to connect to sql server with command line client:
    mysql -u user -h 127.0.0.1 -p

  29. David

    im trying to reconnect to mysql and i cant seem to remember so i tried it the way this post says and this is the error i get :

    80070007: SSH Tunnel: Socket error on connecting. WSAGetLastError return 10061 ($274D)

    how do i resolve this?

    ive used all the ports that ive found in the instructions and i get the same error…ive had mysql connected before i just dont remember how i did it. please help me out. i appreciate it!

Enter Your Email Here to Get Access for Free:

Go check your email!