375

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.

Simon East
  • 55,742
  • 17
  • 139
  • 133
Blaszard
  • 30,954
  • 51
  • 153
  • 233
  • 18
    The recommended way is to store your password in an option file (smth like `[client]` `password=my_password` in `~/.my.cnf`). Surely it has some security implications too, but at least it's not accessible to *anyone* who can run `ps`, and you have control over it with file permissions. – Anton Kovalenko Dec 23 '13 at 21:27
  • 6
    `mysql -u root password root -e "statement" > /dev/null` ? –  Dec 23 '13 at 21:42
  • Oh by the way, you can also use something like Python `pexcept`. It can do terminal insertions and also handle feedback that the command gives. This way you can just skip that verbose output and strip of the actual output you want :) –  Dec 23 '13 at 21:48
  • 9
    The recommended way IMO penalizes the ones that do the right thing to protect the ones that do the wrong thing. If the password is stored inside a script file it will not show up with ps or in any log. That is the correct way to do this. Putting the file in an external file does help the ones that would cron the password but that is bad to start with. In the meantime scripts that have been running for years now fail and we need to modify them just because this warning comes up in the stderr. – Nestor Urquiza Aug 26 '15 at 14:20
  • Even from cron if you use a variable 'ps' will not list the clear text password bu the variable instead so there is not increased risk on using plain text password in a cron variable versus using an external credentials file. You can see this easily: root@server:~# while sleep 1; do ps -ef|grep echo|grep -v grep; done root 3212 3211 0 10:37 ? 00:00:00 /bin/sh -c sleep 5 && echo $SECRET_VAR_IN_CRON > /dev/null – Nestor Urquiza Aug 26 '15 at 14:41
  • Very interesting question, starred for further reference. I am surprised how many different ways I can use to solve this. – Jürgen Steinblock May 25 '16 at 06:32
  • 3
    For the recommended method whcih does not store the password in the clear, is detailed in http://dev.mysql.com/doc/refman/5.6/en/mysql-config-editor.html – anthony Nov 24 '17 at 00:33
  • 1
    @user1467267 : I don't have a mysql at hand, but isn't it that mysql writes warnings and errors to stderr? – user1934428 Feb 14 '20 at 13:39

26 Answers26

296

If your MySQL client/server version is a 5.6.x a way to avoid the WARNING message are using the mysql_config_editor tools:

mysql_config_editor set --login-path=local --host=localhost --user=username --password

Then you can use in your shell script:

mysql --login-path=local  -e "statement"

Instead of:

mysql -u username -p pass -e "statement"
Giacomo1968
  • 25,759
  • 11
  • 71
  • 103
