mysql_config_editor  (utility to configure authentication information for connecting to MySQL server)

Introduction:

Normally we connect to MySQL using username , password , socket  or host etc as shown below.

bash-4.2$  mysql -uroot -p  -h testmysql -S /var/log/mysql.sock 

Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 40974
Server version: 5.6.29-enterprise-commercial-advanced-log MySQL Enterprise Server - Advanced Edition (Commercial)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

Every time when we would like to connect to mysql , we will have to use the string as shown above which is some time very frustrating. There is mysql_config_editor utility (available as of MySQL 5.6.6) enables you to store authentication credentials in an encrypted login path file named .mylogin.cnf . The file location is under mysql home dirctory unix or under %APPDATA%\MySQL on windows.

• mysql_config_editor uses client login path defined to connect to mysql using  particular path name. for example –login-path=dba or –login-path=devuser or –login-path=root etc.
• There is no default login-path for this utility.
• Login path is always saved in .mylogin.cnf witch is encrypted and it gets decrypted when using login-path to connect to the instance.

we can use only following five  options with mysql_config_editor :-

• set [command options] Sets user name/password/host name/socket/port for a given login path (section).
• remove [command options] Remove a login path from the login file.
• print [command options] Print all the options for a specified login path.
• reset [command options] Deletes the contents of the login file.
• help Display this usage/help information.

Setting up login-path:-

Lets create login path using  mysql_config_editor for local host  using set  option.

 

bash-4.2$ mysql_config_editor set --login-path=root --user=root --host=localhost --password --socket=/apps/mysql/mysql.sock
Enter password:

Now try to connect with mysql using log-path

bash-4.2$ mysql --login-path=root
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 41218
Server version: 5.6.29-enterprise-commercial-advanced-log MySQL Enterprise Server - Advanced Edition (Commercial)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

We can use print option to see how many login-paths have been set. We can use login-paths for other hosts as well .

 shell> mysql_config_editor print --all
[root]
user = root
password = *****
host = localhost
socket = /apps/mysql/mysql.sock
 [remote]
 user = remoteuser
 password = *****
 host = remote.example.com

if we want to remove a login path  we can using remove option as shown below

shell> mysql_config_editor remove --login-path=dba

finally the reset  option can be used to remove all the login-paths from .mylogin.cnf .

bash-4.2$ mysql_config_editor reset
bash-4.2$ mysql_config_editor print --all
bash-4.2$

 

Thanks for visiting , dont forget to leave your feedback.
Regards
Raja Naveed