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 libmysqlclient.so!
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',
user='root')
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',
user='root',
read_default_file='/root/.my.cnf')
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.