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 -p
Code 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:
Platform | File location |
---|---|
Windows | C:\Users\<username>\AppData\Roaming\MySQL\.mylogin.cnf |
macOS and Linux | ~/.mylogin.cnf |
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 --password
Code 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:
mysql
Code 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=5555
Code 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 --all
Code 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 = 5555
Code language: SQL (Structured Query Language) (sql)
To show the default login information, you use the mysql_config_editor
print command:
mysql_config_editor print
Code 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 = 5555
Code 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> --port
Code 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 --port
Code 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=testing
Code 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=testing
Code 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 reset
Code 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.