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

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"

Answer 2

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.

Answer 3

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

Answer 4

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.

Answer 5

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

Answer 6

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