# Using `mysql_config_editor` to manage MySQL credentials > Part of Prisma's Data Guide. The complete index is at https://www.prisma.io/dataguide/llms.txt. Manage connection information like usernames, passwords, and MySQL servers with `mysql_config_editor`. Canonical URL: https://www.prisma.io/dataguide/mysql/tools/mysql-config-editor Authors: Justin Ellingwood Content revision: 91b5ea3b95a0824bcd0e0d61f68d6121c6e45039fde2b5f874c4c65eec29030e Source revision: b6716699f6439c3ba6ae3bf7116b7da84963c978 ## 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`](https://dev.mysql.com/doc/refman/8.0/en/mysql-config-editor.html) specifically designed to store and manage MySQL credentials so that you can [authenticate](https://www.prisma.io/dataguide/intro/database-glossary#authentication) 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. ### Setting connection information for a local account 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 `--password` flag. 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. ### Setting connection information for a remote account 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 `testuser`. ### Setting the default connection information 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 - **Host**: `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. For example: ``` 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. ## 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](https://en.wikipedia.org/wiki/INI_file) 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.