Subscribe to 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.

| More
This article was originally written on 06/29/07 Tagged with: Ubuntu

Daily Email Updates

You can get our how-to articles in your inbox each day for free. Just enter your name and email below:


Name:
Email:

Comments (14)

  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.


Leave a Comment




Leave your friendly comment here.

If you have a computer help question, click here to leave it on the forums instead.

Note: Your comment may not show up immediately on the site.

Our Friends
Getting Started


About How-To Geek
What Is That Process?
svchost.exe
jusched.exe
dwm.exe
ctfmon.exe
wmpnetwk.exe
mDNSResponder.exe
wmpnscfg.exe
rundll32.exe
wfcrun32.exe
Ipoint.exe
Itype.exe
Wfica32.exe
Mobsync.exe
conhost.exe
Dpupdchk.exe Adobe_Updater.exe

Copyright © 2006-2009 HowToGeek.com. All Rights Reserved.