(More) Secure local passwords in MySQL 5.6 and up

(More) Secure local passwords in MySQL 5.6 and up

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

Share this post

Comments (5)

  • Fadi El-Eter (itoctopus) Reply

    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!

    December 1, 2014 at 11:50 am
  • Stefan Lasiewski Reply

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

    June 15, 2015 at 4:25 pm
  • kisl0tneg Reply

    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!

    March 7, 2016 at 10:38 am
    • hikerVancouver Reply

      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.

      November 28, 2016 at 2:30 pm
  • J.D. Fagan Reply

    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

    April 14, 2017 at 12:08 pm

Leave a Reply