{"id":12817,"date":"2023-12-10T05:56:55","date_gmt":"2023-12-10T12:56:55","guid":{"rendered":"https:\/\/www.mysqltutorial.org\/?page_id=12817"},"modified":"2023-12-10T18:20:57","modified_gmt":"2023-12-11T01:20:57","slug":"mysql_config_editor","status":"publish","type":"page","link":"https:\/\/www.mysqltutorial.org\/mysql-administration\/mysql_config_editor\/","title":{"rendered":"A Practical Guide to mysql_config_editor for Password Management"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use the mysql_config_editor<\/code> utility to manage and store MySQL credentials securely.<\/p>\n\n\n\n

Introduction to mysql_config_editor utility<\/h2>\n\n\n\n

Whenever you want to connect to the MySQL server<\/a> using the mysql<\/code> client, you need to provide the password. <\/p>\n\n\n\n

For example, the following command uses the mysql<\/code> client to log in to the MySQL server using the root<\/code> account with a password:<\/p>\n\n\n

mysql -u root -p<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
It’ll prompt you for the password.<\/p>\n\n\n\n
To avoid entering the password every time you connect to the MySQL server, you can use the mysql_config_editor<\/code> utility program.<\/p>\n\n\n\n
The mysql_config_editor<\/code> utility program encrypts and stores MySQL credentials in a file named .mylogin.cnf<\/code> in a directory, depending on the operating system:<\/p>\n\n\n\n
Platform<\/th>File location<\/th><\/tr><\/thead>
Windows<\/td>C:\\Users\\<username>\\AppData\\Roaming\\MySQL\\.mylogin.cnf<\/code><\/td><\/tr>
macOS and Linux<\/td>~\/.mylogin.cnf<\/code><\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
Each set of credentials is referred to as a login path<\/strong>, which includes information such as the username, password, hostname, and port for connecting to a MySQL server.<\/p>\n\n\n\n
The mysql<\/code> client automatically uses the information stored in the .mylogin.cnf<\/code> file to facilitate logins.<\/p>\n\n\n\n
To specify which login path to use, you can use the --login-path<\/code> parameter for the mysql<\/code> client. If you don’t provide any login path, the mysql client will use the client<\/code> login path by default.<\/p>\n\n\n\n
Defining credentials by creating a new login path<\/h2>\n\n\n\n
To create a new login path, you use the following syntax:<\/p>\n\n\n
mysql_config_editor set<\/span> [options]<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The options<\/code> include:<\/p>\n\n\n\n
    \n
  • --login-path=<\/code>: The label for the credentials.<\/li>\n\n\n\n
  • --user=<\/code>: The account username.<\/li>\n\n\n\n
  • --password<\/code>: Flag to prompt for a password securely.<\/li>\n\n\n\n
  • --host=<\/code>: The MySQL server’s hostname or IP address.<\/li>\n\n\n\n
  • --port=<\/code>: The port number where the MySQL server is listening.<\/li>\n\n\n\n
  • --socket=<\/code>: The path to the local socket file if connecting to a local server through Unix sockets.<\/li>\n<\/ul>\n\n\n\n
    For example, the following command creates a login path for the root account on the local MySQL server:<\/p>\n\n\n
    mysql_config_editor set<\/span> --user=root --password<\/span><\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    It’ll prompt you to enter the password for the root user. Once you provide the password, the command will create the .mylogin.cnf<\/code> file.<\/p>\n\n\n\n
    Note that to invoke the mysql_config_editor<\/code> program, you type it in the Command Prompt on Windows or Terminal on macOS and Linux.<\/p>\n\n\n\n
    Since we don’t specify the --login-path<\/code>, the command will create a client login path.<\/p>\n\n\n\n
    To log in to the local MySQL server using the mysql<\/code> client, you need to enter mysql<\/code> command only without entering the -u root -p<\/code> and the password:<\/p>\n\n\n
    mysql<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    And you will be logged in.<\/p>\n\n\n\n
    The following example saves the connection information for a remote user named bob<\/code> on a MySQL server at dev.mysqltutorial.org<\/code> port 8888<\/code>:<\/p>\n\n\n
    mysql_config_editor set<\/span> --login-path=testing --user=bob --password --host=dev.mysqltutorial.org --port=5555<\/span><\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    It’ll prompt you to enter a password for the bob<\/code> user account.<\/p>\n\n\n\n
    Displaying available login paths<\/h2>\n\n\n\n
    To show all the configured login paths, you use the following command:<\/p>\n\n\n
    mysql_config_editor print --all<\/span><\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    It returns two login paths that we have created:<\/p>\n\n\n
    [client]\nuser = \"root\"\npassword = *****\n[testing]\nuser = \"bob\"\npassword = *****\nhost = \"dev.mysqltutorial.org\"\nport = 5555<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    To show the default login information, you use the mysql_config_editor<\/code> print command:<\/p>\n\n\n
    mysql_config_editor print<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    It returns the client login path:<\/p>\n\n\n
    [client]\nuser = \"root\"\npassword = *****<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    To show a specific login path, you can use the --login-path<\/code> parameter:<\/p>\n\n\n
    mysql_config_editor print --login-path=<login_path><\/span><\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    For example:<\/p>\n\n\n
    [testing]\nuser = \"bob\"\npassword = *****\nhost = \"dev.mysqltutorial.org\"\nport = 5555<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    Removing connection information<\/h2>\n\n\n\n
    The mysql_config_editor<\/code> utility program allows you to remove a specific parameter of a login path, a login path, and all login paths.<\/p>\n\n\n\n
    1) Removing a specific parameter of a login path<\/h3>\n\n\n\n
    To remove a specific parameter (e.g., port) of a login path, you use the mysql_config_editor<\/code> remove  command:<\/p>\n\n\n
    mysql_config_editor remove --login-path=<loging_path> --port<\/span><\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    For example, the following command removes the port from the testing<\/code> login:<\/p>\n\n\n
    mysql_config_editor remove --login-path=testing --port<\/span><\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    To confirm the removal action, you can print out the testing<\/code> login path:<\/p>\n\n\n
     mysql_config_editor print --login-path=testing<\/span><\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    Output:<\/p>\n\n\n
    [testing]\nuser = \"bob\"\npassword = *****\nhost = \"dev.mysqltutorial.org\"<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    The output indicates that the port has been removed from the testing login path.<\/p>\n\n\n\n
    2) Removing a login path<\/h3>\n\n\n\n
    To remove connection information associated with a login path, you use the remove command:<\/p>\n\n\n
    mysql_config_editor remove --login-path=<login_path><\/span><\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    For example, the following command removes the login path testing:<\/p>\n\n\n
    mysql_config_editor remove --login-path=testing<\/span><\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    3) Removing all login paths<\/h3>\n\n\n\n
    To remove all login paths, you use the mysql_config_editor<\/code> reset command:<\/p>\n\n\n
    mysql_config_editor reset<\/span><\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    Summary<\/h2>\n\n\n\n
      \n
    • Use the mysql_config_editor<\/code> utility to store and manage MySQL credentials securely.<\/li>\n\n\n\n
    • Use the mysql_config_editor<\/code> set command to define a new login path.<\/li>\n\n\n\n
    • Use the mysql_config_editor<\/code> print command to show login paths.<\/li>\n\n\n\n
    • Use the mysql_config_editor<\/code> remove to delete a specific parameter of a login path or a login path.<\/li>\n\n\n\n
    • Use the mysql_config_editor<\/code> reset command to remove all login paths. <\/li>\n<\/ul>\n
      \n\t
      \n\t\t
      Was this tutorial helpful? <\/div>\n\t\t
      \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
      \n\t\t
      \n\t\t\t
      <\/div>\n\t\t\t\n\t\t\t