Is Xtrabackup's --safe-slave-backup still relevant?
Like a lot of people, we’re going through MySQL 8 upgrades and finding the sorts of things that change from major version to major version. One of these things that changed behavior in MySQL 8 is in Xtrabackup.
Specifically, and for time immemorial, we have used the
--safe-slave-backup in Xtrabackup1. We take Xtrabackups from our replicas, so it sounds like a setting everyone can agree with, right?
- If you use Xtrabackup with
--safe-slave-backupon a MySQL 8 replica, it will stop replication for the entire backup when it did not do so on 5.7
- If you don’t know what
--safe-slave-backupreally does and you use it, you should check it out. Hint: You probably don’t need it.
Differences between 5.7 and 8.0 behavior
Take a close look at the differences in the Xtrabackup documentation between 8.0 and 2.3 (the Xtrabackup version that supported MySQL 5.7):
As one might expect from an option called
--safe-slave-backup1, both pages state:
However, looking closely at the differences, Xtrabackup 8.0 doc states:
We did not notice this behavior change until we started getting replication stopped alerts on our backup replicas.
If you read the manual, it turns out this option is specifically for temporary tables.
Which temporary tables?
First, we need to distinguish between internal temporary tables and (what I like to call) explicit temporary tables, or those created explicity by clients with the
CREATE TEMPORARY TABLES statement.
Why do we care about explicit temporary tables and replication?
Why do we care about explicit temporary tables on replicas? It’s complicated, so I’ll let the 5.7 MySQL manual explain: https://dev.mysql.com/doc/refman/5.7/en/replication-features-temptables.html. Got all that?
STATEMENT based replication
Why is Xtrabackup 8 stopping replication for the entire backup?
Ok, but why the change iin Xtrabackup 8? I’m actually not sure. At first I thought it was because explicit temporary tables were still defaulting MyISAM in 5.7, but that isn’t true:
though you can still tell it to use MyISAM:
That behavior hasn’t changed on 8.0. So is the
--safe-slave-backup even working as intended on 5.7? I’m not sure.
Estoeric use cases and binlog formats
But is this problem even still relevant?
Raise your hands if you use
CREATE TEMPORARY TABLES. Now raise your hand if you knew explicit temporary tables even exist. I bet the majority of people writing queries for MySQL are not aware of them, much less use them.
Raise your hands if you still use
MIXED replication. I’m sure some do, but the default has been
ROW for a very long time. Now raise your hands if you have application code that explicitly sets the
SESSION.binlog_format. I’m sure it’s out there, but it can’t be very common.
This is not a fool-proof argument. Some client somewhere can still explicitly set the session
binlog_format=STATEMENT and use temporary tables, right?
Reading the upgrading to MySQL 8 page:
This is helpful to close the gap, but it only seems to do so partially:
That’s great, but the opposite is not true:
I’d honestly just rather have a server variable that totally disallows changing session
All that said, clients can still do bad things. Are there clients out there that are doing BOTH:
- setting session
- using an explicit temporary table to stage data loading into permanent tables?
I’m sure there is, but how likely is it and it is worth Xtrabackup stopping your replica to take the backup?
I don’t fully understand why replication is stopped for the entire backup in
--safe-slave-backup in in Xtrabackup 8 and not in 2.3, but I’m not sure I need to know, I’m turning it off.
It’d be nice if the argument was better named (maybe:
--safe-explicit-replicated-temp-tables) because I expect a lot of people setting up Xtrabackup are using it just based on the name alone and without any real understanding of what it is protecting against.
The gap that this solves is esoteric and an increasingly improbable case. Is it possible by not using it I’ll get some inconsistent backups, but stopping replication is just not an acceptable tradeoff at this point, at least for my purposes.
Interestingly, the Xtrabackup manual, in another spot, does call out that this setting is not needed for