MySQL: Provisioning .mylogin.cnf

MySQL uses connection and config parameters from a number of possible sources. The easiest way to find out where it is looking for config files is to run

$ mysql --help | grep cnf
                      order of preference, my.cnf, $MYSQL_TCP_PORT,
/etc/my.cnf /etc/mysql/my.cnf /Users/kkoehntopp/homebrew/etc/my.cnf ~/.my.cnf

As can be seen, my version of the MySQL client checks in this order

• /etc/my.cnf
• /etc/mysql/my.cnf
• /Users/kkoehntopp/homebrew/etc/my/cnf
• ~/.my.cnf

The cnf file is a file in dot-ini syntax, so you have [groups] and each group contains lines with key = value pairs. Which groups are read?

$ mysql --help | grep "groups are"
The following groups are read: mysql client

So in my case, I would create a /Users/kkoehntopp/.my.cnf looking like this:

[client]
user=kris
password=geheim
host=127.0.0.1

[mysql]
database=kris
show-warnings
prompt=\U [\d]>\_

That is, I put general connection parameters such as the host, user and password into the client group, and program specific parameters such as database, prompt and others into program specific groups such as mysql. That way, the mysqldump and mysql programs will connect automatically, but the mysql options will not interfere with mysqldump.

.mylogin.cnf

Some time in 2012, the .mylogin.cnf mechanism and the mysql_config_editor program were added. They provide little value in security as we will see, but allow storage of more than one set of credentials: It is now possible to store a number of login paths (credential sets) in a mylogin file, and call the client program with --login-path=.... The client program will then read the mylogin file and use the connection parameters from there.

mylogin files are being made with the mysql_config_editor program, for example

$ mysql_config_editor set --login-path=test --user=kris --host=localhost --password
Enter password: geheim

$ mysql_config_editor print --all
[local]
user = root
password = *****
host = localhost
[test]
user = kris
password = *****
host = localhost

will create a new mylogin, or amend an existing one, and define a login path named test. It will take the host and the username on the command line, but you cannot specify the password easily - it has to be typed in.

This makes provisioning hard - templating this in Ansible is not easy and dancing around with expect and friends is just silly.

Working around this

A small Python Program based on a much older article and PHP program of mine changes that.

It can decode and encode mylogin files, opening them up to Ansible templating. Just provision a valid mylogin file in plain text, encode it and delete the plain text original file.

The sample session looks like this:

# generate a dummy file
mysql_config_editor set --login-path=local --user=root --host=localhost --password
Password: keks

# decode this file
./mysql_config_coder.py decode ~/.mylogin.cnf mylogin.out
cat mylogin.out

# make changes to mylogin.out and
./mysql_config_coder.py encode mylogin.out mylogin.cnf
chmod 600 mylogin.cnf

# test with original
MYSQL_TEST_LOGIN_FILE=$(pwd)/mylogin.cnf
mysql_config_editor -v print --all
my_print_defaults -s local

# Note: mysql_config_editor will not print the password, just five stars
#       but my_print_defaults should also show the password.

The program depends on click and pycrypto, but really any implementation of aes-128-ebc should be easily usable.