RBR provides a safe way of
replicating between master and slave. It has several advantages over statement
based replication (SBR), where the lack of non-deterministic operations jumps
to mind rather quickly. However, there is one drawback that drives some users
away from it. RBR ships the changes over to the slave, instead of the
operations as it happens in SBR. This means that RBR may exhibit a large binary
log footprint for operations that make a rather big number of changes or
operate over sizable rows.
Below are some interesting
enhancements in RBR in MySQL 5.6 Replication.
1. Optimized row image:
In RBR, one row event contains
changes to one or more rows in a given table. A row change, in its turn, may
consist of one or two full row copies of the row that is being changed. These
are generally known as row images. The first one, known as before image (BI),
contains data as it was before the row was modified. The second one, known as
after image (AI), is what the row looks like after the changes were done. Each
image has different purpose: the BI is used for locating, in the storage
engine, the row to be updated/deleted, while the AI, is used for replaying the
actual changes. Needless to say, not both images are needed for every
operation. Deletes only need the BI, inserts, on the other hand, just need the
AI, while updates need both.
Large data transfer between master
and slave while using RBR is a thing of past. Why log complete rows on the
master? Why not just the changed colums? The MySQL 5.6 release enables a
DBA to configure the master to log only the changed columns in the row.
The Before Image (BI) will contain the columns for identifying the rows
and the After Image(A I) will just contain the column that has changed.
Before MySQL 5.6:
As one can observe in the
illustration that the row images were full, i.e. all the columns in the row
were logged for both the Before Image(BI) and the After Image AI
In MySQL 5.6:
In MySQL 5.6 the DBA can configure
if full or minimal or NOBLOB image of rows should be logged in the binary log
in the following ways.
1. start the server with --binlog-row-image=MINIMAL|FULL|NOBLOB
2. SET SESSION|GLOBAL
binlog_row_image= MINIMAL|FULL|NOBLOB
In minimal image if there is a
Primary Key (PK) or any kind of Unique Index/Key (UK) that can identify a row
uniquely on the slave then the BI contains just that column. In the After Image
(AI) only the changed columns are logged
In case of NOBLOB is used, the BLOB
fields will be skipped from logging unless changed. In case FULL is
used the logging will continue as before.
As you can see the amount of data
transfer will be reduced by a lot especially if you have used proper
indexes/keys for the tables on the master and the slave.
2. Batch operations on table without PK or UK:
is one of the long awaited
optimization when using RBR. This enables the DBA to configure what algorithm
will be used on the slave to find the appropriate row for
updating/deleting. We have introduced the concept of HASH_SCAN, in which an
in-memory hash is being created on the slave, and provides a way to find and
apply rows in a row event in O(n) time. This enhancement provides a
great scope for performance improvement on the slave for the events logged in row
format.
3. Informational Log events:
One of the difficulties DBA have
faced till now, while using RBR is that, it is difficult to maintain. The Row
images are binary in nature and is not suitable for human
"consumption". This is where this enhancement comes handy. There are
two ways of using this.
1. Enabling switch
--binlog-rows-query-log-events during the server startup
2. Changing the
corresponding session variable.
Once enabled this will cause the
original query for the row event to be logged in the binary log as a special
event which the DBA can check using one of the two methods.
1. One can check these special
events in the binary-log by using SHOW BINLOG EVENTS. The query corresponding
the the row event will be logged as an event type: Row_query. below is one such
example
mysql>
SHOW BINLOG EVENTS;
+---------------+-----+-------------+-------+--------+-----------------------------------------------+
|
Log_name | Pos | Event_type | S..id | End... | Info
|
+---------------+-----+-------------+-------+--------+-----------------------------------------------+
|
master.000001 | 4 | Format_desc | 1 | 114 |
Server ver: 5.6.7-m5-debug-log, Binlog ver: 4 |
|
master.000001 | 114 | Query | 1 |
200 | use `test`; CREATE TABLE NUM_INTS (a INT) |
|
master.000001 | 200 | Query | 1 |
268 | BEGIN
|
|
master.000001 | 268 | Rows_query | 1 | 323 | #
INSERT INTO NUM_INTS VALUES (1), (2), (3) |
|
master.000001 | 323 | Table_map | 1 | 364 |
table_id: 54 (test.NUM_INTS)
|
|
master.000001 | 364 | Write_rows | 1 | 408 |
table_id: 54 flags: STMT_END_F
|
|
master.000001 | 408 | Query | 1 |
477 | COMMIT
|
+---------------+-----+-------------+-------+--------+-----------------------------------------------+
In the above terminal output
(snip-ed), one can see the event at pos= 268 is an informational event for the
next Table_map and Write_rows events.
2. Another way is to use the
mysqlbinlog utility with --vv switch (verbosity level 2 or more).
shell> mysqlbinlog
-vv /master.000001
# at 268
#110401 14:24:29
server id 1 end_log_pos 323 Rows_query
# INSERT INTO t1
VALUES (1), (2), (3)
# at 323
#110401 14:24:29
server id 1 end_log_pos 364 Table_map: `test`.`NUM_INTS` mapped to
number 54
# at 364
#110401 14:24:29
server id 1 end_log_pos 408 Write_rows: table id 54 flags:STMT_END_F
