There should be options for:

1. dump with history or current snapshot only (MDEV-16029);
2. dump at specific point in time.

1. and 2. should be compatible, providing the ability to dump at specific point in time with all prior history.

Task statement

New mysqldump option: --asof-timestamp=T.

If both --dump-history and --asof-timestamp=T are specified, then history is dumped from the beginning up to T. The query is executed roughly like this:

select *, row_start, row_end from t1 for system_time between timestamp '0-0-0 0:0' and timestamp T;

If only --asof-timestamp=T, then data is dumped without prior history. The query is executed roughly like this:

select * from t1 for system_time as of timestamp T;

Note

This option specifies only timestamp literal.

It does not specify TRX_ID for precise-versioned tables (though specifying a timestamp is of course valid). This is the subject for further development (e.g. --asof-transaction).

It does not specify any functional expressions.

Security check

Fail on single quote (') symbol to avoid forged queries.

MDEV-16798 suggests an option to dump "all system versioning history back to a specified date".

Although we don't need this feature, we do need MDEV-16029 which has been unchanged for quite a while.

for the reference:

$ git fetch https://github.com/tempesta-tech/mariadb.git a19495728dea

I'd expect this pushed together with MDEV-16546 and MDEV-16029.
Please review bb-10.7-midenok

This is a very clear, independent, and rather small feature. Let's not delay it unnecessary by combining it with two larger and way more arguable features. It can get pushed much faster it it won't wait for two other MDEVs

Not sure if I missing something here: TODO-3118 Lists this feature as present in 10.7 trunk, and this ticket is closed with a fix version of 10.7. However, neither 10.7 trunk, nor 10.5.11 (the github.com/tempesta-tech repo listed above) nor 10.5 trunk seems to have the feature implemented:

$ ./bin/mysqldump --help --verbose | grep -i asof

$ ./bin/mysqldump --asof-timestamp

./bin/mysqldump: unknown option '--asof-timestamp'

$ ./bin/mysqldump --dump-history

./bin/mysqldump: unknown option '--dump-history'

Lol. The comments above mentioned --asof... not --as-of.... Thanks!

To clarify, --dump-history was (eventually) not added here, only --as-of.

The --as-of option was documented.

Logged MDEV-26943 mysqldump --as-of option ignores hour/minute precision

For any customers/users reviewing the implementation of -as-of please note there is up-to microsecond precision:

$ ./bin/mysqldump -uroot -S./socket.sock --compact test t1 --as-of="2021-11-01 06:23:43.5" --skip-tz-utc

...

INSERT INTO `t1` VALUES

(1),

(2),

(3);

$ ./bin/mysqldump -uroot -S./socket.sock --compact test t1 --as-of="2021-11-01 06:23:43.7" --skip-tz-utc

...

INSERT INTO `t1` VALUES

(2),

(3);

Logged MDEV-26946 Various small mysqldump manual improvements for the --as-of and --tz-utc options