7 May 2011 Leave a comment
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):