Page History

How do connection names resolve to IP's?

It does not answer questions about the SQLyog program as such. But we experience quite often that SQLyog user having problems in getting the connection to one ore more MySQL servers working are missing the basic understanding of the TCP protocol that is used for connection. So this is a 'background article' that supplements the more typical FAQ items here.
The TCP protocol needs an IP to connect to a host. An IP is a group of 4 2-digit hexadecimal numbers. Like '0A.1B.2D.3D'. It is common to express this in decimal format 'v.x.y.z' where v,x,y and z are numbers between 0 and 255. To get your own IP in this format just type (on windows) 'ipconfig' on a command-line and the IP is returned.
The IP returned can be a global ip (accessible directly from the internet) or - if you are behind a router - a local ip that only applies behind the router. All computers behind the router share the global ip of the router itself. The router has the functionality to ensure that communication from each machine behind it to and from the internet works.
The TCP-protocol communicates on 'ports'. There are about 65.000 ports possible. A TCP-port is nothing physical but just a number that each 'packet' of information that is sent is coded with. Various ports are reserved for various server programs and programs with sort-of server functionality. Here, you can review the complete list of

1. Using client hosts' file
2. Using (local) nameserver lookup
3. Using Domain Name Server (DNS) lookup.
4. Using Remote Network routing (using either simple routing based on ports, hosts file or nameserver) on remote network.

1. Client hosts' file
There is an excellent article on the hosts file

127.0.0.1 localhost

You might find more lines too - for several reasons: some viruses and spyware add items to the hosts file. Some users and Sys Admins do it on purpose. But since you are able to connect to a MySQL server running on your local machine with the host name 'localhost' it is because of the above line in the hosts file. The hosts files resolves the name 'localhost' to ip 127.0.0.1. If you prefer to use 'cutie' instead of 'localhost' then just add the line

127.0.0.1 cutie

to your hosts file!
2. A local nameserver.
A local nameserver can be installed explicitly or implicitly. If you have more windows computers connected on a windows' network (using the TCP protocol - not old NETBEUI protocol etc) you already have a local nameserver that was implicitly installed. If you have two machines on your network named 'Bonnie' and 'Clyde', then you can connect to the MySQL server running on 'Bonnie' from the computer 'Clyde' with SQLyog entering 'Bonnie' as the host name. Because the nameserver that was implicitly installed as a part of the Windows network resolves the name 'Bonnie' to 'Bonnie's ip on the network
3. A Domain Name Server
Domain name Servers are part of the Internet structure. Any name/string in the format domain.topleveldomain (ie: school.edu, business.com, whisky.org etc. ) can be looked up on the Domain Name Servers of the Internet. The ip returned is then used for creating the connection.

A typical example

You connect to a remote server with the host name mydb.thisismyisp.com. What happens here? Simply:

• First: local hosts file is checked if there are any matching entry. if not proceed to step 2.
• Second: local nameserver (if exists) is checked if there are any matching entry. if not proceed to step 3.
• Third: Domain Name Server lookup identifies the ip of domain-host thisismyisp.com
• Fourth: local network routing systems (router, nameserver or hosts file) on the remote network identifies the local IP mapped to name 'mydb´'

After following the previous steps mydb.thisismyisp.com is resolved to ip v.x.y.z on the internet and IP a.b.c.d on the remote network. Once connection is established the routers and other networking gear in the complete communications chain keeps that information until it is closed or times-out due to inactivity (or some error occur).

One more detail, when connecting to the MySQL server at an ISP it is common practice that MySQL user names and MySQL database names must be prefixed with the domain user name (the one that is used for FTP for instance). Like me_username and me_mydb. Simple because other users may have created a MySQL user username and a MySQL database mydb. But failure to do so does not result in a connection error, but an authentication error. Also note that MySQL usernames can be up to 16 characters long only, review the error "I have a very long username for the MySQL database at my ISP. SQLyog won't let me use it" that is listed below.

Nevertheless, everything can get 'messed up' if it is the first time you try to connect to a remote MySQL server! And here tunneling is not involved.

