5

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.

Christopher Schultz
  • 327
  • 4
  • 14

1 Answers1

2

--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.

danblack
  • 8,258
  • 2
  • 12
  • 28