I’m having some serious issues with xtrabackup running into deadlocks while backing up a slave which is causing some considerable anguish here. Essentially the command I am running to (and subsequently prepare the backup) is:
xtrabackup --user=$USER --password=$PASSWORD --backup --target-dir=$TARGET_DIR --parallel=4 --rsync --lock-ddl-per-table --slave-info --safe-slave-backup --safe-slave-backup-timeout=3600 > $BASE_DIR/last.log 2>&1
xtrabackup --user=$USER --password=$PASSWORD --prepare --target-dir=$TARGET_DIR >> $BASE_DIR/last.log 2>&1
Now if somebody issues a DDL statement (we use a lot unfortunately) during the first stage, the backup will often deadlock at that point to the extent that I have to kill the backup process itself and occasionally the statement causing the deadlock. Thinks like ALTER TABLE SET auto_increment or TRUNCATE TABLE are very common problems it seems (or perhaps, to be fair, they are encountered more often than not).
I’ve tried using --lock-ddl, however our MySQL server does not appear to support the LOCK TABLES FOR BACKUP statement, so my current workaround is to use mysqladmin to stop the slave’s SQL thread prior to the first xtrabackup call, then re-enable it immediately afterwards so that the SQL thread can try and catch up during the preparation stage. Far from ideal I know as my ideal use case is to use this server as a hot slave - but having of lag of ~4 hours while the backup is taken is simply not acceptable.
I’d be interested to know if other people have encountered this issue and what their solutions may be as currently I’m really stuck as to how to proceed.
Versions:
xtrabackup : version 2.4.13 based on MySQL server 5.7.19 Linux (x86_64) (revision id: 3e7ca7c)
mysql : 5.7.26-0ubuntu0.18.04.1-log
operating system : Ubuntu 18.04.2 LTS