How to connect to MySQL without a password prompt

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.

The .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.

The --user and --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

Output:

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:

mysql --login-path=local

Output:

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:

mysql --login-path=local

Output:

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 tool:

my_print_defaults --show local

Output:

Conclusion

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.