基於statement複製下NULL、RAND、UUID的測試

G8bao7發表於2014-02-19
版本:5.5.29

測試方法:
slave先停止同步,製造同步延遲
master分別使用NULL、RAND()、UUID()進行update
slave延遲3s以上開啟同步
比較主從資料是否一致

結論: NULL、RAND()一致。UUID()不一致
原因:
    NULL: binlog儲存原sql
    RAND(): binlog除了原sql,同時記錄了種子值
    UUID: 
        MASTER : 4b1ae5c8-9952-11e3-b7ca-00151760d8ec
        SLAVE   : 4b2744b2-9952-11e3-afc9-00151760d8ec             
        UUID格式共36個字元:aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee
        前三組值是時間戳換算過來的;
        第四組值是暫時性保持時間戳的唯一性。例如,使用夏令時;
        第五組值是一個IEE 802的節點標識值,它是空間上唯一的。若後者不可用,則用一個隨機數字替換。假如主機沒有網路卡,或者我們不知道如何在某系統下獲得機器地址,則空間唯一性就不能得到保證,即使這楊,出現重複值的機率還是非常小的。    
        官方說明:http://dev.mysql.com/doc/refman/5.5/en/miscellaneous-functions.html#function_uuid
        疑問:
            binlog有時間戳為何不用?
            最後一組的數值為何是一樣,理論上不同機器(主從)上至少最後一位肯定不同?因為測試的主從環境是一臺機器上的兩個埠,並不是兩臺機器

一、表結構及資料

點選(此處)摺疊或開啟

  1. -- 表結構
  2. DROP TABLE IF EXISTS `test_140219_sbr`;
  3. CREATE TABLE `test_140219_sbr` (
  4.   `id` INT(11) NOT NULL AUTO_INCREMENT,
  5.   `sid` INT(11) NOT NULL DEFAULT \'0\',
  6.   `val` VARCHAR(255) DEFAULT NULL,
  7.   `upddt` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  8.   `mupddt` DATETIME DEFAULT NULL,
  9.   PRIMARY KEY (`id`)
  10. ) ENGINE=INNODB DEFAULT CHARSET=utf8;
  11. -- 插入資料
  12. INSERT INTO `test_140219_sbr`(`id`) VALUES ('1'),('2'),('3'),('4');

二、測試sql及相應的binlog
SET @ct:=NOW();
SELECT SLEEP(1);
-- 字串'NULL'
UPDATE `test_140219_sbr` SET sid=@@server_id,`val` = 'NULL',`upddt`=NOW(),`mupddt`=@ct WHERE id=1;

點選(此處)摺疊或開啟

  1. # at 4588
  2. #140219 18:40:47 server id 3307 end_log_pos 4672 Query thread_id=4 exec_time=0 error_code=0
  3. SET TIMESTAMP=1392806447/*!*/;
  4. BEGIN
  5. /*!*/;
  6. # at 4672
  7. #140219 18:40:47 server id 3307 end_log_pos 4726 User_var
  8. SET @`ct`:=_latin1 0x323031342D30322D31392031383A34303A3436 COLLATE `latin1_swedish_ci`/*!*/;
  9. # at 4726
  10. #140219 18:40:47 server id 3307 end_log_pos 4921 Query thread_id=4 exec_time=0 error_code=0
  11. SET TIMESTAMP=1392806447/*!*/;
  12. -- 字串'NULL'
  13. UPDATE `test_140219_sbr` SET sid=@@server_id,`val` = 'NULL',`upddt`=NOW(),`mupddt`=@ct WHERE id=1
  14. /*!*/;
  15. # at 4921
  16. #140219 18:40:47 server id 3307 end_log_pos 4948 Xid = 376
  17. COMMIT/*!*/;
  18. # at 4948

-- NULL值
UPDATE `test_140219_sbr` SET sid=@@server_id,`val` = NULL,`upddt`=NOW(),`mupddt`=@ct WHERE id=2;

點選(此處)摺疊或開啟

  1. # at 4948
  2. #140219 18:40:47 server id 3307 end_log_pos 5032 Query thread_id=4 exec_time=0 error_code=0
  3. SET TIMESTAMP=1392806447/*!*/;
  4. BEGIN
  5. /*!*/;
  6. # at 5032
  7. #140219 18:40:47 server id 3307 end_log_pos 5086 User_var
  8. SET @`ct`:=_latin1 0x323031342D30322D31392031383A34303A3436 COLLATE `latin1_swedish_ci`/*!*/;
  9. # at 5086
  10. #140219 18:40:47 server id 3307 end_log_pos 5271 Query thread_id=4 exec_time=0 error_code=0
  11. SET TIMESTAMP=1392806447/*!*/;
  12. -- NULL值
  13. UPDATE `test_140219_sbr` SET sid=@@server_id,`val` = NULL,`upddt`=NOW(),`mupddt`=@ct WHERE id=2
  14. /*!*/;
  15. # at 5271
  16. #140219 18:40:47 server id 3307 end_log_pos 5298 Xid = 378
  17. COMMIT/*!*/;
  18. # at 5298

-- RAND
UPDATE `test_140219_sbr` SET sid=@@server_id,`val` = RAND(),`upddt`=NOW(),`mupddt`=@ct WHERE id=3;

