在终端内使用 mysql 禁止警告消息，但密码是用 bash 脚本编写的

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

提问by Blaszard

When I tried running the following command on MySQL from within Terminal:

当我尝试从终端内在 MySQL 上运行以下命令时：

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:

但是，我必须使用$password存储密码的环境变量 ( )执行上述语句，因为我想在终端内的 bash 脚本中迭代运行该命令，而且我绝对不喜欢等待提示出现的想法并强迫我在单个脚本中输入密码 50 或 100 次。所以这是我的问题：

• 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?

• 抑制警告是否可行？正如我所说，该命令可以正常工作，但是当我循环并运行该命令 50 或 100 次时，窗口变得非常混乱。

• 我应该遵守警告信息并且不要在我的脚本中写下我的密码吗？如果是这种情况，那么每次提示强迫我输入密码时我都必须输入密码吗？

Running man mysqldoesn't help, saying only

跑步man mysql无济于事，只能说

--show-warnings
Cause warnings to be shown after each statement if there are any. This option applies to interactive and batch mode.

--show-warnings
导致在每个语句之后显示警告（如果有的话）。此选项适用于交互和批处理模式。

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.

我在 OS X 10.9.1 Mavericks 上使用自制软件的 MySQL 5.6。

采纳答案by Cristian Porta

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

如果您的 MySQL 客户端/服务器版本是 5.6.xa 避免警告消息的方法，请使用mysql_config_editor工具：

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

Then you can use in your shell script:

然后你可以在你的shell脚本中使用：

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

Instead of:

代替：

mysql -u username -p pass -e "statement"

回答by David Goodwin

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:

其中 config.cnf 包含：

[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).

这允许您拥有多个配置文件 - 用于不同的服务器/角色/数据库。使用 ~/.my.cnf 将只允许您拥有一组配置（尽管它可能是一组有用的默认值）。

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

如果您使用的是基于 Debian 的发行版，并以 root 身份运行，则可以跳过上述步骤，只需使用 /etc/mysql/debian.cnf 即可进入...：

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

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

回答by Ivan Dokov

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):

请注意，官方文档不建议这样做。
参见6.1.2.1 End-User Guidelines for Password Security（Mysql Manual for Version 5.6）：

Storing your password in the MYSQL_PWDenvironment variable

This method of specifying your MySQL password must be considered extremely insecureand should not be used. Some versions of psinclude 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.

将密码存储在MYSQL_PWD环境变量中

必须认为这种指定 MySQL 密码的方法非常不安全，不应使用。某些版本的ps包括一个选项来显示正在运行的进程的环境。在某些系统上，如果您设置了MYSQL_PWD，则您的密码会暴露给运行ps 的任何其他用户。即使在没有ps版本的系统上，假设没有其他方法可以让用户检查进程环境也是不明智的。

回答by johnmontfx

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

它基本上是将标准错误重定向到标准输出——并使用 grep 删除所有与“警告：使用密码”匹配的行。

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

这样，您可以看到任何其他输出，包括错误。我将它用于各种 shell 脚本等。

回答by Buttle Butkus

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.

下面是我如何让我的 bash 脚本更安全地运行我的日常 mysqldump 数据库备份。这是 Cristian Porta 出色答案的扩展。

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
   

1. 首先使用mysql_config_editor（mysql 5.6+自带）设置加密密码文件。假设您的用户名是“db_user”。从 shell 提示符运行：

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

   它会提示输入密码。输入后，用户/通行证将加密保存在您的home/system_username/.mylogin.cnf

   当然，将“system_username”更改为您在服务器上的用户名。

2. 从此更改您的 bash 脚本：

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

   对此：

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

No more exposed passwords.

不再暴露密码。

回答by phylanx

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

也可以在脚本中运行mysql_config_editor，在指定登录路径时传入密码

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

看到这个帖子

回答by JavaGuy

Easiest way is

最简单的方法是

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

回答by santiago arizti

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

它与其他人提到的类似，但在这里您不需要实际文件，这部分命令伪造文件：（<(echo ...)注意中间没有空格<(

回答by Beta

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:

要查看 mysql_config_editor 写入 .mylogin.cnf 文件的内容，请使用打印命令：

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:

如前面的示例所示，.mylogin.cnf 文件可以包含多个登录路径。通过这种方式，mysql_config_editor 可以轻松设置多个“个性”以连接到不同的 MySQL 服务器。稍后可以在调用客户端程序时使用 --login-path 选项按名称选择其中任何一个。例如，要连接到本地服务器，请使用以下命令：

shell> mysql --login-path=local

To connect to the remote server, use this command:

要连接到远程服务器，请使用以下命令：

shell> mysql --login-path=remote

回答by Nestor Urquiza

From https://gist.github.com/nestoru/4f684f206c399894952d

来自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