Using `mysql_config_editor` to manage MySQL credentials

Introduction

Maintaining credentials for different users and databases across a variety of hosts can be challenging from a usability perspective. If you regularly log into multiple MySQL servers or if you have projects that have separate user accounts with unique privileges for security reasons, you can easily lose track of how to connect to the accounts you need.

Fortunately, MySQL provides a small utility called mysql_config_editor specifically designed to store and manage MySQL credentials so that you can authenticate easily with MySQL clients and tools. In this guide, we'll cover how mysql_config_editor works, how to manage multiple credentials securely, and how to tell your other MySQL tools to leverage the configuration to authenticate to your servers.

How does mysql_config_editor work?

The mysql_config_editor utility is a small program included in MySQL installations that is used to manage credentials for connecting to different MySQL servers or different accounts. It encrypts credential information and stores it in a file called .mylogin.cnf in your home directory.

Each set of credentials describing how to log in to a MySQL account is called a "login path". These usually specify the account's username and password and can additionally store relevant information about how to connect to the appropriate MySQL server like the hostname and port where the MySQL is listening.

MySQL clients and tools are automatically configured to use the information in the .mylogin.cnf file to help login to MySQL servers. You can use the --login-path= parameter on MySQL tools like the mysql client to specify which login details should be used. If no login path is provided, the tools will use the credentials associated with the default login path, known as client, if it is defined.

If the login paths don't define certain values, the MySQL clients and tools will use their configured default values instead. For instance, if you do not specify a host when creating a login path with mysql_config_editor, the mysql client will automatically assume localhost, just as if you were to omit the --host= option when providing credentials manually on the command line.

Defining credentials by creating a new login path

We can get started by using the mysql_config_editor tool to set up a new login path.

The general syntax for defining a new login path is the following:

mysql_config_editor set [options]

Typically, you'll include some of the following options:

• --login-path=: The label you want to use for these credentials
• --user=: The account username
• --password: A flag to tell mysql_config_editor to prompt for a password for the account. The password prompt allows you to securely enter the password so that it isn't recorded to shell history files as it would be if provided it directly on the command line.
• --host=: The host name or IP address where the MySQL server is hosted.
• --port=: The port number where the MySQL server is listening.
• --socket=: The path to the local socket file to connect with if you are connecting to a local server through Unix sockets.

You only need to provide the information that differs from the default options for the MySQL utilities.

As you create entries, keep in mind that mysql_config_editor provides no way to edit the details associated with a login path once it's created. To change any details, you'll need to respecify all of the appropriate connection information again to overwrite the previous entry.

mysql_config_editor set --login-path=sales --user=salesadmin --password

mysql_config_editor set --login-path=testing --user=testuser --password --host=dev.example.com --port=5555

mysql_config_editor set --user=root --password

Logging in with login paths

To use the connection information you've configured, specify the --login-path= on the command line with MySQL clients and tools.

For example, to log in to the local salesadmin account configured earlier, we can tell mysql to use the sales login path:

mysql --login-path=sales

To log in to the dev.example.com server using the testuser account, we can instead specify the testing login path:

mysql --login-path=testing

If you call mysql without --login-path=, it will check the client login path you configured and try to log you into your local database with the root account:

mysql

In each case, you will be logged in to the appropriate account without needing to provide any additional details.

You can verify the user you're connected as by typing:

SELECT user();

 +----------------------+
 | user()               |
 +----------------------+
 | salesadmin@localhost |
 +----------------------+
 1 row in set (0.00 sec)

If you want to verify the user and the method you're connecting with, you can use the status command instead:

status

 --------------
 mysql  Ver 8.0.27-0ubuntu0.20.04.1 for Linux on x86_64 ((Ubuntu))
 
 Connection id:         28
 Current database:  
 Current user:          sammy@localhost
 SSL:                   Not in use
 Current pager:         stdout
 Using outfile:         ''
 Using delimiter:       ;
 Server version:        8.0.27-0ubuntu0.20.04.1 (Ubuntu)
 Protocol version:      10
 Connection:            Localhost via UNIX socket
 Server characterset:   utf8mb4
 Db     characterset:   utf8mb4
 Client characterset:   utf8mb4
 Conn.  characterset:   utf8mb4
 UNIX socket:           /var/run/mysqld/mysqld.sock
 Binary data as:        Hexadecimal
 Uptime:                1 day 21 hours 37 min 49 sec
 
 Threads: 2  Questions: 66  Slow queries: 0  Opens: 186  Flush tables: 3  Open tables: 105  Queries per second avg: 0.000
 --------------

You can see the current user under Current user, but you can also view the details about how you are connected to the server by looking at the Connection and UNIX socket items.

Displaying available login paths

While the connection details you define are stored in a file called .mylogin.cnf in your home directory, the contents are encrypted for security. To view the configured information, you need to use the mysql_config_editor again.

To view the default login information you've configured, which is stored under the client login path, you can use the print subcommand without any additional options:

mysql_config_editor print

[client]
user = "root"
password = *****

MySQL uses an INI style file format to group connection details under the appropriate login path label. You may also notice that the password is obscured. This, again, is a security measure so as not to leak the saved password.

To view a different login path, you can supply the --login-path= option as usual:

mysql_config_editor print --login-path=testing

[testing]
user = "testuser"
password = *****
host = "dev.example.com"
port = 5555

To show all of the configured login paths, you can add the --all flag instead:

mysql_config_editor print --all

[sales]
user = "salesadmin"
password = *****
[testing]
user = "testuser"
password = *****
host = "dev.example.com"
port = 5555
[client]
user = "root"
password = *****

Removing connection information

You can remove the connection information associated with a login path with the remove subcommand. Providing the --login-path will allow mysql_config_editor to target the appropriate entry.

For example, to remove the connection information for the sales login path, you can type:

mysql_config_editor remove --login-path=sales

If you check the configured entries, you will find that the sales login path has been removed:

mysql_config_editor print --all

[testing]
user = "testuser"
password = *****
host = "dev.example.com"
port = 5555
[client]
user = "root"
password = *****

You can also remove a specific parameter from the login path's connection information. For instance, if the MySQL server at "dev.example.com" has been reconfigured to now run on the default 3306 port, you can remove the port information. To do so, you'd provide the --port flag along with the --login-path=:

mysql_config_editor remove --login-path=testing --port

You can verify that the port specification has been removed from the testing login path by printing the entries again:

mysql_config_editor print --all

[testing]
user = "testuser"
password = *****
host = "dev.example.com"
[client]
user = "root"
password = *****

You can also delete all login paths and create a new blank .mylogin.cnf file by typing:

mysql_config_editor reset

This removes all of the configured login paths.

Conclusion

In this guide, we took a look at mysql_config_editor, one of MySQL's small utilities designed to improve user experience by managing connection information. We covered how to configure connection information using login paths and how to call MySQL tools using our configured credentials. We also discussed how to override defaults and manage existing login path information.

By taking advantage of mysql_config_editor and other tools that the MySQL project provides, you can remove some of the frustration that can arise when managing multiple projects from a single location. It is a good example of a relatively simple tool designed to streamline repetitive, error-prone tasks to help you focus on more important work.