How do I use the MYSQL_TEST_LOGIN_FILE environment variable to change the mysql_config_editor file?

Question

I'm using MySQL 5.7 on Ubuntu 16.04.

I want to set up a mysql_config_editor login path. The default file for this information is ~/.mylogin.cnf, but I would prefer to have it in the file ~/.config/mylogin.cnf.

The documentation for mysqli-config-editor says

To specify an alternate login path file name, set the MYSQL_TEST_LOGIN_FILE environment variable.

I tried doing this:

$ MYSQL_TEST_LOGIN_FILE=~/.config/mylogin.cnf

$ echo $MYSQL_TEST_LOGIN_FILE
/home/username/.config/mylogin.cnf

$ mysql_config_editor set --login-path=mysqldump --host=localhost --user=root --password
Enter password: 

As you can see, I confirmed that the environment variable set correctly. However, running the set command above created the file ~/.mylogin.cnf, which I did not want, and did not create the file ~/.config/mylogin.cnf, which I do want.

$ ls ~/.mylogin.cnf
/home/username/.mylogin.cnf

$ ls ~/.config/mylogin.cnf
ls: cannot access '/home/username/.config/mylogin.cnf': No such file or directory

How do I use the MYSQL_TEST_LOGIN_FILE environment variable to change the mysql_config_editor file?

Answer 1

Evidently mysql_config_editor set spawns a shell subprocess, so to keep the environment variable available to it, you have to set it with export. This works:

$ export MYSQL_TEST_LOGIN_FILE=~/.config/mylogin.cnf

$ echo $MYSQL_TEST_LOGIN_FILE
/home/username/.config/mylogin.cnf

$ mysql_config_editor set --login-path=mysqldump --host=localhost --user=root --password
Enter password: 

$ ls ~/.mylogin.cnf
ls: cannot access '/home/username/.mylogin.cnf': No such file or directory

$ ls ~/.config/mylogin.cnf
/home/username/.config/mylogin.cnf

Answer 2

The login path file must be readable and writable to the current user, and inaccessible to other users. Otherwise, mysql_config_editor ignores it, and client programs do not use it either.