An Important Difference Between mysql(1) and MySQLdb

I keep forgetting about this thing, and then every six to twelve months when I have to do it again, I waste a bunch of time rediscovering it. It's important enough that I'm going to blog it.

If you're used to using PostgreSQL, you'll know that with Postgres you can connect over the local AF_UNIX socket using peer authentication. This means that as the evan user I can automagically connect to the evan database without a password. Likewise, to become the Postgres superuser, I simply need to do sudo -u postgres psql. This works using some magic related to either SO_PEERCRED or SCM_CREDENTIALS which let you securely get the credentials of the other end of a connected AF_UNIX socket.

MySQL also has a local AF_UNIX socket, and you can use this socket to make connections to MySQL. This is pretty handy, and for many reasons you may prefer to connect to MySQL over the local socket rather than using a TCP connection to localhost.

However, MySQL does not do the peer authentication thing. It doesn't matter if you're the root user connecting over a local socket. If the root user is configured to require a password (which is what I strongly recommend), then you must supply a password, even if you have sudo privileges on the host.

Fortunately, there's an easy workaround here that prevents you from having to type the root password all the time if you're doing a lot of MySQL administration. When you use the mysql CLI program, it will look for a file called ~/.my.cnf and use it to look up various connection settings. In particular, in this file you can set a default user and password. So let's say you've done this nice thing and made a file called /root/.my.cnf that has the root user's MySQL credentials, and you have the file set to mode 600 and all that and everything is great. You can type sudo mysql and you won't have to supply the root MySQL password (just possibly the root sudo password).

Here is a really important thing to know: the behavior of reading ~/.my.cnf is something that the mysql CLI program implements, it is not something implemented by!

What that means is that when you are writing some script to frob MySQL using Python and MySQLdb, this will not work:

conn = MySQLdb.connect(unix_socket='/run/mysqld/mysql.sock',

You might think that if you ran this script as the root user, it could authenticate. Not so. Instead what you want is this:

conn = MySQLdb.connect(unix_socket='/run/mysqld/mysql.sock',

By the way, using the read_default_file option like this is definitely the best way to authenticate to MySQL from Python in general. You should not be putting database passwords in your Python projects---neither in your source code, nor in your project configs. By using a file in the filesystem like this you can move all of the database credentials into Puppet/Chef/whatever and secure the files so that most users can't read them. It may not seem like a big win today, but a few years later, when you're given the task of auditing everything for passwords, knowing that passwords have only lived in your configuration management software is going to help a lot.