使用 MySQL 在后台运行 SQL 查询

Running a SQL query in the background using MySQL

提问by Christopher Yee

Is there a way to have a SQL query run on the servers even after I turn off my computer?

即使在我关闭计算机后，有没有办法在服务器上运行 SQL 查询？

I heard from a coworker that there was and she wanted me to do that. I believe she said it was called a "screen"?

我从一位同事那里听说有，她希望我这样做。我相信她说它被称为“屏幕”？

回答by Dan

I'm going to assume you are ssh-ing to a server and you want to be able to shutdown you local workstation.

我将假设您正在通过 ssh 连接到服务器，并且希望能够关闭本地工作站。

You can use nohupand background processes. Also useful to know the -eoption for the mysql command.

您可以使用nohup后台进程。了解-emysql 命令的选项也很有用。

So something like this:

所以像这样：

nohup mysql <options> -u <user> -p <pass> -e 'Insert Query Here' &

• nohupis for 'no hang up' i.e. don't end the process when I disconnect.

• mysql ...The mysql command you usually run to connect, just add the -e option to run the query without opening a mysql session

• &starts the process in the background

• nohup是为了“不挂断”，即当我断开连接时不要结束该过程。

• mysql ...你平时用来连接的mysql命令，只需要加-e选项就可以运行查询，不用打开mysql会话

• &在后台启动进程

I'm unsure about this BUT: If you don't want to put your password in the command, run the command without the password, type fgthe password prompt should come back, enter your password, ctrl-zwill send the process back to the background, then you can disconnect.

我对此不确定但是：如果您不想在命令中fg输入密码，ctrl-z请在没有密码的情况下运行命令，输入密码提示应该回来，输入您的密码，将进程发送回后台，然后你可以断开连接。

回答by bluebinary

If you find you are experiencing issues using nohupwith mysql– I found an alternative solution inspired by @dan08's answeron this page, as well as @Node's answerabout putting an already running process into the background from another question on SO.

如果您发现您遇到的问题使用nohup与mysql-我发现@ dan08的灵感的替代解决方案回答这个页面上，以及@节点的答案约把一个已经运行的进程到后台，从另一个问题上的SO。

---

---

I had found that when trying the suggestion of using nohup mysql...to 'background' a long-running mysqlcommand, that the remote MySQL server I was attempting to connect to refused the connection. When running the same mysqlcommand without prefixing it with nohup, the connection succeeded and the mysqlcommand worked as expected.

我发现当尝试使用nohup mysql...“后台”一个长时间运行的mysql命令的建议时，我试图连接的远程 MySQL 服务器拒绝了连接。当运行相同的mysql命令而不使用前缀时nohup，连接成功并且mysql命令按预期工作。

As such I looked for alternative methods of achieving the same end-result of being able to run a long-running SQL command (or set of SQL commands) without concerns about the process terminating if I disconnected from the shell session or if a networking issue caused a disconnection from the remote server. This research led to the working solution proposed below:

因此，我寻找替代方法来实现相同的最终结果，即能够运行长时间运行的 SQL 命令（或一组 SQL 命令），而无需担心如果我与 shell 会话断开连接或网络问题，进程会终止导致与远程服务器断开连接。这项研究导致了以下提出的工作解决方案：

