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

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

⒉ 問題引出：

假設有A,B 兩個數(shù)據(jù)庫實例， 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');// 毫無疑問，從庫會出現(xiàn) 1032 的錯誤，唯一鍵沖突

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

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

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

  請回答 ：B 庫主從是出現(xiàn) 1032 的錯誤呢，還是主從復制正常，如果正常，那么 B 庫 test 表的數(shù)據(jù)是什么？

⑵ 第二問：

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

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');// 毫無疑問，從庫會出現(xiàn) 1032 的錯誤，唯一鍵沖突

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

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

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

請回答： B 庫主從是出現(xiàn) 1032 的錯誤呢，還是主從復制正常，如果正常，那么 B 庫 test 表的數(shù)據(jù)是什么？

⒊ 問題分析與結論：

先別急著看答案，我們來看( 主庫 ) 解析 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) 定位一行嗎？

其實統(tǒng)統(tǒng)都不是，從庫定位一行如下：

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

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

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

系統(tǒng)自動生成的主鍵，不參與復制，不能定位一行

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

問題一：B 庫不會出現(xiàn) 1032 的錯誤，最終從庫 B 的數(shù)據(jù)是 (3,2,'333ab')

問題二：B 庫會出現(xiàn) 1032 的錯誤，最終 B 庫的數(shù)據(jù)是 (2,2,2,'222')