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