Suppress warning messages using mysql from within Terminal, but password written in bash script

It's very simple. This is work for me.

export MYSQL_PWD=password; mysql --user=username -e "statement"

MYSQL_PWD is one of the environment variables that are used directly or indirectly by MySQL.

From the docs:

MYSQL_PWD - The default password when connecting to mysqld. Using this is insecure. See Section 6.1.2.1, “End-User Guidelines for Password Security”.

ok, solution without temporary files or anything:

mysql --defaults-extra-file=<(echo $'[client]\npassword='"$password") -u $user -e "statement"

it is similar to what others have mentioned, but here you don't need an actual file, this part of the command fakes the file: <(echo ...) (notice there is no space in the middle of <(

You can also just redirect the standard error STDERR output to /dev/null

So just do:

mysql -u $user -p$password -e "statement" 2> /dev/null

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 !

Define the helper:

remove-warning () {
    grep -v 'mysql: [Warning] Using a password on the command line interface can be insecure.'
}

Use it:

mysql -u $user -p$password -e "statement" 2>&1 | remove-warning

Tachaan! Your code is clean and nice to read

(tested with bash)

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.

If you happen to use Rundeck for scheduling your tasks, or any other platform where you ask for a mylogin.cnf file, I have successfully used the following shell code to provide a new location for the file before proceeding with sql calls:

if test -f "$CUSTOM_MY_LOGINS_FILE_PATH"; then
   chmod 600 $CUSTOM_MY_LOGINS_FILE_PATH
   export MYSQL_TEST_LOGIN_FILE="$CUSTOM_MY_LOGINS_FILE_PATH"
fi

...

result=$(mysql --login-path=production -NBA -D $schema -e "$query")

Where MYSQL_TEST_LOGIN_FILE is an environment variable that can be set to a different file path than the default one.

This is especially useful if you are running in a forked process and can't move or copy files to the $HOME directory.

See documentation here.

the best solution is to use alias:

alias [yourapp]-mysql="mysql -u root -psomepassword -P3306 -h 127.0.0.1"

example, put this in your script:

alias drupal-mysql="mysql -u root -psomepassword -P3306 -h 127.0.0.1"

then later in your script to load a database:

drupal-mysql database_name < database_dump.sql

to run a statement:

drupal-mysql -e "EXEC SOMESTATEMENT;"

The answer from @david-g is quite excellent, but at least BSD grep will interpret the [Warning] as a regular expression representation of a single character (matching any one of the characters in the word Warning).

For my scripts, I'm using the same approach, wrapped in a function (and only matching parts of the string, for simplicity):

mysql() {
  (
    (
      (
        (
          (
            /usr/local/bin/mysql "$@"
          ) 1>&9
        ) 2>&1
      ) | grep -v 'Using a password on the command line interface can be insecure.'
    ) 1>&2
  ) 9>&1
}

out=$(mysql ...... 2>&1)
rc=$?

The above will call mysql with a set of options, redirect the filtered stderr to stdout, and capture the combined output in $opt and the exit code in $rc.

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

The easiest way:

mysql -u root -p YOUR_DATABASE

Enter this and you'll need to type your password in.

Note: Yes, without a semicolon.