Passwordless authentication (supposedly) using mysql_config_editor

In the DBA operations world there are always challenges with passwords. How do you use the password for login, automation and operation scripts in a way that does not expose the password.

One of the MySQL utilities that addresses some of these questions is mysql_config_editor.

mysql_config_editor enables you to store authentication credentials in a login path file named .mylogin.cnf. On Linux these credentials are stored in the current user's home directory.

Installing mysql_config_editor

To install mysql_config_editor, all you need is the MySQL client installed. It can be particular to the MySQL flavor you use. I use Percona MySQL, so installing the Percona-Server-client rpm is enough for access to mysql_config_editor.

Common mysql_config_editor operations

To set up log in paths using mysql_config_editor 

mysql_config_editor set --login-path=monitor --host=XXXX --port=3306 --user=monitor --password

Enter password:

To log in using mysql_config_editor

 mysql --login-path=monitor

 Welcome to the MySQL monitor.  Commands end with ; or \g.

 Your MySQL connection id is 5087

 ...

 mysql>

To check what logins are set up in mysql_config_editor

mysql_config_editor print --all

[mysqlconn]

user = root

password = *****

host = localhost

[monitor]

user = monitor

password = *****

host = XXXX

port = 3306

Strong Passwords

The advantages of not having to remember or enter passwords are quite a few. For example, now a password can be a secure non dictionary string, that meets password validation rules.

The password for monitor can be B*kA2aBntGYdvJaf

It can be added to mysql_config_editor once using 

mysql_config_editor set --login-path=monitor --host=XXXX --port=3306 --user=monitor --password

Enter password:

And can be used thereafter by using - 

mysql --login-path=monitor

Using the password in scripts

This means that we can run scripts without providing the password. For example, if we were writing a script to take a backup or promote a slave, that will run on the database server. Here is an example - 

[root@ ~]# mysql --login-path=scripts -e "show slave status \G"

               Slave_IO_State:

                  Master_Host: XXXX

                  Master_User: XXXX

                  Master_Port: 3306

                Connect_Retry: 60

              Master_Log_File:

          Read_Master_Log_Pos: 4

               Relay_Log_File: mysql_relay_log.000001

Risks and Caveats

While this is a comparatively better way of logins for automation scripts it is still not secure. This is the reason for the "supposedly" in the title of this post. While this is a convenient way, it is by no means completely secure. 

[root@ ~]# mysql_config_editor print --all

[mysqlconn]

user = root

password = *****

host = localhost

[monitor]

user = monitor

password = *****

host = XXXX

port = 3306

We can read the contents of this encrypted file using the my_print_defaults utility. 

[root@ ~]# my_print_defaults -s monitor

--user=monitor

--password=B*kA2aBntGYdvJaf

--host=XXXX

--port=3306

my_print_defaults is a part of standard MySQL install. 

For this reason even though mysql_config_editor saves the password in an encrypted file, it is recommended that this only be used for "root" linux user, and the logins saved in the mysql_config_editor be restricted by 'user'@'localhost'. The root access in these servers also needs to be tightly controlled and regulated. 

Resources:

mysql_config_editor documentation