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.