Suppress warning messages using mysql from within

Question

When I tried running the following command on MySQL from within Terminal:

mysql -u $user -p$password -e "statement"

The execution works as expected, but it always issues a warning:

Warning: Using a password on the command line interface can be insecure.

However, I have to conduct the statement above using an environment variable ($password) that stores my password, because I want to run the command iteratively in bash script from within Terminal, and I definitely don't like the idea of waiting a prompt showing up and forcing me to input my password 50 or 100 times in a single script. So here's my question:

• Is it feasible to suppress the warning? The command works properly as I stated, but the window becomes pretty messy when I loop over and run the command 50 or 100 times.

• Should I obey the warning message and do NOT write my password in my script? If that's the case, then do I have to type in my password every time the prompt forces me to do so?

Running man mysql doesn't help, saying only

--show-warnings
Cause warnings to be shown after each statement if there are any. This option applies to interactive and batch mode.

and mentions nothing about how to turn off the functionality, if I'm not missing something.

I'm on OS X 10.9.1 Mavericks and use MySQL 5.6 from homebrew.

Answer 1

You can execute mySQL and suppress warning and error messages by using /dev/null for example:

# if you run just a SQL-command
mysql -u ${USERNAME} -p${PASSWORD} -h ${HOST} ${DATABASE} -e "${STATEMENT}" &> /dev/null

# Or you can run SQL-script as a file
mysql -u ${USERNAME} -p${PASSWORD} -h ${HOST} ${DATABASE} < ${FILEPATH} &> /dev/null

Where:

${USERNAME} - existing mysql user

${PASSWORD} - password

${HOST}     - ip or hostname, for example 'localhost'

${DATABASE} - name of database

${STATEMENT}- SQL command

${FILEPATH} - Path to the SQL-script

enjoy!

Answer 2

From https://gist.github.com/nestoru/4f684f206c399894952d

# Let us consider the following typical mysql backup script:
mysqldump --routines --no-data -h $mysqlHost -P $mysqlPort -u $mysqlUser -p$mysqlPassword $database

# It succeeds but stderr will get:
# Warning: Using a password on the command line interface can be insecure.
# You can fix this with the below hack:
credentialsFile=/mysql-credentials.cnf
echo "[client]" > $credentialsFile
echo "user=$mysqlUser" >> $credentialsFile
echo "password=$mysqlPassword" >> $credentialsFile
echo "host=$mysqlHost" >> $credentialsFile
mysqldump --defaults-extra-file=$credentialsFile --routines --no-data $database

# This should not be IMO an error. It is just a 'considered best practice'
# Read more from http://thinkinginsoftware.blogspot.com/2015/10/solution-for-mysql-warning-using.html

Answer 3

Personally, I use script wrapper to catch that error. Here is code sample:

#!/bin/bash

#echo $@ | cat >> /home/mysqldump.log 2>/dev/null
ERR_FILE=/tmp/tmp_mdump.err

# Execute dumper
/usr/bin/mysqldump $@ 2>$ERR_FILE

# Determine error and remove tmp file
ERROR=`cat $ERR_FILE`
rm $ERR_FILE

# Handle an error
if [ "" != "$ERROR" ]; then

        # Error occured
        if [ "Warning: Using a password on the command line interface can be insecure." != "$ERROR" ]; then
                echo $ERROR >&2
                exit 1
        fi
fi

Answer 4

A simple workaroud script. Name this "mysql", and put it in your path before "/usr/bin". Obvious variants for other commands, or if the warning text is different.

#!/bin/sh

(
(
(
(
(
    /usr/bin/mysql "$@"
) 1>&9 
) 2>&1
) | fgrep -v 'mysql: [Warning] Using a password on the command line interface can be insecure.'
) 1>&2 
) 9>&1

Answer 5

It worked for me- Just added 2> null after the $(mysql_command), and it will suppress the Errors and Warning messages only.

Answer 6

The problem I had was using the output in a conditional in a bash script.

This is not elegant, but in a docker env this should really not matter. Basically all this does is ignore the output that isn't on the last line. You can do similar with awk, and change to return all but the first line etc.

This only returns the Last line

mysql -u db_user -pInsecurePassword my_database ... | sed -e '$!d'

It won't suppress the error, but it will make sure you can use the output of a query in a bash script.