I realized recently that I was using --master-data to dump a backup from a replication slave when what I really wanted was --dump-slave. After switching to --dump-slave, I see that mysqldump will stop the slave SQL thread during the backup (which was clear from the documentation -- it wasn't a surprise).

I was wondering if anyone could explain why the slave's SQL thread needs to be stopped for the entire backup? I'm using --single-transaction and I'm expecting to get a consistent snapshot as of the time the backup is started. That should be possible even with transactions being processed by the SQL thread. Even if the slave SQL thread is completely held-up due to some lock that mysqldump has acquired, the slave should be allowed to continue to (try to) execute SQL DML statements during the backup.

I can also understand wanting to at least pause the slave SQL thread so that the get-slave-status then start-transaction operations could happen consistently, but as soon as the dump operation begins, the slave SQL thread could be re-started, right?

When using --master-data with a replication master, global locks are obtained a single time and then released, allowing the regular application-load to continue during the dump operation. Why can't this be done on the slave in the same way? Any information anyone could provide would be greatly helpful.

1 Answer 1


--single-transaction obviously only preserves a transaction for the transactional tables.

The STOP SLAVE is needed to preserve the consistency of the dump of the MyISAM tables. If there was logic in mysqldump to dump the MyISAM tables first, it could START SLAVE at that point (in a different connection as START SLAVE probably an implicit commit`), and continue dumping the InnodDB tables.

The STOP SLAVE is also required as DLL isn't transactional. What could happen is while tables are being dumped, a CREATE TABLE / DROP TABLE could happen in the replication stream, and as those aren't transactional, the dump would then be inconsistent.

Why --master-data assumes it isn't a slave and doesn't need the same level of protection is anyone's guess. Why --master-data issues a FLUSH TABLES WITH READ LOCK around dumping position information and dump slave doesn't? Why the UNLOCK TABLES happens before the data dump starts? Also a mystery.

  • I hadn't considered non-transactional tables, but now it makes much more sense. I happen to only be using InnoDB tables so its frustrating that the database must be completely offline to take a snapshot (using mysqldump). I can also guarantee that no DDL statements are taking place during the dump, so it would be nice to be able to tell MySQL that it's okay to skip those unnecessary protections. As soon as mydumper supports encrypted-export, my days of using mysqldump are over. Commented Aug 18, 2018 at 13:27
  • LOCK TABLES FOR BACKUP - mariadb feature request would also this. mydumper feature request
    – danblack
    Commented Aug 18, 2018 at 23:27
  • 1
    Yeah, that mydumper feature request looks very familiar: I'm the one who wrote it :) Commented Aug 20, 2018 at 13:45

Your Answer

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

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