如何从Shell脚本执行MySQL命令? [英] How to execute a MySQL command from a shell script?

问题描述

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

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

我想使用外壳程序脚本恢复在SQL文件中收集的数据.我想连接到服务器并还原数据.通过SSH命令行单独执行时，该命令才能起作用.

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.

这是我使用的命令:

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

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

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

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

正确的方法是什么?

推荐答案

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

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

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

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

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

$ 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'

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

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:

[client]
user = root
password = XXXXXXXX

然后:

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

---

发表您的评论

---

Re your comment:

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

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脚本进行故障排除时，我也使用-x标志，以便查看其如何执行每个命令:

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

$ bash -x myscript.sh

这篇关于如何从Shell脚本执行MySQL命令?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！