Connecting to the Right MySQL Database – Tips and Tricks

Recently, I had to set up a second MySQL database instance on a machine, and found myself wondering which database I was really connecting to with the mysql client.

How do I make sure? This turns out to be rather easy:

mysqladmin -p -u root variables

Add the appropriate options to connect to the database you want – options like a) --socket (socket path); b) --protocol (specify protocol); c) --port (port); and several others.

When you run mysqladmin, there will be a large set of variables listed. Check datadir – it will show you where the database is located on disk. Also check socket: it shows where the mysql socket is which is used for communications. TCP communications will be done through a port, shown as port in the variables listing.

If you run mysql specifying the port, you might be surprised to see that it is ignored. Unless the client is forced to use TCP (via the --protocol parameter) or it picks TCP (through the use of the --hostname parameter) the client will use the specified socket instead.

To force TCP, you can use one of these commands:

mysql --protocol=TCP --port=3311 -p -u root

mysql --host=hostname --port=3311 -p -u root

Alternately, instead of forcing TCP, specify the local socket:

mysql --socket=/var/lib/mysql2/mysql.socket -p -u root

You can specify these options with mysqladmin to verify the actual database (using the variables command). There are short versions of these options (separate them from arguments by a space, not an equals):

  • host: -h
  • port: -P
  • socket: -S

Speeding Up SSH Access

There have been numerous problems that have slowed down SSH connections over the years. All are resolvable today – and could be fixed in modern operating systems, but typically aren’t. Most or all of these resolutions are related to reverse IP lookups: that is, given an IP address find out what DNS name is associated with it. These fixes turn off reverse IP lookups for the SSH client and SSH server.

The most often suggested fix is to add the following to your server configuration (sshd_config):

UseDNS no

This prevents the SSH server from making a reverse DNS lookup – that is, looking up the IP address and finding what DNS name is associated with that IP. Since the reverse lookup often does not respond, then there is a time-out involved and this is what generates the normal delay seen in SSH connections.

However, there are other things as well. In some distributions (such as Debian and Ubuntu) the system is set up to use Avahi. When SSH attempts to connect to a host, it uses Avahi as part of the authentication process. When the system uses mDNS (part of Avahi) to resolve an IP address, this resolves in a large delay until the system comes back.

Fixing this is simple as well: add this line to the SSH client:

GSSAPIAuthentication no

This authentication method triggers Avahi; by disabling GSSAPIAuthentication this skips Avahi altogether. It is also possible to rewrite the /etc/nsswitch.conf file to do the same thing. Changing the nsswitch.conf file affects the entire system, so be sure this is really what you want.

On my Ubuntu 10.04 system, the original line in nsswitch.conf looks like this:

hosts: files mdns4_minimal [NOTFOUND=return] dns mdns4

To stop using mDNS entirely, change the /etc/nsswitch.conf file hosts line to this:

hosts: files dns

Reverse IP lookups are a common source of delays for all manner of services (including PPP, Sendmail, and many others!).


Get every new post delivered to your Inbox.

Join 43 other followers