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

2021-11-20 00:00:00 shell ssh mysql

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

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

我想使用 shell 脚本恢复我在 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

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

What is the correct way to do this?

推荐答案

您需要使用 -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

相关文章