【MySQL】你所不知道的行復制(binlog_format=row)

⒈ 本文目的：很多MySQL DBA 自認為對 mysql 的行復制有了很深刻的理解（這行復制不是初級 DBA 就該掌握的內容嘛，你怎麼提這麼低階的問題），那麼接下來可能會出乎你的意料。

⒉ 問題引出：

假設有A,B 兩個資料庫例項， A （主） ->B （從）

⑴ 第一問：

① 在主庫上建立表結構如下（特別注意這裡有唯一鍵，沒主鍵）：

CREATE TABLE `test` (
  `a` int(11) DEFAULT NULL,
  `b` int(11) NOT NULL,
  `c` varchar(20) DEFAULT NULL,
  UNIQUE KEY `b_uique` (`b`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

② 我們在從庫B 上插入一條記錄：

    insert into test values(2,2,'222');

③ 然後再主庫A 上插入一條記錄 :

    insert into test values(3,2,'333');// 毫無疑問，從庫會出現 1032 的錯誤，唯一鍵衝突

     然後再從庫上跳過一個事件（ gtid 跳過一個事物）； // 這樣主從複製 A->B 就會正常

④ 接下，我們在主庫A 上做一個操作：

    update test set c='333ab' where b=2;

  請回答 ：B 庫主從是出現 1032 的錯誤呢，還是主從複製正常，如果正常，那麼 B 庫 test 表的資料是什麼？

 

 

⑵ 第二問：

① 在主庫上建立表結構如下（特別注意這裡有主鍵與唯一鍵）：

CREATE TABLE `test1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a` int(11) NOT NULL,
  `b` int(11) NOT NULL,
  `c` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `b` (`b`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4

 

② 我們在從庫B 上插入一條記錄：

    insert into test values(2,2,2,'222');

③ 然後再主庫A 上插入一條記錄 :

    insert into test values(3,2,2,'333');// 毫無疑問，從庫會出現 1032 的錯誤，唯一鍵衝突

      然後再從庫上跳過一個事件（ gtid 跳過一個事物）； // 這樣主從複製 A->B 就會正常

 

④ 接下，我們在主庫A 上做一個操作：

    update test set c='333ab' where b=2;

請回答： B 庫主從是出現 1032 的錯誤呢，還是主從複製正常，如果正常，那麼 B 庫 test 表的資料是什麼？

 

⒊ 問題分析與結論：

先別急著看答案，我們來看( 主庫 ) 解析 update 行復制 binlog 列印的是什麼？

BEGIN
/*!*/;
# at 4255
#190228 20:59:13 server id 212493307  end_log_pos 4307 CRC32 0x902463fc         Table_map: `bcd`.`test1` mapped to number 340
# at 4307
#190228 20:59:13 server id 212493307  end_log_pos 4382 CRC32 0x06b71e7b         Update_rows: table id 340 flags: STMT_END_F
### UPDATE `bcd`.`test1`
### WHERE
###   @1=3 /* INT meta=0 nullable=0 is_null=0 */
###   @2=3 /* INT meta=0 nullable=0 is_null=0 */
###   @3=3 /* INT meta=0 nullable=0 is_null=0 */
###   @4='33333' /* VARSTRING(80) meta=80 nullable=1 is_null=0 */
### SET
###   @1=3 /* INT meta=0 nullable=0 is_null=0 */
###   @2=3 /* INT meta=0 nullable=0 is_null=0 */
###   @3=3 /* INT meta=0 nullable=0 is_null=0 */
###   @4='3aaa33' /* VARSTRING(80) meta=80 nullable=1 is_null=0 */
# at 4382
#190228 20:59:13 server id 212493307  end_log_pos 4413 CRC32 0x9b13368e         Xid = 14175
COMMIT/*!*/;

沒錯，他記錄了 原來行的內容，這裡原來行是 (3,3,3,'33333') 然後被改成了 (3,3,3,'3aaa33');

那麼這條binlog 被複制到從庫後，就涉及到如何定位一行進行修改的問題了：

是透過（3,3,3,'33333' ）所有元組定位一行嗎？是透過主鍵唯一鍵 (3,3) 定位一行嗎？是透過主鍵 (3) 定位一行嗎？是透過唯一鍵 (3) 定位一行嗎？

其實統統都不是，從庫定位一行如下：

當表中有主鍵時，則透過主鍵定位一行

當表中同時有主鍵與唯一鍵時，則透過主鍵定位一行；主鍵不同，唯一鍵相同，代表的是不同行

當表只有唯一鍵是，則透過唯一鍵定位一行

系統自動生成的主鍵，不參與複製，不能定位一行

 

那麼上面的問題，各位看官可以答出來了嗎？

問題一：B 庫不會出現 1032 的錯誤，最終從庫 B 的資料是 (3,2,'333ab')

問題二：B 庫會出現 1032 的錯誤，最終 B 庫的資料是 (2,2,2,'222')