ost Web hosting providers block port 3306 (the MySQL server port) at the firewall to prevent outside access. As a result, many MySQL developers and administrators can’t access and manage their own MySQL servers remotely; they must use the Web-based interfaces that their ISPs provide. Although these Web-based tools generally are well designed, their responsiveness (or lack thereof) is vexing?especially the annoying page-refreshes.

This security issue takes even more prominence with the recent occurrence of a MySQL worm that attacks Windows Server. The UDF Worm is self-propagating code that finds MySQL servers running on Microsoft Windows with poor firewall and password security and exploits them.

To defend against this worm, MySQL experts recommend the following:

• Block port 3306 on firewalls (as most ISPs do).
• Restrict access to root accounts.
• Use strong passwords that are resistant to brute force attacks.

To navigate such restrictive access, this article demonstrates how to securely connect and manage your MySQL server using SQLyog‘s HTTP and SSH tunneling features.

Using SQLyog with HTTP Tunneling
With HTTP tunneling, you can use all the features of a client/server application by just uploading a single PHP page (SQLyogTunnel.php) to your Web server. SQLyogTunnel.php exposes the MySQL API as a set of Web services that can be consumed by SQLyog (see Figure 1). This is exactly the architecture that Microsoft recommends in its “smart client” initiative.

Figure 1. SQLyog and HTTP Tunneling Architecture

SQLyog with HTTP tunneling provides some cool functions that a Web-based admin app can’t, including:

• Ability to upload any ODBC database (Access, MS SQL, Oracle) directly to your MySQL without going through the cumbersome process of CSV/SQL export/import
• Context-sensitive menus
• Synchronizes the schema between your development server and the production server
• Synchronizes the data between your development server and the production server
• Saves your work when you click on the close button of your browser!
• 100 percent keyboard friendly for turbo speed MySQL management

Implement HTTP Tunneling
First, download SQLyog. After installation, provide SQLyog with your MySQL connection details.

To connect to your MySQL DB using HTTP tunneling, upload SQLyogTunnel.php (available with the SQLyog Enterprise installer) in your Web server. SQLyogTunnel.php exposes the MySQL C API as a set of Web services that SQLyog Enterprise can understand. (Note: For SQLyogTunnel.php to work, you need to have PHP compiled with MySQL and XML extensions.)

In the connection window, select the Tunnel tab, check the Use HTTP Tunneling option, and provide the URL of the tunneling page (see Figure 2). Make sure that the MySQL Host Address you provide in the Server tab is accessible from the Tunneling URL. For example, if your Web server and MySQL are hosted on 234.56.45.21, then you need to specify localhost in the MySQL Host Address field of the Server tab instead of 234.56.45.21.

Figure 2. HTTP Tunnel Settings in SQLyog Connection Dialog

That’s it. Press Connect to log into your server and enjoy working with SQLyog.

Using SQLyog with SSH Tunneling
HTTP tunneling has slow response times compared with direct connections since the data is XML encoded and HTTP is stateless by nature. If you are lucky, your Web host provides SSH tunneling to your MySQL or Web server. You can then use SQLyog and SSH tunneling to securely and efficiently manage your MySQL database.

What Are SSH and SSH Tunneling?
SSH stands for secure shell host. You can access most systems in two ways. The most common way is to use a telnet program. Accessing a shell account in this fashion poses a danger, however. Everything that you do during a telnet session is visible in plain text on your local network and the local network of the machine to which you are connected.

The alternative way, SSH, provides a solution by encrypting the data being sent over the network, which prevents human-readable passwords from ever being passed over the network. SSH can use several different forms of encryption, anywhere from 56- to 1024-bit, and it has been ported to operating systems on several platforms including Linux, Microsoft Windows, and Macintosh.

You can use SSH to encrypt communications between SQLyog and your remote MySQL server. This is known as SSH tunneling and it is actually quite simple to use.

Benefit of SSH Tunneling
Many hosting companies that provide MySQL hosting block access to the MySQL server from outside the hosting company’s network and grant access only to users connecting from localhost or from a machine within the network. The major benefit of SSH tunneling is that it allows you to connect to a MySQL server from behind a firewall when the MySQL server port is blocked (see Figure 3). As long as the hosting company provides SSH access, you can still connect through SQLyog’s SSH Tunneling option.

Figure 3. SSH Tunneling Architecture

Setting Up SQLyog for SSH Tunneling
Select the Tunnel tab in the connection window (see Figure 4) and provide the information listed in Table 1.

Figure 4. SQLyog SSH Tunneling Settings

To understand the setup better, connect to a MySQL server using the following real life example:

• Your Web site is hosted on the Web server 234.56.65.78, which also hosts a SSH server that runs and listens on port 22.
• Your MySQL server is located on server 234.56.65.79, and it allows connections only from localhost or another machine within the same network (i.e., 234.56.65.78).
• You publish your Web site on 234.56.65.78 using the username and password your ISP provides (for this example, the username and password are ssh_user and ssh_pwd, respectively).

To complete the setup for this example, you have to input the following values for various server and tunnel options:

Press Connect to log into your server, and you can then use SQLyog and SSH tunneling to securely and efficiently manage your MySQL database.

Secure Connectivity with SQLyog
SQLyog gives you a choice of connectivity options. No matter what your firewall/proxy/ISP settings are, you should be able to use it to smartly manage your MySQL server(s).