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.
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 client will automatically assume
localhost, just as if you were to omit the
--host= option when providing credentials manually on the command line.
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_editorto 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.
For example, to create a login for a user named
salesadmin on the local MySQL server, you could type:
mysql_config_editor set --login-path=sales --user=salesadmin --password
You will be prompted for the account password and the new connection information will be saved to the
.mylogin.cnf file under a label called
sales. We provide the account name with
--user=salesadmin and tell
mysql_config_editor to prompt for the password by including the
Since this is a local account, it will connect through a local socket file if running on a Unix-like system. If you've not modified MySQL to run differently, however, the MySQL tools will know what to do and you do not need to provide those details when configuring.
To save the connection information for a remote user name
testuser on a MySQL server listening on port 5555 on a host called
dev.example.com, you could type:
mysql_config_editor set --login-path=testing --user=testuser --password --host=dev.example.com --port=5555
The entry for the
testing login path will have all of the information required to automatically connect to the MySQL database hosted on
dev.example.com with the user account
MySQL tools are designed to use reasonable defaults when called without explicit connection information. For instance, on Unix-like systems, they will try to connect using the following details if not overwritten:
- User: Your operating system username
- Password: No password
localhost, which by default means you'll be connecting over a Unix socket at the default location for your platform.
If these options aren't appropriate for your use case, you can change the default connection information with
mysql_config_editor. To do so, provide the connection information you'd like use by default without specifying a login path.
mysql_config_editor set --user=root --password
This will store the connection information under the generic
client login path, which MySQL tools read when no other login path is provided.
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:
To log in to the
dev.example.com server using the
testuser account, we can instead specify the
testing login path:
If you call
--login-path=, it will check the
client login path you configured and try to log you into your local database with the
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:
+----------------------+| 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:
--------------mysql Ver 8.0.27-0ubuntu0.20.04.1 for Linux on x86_64 ((Ubuntu))Connection id: 28Current database:Current user: sammy@localhostSSL: Not in useCurrent pager: stdoutUsing outfile: ''Using delimiter: ;Server version: 8.0.27-0ubuntu0.20.04.1 (Ubuntu)Protocol version: 10Connection: Localhost via UNIX socketServer characterset: utf8mb4Db characterset: utf8mb4Client characterset: utf8mb4Conn. characterset: utf8mb4UNIX socket: /var/run/mysqld/mysqld.sockBinary data as: HexadecimalUptime: 1 day 21 hours 37 min 49 secThreads: 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
UNIX socket items.
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
To view the default login information you've configured, which is stored under the
client login path, you can use the
[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 = *****
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
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:
This removes all of the configured login paths.
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.
If you are using Prisma, you can set connection information for MySQL databases when configuring the MySQL database connector.