Programming

How to Administer a Remote MySQL Database using SSH Tunneling

    Craig Buckler
    Share

    MySQL tunnelMost 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:

    MySQL connection

    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?