# mysql_secure_installation… Ya-ha-! (and ~/.my.cnf)

By March 31, 2009Technical

I was setting up mysql-server for a customer recently and noticed something interesting – there’s a helpful script included with mysql called mysql_secure_installation. We thought about that for a moment and had a chuckle. Okay, that was a little unfair; it’s no secret that we prefer to use Postgres wherever possible, but the idea of having a “make it all secure” script isn’t too bad an idea, as long as it doesn’t produce a false sense of security.

The script does good things, but MySQL could probably be doing things better to begin with – make it more secure out-of-the-box, and the last thing they should be doing is shipping it with an empty root password. >_< It pains me to say it, but I think MSSQL probably comes with a more-secure initial configuration. Things like no remote connections, and no test database unless you explicitly ask for it.

The documentation for mysql_secure_installation is brief but functional. One of the things I thought it was doing was writing out a convenient .my.cnf file for the user, but it turns out it just deletes it once it’s finished. This is a shame, because the per-user config file is really cool.

If you’re not already taking advantage of them, you should. MySQL calls them “option files”, and it lets you set default parameters for the client apps you use at the command line, like mysql and mysqldump, etc. We use them to store root’s login credentials so we don’t have to lookup the password for a given machine, then mess around with mysql’s asinine option-handling.

It’s best to at least read up briefly on option files before using them, but they’re pretty straightforward: you have “groups” of options in the file written as [groupname], then one option on each line after that. An instructive example:

[mysql]
database=mysql

client
user=root password=verySecurePassword

If you’re going to keep your password here, you must make sure the file is adequately protected from other users who might try to view it.

The group-names I mentioned correspond to the client program that will use the options.

client
is a special case; those options will be used by all the mysql client programs, so it’s the ideal place for your username and password.

Having the database option in the [mysql] group means you’re automatically connected to a database once you start the mysql command-line client. We don’t put this in the

client
group because it would affect other utilities like mysqldump.