Cristian Porta
  • 4,393
  • 3
  • 19
  • 26
  • 41
    Remember that the `--login-path` has to come before all other arguments. I was trying `mysqldump --tables --login-path=local` and getting the error `unknown variable 'login-path=local'`. – Tulio May 03 '14 at 22:33
  • The mysql command line client will look under the 'client' login path by default. You can simplify the instructions to "mysql -e 'statement'" by making a small change. – Morgan Tocker Dec 14 '15 at 16:05
  • 2
    This is working fine if we directly execute the shell file but not working if called from crontab – Nabeel Arshad Feb 08 '17 at 10:15
  • 1
    @NabeelArshad I think that this is because in your crontab the "home" for the user is not set (ENV vars in general) so in the crontab the client is not able to find the correct ~/.mylogin.cnf – Cristian Porta Feb 08 '17 at 11:03
  • @CristianPorta No, I think it is because `mysql_config_editor` does not accept a value for the `--password` argument, because of the same security related reason. You have to execute the statement and then the tool requires you to type in the password. So this way is quite useless, since scripts can't talk to blocking processes. – StanE Mar 31 '17 at 15:11
  • You need to put --login-path as the first parameter, or else you will get something similar to "unknown variable 'login-path=local'" – user3081809 Nov 25 '17 at 19:40
  • @zhekaus Though you need it for automation. – Nam G VU Dec 09 '17 at 11:05
  • 1
    @NamGVU, I'm not sure, however, I believe that this solution stores encrypted passwords. – zhekaus Dec 09 '17 at 13:20
  • You can simply redirect the Standard Error STDERR output to /dev/null `mysql -u $user -p$password -e "statement" 2> /dev/null` https://stackoverflow.com/a/52179554/2598101 – Joseph Shih Sep 05 '18 at 07:35
  • Good One.. But No option to set default database :-( – Aravinthan K Oct 03 '18 at 14:14
  • Alert: Do remember to quote your password in double quotes when you are prompted for that otherwise you may face `access denied` issues due to special characters in the passwords. I was stuck at that issue and resolved that by reading it here https://stackoverflow.com/questions/19372095/mysql-config-editor-login-path-local-not-working – Imran Zahoor Jan 08 '19 at 09:10
  • 2
    So after all these comments, what is the solution that works for scripts that are called from crontab? – matteo Apr 16 '20 at 16:11
293

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

luke77
  • 2,255
  • 2
  • 18
  • 30
David Goodwin
  • 4,184
  • 1
  • 22
  • 12
  • This worked well for me, except it should just be user= and host= – MECU Apr 16 '14 at 17:09
  • 20
    Note: `--defaults-extra-file` must be first option otherwise mysql complains with `mysqldump: unknown variable 'defaults-extra-file`. – pevik Apr 16 '14 at 20:39
  • 6
    Awesome alternative to the accepted answer. Definitely don't set the `MYSQL_PWD` variable.... – DudeOnRock Oct 29 '14 at 00:07
  • 1
    Definitely a good option for versions below 5.6. Otherwise, I'd go with the accepted answer. – dkniffin Nov 05 '14 at 00:16
  • 27
    An alternative to making a temporary .cnf file is to do this in Bash: `mysql --defaults-extra-file=<(printf "[client]\nuser = %s\npassword = %s" "$user" "$pwd") -e "statement"`. Since the `printf` is executed by Bash directly it doesn't show up in `ps`. – Dave James Miller Nov 13 '14 at 14:46
  • This solved the problem for me, when the accepted answer couldn't; I have a service running under Local System that does not have a "%APPDATA%/MySQL" directory that a login-path can be configured for. Creating the secondary .cnf file worked perfectly – Evan Bechtol Jan 12 '16 at 21:44
  • 7
    I needed to use `--defaults-file` rather than `--defaults-extra-file`, because the latter gave preference to the settings in ~/.my.cnf. – Roger Dueck Jul 19 '16 at 17:28
  • Nice solution as you can place the `cnf` file in your `~/.ssh` folder which is inaccessible to others. – gone Aug 19 '21 at 06:41
227

One method that is convenient (but equally insecure) is to use:

MYSQL_PWD=xxxxxxxx mysql -u root -e "statement"

Note that the official docs recommend against it.
See 6.1.2.1 End-User Guidelines for Password Security (Mysql Manual for Version 5.6):

Storing your password in the MYSQL_PWD environment variable

This method of specifying your MySQL password must be considered extremely insecure and should not be used. Some versions of ps include an option to display the environment of running processes. On some systems, if you set MYSQL_PWD, your password is exposed to any other user who runs ps. Even on systems without such a version of ps, it is unwise to assume that there are no other methods by which users can examine process environments.

Community
  • 1
  • 1
Ivan Dokov
  • 4,013
  • 6
  • 24
  • 36
  • 8
    This doesn't work in my bash script: `Access denied for user 'root'@'localhost' (using password: NO)` – rubo77 Nov 05 '15 at 09:58
  • 30
    In a script, you need to `export MYSQL_PWD=whatever`. – Riot Nov 19 '15 at 14:14
  • Incase you are developing a unix binary with setuid bit to root, this method is propably the most clean solution. If an attacker gets able to hook into root permissions, they don`t need to inspect Environment anymore. Thank you very much – blender Dec 07 '15 at 15:28
  • 3
    Because my query is very quick, I opted for this option. Then I set it to something bogus after executing the query. – TimH - Codidact Apr 06 '16 at 18:04
  • 15
    In a script you don't need to run `export`, just place it all on one line: `MYSQL_PWD=xxxxxxxx mysql -u root -e "statement"` – J.D. Nov 18 '16 at 14:34
  • This doesn't appear to be working anymore (at least, on MySQL 5.7.19). It seems to ignore the MYSQL_PWD env var. – Tim Malone Oct 05 '17 at 01:33
  • It is not surprising that it does not work any more! – anthony Nov 29 '17 at 23:34
  • 1
    @J.D. : works for me in Ubuntu 16.04 with MySQL 5.7.21. – mivk Apr 23 '18 at 10:30
  • 3
    MYSQL_PWD is documented in the 5.5, 5.6 and 5.7 client documentation, but omitted from the 8.0 documentation. – ClearCrescendo Nov 27 '19 at 17:29
  • 4
    MYSQL_PWD is deprecated as of mysql 8.0, see here https://dev.mysql.com/doc/refman/8.0/en/mysql-nutshell.html – santiago arizti Sep 22 '20 at 21:32
93

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.

johnmontfx
  • 1,181
  • 8
  • 6
  • 1
    This is an excellent solution for use in one-liner tasks called in other tasks such as creating a Rake task for a Capistrano deployment. – Giacomo1968 Dec 22 '16 at 14:45
  • 4
    Excellent and simple to mute that message, no need to touch anything in MySQL setup. – ajaaskel Oct 18 '17 at 15:13
  • 2
    How would I use this with mysqldump where I already have a redirect for the SQL? – MacroMan Jan 03 '18 at 10:31
  • 4
    This is a really bad solution compared to the other ones here. It may fail with any subsequent version of MySQL should the text change, might not work in another locale either. – yktoo Sep 24 '18 at 13:22
  • How about redirecting the error back to the stderr? (e.g. `1>&2`) – weakish Dec 18 '20 at 09:04
  • 2
    Notice that warning text may change [Warning] Using a password Warning: Using a password – Firas Abd Alrahman Jan 31 '21 at 01:49
  • doesn't work with mysqldump though – thomas Jun 28 '22 at 15:53
  • 1
    Another problem with this approach: if you are using the exit code of the `mysql` command in any meaningful way, that behaviour will break, because `grep` will always exit with 0. – Sumit Dec 20 '22 at 13:13
52

Here's how I got my bash script for my daily mysqldump database backups to work more securely. This is an expansion of Cristian Porta's great answer.

  1. First use mysql_config_editor (comes with mysql 5.6+) to set up the encrypted password file. Suppose your username is "db_user". Running from the shell prompt:

     mysql_config_editor set --login-path=local --host=localhost --user=db_user --password
    

    It prompts for the password. Once you enter it, the user/pass are saved encrypted in your home/system_username/.mylogin.cnf

    Of course, change "system_username" to your username on the server.

  2. Change your bash script from this:

     mysqldump -u db_user -pInsecurePassword my_database | gzip > db_backup.tar.gz
    

    to this:

     mysqldump --login-path=local my_database | gzip > db_backup.tar.gz
    

No more exposed passwords.

Buttle Butkus
  • 9,206
  • 13
  • 79
  • 120
  • 1
    This answer should be nominated as correct one. Cause it creates `~/.mylogin.cnf` with encrypted content which makes things secure. – num8er Jun 05 '23 at 08:58
28

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”.

nur zazin
  • 1,018
  • 13
  • 13
  • This is the only right answer here! works like a charm :) – rubmz Jul 13 '21 at 13:58
  • 2
    Use of the `MYSQL_PWD` environment variable to specify a MySQL password is deprecated. – Mahmoud Aug 05 '21 at 14:35
  • 1
    @rubmz this is the only wrong answer here! "Use of MYSQL_PWD to specify a MySQL password must be considered extremely insecure and should not be used." https://dev.mysql.com/doc/refman/8.0/en/environment-variables.html – Buttle Butkus Sep 14 '22 at 05:08
19

Easiest way is

mysql -u root -pMYPASSWORD -e "show databases" 2>/dev/null
JavaGuy
  • 639
  • 1
  • 6
  • 11
  • 64
    Problem is this will suppress legitimate errors in your script as well. – man910 Apr 23 '14 at 17:01
  • 6
    Also you can create a log file 2>/var/log/myscript.log to log that errors. – Skamasle Aug 01 '14 at 11:38
  • 6
    use `2>/dev/null | grep -v "mysql: [Warning] Using a password on the command line interface can be insecure."` to supress only the warning in question – Hafenkranich Aug 02 '17 at 07:07
  • 4
    Since 2>/dev/null already redirected to /dev/null - grep is not doing anything there. Redirection needs to go to 2>&1 to filter out just this single warning. – Tomasz Mar 05 '21 at 23:59
16

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 <(

santiago arizti
  • 4,175
  • 3
  • 37
  • 50
  • 2
    this doesn't work if you already have a `.my.cnf` file in your `~/.` with a password entry – santiago arizti Aug 03 '19 at 00:10
  • 1
    I assume the fact that this didn't get any comments to the contrary, it is relatively secure. If that's the case, it is an awesome workaround in my opinion. – stepanian Dec 15 '21 at 06:25
  • @stepanian The security issue reported by `mysql[dump]` comes from the fact that the password will appears in the list of processes running (see `ps -ef | grep mysql`). If you have it in a variable like shown in that statement, it's probably okay. However, if you have the password in clear instead of `$password` then it's not much better (the echo will be really fast, so as a hacker it's harder to get the info in this case, but still possible). – Alexis Wilke Jan 24 '22 at 03:04
10

You can also run the mysql_config_editor in your script to pass in the password when specifying the login path

expect -c "
spawn mysql_config_editor set --login-path=$mySqlUser --host=localhost --user=$mySqlUser --password
expect -nocase \"Enter password:\" {send \"$mySqlPassword\r\"; interact}
"

This starts an expect session which can be used in scripts to interact with prompts

See this post

Community
  • 1
  • 1
phylanx
  • 101
  • 1
  • 3
  • Is that '>' really meant to be there? – David Goodwin Apr 08 '14 at 09:22
  • yes '>' is meant to be there so that mysql_config_editor takes input from stdout. What is being passed from stdout to mysql_config_editor is the password you want this user to have Without the '>' then what happens is that the echo command is parsed and all that you would see is everything after the echo command – phylanx Apr 08 '14 at 16:16
  • So, you mean to use the pipe operator "|", right? At least, in *nix and DOS, ">" will capture the STDOUT and write it to a file called "mysql_config_editor" in the current working directory. – Jay Dansand Apr 09 '14 at 20:03
  • Yes, you are correct, I have edited my original answer – phylanx Apr 09 '14 at 21:45
9

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
David G.
  • 595
  • 1
  • 4
  • 11
  • 4
    Whilst this answer is a little convoluted, I just upvoted it because it shits me so much to see answers that aren't explicitly wrong or are a little unconventional getting downvotes with no comments! No wonder David hasn't answered any other questions! He jumped in and tried to help with a novel solution and has been slammed with no explanation why! FU anonymous downvoters who don't leave comments! – Jeremy Davis Feb 22 '18 at 03:23
  • 3
    +1 agree Jeremy Davis. This is convoluted, but when left with no other option this may be acceptable. It's definitely not wrong, unlike turning off warnings which has to be the stupidest idea ever! – Ben McIntyre Sep 29 '18 at 22:53
  • 1
    @JeremyDavis Yes, this was convoluted, mainly because I wanted to show the work. It could possibly be done without any parentheses, but might have been less clear. This was also my first ever non-reading activity in all of stack exchange... after which I didn't touch it for a long time. Your comment was definitely appreciated. – David G. Jan 09 '20 at 14:37
  • 1
    @DavidG. - Glad my comment was of value to you. Also great to see that you're back and that your answer is now in positive territory. Personally, I don't think that down voting without commenting should be allowed... How is anyone meant to learn when they get slammed for having a go?! – Jeremy Davis Jan 10 '20 at 02:52
  • Having said that, re-looking at your answer, I'm not convinced that a permanent solution (essentially wrapping mysql) to a temporary problem (suppressing an error message for usage in a single script) is the best way to go. IMO wrapping mysql in a function within the script (using your method here would be fine) is a superior approach. My 2c... :) – Jeremy Davis Jan 10 '20 at 02:55
  • Well, I will concede that, if you only want to fix one script, then making a function named mysql is a better solution. But this is a more or less endemic problem that Oracle's hubris foisted on us. (i.e. their belief that database password must always be protected, because the database security is significant in YOUR security model.) – David G. Jan 10 '20 at 05:32
  • Very creative! But I wouldn't call this simple ;) – Philipp Aug 21 '21 at 23:47
  • 1
    Can't this be done with code blocks instead of subshells? – Tripp Kinetics May 11 '23 at 14:39
  • @TrippKinetics As in {} instead of ()? Quite possibly. It has never occurred to me. :-) – David G. May 12 '23 at 14:23
  • @DavidG. Yeah. Exactly. Just so you don't spawn so many extra processes. – Tripp Kinetics May 12 '23 at 16:37
8

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

Joseph Shih
  • 1,244
  • 13
  • 25
5
shell> mysql_config_editor set --login-path=local
     --host=localhost --user=localuser --password
Enter password: enter password "localpass" here
shell> mysql_config_editor set --login-path=remote
     --host=remote.example.com --user=remoteuser --password
Enter password: enter password "remotepass" here

To see what mysql_config_editor wrote to the .mylogin.cnf file, use the print command:

shell> mysql_config_editor print --all
[local]
user = localuser
password = *****
host = localhost
[remote]
user = remoteuser
password = *****
host = remote.example.com

The print command displays each login path as a set of lines beginning with a group header indicating the login path name in square brackets, followed by the option values for the login path. Password values are masked and do not appear as clear text.

As shown by the preceding examples, the .mylogin.cnf file can contain multiple login paths. In this way, mysql_config_editor makes it easy to set up multiple “personalities” for connecting to different MySQL servers. Any of these can be selected by name later using the --login-path option when you invoke a client program. For example, to connect to the local server, use this command:

shell> mysql --login-path=local

To connect to the remote server, use this command:

shell> mysql --login-path=remote
Beta
  • 189
  • 2
  • 3
  • What if I want to execute a mysqldump command as www-data user? www-data has no home directory... how do I setup the mysql_config_editor for www-data user? – lewis4u Feb 29 '20 at 23:12
5

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
Nestor Urquiza
  • 2,821
  • 28
  • 21
4

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

sshpass -p topsecret mysql -u root -p username -e 'statement'
lewiz
  • 117
  • 4
3

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 !

Community
  • 1
  • 1
  • Not bad. I just used one call with a return in, e.g. `docker exec [MYSQL_CONTAINER_NAME] sh -c 'exec echo "[client] [RETURN HERE] password=pa55" > /root/defaults'` Using the `--defaults-file` it already picks up root too. – Matthew Wilcoxson Aug 04 '18 at 20:52
3

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)

volingas
  • 1,023
  • 9
  • 21
1

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
And
  • 97
  • 5
1

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.

tresf
  • 7,103
  • 6
  • 40
  • 101
1

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.

Patrick.SE
  • 4,444
  • 5
  • 34
  • 44
1

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.

ltning
  • 21
  • 2
-1

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.

WiR3D
  • 1,465
  • 20
  • 23
-1

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;"
Neil Davis
  • 238
  • 1
  • 5
-2

Another solution (from a script, for example):

 sed -i'' -e "s/password=.*\$/password=$pass/g" ~/.my.cnf
 mysql -h $host -u $user $db_name -e "$sql_cmd"

The -i'' option is here for compatibility with Mac OS X. Standard UNIX OSes can use straight -i

Benoit Duffez
  • 11,839
  • 12
  • 77
  • 125
  • 2
    password is still on command line of the 'sed' so remains visible in process listings, even if only briefly. – anthony Nov 27 '17 at 00:37
-3

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

Mr. Pyramid
  • 3,855
  • 5
  • 32
  • 56
MMG
  • 9
  • 1
  • 3
    That also means you don't get a report when something else goes wrong! – anthony Nov 27 '17 at 00:35
  • 1
    Also, you probably wanted `2>/dev/null`. Using `2> null` would just put output into a file called "null" in current directory. – thelogix Jul 23 '18 at 17:55
-4

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!

-6

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.

RobC
  • 22,977
  • 20
  • 73
  • 80