What should I enter as 'hostname' when connecting to a MySQL server at an ISP.

It depends on the systems and the network settings at your ISP. The systems that ISP's operate are too numerous to mention. Some operate almost all their server programs on one very powerful (UNIX-) machine, others operate a network of many computers that are basically PC-type hardware. Some use Linux/Unix operating systems other Windows server systems (and some mix it!). And ISP's vary in size from 200 customers to 2.000.000. They can't all operate the same systems!
But the general answer is that probably the ISP operates some name server internally. Such name server will typically be configured with the some name like 'mysqlserver' pointing to the ip of the mysqlserver on the local network.

• Direct connection:

If the ISP has the domain name thisisanisp.com then with direct connection you will use mysqlserver.thisisanisp.com.
The information about the name assigned to the mysql server and thus the exact URL to the MySQL server usually is available as part of your account info from some web-based control panel application. Most likely it is something like 'mydb1.thisisanisp.com' or 'mysql.thisisanisp.com'. If you cannot find the information yourself you will have to ask the support/help desk there.
It also could happen that you could simply use thisisanisp.com or the global IP of the ISP, and traffic on port 3306 is routed to the MySQL server - but it will not always work - particularly not if it is a big ISP!. But everything here depends on the local routing systems used at the remote host - whether port-number based routing is active or not.

• HTTP-tunneling:

With HTTP-tunneling 'host' can simply be 'mysqlserver' (ie: 'mydb1' or 'mysql' or whatever). mysqlserver.thisisanisp.com will work too, but there is no need for an extra domain lookup! If it is the same computer that runs the Web-server (with PHP-interpreter) and the MySQL server it can also be 'localhost' or the ip '127.0.0.1'. PHP will connect to MySQL through a Unix-Socket if 'localhost' is specified and TCP if '127.0.0.1'. Both will normally work. TCP may be a little bit slower on Linux machines than Socket, so you can try 'localhost' first. However there are situations where 'localhost' will return an error like error: 'Error No. 2013: Lost connection to MySQL server during query'. This is likely because PHP looks for the socket file where it does not exist. The underlying reason could be that MySQL and PHP have been installed from different repositories using different file positions for the socket file and the mysql datadir.

• SSH-tunneling.

With SSH-tunneling there are two hosts' settings:

1. The 'MySQL Host Address' on the Server Tab on the connections manager: Here the 'host' is the 'path' to the MySQL-server relative to the SSH-server on the remote local network. Most often 'localhost' will do. If it does not work and 'mysqlserver' does not either you will have to ask the support/Sys Admin for more information. There are lots of possible ways to configure SSH on a network! Also here if the MySQL host as entered here cannot be reached from the SSH-server the MySQL client error: 'Error No. 2013: Lost connection to MySQL server during query' occurs. Thus this can be caused by an erroneous 'host' entry, but it can also be a network/configuration error on the remote network. Or the MySQL server simply could be down.
2. The 'SSH host' on the Tunnel Tab on the connections manager: This is the 'global URL' of the SHH server at the ISP. The ip will normally work fine, thisisanisp.com too. And maybe SSH is available via the internal name server too - then use something like sshserver.thisisanisp.com.

Do I need PHP to use SQLyog with MySQL?

No.

You do not need PHP or any PHP-based tool like phpMyAdmin. All you need is MySQL and SQLyog. But of course if you want to use your MySQL database with a website you must use a scripting language (PHP, ASP or JSP for instance)and the corresponding "preprocessor" must installed with the web(HTTP-)server. That is because a webserver without such extension is not able to connect to MySQL and interpret data from a database.

However - you can continue to use SQLyog and the SQLyog Job Agent (SJA) for administering your databases and for performing database maintenance, data import, and synchronization with other MySQL databases etc., no matter which other type of connections are made to the database.

