如何使用 MySQL 管理服务器端进程 [英] How to manage server-side processes using MySQL

问题描述

我有一个 perl 脚本，它接受唯一的参数(其中一个参数是 --user=username_here).用户可以使用我正在开发的 Web 界面启动这些流程.

I have a perl script which takes in unique parameters (one of the parameters being --user=username_here). Users can start these processes using a web interface I am developing.

一个 MySQL 表，transactions，跟踪运行 perl 脚本的用户

A MySQL table, transactions, keeps track of users that run the perl script

id  user    script_parameters           execute last_modified
23  alex    --user=alex --keywords=thisthat     0   2014-05-06 05:49:01
24  alex    --user=alex --keywords=thisthat     0   2014-05-06 05:49:01
25  alex    --user=alex --keywords=lg       0   2014-05-06 05:49:01
26  alex    --user=alex --keywords=lg       0   2014-04-30 04:31:39

如果进程应该运行，给定行的执行值将为1".如果进程应该结束，它被设置为0".

The execute value for a given row will be "1" if the process should be running. It is set to "0" if the process should be ended.

我的 perl 脚本不断检查这个值以确保它不是0"，如果是，perl 脚本将终止.

My perl script constantly checks this value to make sure it's not "0" and if it is, the perl script terminates.

但是，我需要管理这些过程以防止出现此问题:

However, I need to manage these process to protect against this problem:

1. 如果我的服务器突然崩溃并重新启动，或者脚本崩溃怎么办?我需要在后台运行一些东西，读取 transactions 表并确保它使用适当的参数根据需要多次重新启动 perl 脚本.

1. What if my server abruptly crashes and restarts, OR the script crashes? I will need something running in the background, reading the transactions table and make sure it restarts the perl script as many times as needed using the appropriate parameters.

因此，我无法弄清楚如何平衡将控制权交给用户来管理他/她自己的事务，同时我还要确保应该运行的事务、正在运行的事务以及那些那不是，不是.

And so, I'm having trouble figuring out how to balance giving control to the user to manage his/her own transaction(s), while I also make sure that the transactions that SHOULD be running, ARE running, and those that AREN'T, AREN'T.

希望这是有道理的，我感谢任何帮助！

Hope that makes sense and I appreciate any help!

推荐答案

您似乎正在尝试从 Web 服务器启动长时间运行的进程，然后在数据库中跟踪这些进程.这并非不可能，但不是推荐的做法.

It seems you're trying to launch long-running processes from a web server and then track those processes in a database. That's not impossible, but not a recommended practice.

主要问题是当前需要在您的 Web 服务器中处理 HTTP 请求，因为您实际上做任何事情(包括跟踪系统上运行的进程)——您需要一些可以一直运行...

The main problem is that an HTTP request needs to be currently being handled in your web server for you do actually do anything (including track processes running on the system) -- you need something that can run all the time...

相反，更好的主意是拥有另一个守护进程的管理器"进程(正如您提到的 perl，这是一种编写它的好语言) spawn &跟踪长时间运行的任务(通过 PID 和信号)，并为该进程更新 SQL 数据库.

