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

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

User · Answer

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 !

User · Answer

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  lt  database dump sql   to run a statement   drupal-mysql -e  EXEC SOMESTATEMENT

User · Answer

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

User · Answer

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    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  Change your bash script from this   mysqldump -u db user -pInsecurePassword my database   gzip  gt  db backup tar gz   to this   mysqldump --login-path local my database   gzip  gt  db backup tar gz    No more exposed passwords

User · Answer

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

User · Answer

It worked for me- Just added 2 gt  null after the   mysql command   and it will suppress the Errors and Warning messages only

User · Answer

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    amp  gt   dev null    Or you can run SQL-script as a file mysql -u   USERNAME  -p  PASSWORD  -h   HOST    DATABASE   lt    FILEPATH   amp  gt   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

User · Answer

You can also just redirect the standard error STDERR output to  dev null  So just do   mysql -u  user -p password -e  statement  2 gt   dev null

User · Answer

One method that is convenient  but equally insecure  is to use  MYSQL PWD xxxxxxxx mysql -u root -e  quot statement quot    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

User · Answer

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    quot whatever quot  password    quot whatever quot  host    quot whatever quot   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

User · Answer

shell gt  mysql config editor set --login-path local      --host localhost --user localuser --password Enter password  enter password  localpass  here shell gt  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 gt  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 gt  mysql --login-path local   To connect to the remote server  use this command   shell gt  mysql --login-path remote

User · Answer

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 gt  amp 1   remove-warning   Tachaan  Your code is clean and nice to read   tested with bash

User · Answer

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

User · Answer

Easiest way is    mysql -u root -pMYPASSWORD -e  show databases  2 gt  dev null

User · Answer

it s very simple  this is work for me  export MYSQL PWD password  mysql --user username -e  quot statement quot   MYSQL PWD is one of environment variables from mysql  it s default password when connecting to mysqld

User · Answer

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

User · Answer

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

User · Answer

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    gt   credentialsFile echo  user  mysqlUser   gt  gt   credentialsFile echo  password  mysqlPassword   gt  gt   credentialsFile echo  host  mysqlHost   gt  gt   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

User · Answer

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

User · Answer

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 gt  amp 9    2 gt  amp 1     fgrep -v  mysql   Warning  Using a password on the command line interface can be insecure     1 gt  amp 2    9 gt  amp 1

User · Answer

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 gt  amp 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

User · Answer

Another alternative is to use sshpass to invoke mysql  e g    sshpass -p topsecret mysql -u root -p username -e  statement

User · Answer

Personally  I use script wrapper to catch that error  Here is code sample      bin bash   echo      cat  gt  gt   home mysqldump log 2 gt  dev null ERR FILE  tmp tmp mdump err    Execute dumper  usr bin mysqldump    2 gt  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  gt  amp 2                 exit 1         fi fi

User · Answer

ok  solution without temporary files or anything   mysql --defaults-extra-file  lt  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   lt  echo       notice there is no space in the middle of  lt

User · Answer

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     amp mysql --user foo --password bar 2 gt  amp 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

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

Examples related to mysql

Examples related to bash

Examples related to shell