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:
|
1 |
[client]<br>user=root<br>password=secret<br> |
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):
|
1 |
Warning: Using a password on the command line interface can be insecure.<br> |
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:
|
1 |
[vagrant@localhost ~]$ mysqladmin -u root password<br>New password:secret<br>Confirm new password:secret<br><br>[vagrant@localhost ~]$ mysql -u root<br>ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)<br> |
Ok, so I’ve added a password, now I want to create my .my.cnf file:
|
1 |
[vagrant@localhost ~]$ mysql_config_editor set --user=root --password<br>Enter password:secret<br><br>[vagrant@localhost ~]$ mysql<br>Welcome to the MySQL monitor. Commands end with ; or g.<br>Your MySQL connection id is 10<br>Server version: 5.6.21-70.0 Percona Server (GPL), Release 70.0, Revision 688<br><br>Copyright (c) 2009-2014 Percona LLC and/or its affiliates<br>Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.<br><br>Oracle is a registered trademark of Oracle Corporation and/or its<br>affiliates. Other names may be trademarks of their respective<br>owners.<br><br>Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.<br><br>mysql><br> |
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:
|
1 |
[vagrant@localhost ~]$ ls -alh .my*<br>-rw-------. 1 vagrant vagrant 100 Nov 19 16:20 .mylogin.cnf<br>-rw-------. 1 vagrant vagrant 29 Nov 19 16:20 .mysql_history<br><br>[vagrant@localhost ~]$ cat .mylogin.cnf<br><br><br>��>NTv�&�S���/�, >ј$%KZ 9i�V�jK䉦H[���<br> k.<br>[vagrant@localhost ~]$ mysql_config_editor print<br>[client]<br>user = root<br>password = *****<br> |
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:
|
1 |
[vagrant@localhost ~]$ mysql_config_editor set --login-path=remote --host=remote --user=remote --password<br>Enter password:secure<br><br>[vagrant@localhost ~]$ mysql_config_editor print --all<br>[client]<br>user = root<br>password = *****<br>[remote]<br>user = remote<br>password = *****<br>host = remote<br><br>[vagrant@localhost ~]$ mysql --login-path=remote<br>ERROR 2005 (HY000): Unknown MySQL server host 'remote' (0)<br> |
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.
