MySQL and MongoDB DBA

It is widely known that MySQL now supports two kinds of replication: statement based replication (SBR) and row based replication (RBR). And the corresponding logging methods used in replication are called statement based logging (SBL) and row based logging (RBL). In this post, I am going to find out a few more details inside of different logging methods.

Before that, make sure you have read the basics in MySQL official document. Please keep in mind that binlog is used for recovering data and replication is nothing but an online recovery process. That will make it easier to understand.
1. Binary Logging Formats
2. Advantages and Disadvantages of Statement-Based and Row-Based Replication

OK, let’s begin our journey to binlog’s design. The first question, what will you do if you want to save all the data changes in log?
The easiest way may be to record every commands related to data changes into log. That is SBL and here is a log example for insertion:

# at 463
#120618 6:01:57 server id 1 end_log_pos 577 Query thread_id=1 exec_time=0 error_code=0
SET TIMESTAMP=1339999317/*!*/;
insert into test(idx,data) values (2,3),(4,5),(6,7)

SBL seems to be good enough until we find some records are not completely same after recovery. Why is datetime filed different from it was before? We are talking about nondeterministic functions here. Try to imagine the followings:
1. Insert one record with one datetime filed by execute “insert into test values (sysdate())”
2. Data in MySQL will be “2012-06-16 08:31:47″  and the command in binlog (SBL format) will be “insert into test values (sysdate())”
3. One month later, recover data from binlog. Then we will find that record’s value change to “2012-07-16 08:31:47″

(more…)