Point in Time options with logical/mysqldump?
New to backup, new to mysql - what are incremental or PITR options for logical backups with mysqldump?
Would like to use to migrate to remote server, for example.
May one
Loop through source server databases (or tables) running mysqldump for each (in parallel) collect all database/table dump files.. .... ..ship dump files to remote...including cnf and binary logs .... on remote server loop through per-database/table dumps load in parallel .....
And finally, make these consistent by applying mysqlbinlog to roll forward/rollback transaction in binlogs as necessary?
Thanks PS I understand the process for physical innobackupex with incremental and prepare, better, but want a "logical" dump based PITR/incremental solution here.
Answer Answered by Daniel Black in this comment.
With mysqldump, incremental backups are the binary logs from the point in time when the consistent snapshot was taken.
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.
When using these options always use --single-transaction to ensure its a consistent transaction.
To do this in parallel, a consistent snapshot of independently running mysqldumps is needed (per database, or individual table - with --single-transaction). To do this use flush table with read lock before starting all of the mysqldump instances. Once started this all tables read lock can be released. Also see backup-lock to ensure that tables aren't changed during the dump resulting in an unusable backup. Only one of these needs to include --master-data/dump-slave. Recommend the =2 option to ensure that the information is in a comment. Because of MDEV-7611 (sigh, still not fixed) the recovery should have an invalid host/user/password during restore, and the correct this when starting to replicate again.
Parallel loading is loading these each in a separate session. Because the data is independent of the other dumps there's no conflict here.
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.