1. Run your mysqlcommand as though you would like to run it in the foreground; for example: mysql <options> -h <host> -u <user> -p -e "source /absolute/path/to/commands.sql" <database name> 2>&1 > ./mysql_output.log
2. When asked, input the relevant password at the MySQL command prompt for the specified <user>(this is triggered by the inclusion of the -pflag in the mysqlcommand above).
3. Immediately press Ctrl+Zto suspend the current process, in this case mysql.
4. Run the bgcommand to move the currently suspended process into the background, and resume its processing.
5. Run the jobscommand to list the jobs currently running in the shell session (you may only have one job listed if you don't have any other background processes running in the current session). From this list note the job number for mysql, which will likely be 1if you only have one job running.
6. Last, but not least, run the disown -h %<job number>command, thus disown -h %1(assuming the job number obtained in step #5 above was 1). This last command is very important, as it disassociates the current shell session from our mysqlprocess which we have just moved into the background, so that the mysqlprocess does not get killed when the terminal shell session ends. Without the call to disown, your mysqlprocess that is now running in the background could still get killed if your terminal session ends.

1. 运行你的mysql命令，就像你想在前台运行一样；例如：mysql <options> -h <host> -u <user> -p -e "source /absolute/path/to/commands.sql" <database name> 2>&1 > ./mysql_output.log
2. 当被问到时，在 MySQL 命令提示符处输入相关密码以指定<user>（这是由在上面-p的mysql命令中包含标志触发的）。
3. 立即按Ctrl+Z暂停当前​​进程，在本例中为mysql。
4. 运行bg命令将当前挂起的进程移到后台，并恢复其处理。
5. 运行该jobs命令以列出当前在 shell 会话中运行的作业（如果当前会话中没有任何其他后台进程正在运行，则可能只列出一个作业）。从该列表中记下 的作业编号mysql，1如果您只有一个作业正在运行，则可能是该编号。
6. 最后，但并非最不重要的是，运行disown -h %<job number>命令，因此disown -h %1（假设在上面的第 5 步中获得的作业编号是1）。最后一条命令非常重要，因为它将当前 shell 会话与我们mysql刚刚移入后台的mysql进程分离，以便在终端 shell 会话结束时不会终止该进程。如果没有调用disown，mysql如果您的终端会话结束，您现在在后台运行的进程仍可能被终止。

The above sequence of commands effectively mimics a call to nohup mysql...by allowing you to move a long-running call to mysqlinto the background and not have the process terminated if you close your terminal session or get disconnected from the remote server. If you find that running nohup mysql...works in your circumstances that is great as it is certainly the easier and quicker approach, but at least in our application/server environment, we were unable to use nohup mysql...successfully.

上述命令序列有效地模拟了调用nohup mysql...，允许您将长时间运行的调用移到mysql后台，并且如果您关闭终端会话或与远程服务器断开连接，则进程不会终止。如果您发现运行nohup mysql...在您的环境中很有效，因为它当然是更简单快捷的方法，但至少在我们的应用程序/服务器环境中，我们无法nohup mysql...成功使用。

The above sequence should also give you more control over executing the mysqlcommand as you are initially running the command in the foreground; for instance running mysqlin the foreground will allow you to enter your MySQL connection password securely at the MySQL prompt without having to provide the password as plain-text on the command line (which may be an important security consideration in some hosting/server environments, especially shared hosting where passwords entered on the command line would likely end up in log files such as ~/.bash_history or similar). Additionally, running the command in the foreground, will allow you to see and respond to any connection or other errors before the MySQL command gets moved into the background.

mysql当您最初在前台运行命令时，上述序列还应该让您更好地控制执行命令；例如mysql，在前台运行将允许您在 MySQL 提示符下安全地输入您的 MySQL 连接密码，而无需在命令行上以纯文本形式提供密码（这在某些托管/服务器环境中可能是一个重要的安全考虑因素，尤其是共享主机，其中在命令行上输入的密码可能最终会出现在日志文件中，例如 ~/.bash_history 或类似文件）。此外，在前台运行该命令将允许您在 MySQL 命令移入后台之前查看并响应任何连接或其他错误。

Credit must also go @dan08's answeron this page, and to @Node for his answerregarding placing an already running foreground process into the background, for helping inspire the solution proposed here.

还必须感谢@dan08在此页面上的回答，以及@Node关于将已经运行的前台进程置于后台的回答，以帮助激发此处提出的解决方案。

Hopefully this proposed solution will help others who find themselves in a similar situation, where running nohup mysql...does not work as expected.

希望这个提议的解决方案能帮助其他发现自己处于类似情况的人，在这些情况下，跑步nohup mysql...无法按预期工作。

回答by Rams

nohup mysql -h [host_ip_addr] -u [username] -p[password] [database_name] -e "sql_query" &

Note : don't give space after -p

注意： -p 后不要留空格

回答by Stalinko

I prefer this way:

我更喜欢这种方式：

mysql -u <user> -p<pass> -e 'Insert Query Here' &>> query.log & disown

Example:

例子：

mysql -u root -pmypassword -e 'UPDATE bigtable SET column = "value";' &>> query.log & disown

• no space after -p
• &>> query.logredirects both stdout and stderr into query.log(use your path), otherwise entire output will be translated into terminal
• &runs the command in background
• disowndetaches the command from current terminal, if you close the terminal then command won't die after timeout

• 之后没有空格 -p
• &>> query.log将 stdout 和 stderr 重定向到query.log（使用您的路径），否则整个输出将被转换为终端
• &在后台运行命令
• disown从当前终端分离命令，如果关闭终端，则命令在超时后不会死

You can control the command using

您可以使用控制命令

SHOW FULL PROCESSLIST;

Also you can stop it using

你也可以停止它使用

KILL [id]; -- get the ID from previous command