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.
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.
Daily Email Updates
You can get our how-to articles in your inbox each day for free. Just enter your name and email below:
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
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
is your mysql server running?
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.
You mean remote server is over the internet. i also having this problem, can someone help.
I just solve this problem, it may cause by FireWall or Mysql Server Port Number wrong or Error. try to reset it.
i think this is what is missing when installing mythbuntu
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
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
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.
Rene:
Thanks a lot! Solved my problem
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
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.
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.