由于數(shù)據(jù)庫 Blob字段太多，導致從庫進行binlog不能正常進行的處理方法

由于數(shù)據(jù)庫 Blob字段太多，導致從庫進行binlog不能正常進行的處理方法

binlog_format為row格式的時候記錄的不是簡單的sql，而是實際變更的行，一些大的DML操作，會導致binlog量增加很大，消耗額外的IO、網(wǎng)絡資源


可以通過設置binlog_row_p_w_picpath=minimal解決


測試：

binlog_row_p_w_picpath默認值是full

對user表進行update

進入binlog里面查看更新記錄，binlog日志將所有影響的行都進行了記錄

現(xiàn)在將binlog_row_p_w_picpath=minimal

對表中的行進行相同的update操作 再來觀察下binlog記錄

結論：可以對比發(fā)現(xiàn)當binlog_row_p_w_picpath=minimal的時候binlog只記錄了影響的那一行記錄，有效減少了binlog日志量。

數(shù)據(jù)庫版本：5.6.*

1.row日志p_w_picpath類型

參數(shù)binlog_row_p_w_picpath 控制著這種p_w_picpath類型，默認為FULL（log all columns），即記錄before&after p_w_picpaths。
該參數(shù)還有兩種，minimal和noblob，minimal表示只記錄after更改后的值，并且如果有主鍵或者非空唯一索引，則只以該字段作為where條件判斷；noblob同full，只是不記錄blob、text列。

2.binlog日志

對于insert則沒有什么好說的，我們主要重點關注一下update和delete操作。

binlog_row_p_w_picpath=full的情況下，對于update和delete所有的表（包含帶有主鍵、非空唯一索引，唯一索引，沒有索引）產(chǎn)生的binlog均一致，binlog情況如下：

1. --建表語句

2. CREATE TABLE `pk_test`(

3. `id` bigint(20) NOT NULL,

4. `username` varchar(30) NOT NULL,

5. PRIMARY KEY (`id`)

6. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

7. insert into pk_test values (1,2);

8. insert into pk_test values (2,2);

9. commit;

11. show master statusG;--記錄binlog文件和pos

13. deletefrom pk_test where id =1;

14. update pk_test set username='3';

15. commit；

16. mysqlbinlog --no-defaults -v --start-position=637945822/mysqllog/3307/binlog/mysql-bin.000001| more

18. ### DELETE FROM `baofeng`.`pk_test`

19. ### WHERE

20. ### @1=1

21. ### @2='2'

22. .....

23. ### UPDATE `baofeng`.`pk_test`

24. ### WHERE

25. ### @1=2

26. ### @2='2'

27. ### SET

28. ### @1=2

29. ### @2='3'

從上面我們可以看到，在默認為FULL的binlog_row_p_w_picpath下，無論表有沒有主鍵、唯一索引，全部按照全表字段作為條件，且update會更新全部字段。

binlog_row_p_w_picpath=minimal的情況下：

1. --建表語句

2. CREATE TABLE `ui_test`(

3. `id` bigint(20) NOT NULL,

4. `username` varchar(30) NOT NULL,

5. UNIQUE (`id`)

6. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

8. CREATE TABLE `ui_test_null`(

9. `id` bigint(20),

10. `username` varchar(30) NOT NULL,

11. UNIQUE key (`id`)

12. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

14. CREATE TABLE `null_test`(

15. `id` bigint(20),

16. `username` varchar(30) NOT NULL

17. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

19. insert into pk_test values (1,2);

20. insert into ui_test values (1,2);

21. insert into ui_test_null values (1,2);

22. insert into null_test values (1,2);

23. commit;

25. update pk_test set username='4';

26. deletefrom pk_test;

27. deletefrom ui_test;

28. deletefrom ui_test_null;

29. update null_test set username='4';

30. deletefrom null_test;

32. ### UPDATE `baofeng`.`pk_test`

33. ### WHERE

34. ### @1=1

35. ### SET

36. ### @2='4'

37. ....

38. ### DELETE FROM `baofeng`.`pk_test`

39. ### WHERE

40. ### @1=1

41. .....

42. ### DELETE FROM `baofeng`.`ui_test`

43. ### WHERE

44. ### @1=1

45. .....

46. ### DELETE FROM `baofeng`.`ui_test_null`

47. ### WHERE

48. ### @1=1

49. ### @2='2'

50. .....

51. ### UPDATE `baofeng`.`null_test`

52. ### WHERE

53. ### @1=1

54. ### @2='2'

55. ### SET

56. ### @2='4'

57. .....

58. ### DELETE FROM `baofeng`.`null_test`

59. ### WHERE

60. ### @1=1

61. ### @2='2'

從上面的例子可以看到，當binlog_row_p_w_picpath=minimal的情況下，where條件只有主鍵或不為空的唯一索引，且只會更新被改變的字段。

3.總結：

在上面的測試我們可以看到，如果采用minimal格式，將減少主鍵和非空唯一索引表的before值，以及減少所有表update的after未被改變的值。
從效率上來說，減少了網(wǎng)絡傳輸以及加快了update的效率。

參考資料：
https://dev.mysql.com/doc/refman/5.6/en/replication-options-binary-log.html#sysvar_binlog_row_p_w_picpath