點選(此處)摺疊或開啟

  1. #140219 18:40:47 server id 3307 end_log_pos 5382 Query thread_id=4 exec_time=0 error_code=0
  2. SET TIMESTAMP=1392806447/*!*/;
  3. BEGIN
  4. /*!*/;
  5. # at 5382
  6. #140219 18:40:47 server id 3307 end_log_pos 5417 Rand
  7. SET @@RAND_SEED1=54715179, @@RAND_SEED2=199865937/*!*/;
  8. # at 5417
  9. #140219 18:40:47 server id 3307 end_log_pos 5471 User_var
  10. SET @`ct`:=_latin1 0x323031342D30322D31392031383A34303A3436 COLLATE `latin1_swedish_ci`/*!*/;
  11. # at 5471
  12. #140219 18:40:47 server id 3307 end_log_pos 5655 Query thread_id=4 exec_time=0 error_code=0
  13. SET TIMESTAMP=1392806447/*!*/;
  14. -- RAND
  15. UPDATE `test_140219_sbr` SET sid=@@server_id,`val` = RAND(),`upddt`=NOW(),`mupddt`=@ct WHERE id=3
  16. /*!*/;
  17. # at 5655
  18. #140219 18:40:47 server id 3307 end_log_pos 5682 Xid = 380
  19. COMMIT/*!*/;
  20. # at 5682

-- UUID
UPDATE `test_140219_sbr` SET sid=@@server_id,`val` = UUID(),`upddt`=NOW(),`mupddt`=@ct WHERE id=4;

點選(此處)摺疊或開啟

  1. #140219 18:40:47 server id 3307 end_log_pos 5766 Query thread_id=4 exec_time=0 error_code=0
  2. SET TIMESTAMP=1392806447/*!*/;
  3. BEGIN
  4. /*!*/;
  5. # at 5766
  6. #140219 18:40:47 server id 3307 end_log_pos 5820 User_var
  7. SET @`ct`:=_latin1 0x323031342D30322D31392031383A34303A3436 COLLATE `latin1_swedish_ci`/*!*/;
  8. # at 5820
  9. #140219 18:40:47 server id 3307 end_log_pos 6004 Query thread_id=4 exec_time=0 error_code=0
  10. SET TIMESTAMP=1392806447/*!*/;
  11. -- UUID
  12. UPDATE `test_140219_sbr` SET sid=@@server_id,`val` = UUID(),`upddt`=NOW(),`mupddt`=@ct WHERE id=4
  13. /*!*/;
  14. # at 6004
  15. #140219 18:40:47 server id 3307 end_log_pos 6031 Xid = 382
  16. COMMIT/*!*/;
  17. # at 6031


三、主從資料
MASTER

點選(此處)摺疊或開啟

  1. mysql> SELECT id,sid,ifnull(val,'null值') as 'val',upddt,mupddt FROM `test_140219_sbr`\G;
  2. *************************** 1. row ***************************
  3.     id: 1
  4.    sid: 3307
  5.    val: NULL
  6.  upddt: 2014-02-19 18:40:47
  7. mupddt: 2014-02-19 18:40:46
  8. *************************** 2. row ***************************
  9.     id: 2
  10.    sid: 3307
  11.    val: null值
  12.  upddt: 2014-02-19 18:40:47
  13. mupddt: 2014-02-19 18:40:46
  14. *************************** 3. row ***************************
  15.     id: 3
  16.    sid: 3307
  17.    val: 0.3390121034709849
  18.  upddt: 2014-02-19 18:40:47
  19. mupddt: 2014-02-19 18:40:46
  20. *************************** 4. row ***************************
  21.     id: 4
  22.    sid: 3307
  23.    val: 4b1ae5c8-9952-11e3-b7ca-00151760d8ec
  24.  upddt: 2014-02-19 18:40:47
  25. mupddt: 2014-02-19 18:40:46

SLAVE

點選(此處)摺疊或開啟

  1. mysql> SELECT id,sid,ifnull(val,'null值') as 'val',upddt,mupddt FROM `test_140219_sbr`\G;
  2. *************************** 1. row ***************************
  3.     id: 1
  4.    sid: 3308
  5.    val: NULL
  6.  upddt: 2014-02-19 18:40:47
  7. mupddt: 2014-02-19 18:40:46
  8. *************************** 2. row ***************************
  9.     id: 2
  10.    sid: 3308
  11.    val: null值
  12.  upddt: 2014-02-19 18:40:47
  13. mupddt: 2014-02-19 18:40:46
  14. *************************** 3. row ***************************
  15.     id: 3
  16.    sid: 3308
  17.    val: 0.3390121034709849
  18.  upddt: 2014-02-19 18:40:47
  19. mupddt: 2014-02-19 18:40:46
  20. *************************** 4. row ***************************
  21.     id: 4
  22.    sid: 3308
  23.    val: 4b2744b2-9952-11e3-afc9-00151760d8ec
  24.  upddt: 2014-02-19 18:40:47
  25. mupddt: 2014-02-19 18:40:46





來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/26250550/viewspace-1084211/,如需轉載,請註明出處,否則將追究法律責任。

相關文章