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

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.sql and pipes it into mysql.

这是创建文件ds_f投标的shell脚本代码。将sql导入到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?

正确的方法是什么?

11 个解决方案

#1

132

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.

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

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

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:

如果在-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 -x flag so I can see how it's executing each command:

另外，当我对shell脚本进行故障排除时，我使用-x标志，这样我就可以看到它是如何执行每个命令的:

$ bash -x myscript.sh

#2

Use this syntax:

使用这种语法:

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

#3

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';"

#4

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:

如果您使用主机作为localhost，您就不需要提到它。您可以使用:

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.

如果密码内容包含一个!(感叹号)你应该在它前面加一个\(反斜杠)。

#5

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:

这个问题的核心已经被回答了好几次了，我只是想补充一下，在shell脚本和SQL中，backticks (' s)都是有问题的。如果您需要在SQL中使用它们来指定一个表或数据库名称，那么您需要在shell脚本中这样做:

mysql -p=password -u "root" -Bse "CREATE DATABASE \`${1}_database\`;
CREATE USER '$1'@'%' IDENTIFIED BY '$2';
GRANT ALL PRIVILEGES ON `${1}_database`.* TO '$1'@'%' 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中，将会更加安全。

#6

You forgot -p or --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.

注意，manpage也说，在命令行上提供凭证是不安全的。所以按照比尔关于我的建议。

#7

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脚本可能还没有完成。

#8

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

(use full path for sql_script_file if 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

#9

mysql_config_editor set --login-path=storedPasswordKey --host=localhost --user=root --password

How do I execute a command line with a secure password?? use the config editor!!!

如何使用安全密码执行命令行?使用配置编辑器! ! !

As of mysql 5.6.6 you can store the password in a config file and then execute cli commands like this....

的mysql 5.6.6可以将密码存储在一个配置文件,然后执行cli命令这样....

mysql --login-path=storedPasswordKey ....

--login-path replaces variables... host, user AND password. excellent right!

——登录路径替换变量……主机、用户名和密码。优秀的对吧!

#10

#!/bin/sh
#Procedures = update
#Scheduled at : Every 00.05 

v_path=/etc/database_jobs
v_cnt=0

MAILTO="indd@abc.in joanson@abc.in sturt@abc.in"
touch "$v_path/db_db_log.log"

#test
mysql -uusername -ppassword -h111.111.111.111 db_name -e "CALL functionName()" > $v_path/db_db_log.log 2>&1
if [ "$?" -eq 0 ]
  then
   v_cnt=`expr $v_cnt + 1`
  mail -s "db Attendance Update has been run successfully" $MAILTO < $v_path/db_db_log.log
 else
   mail -s "Alert : db Attendance Update has been failed" $MAILTO < $v_path/db_db_log.log
   exit
fi

#11

An important consideration for accessing mysql from a shell script used in cron, is that mysql looks at the logged in user to determine a .my.cnf to load.

从cron中使用的shell脚本访问mysql的一个重要考虑是，mysql查看登录用户来确定一个.my.cnf来加载。

That does not work with cron. It can also get confusing if you are using su/sudo as the logged in user might not be the user you are running as.

这与cron没有关系。如果使用su/sudo作为登录用户可能不是您正在运行的用户，这也会让人感到困惑。

I use something like:

我用类似:

mysql --defaults-extra-file=/path/to/specific/.my.cnf -e 'SELECT something FROM sometable'

Just make sure that user and group ownership and permissions are set appropriately and tightly on the .my.cnf file.

只要确保用户和组的所有权和权限在.my.cnf文件上得到适当的设置。

#1

132