EmergencyEMERGENCY? Get 24/7 Help Now!

(More) Secure local passwords in MySQL 5.6 and up

 | November 25, 2014 |  Posted In: MySQL, Security

PREVIOUS POST
NEXT POST

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.

PREVIOUS POST
NEXT POST
Jay Janssen

Jay joined Percona in 2011 after 7 years at Yahoo working in a variety of fields including High Availability architectures, MySQL training, tool building, global server load balancing, multi-datacenter environments, operationalization, and monitoring. He holds a B.S. of Computer Science from Rochester Institute of Technology.

5 Comments

  • 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!

  • > 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”.

  • 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!

    • 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.

  • 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

Leave a Reply