Comments - Point in Time options with logical/mysqldump?

Daniel, thanks.

Some responses below...quoting you and then responding just below....

"With mysqldump, incremental backups are the binary logs from the point in time when the consistent snapshot was taken."

>. Yes. I would rollforward with binlogs -- this is based on LSN, right? (And if, instead, doing this for *physical* backup, the incremental is an additional raw backup file to be "prepared", instead of binlogs...but also responsive to LSN.)

"To keep a record of this point in time look at the --master-data and --dump-slave options. Which to use depends if the binary logs are from the current replica, or from the master of the current replica. If using a single machine use --master-data."

> yes, and in this case, no replication (master/slave) is planned. This is purely for *migration*. So --master-data=2 and to get the right binlogs, FLUSH TABLES WITH READ LOCK so that once i migrate, i can do point-in-time-recovery (PITR) based on LSN (I think....)

"When using these options always use --single-transaction to ensure its a consistent transaction"

> Yes, this is nonlocking and statement/transaction-based, right? and to be preferred over --lock-tables, right? Seems to work on a per-database level...not across entire collection of databases, so any "cross-database" transactions would still be at risk of violating consistency, once restored/recovered.

"Parallel loading is loading these each in a separate, concurrent session. Because the data is independent of the other dumps there's no conflict here."

> I can load in parallel...but...can I safely apply binlogs for PITR in parallel? That has to be serial.....

"When shipping binlogs use --start-position set to --master-data/dump-slave position, as an absolute position on where to start on the first file"

> I don't know if i can control the binlog down to --start-position granularity? All I can do is flush/purge logs, not determine where they start or end. I can, however, start PITR at a certain transaction (position), as you cite....

> It would be good to have a workbook, with fully-detailed examples of these. People must be doing PITR recovery based on mysqldump, but the information is somewhat scarce. I guess it's so easy and clear those of us who don't immediately get it are rare. Thanks for your responses!

LSN are purely a concept of the innodb not relevant here currently but has some role in physical mariadb-backup. mysqldump/mariadb-dump --master-data/dump-slave replication offset is an absolute number of bytes offset withing a binlog file. Logicial mysqldump backups don't need a preparation stage.

Replication can still be used in a migration, it takes the pressure of the downtime window and can help ensure the new machine has a hot cache ready for the switch over (stop writes on master, wait until replica caught up, switch application IP over, stop the master, reset the replication).

With a migration, if you are holding Attachment 'FLUSH_TABLES_WITH_READ_LOCK' not found until the server is decommissioned, then --single-transaction is unnecessary (though maybe you want to use it for a test run of the migration so your database can remain fully online). Yes, its a transaction, and transactions are global and not per database (based on an LSN view which hopefully isn't too confusing). Under --single-transaction, non-transactional tables are dumped first under a lock however.

You can't load binlogs in parallel. If you have taken FLUSH TABLES WITH READ LOCK and not released this, then the binary logs will contain nothing extra that needs to be applied as there cannot be any table updates.

Yes, binlog start-position isn't for controlling. Flush binary logs is useful to minimize the amount of uneeded contents on the first file.

I agree, its a good idea for a workbook. There's some more advanced items like relay log position that I really haven't covered.