Even if the TCP-port (3306) normally used by the MySQL server is blocked (as often is the case at ISP's) SQLyog still let you connect using HTTP(s)-tunneling or SSH-tunneling.

SQLyog gives you the most options for connecting to MySQL

SQLyog connects to MySQL using the native C-API from MySQL - the fastest and most effective way to manage MySQL. This API is compiled into SQLyog code itself.

Even if the TCP-port (3306) normally used by the MySQL server is blocked (as often is the case at ISP's) SQLyog still let you connect using HTTP(s)-tunneling or SSH-tunneling. And if you are behind a proxy SQLyog can handle that too. The client for SSH connection is installed with SQLyog, and a PHP-script for HTTP-tunneling is too. That script must be uploaded to your webhost. The PHP-script uses php_mysql extension that is available practically everywhere where MySQL is available.

SQLyog works with MySQL version 3.23 and upwards.

SQLyog takes long time to connect when using SSH-tunnel.

If SQLyog is taking long time (more than 5-10 seconds) to connect to your MySQL server using SSH tunneling then there could be problem with the name resolution.
The first thing that the SSH server does before forwarding the connection to the specified MySQL server is to perform a reverse DNS lookup on the client’s IP. The SSH server may cause an unnecessary delay during authentication due to incorrect or absent of reverse DNS settings. So in case of any slowness you should check those settings.
You can also disable most of the server-side lookups by setting UseDNS = "no" in SSHD configuration file (/etc/ssh/sshd_config on most systems). But in that case the MySQL host must be specified with an ip and not a hostname.

What Is SSH and SSH-tunneling?

The Acronym SSH stands for Secure Shell Host. SSH was originally created to provide a secure way to access server systems at "low level", to be used instead of common (but insecure) telnet methods. SSH can use several different forms of encryption, anywhere from 56 to 1024 bit. SSH has been ported to Operating Systems on several platforms including Linux, Microsoft Windows and Macintosh. There are SSH servers and SSH clients available for different types of communication.

Here you may notice this: "OpenSSH includes the ability to forward remote TCP ports over a secure tunnel, allowing that way arbitrary TCP ports on the server side and on the client side to be connected through an SSH tunnel". This is excatly what we make use of.

The term "SSH tunneling" in relation to a database server means that in- and outgoing communications to the network that hosts the database server "passes through" the SSH-server and uses the communications port (usually port 22) and the protocol of the SSH-server. The SSH-server then "translates" and "transfers" that in-and outgoing communication to the database server. SSH is actually quite simple to use. However if you are totally unfamiliar with networking terminology you will have to study it somewhat. Actually you may even install an SSH server at your own local machine and use it for connecting to a local MySQL server. Not much use of that, but it will give you an excellent understanding of what SSH is!

There is a built-in SSH-client in SQLyog that lets you connect to a MySQL server using SSH.

Basically there are two benefits of SSH Tunneling:
* SSH can be used to encrypt communications between SQLyog and your remote MySQL server.
* It lets you access the MySQL server even if the MySQL port (3306) is blocked.

Unlike HTTP-tunneling, you cannot take it for granted that SSH-tunneling is available at your webhost. In general the "more professional" and "more expensive" hosting providers offer SSH and the cheaper ones don't.

Refer to the SQLyog help file for instructions how to set up the SQLyog Connections Manager, if you want to use SSH-tunneling with SQLyog. Each SSH-connection occupies a TCP-port at your local machine. With recent SQLyog versions this port is picked automatically from the pool of high-numbered ports not already in use.

A concluding note on the popular 'Putty' program and SQLyog SSH-tunneling.

Sometimes when people are having problems with SSH-connections, we often hear "I can connect with Putty without problems". Maybe so, but it does not tell very much (almost nothing actually!) because the type of connection with Putty or a similar program referred to here by users is not tunneling and does not make use of port forwarding. Putty creates a remote (and secure) shell on the client machine, and connects to the 'mysql' client program on the server. So here the MySQL client is the 'mysql' client on the remote server. It is true that Putty can be used for setting up a SSH-tunnel as well, but this is not the simple 'connect with Putty' most often referred to and compared with here. With (SQLyog) SSH-tunnel the MySQL client is the client API that is compiled into SQLyog (and SJA). That is why port forwarding is needed and must be functional with SQLyog SSH-tunneling!

You can review the SSH-related error messages.

Connection Issues