如何从 shell 脚本执行 MySQL 命令？

How to execute a MySQL command from a shell script?

提问by MUFC

How can I execute an SQL command through a shell script so that I can make it automated?

如何通过 shell 脚本执行 SQL 命令以使其自动化？

I want to restore data I have collected in a SQL file using a shell script. I want to connect to a server and restore data. The command works when executed separately via SSH command line.

我想使用 shell 脚本恢复我在 SQL 文件中收集的数据。我想连接到服务器并恢复数据。该命令在通过 SSH 命令行单独执行时有效。

This is the command I use:

这是我使用的命令：

mysql -h "server-name" -u root "password" "database-name" < "filename.sql"

This is the shell script code that creates the file ds_fbids.sqland pipes it into mysql.

这是创建文件ds_fbids.sql并将其通过管道传输到 mysql的 shell 脚本代码。

perl fb_apps_frm_fb.pl
perl fb_new_spider.pl ds_fbids.txt ds_fbids.sql
mysql -h dbservername -u username -ppassword dbname < ds_fbids.sql

What is the correct way to do this?

这样做的正确方法是什么？

回答by Bill Karwin

You need to use the -pflag to send a password. And it's tricky because you must have no space between -pand the password.

您需要使用该-p标志来发送密码。这很棘手，因为您必须在-p和 密码之间没有空格。

$ mysql -h "server-name" -u "root" "-pXXXXXXXX" "database-name" < "filename.sql"

If you use a space after -pit makes the mysql client prompt you interactively for the password, and then it interprets the next command argument as a database-name:

如果在-p它使 mysql 客户端以交互方式提示您输入密码之后使用空格，然后它将下一个命令参数解释为数据库名称：

$ mysql -h "server-name" -u "root" -p "XXXXXXXX" "database-name" < "filename.sql"
Enter password: <you type it in here>
ERROR 1049 (42000): Unknown database 'XXXXXXXX'

Actually, I prefer to store the user and password in ~/.my.cnf so I don't have to put it on the command-line at all:

实际上，我更喜欢将用户名和密码存储在 ~/.my.cnf 中，因此我根本不必将其放在命令行中：

[client]
user = root
password = XXXXXXXX

Then:

然后：

$ mysql -h "server-name" "database-name" < "filename.sql"

---

---

Re your comment:

回复您的评论：

I run batch-mode mysql commands like the above on the command line and in shell scripts all the time. It's hard to diagnose what's wrong with your shell script, because you haven't shared the exact script or any error output. I suggest you edit your original question above and provide examples of what goes wrong.

我一直在命令行和 shell 脚本中运行像上面这样的批处理模式 mysql 命令。很难诊断您的 shell 脚本出了什么问题，因为您没有共享确切的脚本或任何错误输出。我建议您编辑上面的原始问题并提供错误示例。

Also when I'm troubleshooting a shell script I use the -xflag so I can see how it's executing each command:

此外，当我对 shell 脚本进行故障排除时，我会使用该-x标志，以便我可以看到它是如何执行每个命令的：

$ bash -x myscript.sh

回答by Kshitij Sood

Use this syntax:

使用以下语法：

mysql -u $user -p$passsword -Bse "command1;command2;....;commandn"

回答by Jericon

All of the previous answers are great. If it is a simple, one line sql command you wish to run, you could also use the -e option.

以前的所有答案都很棒。如果它是您希望运行的简单的单行 sql 命令，您还可以使用 -e 选项。

mysql -h <host> -u<user> -p<password> database -e \
  "SELECT * FROM blah WHERE foo='bar';"

回答by Milinda Bandara

How to execute an SQL script, use this syntax:

如何执行 SQL 脚本，请使用以下语法：

mysql --host= localhost --user=root --password=xxxxxx  -e "source dbscript.sql"

If you use host as localhost you don't need to mention it. You can use this:

如果您使用主机作为本地主机，则无需提及。你可以使用这个：

mysql --user=root --password=xxxxxx  -e "source dbscript.sql"

This should work for Windows and Linux.

这应该适用于 Windows 和 Linux。

If the password content contains a !(Exclamation mark) you should add a \(backslash) in front of it.

如果密码内容包含!（感叹号），则应\在其前面添加（反斜杠）。

回答by Li1t

The core of the question has been answered several times already, I just thought I'd add that backticks (`s) have beaning in both shell scripting and SQL. If you need to use them in SQL for specifying a table or database name you'll need to escape them in the shell script like so:

