A Practical Guide to mysql_config_editor for Password Management

Summary: in this tutorial, you will learn how to use the mysql_config_editor utility to manage and store MySQL credentials securely.

Introduction to mysql_config_editor utility

Whenever you want to connect to the MySQL server using the mysql client, you need to provide the password.

For example, the following command uses the mysql client to log in to the MySQL server using the root account with a password:

mysql -u root -pCode language: SQL (Structured Query Language) (sql)

It’ll prompt you for the password.

To avoid entering the password every time you connect to the MySQL server, you can use the mysql_config_editor utility program.

The mysql_config_editor utility program encrypts and stores MySQL credentials in a file named .mylogin.cnf in a directory, depending on the operating system:

Each set of credentials is referred to as a login path, which includes information such as the username, password, hostname, and port for connecting to a MySQL server.

The mysql client automatically uses the information stored in the .mylogin.cnf file to facilitate logins.

To specify which login path to use, you can use the --login-path parameter for the mysql client. If you don’t provide any login path, the mysql client will use the client login path by default.

Defining credentials by creating a new login path

To create a new login path, you use the following syntax:

mysql_config_editor set [options]Code language: SQL (Structured Query Language) (sql)

The options include:

• --login-path=: The label for the credentials.
• --user=: The account username.
• --password: Flag to prompt for a password securely.
• --host=: The MySQL server’s hostname or IP address.
• --port=: The port number where the MySQL server is listening.
• --socket=: The path to the local socket file if connecting to a local server through Unix sockets.

For example, the following command creates a login path for the root account on the local MySQL server:

mysql_config_editor set --user=root --passwordCode language: SQL (Structured Query Language) (sql)

It’ll prompt you to enter the password for the root user. Once you provide the password, the command will create the .mylogin.cnf file.

Note that to invoke the mysql_config_editor program, you type it in the Command Prompt on Windows or Terminal on macOS and Linux.

Since we don’t specify the --login-path, the command will create a client login path.

To log in to the local MySQL server using the mysql client, you need to enter mysql command only without entering the -u root -p and the password:

mysqlCode language: SQL (Structured Query Language) (sql)

And you will be logged in.

The following example saves the connection information for a remote user named bob on a MySQL server at dev.mysqltutorial.org port 8888:

mysql_config_editor set --login-path=testing --user=bob --password --host=dev.mysqltutorial.org --port=5555Code language: SQL (Structured Query Language) (sql)

It’ll prompt you to enter a password for the bob user account.

Displaying available login paths

To show all the configured login paths, you use the following command:

mysql_config_editor print --allCode language: SQL (Structured Query Language) (sql)

It returns two login paths that we have created:

[client]
user = "root"
password = *****
[testing]
user = "bob"
password = *****
host = "dev.mysqltutorial.org"
port = 5555Code language: SQL (Structured Query Language) (sql)

To show the default login information, you use the mysql_config_editor print command:

mysql_config_editor printCode language: SQL (Structured Query Language) (sql)

It returns the client login path:

[client]
user = "root"
password = *****Code language: SQL (Structured Query Language) (sql)

To show a specific login path, you can use the --login-path parameter:

mysql_config_editor print --login-path=<login_path>Code language: SQL (Structured Query Language) (sql)

For example:

[testing]
user = "bob"
password = *****
host = "dev.mysqltutorial.org"
port = 5555Code language: SQL (Structured Query Language) (sql)

Removing connection information

The mysql_config_editor utility program allows you to remove a specific parameter of a login path, a login path, and all login paths.

1) Removing a specific parameter of a login path

To remove a specific parameter (e.g., port) of a login path, you use the mysql_config_editor remove command:

mysql_config_editor remove --login-path=<loging_path> --portCode language: SQL (Structured Query Language) (sql)

For example, the following command removes the port from the testing login:

mysql_config_editor remove --login-path=testing --portCode language: SQL (Structured Query Language) (sql)

To confirm the removal action, you can print out the testing login path:

 mysql_config_editor print --login-path=testingCode language: SQL (Structured Query Language) (sql)

Output:

[testing]
user = "bob"
password = *****
host = "dev.mysqltutorial.org"Code language: SQL (Structured Query Language) (sql)

The output indicates that the port has been removed from the testing login path.

2) Removing a login path

To remove connection information associated with a login path, you use the remove command:

mysql_config_editor remove --login-path=<login_path>Code language: SQL (Structured Query Language) (sql)

For example, the following command removes the login path testing:

mysql_config_editor remove --login-path=testingCode language: SQL (Structured Query Language) (sql)

3) Removing all login paths

To remove all login paths, you use the mysql_config_editor reset command:

mysql_config_editor resetCode language: SQL (Structured Query Language) (sql)

Summary

• Use the mysql_config_editor utility to store and manage MySQL credentials securely.
• Use the mysql_config_editor set command to define a new login path.
• Use the mysql_config_editor print command to show login paths.
• Use the mysql_config_editor remove to delete a specific parameter of a login path or a login path.
• Use the mysql_config_editor reset command to remove all login paths.