Database Reference
In-Depth Information
This will restore all of the remaining log entries, but omitting the
DELETE
statements.
This method is very precise in that it utilizes exact positions in the binary log for specific
transactions. You may also perform a point-in-time recovery using starting and ending
times. To do that, use the
--start-datetime
and
--stop-datetime
optionswith
mysqlbinlog
. Looking back at the binary log excerpts, you could do the following to ac-
complish the same point-in-time recovery that we made:
mysqlbinlog --database=
rookery
--stop-datetime="
140916 13:10:24
" \
/data/mysql/mysqlresources-bin.000002
|
mysql --user=
admin_maintenance
--password
mysqlbinlog --database=
rookery
--start-datetime="
140916 13:10:29
"
--to-last-log \
/data/mysql/mysqlresources-bin.000002
|
mysql --user=
admin_maintenance
--password
Our first invocation of
mysqlbinlog
gives it the date and time we noted earlier for the stop
point just before the first
DELETE
statement. Our second invocation specifies one second
past the time of the transaction for the second
DELETE
statement as the start point for
restoring data. This will work just fine, but using position numbers is more precise, be-
cause plenty can happen in asecond.
NOTE
A similar method of making backups with the binary logs is to use MySQL replication. With replication,
you would have another server, a slave that has been continuously reading the binary log of the main or
master server. The slave can use the binary log entries to maintain an exact duplicate of the databases on
the master. When you want to make a backup, you need only stop the slave from replicating the master
and make a backup of the databases on the slave. When you're finished, begin replicating again, and
within seconds the slave is current again. This topic is beyond the scope of this topic. However, my book
MySQL Replication: An Administrator's Guide to Replication in MySQL
(A Silent Killdeer Publishing,
2010) explains replication and how to resolve problems with MySQL.
