如果终止长时间运行的更改查询会发生什么？
如果终止长时间运行的更改查询会发生什么？更改查询会简单地恢复吗？这需要多长时间（占已运行时间的比例）？
如果该查询被复制到另一台服务器上怎么办？终止其他服务器上的进程是否会恢复原始服务器的更改查询？
我们正在运行 mysql
What happens if you kill a long-running alter query? Will the alter query simply revert? How long could that take (as a proportion of the time it has already been running)?
What if that query is being replicated onto another server? Will killing the process on the other server revert the original server's alter query?
We're running mysql
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
这取决于你在做什么。如果您在
InnoDB表上运行alter table...add index命令（不太确定MyISAM），那么它将只需运行并运行，因为它首先复制整个该死的表锁库存和桶：如果它处于“复制到临时表”的中间，那么它几乎是不可阻挡的。请参见此处：
It depends what you're doing. If you're running
an alter table...add indexcommand on anInnoDBtable (not so sure aboutMyISAM), then it will just run and run as it copies the whole darn table lock-stock-and-barrel first: if it's in the middle of "copy to temp table" then it's pretty much unstoppable.See here:
ALTER 也将在该服务器上执行，并产生相关影响。
没有。原始服务器没有反向通道来了解从属服务器上发生（或未发生）的情况。如果您终止从属设备上的 ALTER，那么您将面临这样的情况：主设备具有新的约束或索引，而从设备没有。这很少是幸福的秘诀:)
一旦 ALTER 进入复制日志，您要么必须让它到处运行，要么到处杀死它。
The ALTER will be executed on that server as well, with the associated impacts.
Nope. The original server has no back channel to learn about what occurred (or didn't) on the slave. If you kill the ALTER on the slave, then you will wind up in the situation where the master has the new constraint or index, and the slave doesn't. This is rarely a recipe for happiness :)
Once an ALTER enters the replication log, you either have to let it run everywhere, or kill it everywhere.
首先执行以下命令：
接下来，仔细检查进程列表并查找状态为“正在复制到临时表”的任何条目。如果您识别出具有此状态的查询，则表明终止它们的风险较小。在这种情况下，您可以通过执行以下命令安全地终止查询：
将“QUERY_ID”替换为您希望终止的查询的实际 ID。您可以在
SHOW FULL PROCESSLIST命令显示的列表中找到查询的 ID。当您使用 KILL QUERY 命令终止 MySQL 中的查询时，它会指示 MySQL 停止执行指定的查询。
这通常会导致查询状态从“复制到临时表”或“显示完整进程列表”中的其他状态更改为“已终止”。
此外，MySQL 还会启动回滚过程来撤消查询所做的任何更改。回滚的影响可能会根据查询的进度而有所不同。终止查询可以释放服务器资源、解决死锁并提高服务器上其他查询的性能。
但是，明智地使用此命令至关重要，因为它可能会中断正在进行的操作并可能影响数据一致性。
Start by executing the command:
Next, carefully examine the process list and look for any entries with the status
copying to temp table.If you identify queries with this status, it indicates that they are less risky to terminate. In such cases, you can safely terminate the query by executing the following command:Replace 'QUERY_ID' with the actual ID of the query you wish to terminate. You can find the query's ID in the list displayed by the
SHOW FULL PROCESSLISTcommand.When you kill a query in MySQL using the KILL QUERY command, it instructs MySQL to stop the execution of the specified query.
This usually results in a change of the query's state from "copying to temp table" or another state to "killed" in the SHOW FULL PROCESSLIST.
Additionally, MySQL initiates a rollback process to undo any changes made by the query. The impact of the rollback may vary depending on the query's progress. Killing a query can free up server resources, resolve deadlocks, and improve the performance of other queries on the server.
However, it's essential to use this command judiciously, as it can disrupt ongoing operations and potentially affect data consistency.