Troubleshooting MySQL Slave Replication Errors Caused by XtraBackup Kill‑Long‑Query Settings

Problem background

The MySQL slave reported errors like:

Last_Errno:1317
Last_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction 'a838ba08-c009-11e9-85e1-fa163ea2992d:31622919405' at master log master-bin.005599, end_log_pos 2297.

These messages indicate that the replication coordinator stopped due to a worker failure.

Investigation steps

Query performance_schema.replication_applier_status_by_worker to see which transaction was interrupted.

mysql> select * from performance_schema.replication_applier_status_by_worker;

The result showed that a statement was aborted during replay.

Check the MySQL error log, which confirmed that a worker thread was disconnected, causing the query to stop.

After confirming the cause, the next step was to restart replication.

Restart the slave:

mysql> stop slave;
mysql> start slave;

Replication resumed, but the underlying reason for the interruption needed further investigation.

Examine server activity at the failure time and discover that XtraBackup was running with the options --kill-long-queries-timeout=60 and --kill-long-query-type=all.

These options cause XtraBackup to kill any query that runs longer than 60 seconds during a FLUSH TABLES WITH READ LOCK operation, which can interrupt replication.

Review the XtraBackup backup logs, which confirmed that the kill‑long‑query settings were responsible for the replication error.

By removing or adjusting these options (or granting the necessary privileges), the replication issue can be avoided.