I log into a lot of different servers running MySQL and one of the first things I do is create a file in my home directory called ‘.my.cnf’ with my credentials to that local mysql instance:

This means I don’t have to type my password in every time, nor am I tempted to include it on the command line with -p and get the dreaded (but completely accurate):

MySQL 5.6 introduces a utility to make this easier and more secure. First, let’s start with a new mysqld instance with a blank root password and make it more secure:

Ok, so I’ve added a password, now I want to create my .my.cnf file:

What did ‘mysql_config_editor set’ actually do? It creates a .mylogin.cnf file (which stands in for a .my.cnf) in my home directory that contains my credentials, just in encrypted form:

The mysql client picks this up right away and will use it by default. This file has good default filesystem permissions, is local to my homedir, and is a whole lot better than specifying it on the command line or typing it in every time.

This utility also supports a feature called ‘login-path’ wherein you can add multiple mysql logins (perhaps to different servers) and refer to them with the —login-path option in the mysql client:

The ‘remote’ host doesn’t exist here, but you get the idea. You can create as many login-paths as you want with varied hostnames, credentials and other login parameters and quickly access them with any client supporting login-path.

Now, how secure is this really?  This isn’t secure from anyone who roots your DB server.  I would say the benefits are more about reducing careless password storage and tidier management of local credentials.

5 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Fadi El-Eter (itoctopus)

Hi Jay,

I like the idea of a stored password in a local my.cnf – personally I have never used it before.

Using the config editor to store login credentials is an excellent idea because the alternative will allow someone with root access to the server to just read the passwords (because they are stored unenrypted). But then again, I wonder if people actually care about security at this level, most of the phpMyAdmin installations out there have the MySQL root password stored in the the config file!

0
Stefan Lasiewski

> Ok, so I’ve added a password, now I want to create my .my.cnf file:

FYI: This appears to be a typo. I think you meant to say “.mylogin.cnf” instead of “.my.cnf”.

0
kisl0tneg

Hi

Is it possible to generate the .mylogin.cnf programmatically?
I’ve got a custom program that manages databases – sets up credentials, etc. and I would really like to write out the .mylogin.cnf from my code.

thanks!

0
hikerVancouver

One way:

sprintf_s(systemCommand,_countof(systemCommand), “\”%s\” set –host=%s –login-path=%s –user=%s –port=%d –password”,
mysqlConfigEditor,serverID,loginPath,userName,serverPort);
ret = system(systemCommand);
if (ret==0) //OK, file created and populated

where mysqlConfigEditor is a full path to “mysql_config_editor.exe”, and loginPath is a label naming this connection to the server in the file.

0
J.D. Fagan

Does the utility, mysqldbcompare, support .mylogin.cnf? I can’t seem to make it work with either .mylogin.cnf or .my.cnf files. Only specifying the credentials on the command line is working for me with this utility.

This is how I’m invoking it via a wrapper script with the variables substition happening when I run it:
mysqldbcompare –disable-binary-logging –server1=${hostname1} –server2=${hostname2} ${host1_database}:${host2_database}

Note, I’m using Mac OS version installed via MySQL Workbench + MySQL Utilities:
$ mysqldbcompare –version
MySQL Utilities mysqldbcompare version 1.6.5
License type: GPLv2

0