Oct 22, 2018

How to fix MySQL Warning: Using a password on the command line interface can be insecure

The mysql_config_editor utility (available as of MySQL 5.6.6) enables you to store authentication credentials in an encrypted login file named .mylogin.cnf. The file location is the %APPDATA%\MySQL directory on Windows and the current user's home directory on non-Windows systems. The file can be read later by MySQL client programs to obtain authentication credentials for connecting to MySQL Server.

mysql_config_editor set --login-path=local --host=localhost --user=username --password

Then you can use in your shell script:

mysql --login-path=local 

instead of:

mysql -u username -p pass 

You need to put --login-path as the first parameter, or else you will get something similar to
"unknown variable 'login-path=local'"


SET

shell> mysql_config_editor set --login-path=local
         \ --host=localhost --user=localuser --password
Enter password: enter password "localpass" here
shell> mysql_config_editor set --login-path=remote
        \ --host=remote.example.com --user=remoteuser --password
Enter password: enter password "remotepass" here

SHOW


shell> mysql_config_editor print --all
[local]
user = localuser
password = *****
host = localhost
[remote]
user = remoteuser
password = *****
host = remote.example.com

USE

For example, to connect to the local server, use this command:
shell> mysql --login-path=local
To connect to the remote server, use this command:
shell> mysql --login-path=remote

To use it in mysqldump to backup structures:
$ mysqldump  --login-path=root --all-databases  --no-data --routines --events --triggers | tee  live.structure.sql