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.
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:
