I’m setting up a live MySQL backup procedure using xtrabackup.
The server is ~99% InnoDB, but the final backup phase still executes “FLUSH TABLES WITH READ LOCK”, and keeps that lock for approximately 6 minutes, while slowly copying all the .frm files one by one. During these whole 6 minutes, the backup barely grows a single MiB, maximum.
However it’s still a 6 minute server lock, every time.
Regardless of it being a multiple gigabyte full backup, or a negligible 1MiB incremental backup taken 10 seconds later, where it takes 8 seconds to go through the non-locking, InnoDB phase.
[INDENT]180824 14:52:53 Executing FLUSH NO_WRITE_TO_BINLOG TABLES…
180824 14:52:53 Executing FLUSH TABLES WITH READ LOCK…
180824 14:58:55 Executing UNLOCK TABLES
180824 14:58:55 All tables unlocked
However, using <innobackupex (…) --rsync> the tables stay locked for less than a second. Literally.
[INDENT]180824 13:07:28 Starting prep copy of non-InnoDB tables and files
180824 13:07:28 Executing FLUSH NO_WRITE_TO_BINLOG TABLES…
180824 13:07:28 Executing FLUSH TABLES WITH READ LOCK…
180824 13:07:28 Starting to backup non-InnoDB tables and files
180824 13:07:28 Starting rsync as: rsync -t . --files-from=/tmp/xtrabackup_rsyncfiles_pass2 /home/2018-08-24_13-04-18/
180824 13:07:28 rsync finished successfully.
180824 13:07:28 Executing UNLOCK TABLES
180824 13:07:28 All tables unlocked[/INDENT]
There are no DDL, DCL or DTL queries being run at any times. Just DML and DQL queries.
Pretty much just INSERTs, UPDATEs and SELECTs.
And that’s only on the already backed-up InnoDB databases.
So, given that a 6-minute global lock is completely unreasonable for the server applications, what should be my most correct course of action?
Should I set up this brand new procedure using innobackupex then? Even with the documentation somewhat explicitly warning against it?
Or should I do something else?
Is there any plans to implement --rsync in xtrabackup?
Is there any other way I could work around the long time to copy the frm files? Or some other suggestion?
Thank you for your attention.