Posted: 2017-02-10 14:27:55 by Alasdair Keyes
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
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
I'm now available for IT consultancy and software development services - Cloudee LTD.
Happy user of Digital Ocean (Affiliate link)