Databases Reference
In-Depth Information
Binary logs are “special.” If you lose your data, you really don't want to lose the binary
logs as well. To minimize the chances of this happening, you can keep them on a sep-
arate volume. It's OK to do this even if you want to snapshot the binary logs with LVM.
For extra safety, you can keep them on a SAN or replicate them to another device with
DRBD.
It's a good idea to back up binary logs frequently. If you can't afford to lose more than
30 minutes' worth of data, back them up at least every 30 minutes. You can also use a
read-only replica with
--log_slave_updates
, for an extra degree of safety. The log posi-
tions won't match the master's, but it's usually not hard to find the right positions for
recovery. Finally, MySQL 5.6's version of
mysqlbinlog
has a very handy feature to con-
nect to a server and mirror its binary logs in real time, which is simpler and more
lightweight than running an instance of
mysqld
. It's backward-compatible with older
server versions.
See
Chapter 8
and
Chapter 10
for our recommended server configuration for binary
logging.
The Binary Log Format
The binary log consists of a sequence of events. Each event has a fixed-size header that
contains a variety of information, such as the current timestamp and default database.
You can use the
mysqlbinlog
tool to inspect a binary log's contents, and it prints out
some of the header information. Here's an example of the output:
1 # at 277
2 #071030 10:47:21 server id 3 end_log_pos 369 Query thread_id=13 exec_time=0
error_code=0
3 SET TIMESTAMP=1193755641/*!*/;
4 insert into test(a) values(2)/*!*/;
Line 1 contains the byte offset within the log file (in this case, 277).
Line 2 contains the following items:
• The date and time of the event, which MySQL also uses to generate the
SET TIME
STAMP
statement.
• The server ID of the originating server, which is necessary to prevent endless loops
in replication and other problems.
• The
end_log_pos
, which is the byte offset of the next event. This value is incorrect
for most of the events in a multistatement transaction. MySQL copies the events
into a buffer on the master during such transactions, but it doesn't know the next
log event's position when it does so.
• The event type. Our sample's type is
Query
, but there are many different types.
• The thread ID of the thread that executed the event on the originating server, which
is important for auditing as well as for executing the
CONNECTION_ID()
function.
