MySQL Shell and storing passwords

on

If you plan for example to have a bastion host in the cloud to access all your MySQL servers and use only MySQL Shell, the first time you connect to one of the host, you will realize that MySQL Shell prompts for a password but doesn’t ask you to store it of not.

Example:

mysqlsh fred@192.168.5.94
Please provide the password for 'fred@192.168.5.94': ****
MySQL Shell 8.0.21
Copyright (c) 2016, 2020, 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 '\?' for help; '\quit' to exit.
Creating a session to 'fred@192.168.5.94'
Fetching schema names for autocompletion… Press ^C to stop.
Your MySQL connection id is 16 (X protocol)
Server version: 8.0.21 MySQL Community Server - GPL
No default schema selected; type \use to set one.
MySQL 192.168.5.94:33060+ ssl JS >
Bye!

As you could see, no saving password question appeared. And next time we will connect again to that host, the password will be prompted again.

Why ?

This is because there is no default Credential Helper on Linux. If you want to be able to store passwords, you will need to install mysql-client. Yes the good old MySQL Client package.

Let’s take a look at the installed credential helpers before installing mysql-client:

MySQL JS > shell.listCredentialHelpers()
[]

And after installing mysql-client:

MySQL JS > shell.listCredentialHelpers()
[
"login-path"
]

Héhé ! This time we have one credential helper !

This is also in the manual:

MySQL login-path, available on all platforms supported by the MySQL server (as long as MySQL client package is installed), and offers persistent storage.

Now we know why the password was not stored. Let’s see what happens since we have installed mysql-client:

MySQL JS > \c fred@192.168.5.94
Creating a session to 'fred@192.168.5.94'
Please provide the password for 'fred@192.168.5.94': ****
Save password for 'fred@192.168.5.94'? [Y]es/[N]o/Ne[v]er (default No): y
Fetching schema names for autocompletion… Press ^C to stop.
Your MySQL connection id is 18 (X protocol)
Server version: 8.0.21 MySQL Community Server - GPL
No default schema selected; type \use to set one.
MySQL 192.168.5.94:33060+ ssl JS >

You can notice that this time we got a question to save or not the password, which I did.

Where is my password stored ?

Now every time we will connect with that user on that host, if the password is not changed, we won’t have to enter the password anymore.

But where is the password stored ?

In MySQL Shell, you can list the stored credentials:

MySQL 192.168.5.94:33060+ ssl JS > shell.listCredentials()
[
"fred@192.168.5.94"
]

From MySQL Shell it’s also possible to store and delete credentials. See here.

So mysql_config_editor is the one taking care of storing the password. This is explained in this blog from my colleague Pawel.

Let have a look:

$ mysql_config_editor print --all
[fred@192.168.5.94]
user = fred
password = *
host = 192.168.5.94

The password is stored encrypted in ~/.mylogin.cnf.

Conclusion

MySQL Shell is a very nice product and it won’t store the passwords for your directly, it uses helpers. mysql_config_editor is the one available on all OS when mysql-client is installed. On other OS you have other options too. So if you plan to use MySQL Shell only on a dedicated machine and store your credentials, don’t forget to also install mysql-client!

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

I deeply value the diversity of MySQL developers, users, community and my blog readers. I cannot agree the suffering, oppression, and systemic racism the Black community faces every day. Black lives matter.
recent
categories