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:

[client] user=root password=secret 1 2 3 [ client ] user = root password = secret

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):

Warning: Using a password on the command line interface can be insecure. 1 Warning : Using a password on the command line interface can be insecure .

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:

[vagrant@localhost ~]$ mysqladmin -u root password New password:secret Confirm new password:secret [vagrant@localhost ~]$ mysql -u root ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO) 1 2 3 4 5 6 [ vagrant @ localhost ~ ] $ mysqladmin - u root password New password : secret Confirm new password : secret [ vagrant @ localhost ~ ] $ mysql - u root ERROR 1045 ( 28000 ) : Access denied for user 'root' @ 'localhost' ( using password : NO )

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

[vagrant@localhost ~]$ mysql_config_editor set --user=root --password Enter password:secret [vagrant@localhost ~]$ mysql Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 10 Server version: 5.6.21-70.0 Percona Server (GPL), Release 70.0, Revision 688 Copyright (c) 2009-2014 Percona LLC and/or its affiliates Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or 'h' for help. Type 'c' to clear the current input statement. mysql> 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 [ vagrant @ localhost ~ ] $ mysql_config_editor set -- user = root -- password Enter password : secret [ vagrant @ localhost ~ ] $ mysql Welcome to the MySQL monitor . Commands end with ; or g . Your MySQL connection id is 10 Server version : 5.6.21 - 70.0 Percona Server ( GPL ) , Release 70.0 , Revision 688 Copyright ( c ) 2009 - 2014 Percona LLC and / or its affiliates Copyright ( c ) 2000 , 2014 , Oracle and / or its affiliates . All rights reserved . Oracle is a registered trademark of Oracle Corporation and / or its affiliates . Other names may be trademarks of their respective owners . Type 'help;' or 'h' for help . Type 'c' to clear the current input statement . mysql >

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:

[vagrant@localhost ~]$ ls -alh .my* -rw-------. 1 vagrant vagrant 100 Nov 19 16:20 .mylogin.cnf -rw-------. 1 vagrant vagrant 29 Nov 19 16:20 .mysql_history [vagrant@localhost ~]$ cat .mylogin.cnf ��>NTv�&�S���/�, >ј$%KZ 9i�V�jK䉦H[��� k. [vagrant@localhost ~]$ mysql_config_editor print [client] user = root password = ***** 1 2 3 4 5 6 7 8 9 10 11 12 13 [ vagrant @ localhost ~ ] $ ls - alh .my * - rw -- -- -- - . 1 vagrant vagrant 100 Nov 19 16 : 20 .mylogin .cnf - rw -- -- -- - . 1 vagrant vagrant 29 Nov 19 16 : 20 .mysql_history [ vagrant @ localhost ~ ] $ cat .mylogin .cnf �� > NTv � & � S ��� / � , > ј $ % KZ 9i � V � jK 䉦 H [ ��� k . [ vagrant @ localhost ~ ] $ mysql_config_editor print [ client ] user = root password = * * * * *

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:

[vagrant@localhost ~]$ mysql_config_editor set --login-path=remote --host=remote --user=remote --password Enter password:secure [vagrant@localhost ~]$ mysql_config_editor print --all [client] user = root password = ***** [remote] user = remote password = ***** host = remote [vagrant@localhost ~]$ mysql --login-path=remote ERROR 2005 (HY000): Unknown MySQL server host 'remote' (0) 1 2 3 4 5 6 7 8 9 10 11 12 13 14 [ vagrant @ localhost ~ ] $ mysql_config_editor set -- login - path = remote -- host = remote -- user = remote -- password Enter password : secure [ vagrant @ localhost ~ ] $ mysql_config_editor print -- all [ client ] user = root password = * * * * * [ remote ] user = remote password = * * * * * host = remote [ vagrant @ localhost ~ ] $ mysql -- login - path = remote ERROR 2005 ( HY000 ) : Unknown MySQL server host 'remote' ( 0 )

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.