Backing Up and Restoring Databases - Learning MySQL and MariaDB - page 362

# at 1258707
#140916 13:10:24 server id 1 end_log_pos 1258778 Query thread_id=382
The position number for that entry is 1258707. The date and time of the entry is 140916
13:10:24 (i.e., 2014 September 16 at 1:10 p.m. and 24 seconds). We now know the po-
sition number and time for the transaction that contains the first DELETE . You may notice
that the same line has a number following end_log_pos . That's the position number
for the next log entry (1258778), which is the entry for the DELETE . Don't let that con-
fuse you. Position numbers are based on positions in the file; they're not from an incre-
mental counter.
We want to restore the binary log from the beginning until the start of the transaction con-
taining the first DELETE , which means until position 1258707. We could edit the text file
that we created with mysqlbinlog (i.e., recovery-research.txt ) and delete the transactions
that we don't want, and then just execute the file with the mysql client. However, there's
an easier and better way to do this. We can have the mysqlbinlog exportthe transactions
again, but have it stop just before position 1258707. To do this, enter the following at the
command line:
mysqlbinlog --database= rookery --stop-position=" 1258707 " \
/data/mysql/mysqlresources-bin.000002 |
mysql --user= admin_maintenance --password
This will extract the same log entries, starting from the beginning of the same binary log
file, but stopping at the position we gave it.
At this point, we've restored all of the transactions up until the DELETE statements — but
not including them. Now we need to restore all of the transactions starting from the trans-
action immediately after the transaction containing the second DELETE statement.
Looking at the binary log excerpt for the COMMIT for that transaction for the second
DELETE statement, we see that the end_log_pos has a value of 1284889. That is the
position of the start of the next transaction. We want to restore from that point forward. As
for where we want to stop restoring, we don't need to specify a position number for it. In-
stead, we'll use the option --to-last-log to indicate that we want to install to the end
of the log. This may be further than the end of the log file, if the logs have been flushed
and more log files were added. Given these two factors, execute the following:
mysqlbinlog --database= rookery --start-position=" 1284889 "
--to-last-log \
/data/mysql/mysqlresources-bin.000002 |
mysql --user= admin_maintenance --password