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