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…)

本篇blog的讨论是基于Data Access Layer的。之所以有这个前提是因为，MySQL集群只有工作在DAL下才能方便地实现读写分离和高可用。先用图来分别描述一下级联复制和主主复制：

(more…)

MySQL的同步相当简单，但是怎么用好同步，根据业务需求为应用层提供高性能、高可用是一个值得探讨的问题。下面讨论一下几个常见MySQL数据库架构的优缺点。

一、（多）单库结构
这个恐怕是最最简单的一种方案了，完全没有数据一致性问题。最大的缺点是无法容灾，并且只能承受较小的压力，不管压力来自读或者写。不过在分布式数据层解决方案目不暇接的今天，单库结构可以拓展成多单库结构来平分压力。数据库可以从业务上先进行垂直拆分，将关联性较强的表放在一个库中，将数据变化较小的表也放在一个库中；其次是将读写频繁的表进行水平拆分，以某字段值为基础，根据业务需求来选取适当的表路由算法。
(more…)