问题的核心已经多次回答，我只是想补充一点，反引号 (`s) 在 shell 脚本和 SQL 中都有 bean。如果您需要在 SQL 中使用它们来指定表或数据库名称，则需要在 shell 脚本中对它们进行转义，如下所示：

mysql -p=password -u "root" -Bse "CREATE DATABASE \`_database\`;
CREATE USER ''@'%' IDENTIFIED BY '';
GRANT ALL PRIVILEGES ON `_database`.* TO ''@'%' WITH GRANT OPTION;"

Of course, generating SQL through concatenated user input (passed arguments) shouldn't be done unless you trust the user input.It'd be a lot more secure to put it in another scripting language with support for parameters / correctly escaping strings for insertion into MySQL.

当然，除非您信任用户输入，否则不应通过连接的用户输入（传递的参数）生成 SQL。将其放入另一种支持参数/正确转义插入字符串的脚本语言中会更安全进入 MySQL。

回答by vine

mysql -h "hostname" -u usr_name -pPASSWD "db_name" < sql_script_file

(use full path for sql_script_fileif needed)

（sql_script_file如果需要，请使用完整路径）

If you want to redirect the out put to a file

如果要将输出重定向到文件

mysql -h "hostname" -u usr_name -pPASSWD "db_name" < sql_script_file > out_file

回答by PointedEars

You forgot -por --password=(the latter is better readable):

您忘记了-p或--password=（后者可读性更好）：

mysql -h "$server_name" "--user=$user" "--password=$password" "--database=$database_name" < "filename.sql"

(The quotes are unnecessary if you are sure that your credentials/names do not contain space or shell-special characters.)

（如果您确定您的凭据/名称不包含空格或 shell 特殊字符，则不需要引号。）

Note that the manpage, too, says that providing the credentials on the command line is insecure. So follow Bill's advice about my.cnf.

请注意，联机帮助页也说在命令行上提供凭据是不安全的。因此，请遵循 Bill 关于 my.cnf 的建议。

回答by Sterling Hamilton

As stated before you can use -p to pass the password to the server.

如前所述，您可以使用 -p 将密码传递给服务器。

But I recommend this:

但我推荐这个：

mysql -h "hostaddress" -u "username" -p "database-name" < "sqlfile.sql"

Notice the password is not there. It would then prompt your for the password. I would THEN type it in. So that your password doesn't get logged into the servers command line history.

注意密码不存在。然后它会提示您输入密码。我会然后输入它。这样您的密码就不会登录到服务器命令行历史记录中。

This is a basic security measure.

这是一项基本的安全措施。

If security is not a concern, I would just temporarily remove the password from the database user. Then after the import - re-add it.

如果安全性不是问题，我只会暂时从数据库用户中删除密码。然后在导入后 - 重新添加它。

This way any other accounts you may have that share the same password would not be compromised.

这样，您可能拥有的任何其他共享相同密码的帐户都不会受到损害。

It also appears that in your shell script you are not waiting/checking to see if the file you are trying to import actually exists. The perl script may not be finished yet.

在您的 shell 脚本中，您似乎也没有等待/检查您尝试导入的文件是否确实存在。perl 脚本可能还没有完成。

回答by senninha

Use

用

echo "your sql script;" | mysql -u -p -h db_name

回答by user7412956

To "automate" the process of importing the generated .sqlfile, while avoiding all the traps that can be hidden in trying to pass files through stdinand stdout, just tell MySQL to execute the generated .sqlfile using the SOURCEcommand in MySQL.

要“自动化”导入生成.sql文件的过程，同时避免尝试通过stdin和传递文件时可能隐藏的所有陷阱stdout，只需告诉 MySQL.sql使用SOURCEMySQL 中的命令执行生成的文件。

The syntax in the short, but excellent, answer, from Kshitij Sood, gives the best starting point. In short, modify the OP's command according to Kshitij Sood's syntax and replace the commands in that with the SOURCEcommand:

来自Kshitij Sood的简短但出色的答案中的语法提供了最佳起点。简而言之，根据 Kshitij Sood 的语法修改 OP 的命令，并将其中的命令替换为以下命令：SOURCE

#!/bin/bash
mysql -u$user -p$password $dbname -Bse "SOURCE ds_fbids.sql
SOURCE ds_fbidx.sql"

If the database name is included in the generated .sqlfile, it can be dropped from the command.

如果数据库名称包含在生成的.sql文件中，则可以从命令中删除它。

The presumption here is that the generated file is validas an .sqlfile on its own. By not having the file redirected, piped, or in any other manner handled by the shell, there is no issue with needing to escape any of the characters in the generated output because of the shell. The rules with respect to what needs to be escaped in an .sqlfile, of course, still apply.

这里的假设是生成的文件作为文件本身是有效.sql的。通过不让 shell 重定向、管道传输或以任何其他方式处理文件，由于 shell 需要对生成的输出中的任何字符进行转义都没有问题。.sql当然，关于需要在文件中转义的内容的规则仍然适用。

How to deal with the security issues around the password on the command line, or in a my.cnffile, etc., has been well addressed in other answers, with some excellent suggestions. My favorite answer, from Danny, covers that, including how to handle the issue when dealing with cronjobs, or anything else.

如何在命令行或my.cnf文件等中处理围绕密码的安全问题，已在其他答案中得到很好的解决，并提供了一些很好的建议。我最喜欢的答案来自Danny，涵盖了这一点，包括在处理cron工作或其他任何事情时如何处理问题。

---

---

To address a comment(question?) on the short answer I mentioned: No, it cannot be used with a HEREDOC syntax, as that shell command is given. HEREDOC can be used in the redirectionversion syntax, (without the -Bseoption), since I/O redirection is what HEREDOC is built around. If you need the functionality of HEREDOC, it would be better to use it in the creation of a .sqlfile, even if it's a temporary one, and use that file as the "command" to execute with the MySQL batch line.

为了解决我提到的简短回答的评论（问题？）：不，它不能与 HEREDOC 语法一起使用，因为给出了该 shell 命令。HEREDOC 可用于重定向版本语法（不带-Bse选项），因为 I/O 重定向是围绕 HEREDOC 构建的。如果您需要 HEREDOC 的功能，最好在创建.sql文件时使用它，即使它是一个临时文件，并将该文件用作“命令”以使用 MySQL 批处理行执行。

#!/bin/bash
cat >temp.sql <<SQL_STATEMENTS
...
SELECT \`column_name\` FROM \`table_name\` WHERE \`column_name\`='$shell_variable';
...
SQL_STATEMENTS
mysql -u $user -p$password $db_name -Be "SOURCE temp.sql"
rm -f temp.sql

Bear in mind that because of shell expansion you can use shell and environment variables within the HEREDOC. The down-side is that you must escape each and everybacktick. MySQL uses them as the delimiters for identifiers but the shell, which gets the string first, uses them as executable command delimiters. Miss the escape on a single backtick of the MySQL commands, and the whole thing explodes with errors. The whole issue can be solved by using a quoted LimitString for the HEREDOC:

请记住，由于 shell 扩展，您可以在 HEREDOC 中使用 shell 和环境变量。不利的一面是你必须逃避每一个反引号。MySQL 将它们用作标识符的分隔符，但首先获取字符串的 shell 将它们用作可执行命令分隔符。错过 MySQL 命令的单个反引号的转义，整个事情会因错误而爆炸。整个问题可以通过对 HEREDOC 使用带引号的 LimitString 来解决：

#!/bin/bash
cat >temp.sql <<'SQL_STATEMENTS'
...
SELECT `column_name` FROM `table_name` WHERE `column_name`='constant_value';
...
SQL_STATEMENTS
mysql -u $user -p$password $db_name -Be "SOURCE temp.sql"
rm -f temp.sql

Removing shell expansion that way eliminates the need to escape the backticks, and other shell-special characters. It also removes the ability to use shell and environment variables within it. That pretty much removes the benefits of using a HEREDOC inside the shell script to begin with.

以这种方式删除 shell 扩展消除了对反引号和其他 shell 特殊字符进行转义的需要。它还删除了在其中使用 shell 和环境变量的能力。这几乎消除了在 shell 脚本中使用 HEREDOC 的好处。

The other option is to use the multi-line quoted strings allowed in Bash with the batch syntax version (with the -Bse). I don't know other shells, so I cannot say if they work therein as well. You would need to use this for executing more than one .sqlfile with the SOURCEcommand anyway, since that is notterminated by a ;as other MySQL commands are, and only one is allowed per line. The multi-line string can be either single or double quoted, with the normal effects on shell expansion. It also has the same caveats as using the HEREDOC syntax does for backticks, etc.

另一种选择是将 Bash 中允许的多行引用字符串与批处理语法版本（带有-Bse）一起使用。我不知道其他 shell，所以我不能说它们是否也能在其中工作。你可能会需要使用这个执行多个.sql与文件SOURCE反正命令，因为这是不以终止;为其他MySQL命令，只有每行一个是允许的。多行字符串可以是单引号或双引号，对 shell 扩展具有正常影响。它也有与使用 HEREDOC 语法对反引号等相同的警告。

A potentially better solution would be to use a scripting language, Perl, Python, etc., to create the .sqlfile, as the OP did, and SOURCEthat file using the simple command syntax at the top. The scripting languages are much better at string manipulation than the shell is, and most have in-built procedures to handle the quoting and escaping needed when dealing with MySQL.

一个可能更好的解决方案是使用脚本语言、Perl、Python 等来创建.sql文件，就像 OP 所做的那样，并SOURCE使用顶部的简单命令语法创建该文件。脚本语言在字符串操作方面比 shell 好得多，并且大多数都有内置程序来处理处理 MySQL 时所需的引用和转义。