Seamless MySQL Login: Connecting Without a Password Prompt

Introduction

Achieving passwordless logins for MySQL can streamline your workflow, especially when running MySQL commands from within a script. This can be done using the mysql_config_editor utility, which comes with your MySQL installation and creates an obscured file called .mylogin.cnf to store user credentials.

The .mylogin.cnf file functions similarly to other MySQL configuration files but focuses solely on authentication options like usernames, passwords, and hosts. Each set of these options is called a login path within the file.

Note: Although the .mylogin.cnf file is obscured, it can be easily converted to plain text as shown later in this tutorial.

Setting Up Login Paths

You can use the mysql_config_editor tool to add entries to the .mylogin.cnf file, which is located in your home directory.

To add a login path, use the following command:

mysql_config_editor set --login-path=local --host=localhost --user=root --password

You will be prompted to enter the MySQL password for the specified user.

Breakdown of the Command:

• --login-path: Specifies the group option name to be added to the .mylogin.cnf file. This value will be used later for login.
• --user and --host: Specify the MySQL user and host for the login, in this case, root and localhost.

To view the clear text content of .mylogin.cnf, use:

 mysql_config_editor print --all

Output:

The name of our login path is local in this example

Logging in with the Login Path

Once the login path is set, you can log in to your MySQL instance using:

mysql --login-path=local

Output:

This command allows you to log in without entering a password.

Removing a Login Path

To remove a login path, use the following command:

mysql_config_editor remove --login-path=local

Attempting to log in with the removed login path will result in an access denied error:

mysql --login-path=local

Output:

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

Viewing .mylogin.cnf Contents in Plain Text

As mentioned earlier, the content of .mylogin.cnf can be deciphered. To see the content of a login path in plain text, use the my_print_defaults tool:

my_print_defaults --show local

Output:

Conclusion

The mysql_config_editor tool allows you to specify a login path along with the user and host, creating an entry in the hidden and obfuscated .mylogin.cnf file. This enables passwordless logins by simply specifying the --login-path option on the command line.

Important: Since the contents of the .mylogin.cnf file can be easily viewed in plain text, this method is not suitable for production environments.