mySQL does some dumb things

Today I struggled for quite sometime with mysql and in the process I narrowly avoided a complete disaster. My complaint is with the behaviour of the mySQL client.

Most rational people would assume that when you specify the “–port” option on the command line, mysql would use it as follows:

# mysql --port=3306 -u root -p

that the mysql client would connect using TCP on the port specified, right? Wrong. In fact mysql silently ignores the port option and defaults back to the /tmp/mysql.sock connection.

The only way to get it to use TCP is to combine it with the –host option. Except here again there is another gotcha! If you specify:

# mysql --host=localhost --port=3306 -u root -p

mysql will again silently ignore the port option and use the socket. The only way to get mysql to use TCP is to specify localhost as an IP address:

# mysql --host=127.0.0.1 --port=3306 -u root -p

What makes this so dangerous? This afternoon I was working on a database migration project and I had to connect to the second mysql-server (it was actually via an ssh tunnel) on a non-standard port. Little did I realize that I was actually working on the local production database. It was only because the new database sever does not have the same login credentials for the root user that I was not able to drop the databases and do other destructive things on the production machine.

This was discussed as a possible bug but unbelievably it was decided this was expected behavior.