MariaDB / (MySQL ?) point in time recovery

Question

I am trying to get know the exact workflow of point in time recovery, or more general using the binary logs for backup/restore (meaning: using binary log files with mysqlbinlog) This is important not only because of PITR, but also the use binary log file in case of data file loss, but not lost binary logs from the last backup, this case I can restore from the backup, then "apply" the remaining changes from the not lost current binary log files.

I do understand the main concept of restoring offline to from available backups, then apply further changes from binary log using mysqlbinlog.

Two things are not clear for me:

1. When I everywhere see mysqlbinlog <files>.... | mysql -u ... it indicates that server must run and online. How can I assure that after I offline restored some state, the server remains that state and my piped SQL statements are the only exclusively executed statement on the server?

2. The other thing I can not find the answer for, is how can I determine and apply this lsn from what the binlog statements should be executed? I do know that I can query the current lsn from the server for example SHOW MASTER STATUS; So say I restore some state offline from my last backup, then somehow manage to connect my server exclusively (how?), then ask the current lsn. Now how can I parametrize mysqlbinlog to start from that position? Its command line switches seems it can start from date or line number...

Answer 1

1. my piped SQL statements are the only exclusively executed statement on the server?

one or more of the following

• read_only=1 as a server configuration during restore. (ensure apps/cron process aren't using SUPER/READ ONLY ADMIN privileges

• start server with skip-networking if all the applications are remote and you are doing the restore from the actual machine.

• shut down applications

• enable firewall rules from everywhere except your access required to restore.

2. how can I determine and apply this lsn

Normally go by the position or gtid rather than LSN. The full backup snapshot should record its snapshot position like on mariadb-dump the --dump-slave or --master-data (and maybe --gtid). This will record a --start-position for the restore depending on if you are using the master's binlog or local.

The end position can be time based, but you'd normally look for an explicit damaging event (e.g.: drop tables) in the mariadb-binlog and run the replay up to there (--stop-position). For further recovery you may run with the start point after the damaging event for other bits that might be recoverable.