Tuesday, August 31, 2021

The MySQL Shell and the MySQL Config Editor

    One of the things I love about the MySQL Shell is that is saves your authentication credentials (no longer just passwords) in an obfuscated login path file named .mylogin.cnf. You can find that file in the %APPDATA%\MySQL directory on Windows or the current user's home directory on non-Windows systems. The file can be read later by MySQL client programs, such as the MySQL Shell, to obtain your authentication credentials for connecting to MySQL Server.

Which Credentials?

I had an email this morning from someone wanting to a) change the password stored by mysqlsh and b) find out which credentials are stored on the shell account being used.

Let's start out in reverse order.  The mysql_config_editor will show you the accounts with credentials saved for that login. 

$ mysql_config_editor print --all
user = "root"
password = *****
host = "localhost"
user = "demouser"
password = *****
host = "localhost"

The two accounts on the shell account being used are root@localhost and demouser@localhost.

Quick Password Reset

To reset the server password on the second account use ALTER USER 'demouser'@'localhost' IDENTIFIED BY '<newpasswordhere>';  

If you login again from mysqlsh and the the stored password does not work, you will be prompted for the new password.  And that new password will be encrypted in stores in the .mylogin.cnf file. 

Start From Scratch

Is you want remove all the stored authentication information then type mysql_config_editor reset and all will be removed.