MySQL encrypted client password storage

Posted: 2017-02-10 14:27:55 by Alasdair Keyes

Direct Link |

---

For years I've been using MySQL's ~/.my.cnf file to automatically manage logins for databases. However it's never sat well with me due to the fact that the file is plain text and even though you can restrict access with 0600 permissions, it's never good to have a password stored in plaintext.

I've recently been working on a MySQL 5.7 cluster and needed access to the production slave database and this issue raised it's head again. However as of MySQL 5.6, there is the option to store login details encrypted using mysql_config_editor

This tool allows you to setup profiles to access servers and store the details encrypted.

For example my previous ~/.my.cnf/ file might have

[mysql]
username=al
password=ComplexPassword

I could then access mysql like so...

# mysql
mysql> 

Now you define a profile so for the above example use

# mysql_config_editor set --login-path=localhost --host=localhost --user=root --password
Enter Password: <enter password>

--login-path is just a name and can be anything you like.

I can now login by specifying the login path

# mysql --login-path=localhost

What's nice is that you don't need to specify all the details, if you had a production and beta environment both with multiple servers you could run the following with different passwords and then supply the hostname on the command line

# mysql_config_editor set --login-path=production --user=root --password
Enter Password: <enter password>
# mysql_config_editor set --login-path=beta --user=root --password
Enter Password: <enter password>

# mysql --login-path=production -h proddb3
mysql>

The data is now stored in ~/.mylogin.cnf and is not readable

# cat ~/.mylogin.conf
<<JUMBLEDMESS>>

If you want to make backups or see what profiles you have, you can use

# mysql_config_editor print --all
[production]
user = root
password = *****
[beta]
user = root
password = *****

Removing profiles is as easy as

# mysql_config_editor remove --login-path=production

If you found this useful, please feel free to donate via bitcoin to 1NT2ErDzLDBPB8CDLk6j1qUdT6FmxkMmNz