Bash Script Mysql Warning: Using a password on the command line interface can be insecure

Question

Hi I have a script to partition some mysql databases. We are upgrading from 5.5 to 5.6. While testing the scripts i noticed that with the new 5.6 version mysql returns Warning: Using a password on the command line interface can be insecure. what is the best way to fix this? I read a workaround would be 2>/dev/null but I wont be able to get the exit code or any errors if they happen. Is there any other way to do this. Here is the problematic line of code:

MYSQL_RESULT=`echo "SET sql_log_bin=0;SET @pdb='$DB',@ptable='$table';CALL maintenance(@pdb,@ptable);SET sql_log_bin=1;"|mysql -uUSER -pPASSWORD database`

Answer 1

One way to get around this is to set the appropriate variables in your ~/.my.cnf file. Something similar to this should help:

[mysql]                                                                                                                                                   
user=my_username                                                                                                                                          
password=my_password

This should live in the home directory of the user executing the command. And don't forget to set the right permissions on the file to avoid it being readable by other users: chmod 600 ~/.my.cnf.

Answer 2

If you are using MySQL/5.6.6 or greater you can use a bundled tool called mysql_config_editor:

The mysql_config_editor utility [...] enables you to store authentication credentials in an encrypted login path 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.

With such tool, you can assign a number of named credentials ("login paths"):

$ mysql_config_editor set --login-path=backup-user --host=localhost --user=backup --password
Enter password:
$ mysql_config_editor print --all
[backup-user]
user = backup
password = *****
host = localhost

... which are can be used later by clients that support the feature (such as the official command-line client or mysqldump):

$ mysql --login-path=backup-user
Welcome to the MySQL monitor.  Commands end with ; or \g.

Please note that this doesn't really encrypt passwords (credentials at .mylogin.cnf are obfuscated only), it just moves them away from your scripts.

_{Of course, having 5.6.6+ is the main reason of getting «Warning: Using a password on the command line interface can be insecure» in the first place ;-)}