Most MySQL developers have a copy of their databases running on a local PC. Although the MySQL command line may be fun for a while, it is no substitute for a decent database design application such as Workbench or SQLyog.
Connecting to a local database on localhost’s port 3306 is easy. However, when it comes to configuring the remote database, many developers resort to SSH or slower web applications such as phpMyAdmin. Fortunately, SSH tunneling allows you to use your favorite desktop design application to access your remote MySQL database.
What is Tunneling?
Wikipedia has a great description of tunneling – but life is too short to understand it. In brief, a “tunnel” is created between your PC and the server and it appears as though the remote MySQL database is run locally. If your web host supports SSH, you should be able to configure tunneling.
Tunneling Software
Windows users can download Putty and Plink from the Putty website. Copy both files to a folder of your choice.
Mac or Linux users have SSH built in; use the following Windows instructions as a guide and refer to your documentation.
Digging the Tunnel
Start a command line console, CD to the Putty/Plink folder, and enter:
plink -L 3307:localhost:3306 myusername@mydomain.com
Where:
- 3307 is the local port used to connect to the remote database. You can use 3306, however, this could conflict with your PC’s MySQL installation.
- localhost and 3306 is the MySQL address from the remote server (most people will not need to change this)
- myusername is your SSH user name
- mydomain.com is the remote server’s domain or IP address.
The first time you connect, you will be asked if the SSH key can be added to the cache. You will then be asked for your SSH password before the tunnel is established.
(Tip: copy the Plink command in to a .bat file so it can be launched with a click.)
Attaching to the Remote Database
You can now connect to the remote database from SQLyog or any other database client:
The connection details are:
- ‘localhost’ for the MySQL host address (your end of the tunnel)
- ‘3307′ for the local port to the remote database (specified in the Plink command)
- the remote database name, and
- the remote username and password, i.e. a MySQL account with permissions to access and configure the database.
All going well, you should now be able to manage your remote database as easily as a local version.
Does anyone have further tips or advice regarding remote MySQL administration?
Related posts:
- How to Install MySQL Installing MySQL is easier than you think. Craig provides a...
- How to Use MySQL Foreign Keys for Quicker Database Development Craig shows how MySQL can automate data updates and retain...
- Build Your Own Dev Server with VirtualBox What's the best way to test your web site on...
- MySQL Cross-Platform Table Naming Craig highlights the biggest potential pitfall of porting your MySQL-based...
- EU Object to Oracle’s MySQL Takeover Oracle's takeover of Sun Microsystems has been thrown into doubt...
Nice little how-to.
Navicat mySQL for Windows http://mysql.navicat.com/ which can do a similar SSH tunnelling out of the box.
March 10th, 2009 at 3:10 am
For OS X, I’d recommend SSH Keychain: http://www.sshkeychain.org/
It’s a nice, quick way to create tunnels – especially for creating regular tunnels.
Cheers
March 10th, 2009 at 9:18 am
For Mac OS X users, you should be aware that you need to use the ssh command, not the plink command, e.g.:
ssh -L 3307:127.0.0.1:3306 myusername@mydomain.com
For whatever reason, when tunneling for MySQL I have more success using the loopback IP address, rather than localhost, hence the other change in the line above.
Finally, and also for Mac OS X users, there’s a nice (and free) GUI tool called SSH Tunnel Manager, look for it on VersionTracker.com for download link, etc. It makes setting up and saving all of your SSH tunnels simple, adds further options, and will even show you the equivalent command line command, if you need to use it in a script or something.
March 10th, 2009 at 1:36 pm
Thank you for mentioning SQLyog.
SQLyog Enterprise takes care of SSH tunneling and you don’t have to set up the tunnel manually by using Putty/Plink or something similar.
Also, many shared hosting providers don’t support SSH tunneling. Notable examples of such hosting providers are Yahoo Small Business Hosting and GoDaddy. For such setups, SQLyog Enterprise provides HTTP tunneling wherein you upload a PHP page to your web-server and configure SQLyog Enterprise to communicate with that PHP page.
A detailed tutorial is available at: HTTP Tunneling
March 10th, 2009 at 3:46 pm
Thanks for the OS X tips. Any Linux users out there with helpful tips?
March 10th, 2009 at 5:07 pm
well have done this thing severally using navicat and its not really a big deal if u ask me….all i did was use the remote connection parameters as is….and that was all….anyways keep it coming
March 10th, 2009 at 5:39 pm
@Rohit Nadhani
I’ve been using SQLyog for several years, and I can confirm that it’s an excellent product. If I remember correctly, the Enterprise version can use a PHP proxy so you just need to upload a single file to your server.
March 12th, 2009 at 4:16 am
@Craig,
Yes. You just need to upload a single PHP file for the proxy. We call this feature HTTP Tunneling.
March 12th, 2009 at 2:46 pm
For those who perhaps have a virtual private server hosted somewhere. port 3306 needs to be included in the TCP_IN so your firewall can grant remote access via that port to mysql server.
Mysql Query browser also does a good job in connecting remotely to your remote mysql server. same details are reqiured as that of the screenshot above.
March 14th, 2009 at 7:40 am
Peter M. mentioned Navicat MySQL – does anyone know if Navicat can handle PHP functions also, or is it strictly for MySQL database dev/admin?
March 16th, 2009 at 9:04 pm
I have just purchased a full featured version of navicat. It does cost some money but you can try it for free to see if you like it. I personally think it is the cat’s pajamas and I use it to admin my remote MySQL everyday.
A tutorial could be found at: http://mysql.navicat.com/SSH-Tunnel-for-MySQL-Server-management/SSH.html
March 23rd, 2009 at 5:36 pm