Secure authentication in Mysql (For DBA Administration purpose)

Dear Folks,

For secure authentication in mysql database server, "mysql_config_editor" tool ( available from mysql 5.6.6 version) enables you to store authentication information in encrypted format like below-

[devpcs-readonly]

user = dba

password = *****

host = 10.0.7.1

[devcpdb-readonly]

user = dba

password = *****

host = 10.0.2.1

[sitpcs-repl]

user = dba

password = *****

host = 172.31.2.14

[sitcpdb-repl]

user = dba

password = *****

host = 172.31.2.1

Above details can be seen by the below command -

mysql_config_editor print --all

Now we will see a demo to save a new authentication connection-

shell> mysql_config_editor set --login-path=<name_to_which_you_want_to_connect> —host=<IP_of_remote/local_server> —user=username —password

Enter Password:xxxxxxxxx

E.g
shell>mysql_config_editor --login-path='uat-pcs-readonly' --host='10.22.0.1' --user='readonly' --password
Enter Password: xxxxxx

Trying to login now-

shell>mysql --login-path=uat-pcs-readonly 
mysql>

You can also use --prompt option for avoiding any discrepancy among the multiple mysql console.

Example-

shell>mysql --login-path=uat-pcs-readonly --prompt="\u@\h [\d@\p] mysql> " 
readonly@172.22.0.1 [(none)@3306] mysql>


In this way, we do not need to write or provide database password again and again.

Obj-
Do not save mysql password in plain text flat file (csv/txt/.sh/etc)

Pros-
For DBA Administration related automate scripts, this feature can be useful in many cases.

Cons-
Anybody can login into mysql database by issuing --login-path option. So do not provide "root" credentials anywhere. Restrict user privileges.

Reference-
http://dev.mysql.com/doc/refman/5.6/en/mysql-config-editor.html