Instead, a better idea would be to have another daemonized "manager" process (as you mention perl, that'd be a good language to write it in) spawn & track the long running tasks (by PID and signals), and for that process to update your SQL database.

然后，您可以让您的管理器"进程侦听从您的 Web 服务器启动新进程的请求.您可以使用多种 IPC 机制.(例如:信号、SysV shm、unix 域套接字、进程内队列，如 ZeroMQ 等).

You can then have your "manager" process listen for requests to start a new process from your web server. There are various IPC mechanisms you could use. (e.g: signals, SysV shm, unix domain sockets, in-process queues like ZeroMQ, etc).

这有多种好处:

• 如果您生成的脚本需要在基于用户/组的隔离下运行(与系统或彼此隔离)，那么您的网络服务器不需要以 root 用户身份运行，也不需要 setgid.
• 如果生成的进程崩溃"，则会向管理器"进程发送信号，因此它可以毫无问题地跟踪错误执行.
• 如果您使用进程内队列(例如:ZeroMQ)将请求传递给管理器"进程，它可以限制"来自 Web 服务器的请求(这样用户就不会有意或无意地导致 DOS).
• 无论生成的进程是否顺利结束，您都不需要向网络服务器发送活动"HTTP 请求来更新您的跟踪数据库.

至于应该运行的东西是否正在运行，这完全取决于您的语义.(即:它是否基于已知的运行时间?基于消耗的数据?等).

As to whether something that should be running is running, that's really up to your semantics. (i.e: is it based on a known run time? based on data consumed? etc).

检查它是否正在运行可以分为两方面:

The check as to whether it is running can be two-fold:

1. 管理器"进程会根据需要更新数据库，包括生成的 PID.
2. 您的 Web 服务器托管代码实际上可以列出进程，以确定数据库中的 PID 是否实际上正在运行，甚至它已经做了多少有用的事情！

1. The "manager" process updates the database as appropriate, including the spawned PID.
2. Your web server hosted code can actually list processes to determine if the PID in the database is actually running, and even how much time it's been doing something useful!

检查它是否未运行必须基于约定:

The check for whether it is not running would have to be based on convention:

1. 为产生的进程命名一些您可以预测的名称.
2. 获取进程列表以确定哪些不应该继续运行(已失效?).

在任何一种情况下，您都可以通知请求生成进程的用户和/或实际做一些事情.

In either case, you could either inform the users who requested the processes be spawned and/or actually do something about it.

一种方法可能是让 CRON 作业从 SQL 数据库中读取并执行 ps 以确定哪些产生的进程需要重新启动，然后重新请求管理器"进程执行所以使用与 Web 服务器相同的 IPC 机制.如何区分跟踪/监控/记录中的启动与重启取决于您.

One approach might be to have a CRON job which reads from the SQL database and does ps to determine which spawned processes need to be restarted, and then re-requests that the "manager" process does so using the same IPC mechanism used by the web server. How you differentiate starts vs. restarts in your tracking/monitoring/logging is up to you.

如果服务器本身断电或崩溃，那么您可以让管理器"进程在首次运行时执行清理，例如:

If the server itself loses power or crashes, then you could have the "manager" process perform cleanup when it first runs, e.g:

1. 在数据库中查找据称在服务器关闭之前运行的衍生进程的条目.
2. 通过 PID 和运行时间检查这些进程(这很重要).
3. 要么重新生成未完成的生成进程，要么在数据库中存储一些内容以向 Web 服务器表明情况确实如此.

1. Look for entries in the database for spawned processes that were alegedly running before the server was shut down.
2. Check for those processes by PID and run time (this is important).
3. Either re-spawn the spawned proceses that didn't complete, or store something in the database to indicate to the web server that this was the case.

<小时>

更新 #1

根据您的评论，以下是一些入门指南:

Per your comment, here are some pointers to get started:

你提到了 perl，所以假设你对它有一定的了解——这里有一些 perl 模块可以帮助你编写管理器"流程脚本:

You mentioned perl, so presuming you have some proficiency there -- here are some perl modules to help you on your way to writing the "manager" process script:

如果您还不熟悉 CPAN 是 perl 模块的存储库，它基本上可以做任何事情.

If you're not already familiar with it CPAN is the repository for perl modules that do basically anything.

Daemon::Daemonize -守护进程，使其在您注销后继续运行.还提供了编写脚本来启动/停止/重启守护进程的方法.

Daemon::Daemonize - To daemonize process so that it will continue running after you log out. Also provides methods for writing scripts to start/stop/restart the daemon.

Proc::Spawn - 帮助生成"' 子脚本.基本上是 fork() 然后是 exec()，但也处理子进程的 STDIN/STDOUT/STDERR(甚至 tty).您可以使用它来启动长时间运行的 perl 脚本.

Proc::Spawn - Helps with 'spawning' child scripts. Basically does fork() then exec(), but also handles STDIN/STDOUT/STDERR (or even tty) of child process. You could use this to launch your long-running perl scripts.

如果您的 Web 服务器前端代码还不是用 perl 编写的，那么您将需要一些可移植的东西，用于进程间消息传递和排队；我可能会让您的 Web 服务器前端采用易于部署的方式(例如 PHP).

If your web server front-end code is not already written in perl, you'll need something that's pretty portable for inter-process message-passing and queuing; I'd probably make your web server front end in something easy to deploy (like PHP).

这里有两种可能性(还有更多):

Here are two possibilities (there are many more):

• Perl 和 PHP 实现 Spread Toolkit.
• Perl 和 PHP 实现 ZeroMQ 库.

• Perl and PHP implementations for the Spread Toolkit.
• Perl and PHP implementations for the ZeroMQ library.

Proc::ProcessTable - 你可以使用这个检查正在运行的进程(并获取如上所述的各种统计信息).

Proc::ProcessTable - You can use this check on running processes (and get all sorts of stats as discussed above).

Time::HiRes - 使用高-这个包中的粒度时间函数来实现你的节流"框架.基本上只是限制每单位时间出队的请求数量.

Time::HiRes - Use the high-granularity time functions from this package to implement your 'throttling' framework. Basically just limit the number of requests you de-queue per unit of time.

DBI(带有 mysql) - 从管理器"进程更新您的 MySQL 数据库.

DBI (with mysql) - Update your MySQL database from the "manager" process.

这篇关于如何使用 MySQL 管理服务器端进程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！