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