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

Here is a solution for Docker in a script /bin/sh :

docker exec [MYSQL_CONTAINER_NAME] sh -c 'exec echo "[client]" > /root/mysql-credentials.cnf'

docker exec [MYSQL_CONTAINER_NAME] sh -c 'exec echo "user=root" >> /root/mysql-credentials.cnf'

docker exec [MYSQL_CONTAINER_NAME] sh -c 'exec echo "password=$MYSQL_ROOT_PASSWORD" >> /root/mysql-credentials.cnf'

docker exec [MYSQL_CONTAINER_NAME] sh -c 'exec mysqldump --defaults-extra-file=/root/mysql-credentials.cnf --all-databases'

Replace [MYSQL_CONTAINER_NAME] and be sure that the environment variable MYSQL_ROOT_PASSWORD is set in your container.

Hope it will help you like it could help me !

Answer 2

I use something like:

mysql --defaults-extra-file=/path/to/config.cnf

or

mysqldump --defaults-extra-file=/path/to/config.cnf 

Where config.cnf contains:

[client]
user = whatever
password = whatever
host = whatever

This allows you to have multiple config files - for different servers/roles/databases. Using ~/.my.cnf will only allow you to have one set of configuration (although it may be a useful set of defaults).

If you're on a Debian based distro, and running as root, you could skip the above and just use /etc/mysql/debian.cnf to get in ... :

mysql --defaults-extra-file=/etc/mysql/debian.cnf

Answer 3

Another alternative is to use sshpass to invoke mysql, e.g.:

sshpass -p topsecret mysql -u root -p username -e 'statement'

Answer 4

If you wish to use a password in the command line, I've found that this works for filtering out the specific error message:

mysqlcommand 2>&1 | grep -v "Warning: Using a password"

It's basically redirecting standard error to standard output -- and using grep to drop all lines that match "Warning: Using a password".

This way, you can see any other output, including errors. I use this for various shell scripts, etc.

Answer 5

Easiest way is

mysql -u root -pMYPASSWORD -e "show databases" 2>/dev/null

Answer 6

For PowerShell (pwsh, not bash), this was quite a rube-goldberg solution... My first attempt was to wrap the calls to mysql in a try/catch function, but due to some strange behavior in PowerShell error handling, this wasn't viable.

The solution was to override the $ErrorActionPreference just long enough to combine and capture STDERR and STDOUT and parse for the word ERROR and re-throw as needed. The reason we couldn't catch and release on "^mysql.*Warning.*password" is because PowerShell handles and raises the error as one stream, so you must capture it all in order to filter and re-throw. :/

Function CallMySQL() {
    # Cache the error action preference
    $_temp = $ErrorActionPreference
    $ErrorActionPreference = "Continue"

    # Capture all output from mysql
    $output = (&mysql --user=foo --password=bar 2>&1)

    # Restore the error action preference
    $ErrorActionPreference = $_temp

    if ($output -match "ERROR") {
        throw $output
    } elseif($output) {
        "   Swallowing $output"
    } else {
        "   No output"
    }
}

Note: PowerShell is available for Unix, so this solution is cross-platform. It can be adapted to bash with some minor syntax modifications.

Warning: There are dozens of edge-cases where this won't work such as non-english error messages or statements that return the word ERROR anywhere in the output, but it was enough to swallow the warning for a basic call to mysql without bombing out the entire script. Hopefully others find this useful.

It would be nice if mysql simply added an option to suppress this warning.