Passwordless authentication using mysql_config_editor with MySQL 5.6

This post elaborates how to use passwordless authentication using mysql_config_editor with MySQL 5.6. The mysql_config_editor utility is available for production use as of MySQL 5.6.6 [Technically 5.6.10 since its first cut GA of MySQL 5.6 server]. This utility enables you to store authentication credentials in an encrypted login file named .mylogin.cnf. Running this utility creates a file named .mylogin.cnf file in %APPDATA%\MySQL directory on Windows and the current user’s home directory on non-Windows systems such as Redhat EL6/CentOS/Fedora/Ubuntu. The file is read later by MySQL client programs to obtain authentication credentials for connecting to MySQL Server.mysql_config_editor encrypts .mylogin.cnf unlike its predecessor .my.cnf file so it cannot be read as clear text and supposedly contents after decrypted are only used in memory. If you have access to the system where you have stored these passwords then you can print them, except password is not visible.

We invoke mysql_config_editor utility like this:

mysql_config_editor [program_options] command [command_options]

To display help:

mysql_config_editor --help

To generalize we can say that this utility parameters can be broken down into three sets:

program_options: Consists of general mysql_config_editor options.

command: command indicates what action to perform on the .mylogin.cnf login file. There are three commands currently which are – set writes a login path to the file, remove removes a login path, and print displays login path contents. Any options given provide information to the command, such as the login path name and the values to use in the login path. Think login path as connection profile.

command_options: command_options indicates any additional options needed by the command like host/user/password information

I divided the rest of this post into three sections for understanding it easily. First part we will set, second part we will print and in third part we will remove profiles.

1. Adding a login-path / Profile and connecting passwordless:

Before we connect to a server for logging in without prompting for a password, we need to create a profile. To create a profile simply issue the following command:

mysql_config_editor set --login-path=testbed1 --host=opensourcedbms_tb1 --user=superadmin --password

When prompted for password, please type in password that you set for this user for that particular host and hit enter. You are done creating profile for this user at this point and you can use to connect to this host password less and here is how you connect without a password:

mysql --login-path=testbed1

You have now successfully got authenticated with .mylogin.cnf file without a password prompt. You can add more profile/login-paths as desired.

mysql_config_editor set --login-path=testbed2 --host=opensourcedbms_tb2 --user=superadmin --password
mysql_config_editor set --login-path=production1 --host=opensourcedbms_prd1 --user=superadmin --password

2. Print all login-paths / Profiles:

To list all the existing paths/profiles we can simple issue following command:

mysql_config_editor print --all

3. Removing a login-path/Profile:

To remove a profile we can simple issue following command [Replace testbed2 with your login-path/profile name ]:

mysql_config_editor remove --login-path=testbed2

You can also remove parts of your profile like hostname from existing profile and here is how you do it, for example lets remove host from testbed1 profile, to do that you can simply run the following command:

mysql_config_editor remove --login-path=testbed1 --host

Notice that host variable is removed from testbed1 profile.

That’s it folks, as simple as that. If you have questions/suggestions hit them up in comments below.

Note: --port and --socket options are supported for the set/remove commands as of MySQL 5.6.11