--safe-slave-backup with MySQL 8 binlog_format MIXED

MySQL & MariaDB Percona XtraBackup
#1

Hi. Is it true that --safe-slave-backup is specifically only for temporary tables and so not needed in MySQL 8 when using binlog_format of ROW or MIXED?

I’m backing up a MySQL 8 slave/replica using xtrabackup 8.0.26-18 with --safe-slave-backup option. MySQL 8 is configured with binlog_format of MIXED. I’d prefer not to use --safe-slave-backup option because this stops the replication thread for the entire backup. My backup takes more than 1 hour to complete due to the size of the database and so replication falls quite far behind and I’d like to avoid that if possible.

The xtrabackup Option Reference hints that --safe-slave-backup is specifically for temporary tables and so it’s not needed in MySQL 5.7 if binlog_format is ROW.

In MySQL 8 according to MySQL :: MySQL 8.0 Reference Manual :: 17.5.1.31 Replication and Temporary Tables and MySQL :: MySQL 8.0 Reference Manual :: 5.4.4.3 Mixed Binary Logging Format, it seems that binlog_format of ROW or MIXED doesn’t replicate temporary tables and this is why I’m thinking (and hoping) that I don’t need option --safe-slave-backup.

1 Like
#2

Hi @aviddavid , yes you are correct. --safe-slave-backup makes sense only when STATEMENT row replication, it was meant to overcome an issue when the slave thread was in the middle of a temporary table operation at the end of the backup. However, both ROW and MIXED row formats will disable binlog events for temporary tables.

Also remember, always test your backups for the porpouse you have them (replace a master, create a new slave, perform PITR), this is still the best technique to avoid undesired surprises when a restore is needed.

1 Like
#3

Thanks @Marcelo_Altmann - much appreciated.

1 Like