Tuesday, January 29, 2019

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.

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.