We can achieve passwordless logins using
mysql_config_editor utility which comes with your MySQL installation and creates an obscured file called
.mylogins.cnf for storing user credentials.
.mylogins.cnf is similar to any other MySQL configuration file but only groups options related to authentications like usernames, passwords, and hosts. Each group option is called a
login-path in this file.
This is useful especially when you want to run MySQL commands from within a script and where don’t want a password prompt.
NOTE: Even though the .
mylogins.cnf file is obscured, it can be easily converted to plain text as shown later in this post. Therefore, this is not recommended to be used in production.
How to set login-paths
We can use
mysql_config_editor to add entries to
.mylogins.cnf file. The file will be created on your home directory.
mysql_config_editor set --login-path=local --host=localhost --user=root --password
You will be prompted to enter MySQL password for the user you specified with –user.
To break it down,
--login-path option is the group option name that is going to be added to the
.mylogins.cnf file. We will use its value to login in a bit.
--host options specify what MySQL user and host to use for login. In our case, root and localhost.
The clear text content of
.mylogin.cnf can be seen using the following command:
mysql_config_editor print --all
The name of our login path is local in this example
Now you only need to specify the
--login-path option to log in to your MySQL instance:
As you can see we are able to log in without entering any password.
How to remove a login-path from .mylogin.cnf
It’s pretty easy to remove a login path:
mysql_config_editor remove --login-path=local
Now if we try to login with that login path we will get Access denied:
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
How to see contents of .mylogin.cnf file in plain text
As mentioned earlier, the content of .mylogin.cnf can be deciphered easily. To get the content of a login-path in plain text, pass the login-path name to the
my_print_defaults --show local
We can use
mysql_config_editor tool to specify a login path along with the user and host to connect and that will add an entry to the hidden and obfuscated .mylogin.cnf file. That way, we only need to specify the –login-path on the command line.
It is important to note that anyone who has access to the system can see the contents of .mylogins.cnf file in plain text, therefore, this is not recommended to be used in production.