Introduction to Secure Credential Storage

Starting with MySQL 5.6, the mysql_config_editor utility allows administrators to securely store authentication credentials in an obfuscated file named .mylogin.cnf. On Windows, this file is located in the %APPDATA%\MySQL directory, while on Linux and Unix systems, it resides in the user's home directory. Once configured, MySQL client tools automatically reference this file for authentication details, eliminating the need to expose passwords on the command line or in scripts.

Creating a Login Profile

Without a configured profile, connecting to MySQL requires manual credential input:

mysql -u admin -p
Enter password:

To streamline this, use mysql_config_editor set to define a connection profile. The following command creates a profile named dev_primary:

mysql_config_editor set --login-path=dev_primary --host=127.0.0.1 --port=3307 --user=admin --password
Enter password:

After executing the command, an obfuscated hidden file is generated in the home directory with strict permissions:

ls -l ~/.mylogin.cnf
-rw------- 1 sysadmin sysadmin 152 Oct 10 10:00 .mylogin.cnf

You can now connect to the database instantly without typing a password:

mysql --login-path=dev_primary
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 45
Server version: 8.0.30 MySQL Community Server - GPL
...

Command Reference

The mysql_config_editor supports several core commands for managing credentials.

set

Writes a new login profile. Available options include:

• --login-path=name (-G): Assigns a name to the profile. Defaults to client if omitted.
• --host=host_name (-h): Specifies the database host.
• --user=user_name (-u): Specifies the database user.
• --password (-p): Prompts for the password securely. Do not append the password directly to this flag.
• --port=port_num (-P): Defines the TCP/IP port.
• --socket=file_name (-S): Defines the Unix socket file path.
• --warn (-w): Warns if overwriting an existing profile. Enabled by default; use --skip-warn to suppress.

print

Displays the contents of the login file. Passwords are always masked as *****. To view all profiles, use the --all flag. To view a specific profile, use --login-path=name. If neither is provided, it defaults to displaying the client profile.

mysql_config_editor print --all
[client]
user = default_user
password = *****
host = localhost
[dev_primary]
user = admin
password = *****
host = 127.0.0.1

remove

Deletes an entire profile or specific options within a profile. If you specify an option like --host or --user, only that attribute is removed. If no specific attribute is provided, the entire profile is deleted.

mysql_config_editor remove --login-path=dev_primary --port
mysql_config_editor remove --login-path=dev_primary

reset

Completely empties the .mylogin.cnf file, removing all stored login profiles.

mysql_config_editor reset

File Structure and Option Precedence

Before obfuscation, the .mylogin.cnf file operates similarly to standard MySQL option files, organized into groups corresponding to login path names. Each group can only contain the following specific options: host, user, password, port, and socket.

When a client connects, it merges options from multiple sources. The precedence hierarchy dictates that command-line arguments override the login path file, which in turn overrides standard option files like /etc/my.cnf. If a default profile like [client] is defined, the client will read it automatically without needing the --login-path flag.

Because the login file holds high precedence, it is advisable to define generic configurations (like [client]) first, followed by specific tool configurations (like [mysqldump]). Later definitions will override earlier ones within the same file.

Notably, the .mylogin.cnf file is parsed even when the --no-defaults flag is used. This design ensures that passwords can be securely retrieved from the obfuscated file rather than being forced onto the command line.

Security Considerations and Special Characters

The mysql_config_editor uses obfuscation to prevent credentials from being stored in plain text and visible via screen shares or process lists. However, this obfuscation is not unbreakable encryption; a system administrator with elevated privileges could potentially decrypt the file. Consequently, the file must have strict read/write permissions restricted to the owner. If the permissions are too open, MySQL clients will ignore the file for security reasons.

Handling Special Characters

If your password contains a hash symbol (#) or other special comment characters, it may be misinterpreted as the start of a comment within the configuration file, leading to Access denied errors. To prevent this, ensure the password is enclosed in double quotes when prompted or handled via scripts.

Overriding Profiles

You do not need to specify every parameter inside a login profile. Missing parameters can be supplied on the command line. For instance, if the dev_primary profile contains credentials and a port, but you need to connect to a different host using those same credentials, you can override just the host:

mysql --login-path=dev_primary --host=db-replica.internal