Page History

...

How do connection names resolve to IP's?

...

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´'

Voila! '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!) 

And finally let me remind you of one detail: When connecting to the MySQL server at an ISP it is common practice that MySQL user names and MySQL database names must be prefix'ed 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. Read this.

...

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 nameserver internally. Such nameserver 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 can't 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 won't 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 won't 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.

...

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.

SSH error messages

...

Starting from SQLyog version 5.15 we have improved the error message handling in relation to SSH-tunnel. The most common error messages now are:

...

Also see the note to the MySQL client error 2013 "Lost connection ..." below! 

MySQL - related:

As a result of this improvement we can now also retrieve more meaningful MySQL server and client errors than before - for instance:

If MySQL user/pw is wrong result is MySQL server error 1045: "Access denied ..." as with any type of connection.

If MySQL port is wrong result is MySQL client error 2013 "Lost connection ...".
Note that this error also occurs if port forwarding is disabled in SSH configuration (the configuration parameter 'AllowTcpForwarding' is set to 'no' in the 'sshd_config' file). It (here) simply tells that there is no connection from SSH to MySQL for some reason. But the mySQL client API 'thinks' there was one connection and that is why is says 'Lost connection ...' and not 'Can’t connect...'. There was one successful connection - but not to the MySQL server - to the SSH daemon only! But the MySQL client API is not designed to 'see' the difference!

If MySQL port is empty or ZERO however result is MySQL client error 2003 "Can’t connect to mysql server ..."

If MySQL host is wrong (or cannot be reached from SSH for some reason) result is MySQL client error 2005 "Unknown MySQL server..." 

Unspecified error:

And this one is displayed if SSH connection cannot be established for some other reason and it has not been possible to resolve the error to one of the above.

"Could not establish SSH connection.
Make sure that the SSH server is running and you are entering correct values for SSH port forwarding."
 

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

...

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. You can find more information about SSH at https://en.wikipedia.org/wiki/OpenSSH/. 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!

...

You can read about SSH-related error messages here. 

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

...

I have a very long username for the MySQL database at my ISP. SQLyog won't let me use it.

...

Right! Because a MySQL user name is up to 16 characters long. The MySQL docs clearly state:

...

So with the most recent builds in the MySQL 4.1.x series and with MySQL 5.0 it would not work anyway. There is now code in the server binary itself that truncates any user name to 16 characters.

Expand
title Connection problems

I get Error no. 2002. Can't connect to local MySQL server through socket ...

This can occur when connecting using HTTP-tunneling to a MySQL server running on Unix/Linux platforms.

MySQL writes this

How to cope with this would depend on which webserver and which php version is used. But here is a workaround that has worked with Apache:

if the directory /var/lib/mysql doesn't exist than create it and chown to user mysql:
"mkdir /var/lib/mysql; chown mysql /var/lib/mysql"

Then edit the /etc/my.cnf file and specify
[mysqld]
socket=/var/lib/mysql/mysql.socket
[client]
socket=/var/lib/mysql/mysql.socket

and restart the mysql server ("etc/init.d/mysql restart")

That is enough for MysSQL. However the chances are that php was compiled with a different default mysql socket location (e.g. /tmp/mysql.sock). In which case you have to edit the php.ini file and find the variable "mysql.default_socket". Set this to the above value

mysql.default_socket = /var/lib/mysql/mysql.socket

and restart Apache to re-read the php.ini file

...

Error no. 1251: "Client does not support authentication..."

...

Error no. 1251: "Client does not support authentication protocol requested by server - consider upgrading MySQL client" occurs when the hashing-method for storing password used by the client differs from the one of the server. Typically it occurs when trying to connect to MySQL 4.1 or 5.x with a client compiled for 3.x or 4.0.

...

At ISP's you should expect the Sys Admin there to have MySQL-installations and PHP-binaries that "fit". If you operate your own MySQL server the hashing method may or may not change when upgrading the server from 4.0 to a newer version. That depends on the upgrade method. 

This one is very similar to Error no. 1045: "Connection denied .."

...

I am able to connect using phpMyAdmin, but SQLyog won't let me connect.

...

phpMyAdmin is running on the server itself so when connecting to MySQL with phpMyAdmin you are NOT connecting from a remote host! With SQLyog you are connecting from a remote host. This is a very important difference as far as user configuration with MySQL is concerned.

...

Error no. 1044: "Access denied..."

...

This one is very similar to Error no. 1045: "Connection denied .."

...

I am able to connect but can't see the list of databases/tables.

...

You must have at least SELECT privilege (as a global privilege or a SCHEMA/COLUMN privilege, to be able to see any data.

...

I am getting "Protocol Mismatch; Server Version 9; Client Version 10;"

The protocol version 9 is used by MySQL 3.22 and earlier. The current version of SQLyog supports 3.23.x and above. To use it with 3.22 we have to provide you with a special build of SQLyog. Registered users can request such copy from support@webyog.com.

We can't guarantee that it will be the latest version of SQLyog. 

The 'opposite' error message appears if you try to connect from an application with a connector (for instance an ODBC-driver) that is too old too be used with the actual MySQL version.

I have an account with Yahoo. Can I use SQLyog…

...

Yes. But several users have had problems getting connection parameters right. Here is what Yahoo say themselves: 

Why can't I access my database?

First, make sure that you are using the hostname "mysql" and not "localhost" in any of your PHP or Perl configuration files that require a MySQL hostname.

...

You will need to use HTTP Tunneling. First upload SQLyogTunnel.php (available with the SQLyog installer). Put the correct URL in the HTTP Tunneling field and use the same credentials as you will use in your PHP pages. That is that 'hostname' should be "mysql" (case-sensitive).

I get error 1130 "Host is not allowed to connect ..." or "Access denied ..." or "Could not connect ..."

...

Error 1130 is a networking error. The server cannot resolve the hostname of the client. Or the host is not allowed to connect to the MySQL server.

...

Error no. 1045: "Connection denied..."

...

The error message 

Error No. 1045: Connection denied for 'someuser@somehost' (using password: YES/NO)

 

is a user authentication error. The user details specified do not "match" the user tables of the specified MySQL server. Common situations are

...

Error No. 2005: Unknown MySQL server host...

...

The error message

Error No. 2005: Unknown MySQL server host 'some_URL_or_ip' 

simply means that connection is not possible for the following (or similar) reasons: 

A protocol is specified in the "MySQL host address" field of the SQLyog Connection Manager that does not support MySQL connection. It is a common mistake among beginners to use "http://...". Don't! Just "www.myveryowndomain.com" or "sales.myowncompany.biz" or "localhost" (if the webserver and the MySQL server is running on the same computer). When connecting to a remote network you may need to ask the Sys Admin there for the correct URL to use for addressing the MySQL server.

 

However if you use HTTP-tunneling the URL-field on the Tunnel -tab of the SQLyog Connection Manager should be a complete URL with the "http://" protocol specified. This is because the tunneling script must be addressed through a webserver (that is the idea of HTTP-tunneling!).

Error no. 2003: Can't connect...

...

The error message

 

Error No. 2003: Can't connect to MySQL server on 'localhost' (or some other host) 

simply means that connection is not possible for one of the following (or similar) reasons:

...