Database Reference
In-Depth Information
# at 1284668
#140916 13:10:28 server id 1 end_log_pos 1284739
Query thread_id=382 exec_time=0 error_code=0
SET TIMESTAMP=1410887428/*!*/;
SET @@session.sql_mode=0/*!*/;
BEGIN
/*!*/;
# at 1284739
#140916 13:10:28 server id 1 end_log_pos 1284862
Query thread_id=382 exec_time=0 error_code=0
SET TIMESTAMP=1410887428/*!*/;
DELETE FROM birds_simple WHERE common_name LIKE '%Green%'
/*!*/;
# at 1284862
#140916 13:10:28 server id 1 end_log_pos 1284889 Xid = 45553
COMMIT/*!*/;
This may seem very confusing, but it's not too bad when you understand how binary log
entries are organized and a few things about transactions.
Binary log entries always start with two comment lines for a header — comments start
with a hash sign (i.e.,
#
). The first comment line contains the position number of the entry
after the word
at
. This is the number we need to restore to a specific point. The second
comment line of the header provides the time of the entry and other information. A binary
log entry ends with
/*!*/;
.
A transaction is a set of SQL statements that are executed together and are generally re-
lated. Transactions are used with transactional tables (e.g., InnoDB) and not non-transac-
tional tables (e.g., MyISAM). Any SQL statements contained within a transaction can be
undone or rolled back if they're not yet committed. The binary log uses transactions so
that when data is restored, it can be restored properly. This will make more sense as we
look at the components of a transaction in the excerpt shown.
Transactions alwaysstart with a
BEGIN
statement and end generally with a
COMMIT
statement, which commits the SQL statements between the two — they can't be rolled
back or otherwise undone once they are committed. Near the start of the excerpt from the
binary log, you can see a
BEGIN
statement, followed soon after by the first
DELETE
statement. Therefore, the
DELETE
is in the midst of a transaction.
The position number for the entry containing the first
DELETE
is 1258778. However, we
need to go back to the entry containing the
BEGIN
before it so that we can get the whole
transaction. Let's look at the header for that entry:
