1

Currently I'm studying Percona's xtrabackup. In the manual in the paragraph "Taking Backups in Replication Environments" it says that using the --safe-slave-backup option is always recommended and I understand the reasons behind it.

I'm just wondering now, if there is actually a difference in the result when I do not use this option. After applying the logs on a backup, I don't see why there should be a difference between a backup taken with or without this option.

I'm asking, because in our production environment we do not use this option. The backup runs at midnight, but it failed this night. The backup is taken on a slave and I'd have a bad feeling about stopping the SQL thread now to take the backup.

Improve this question

1 Answer 1

Reset to default
1

As the manual says:

this option stops the slave SQL thread and wait to start backing up until Slave_open_temp_tables in SHOW STATUS is zero. [...] the SQL thread will be started and stopped until there are no open temporary tables

The reason for this is because Percona Xtrabackup basically mimics a controlled crash/shutdown of the server, and temporary tables can make the slave inconsistent, as you can see on the MySQL manual. This is not a problem of consistency per se (buckups will be consistent with the given timestamp/binlog), but it may make a slave lose some transactions when resynchronized with a master (typical usage- cloning a slave for creating another).

This does not happen if you use ROW-based replication, so I would recommend you using that. But some people cannot or do not want to use it, so this is the way to be 100% sure that new slaves work well. On a typical replication scenario, using --safe-slave-backup may not be very problematic, assuming there are not many temporary tables being created, but that is the workaround (and typically these options are added because someone had a problem in the past).

What I would recommend you is to use always --slave-info unless you are using GTID replication.

As the own manual says, using pt-table-checksum to test the backups is a good piece of advise.

Improve this answer
8
  • Sorry for unaccepting, I think I haven't understood it right at first and would like to clarify a bit more first. Doesn't this mean, that it's only a problem, when I take a backup when I carelessly just issued a stop slave before? But when I just let replication run during backup, there shouldn't be a problem at all, right? Then my backup is consistent?
    – user68293
    Sep 16, 2014 at 9:36
  • @user68293 No, if you are running replication in STATEMENT or MIXED mode this is always a potential problem. Stopping the sql thread until the created temporary tables is 0 is the fix (which is what safe-slave-backup parameter does), not the problem. Performing a backup with Xtrabackup is equivalent to perform a crash recovery, thus the problem.
    – jynus
    Sep 16, 2014 at 10:23
  • @user68293 But if you do not intend ever to use the backup as a slave/replicate more events on it through point in time recovery, it may not be needed.
    – jynus
    Sep 16, 2014 at 10:28
  • Say, do you happen to have any source for this? I can't find it explicitly in the manual.
    – user68293
    Sep 16, 2014 at 14:32
  • @user68293 I put it on the link of my answer: dev.mysql.com/doc/refman/5.6/en/… unless you mean the source for anything else.
    – jynus
    Sep 16, 2014 at 14:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.