load data語句如何保證主備複製資料一致性(一)

沃趣科技發表於2017-05-25
  • 背景

    • 機器配置
      * CPU:16 vcpus
      * 磁碟:100G flash卡 data&binlog混用
      * 記憶體:64G
    • 資料庫版本:MySQL 5.7.18
    • 資料庫IP資訊:主庫IP 10.10.30.241,從庫IP 10.10.30.250

    • 資料庫引數配置:
      * 主庫:雙一,log_slave_updates,log-bin,secure_file_priv='',server-id=3306241,binlog_rows_query_log_events=ON,sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'
      * 從庫:雙一,log_slave_updates,log-bin,binlog_rows_query_log_events=ON,server-id=3306250,sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'

    • 測試目的:透過示例演示並檢視過程中binlog中如何記錄load data語句等方式來驗證 load data語句如何保證主備複製資料一致性
      * 將分別在四種隔離級別下(會話級別修改tx_isolation)針對binlog_format(會話級別修改binlog_format)的三種格式分別進行演示執行load data語句,觀察MySQL server層如何處理,主庫binlog中如何記錄load data語句,備庫的binlog中如何如何記錄load data語句
      * 本文假定你已經搭建好了主備複製環境,如果未搭建請先自行搭建主備複製環境

    • PS:本文僅針對測試目的做驗證演示,關於binlog_format三種格式以及事務的四種隔離級別詳細說明請查閱相關資料,本文不做解讀
      * binlog_format參考資料:https://dev.mysql.com/doc/refman/5.7/en/replication-formats.html
      * 事務隔離級別參考資料:https://dev.mysql.com/doc/refman/5.7/en/innodb-transaction-isolation-levels.html

1、製造測試資料

  • 建庫
    1. admin@localhost : (none) 07:34:39> create database if not exists xiaoboluo;
    2. Query OK, 1 row affected (0.00 sec)
  • 建表
    1. admin@localhost : (none) 10:04:52> use xiaoboluo
    2. Database changed
    3. admin@localhost : xiaoboluo 10:04:57> create table if not exists test_load(id int unsigned not null primary key auto_increment,test varchar(100));
    4. Query OK, 0 rows affected (0.01 sec)
  • 插入測試資料
    1. admin@localhost : xiaoboluo 10:05:32> insert into test_load(test) values('1'),('2'),('null'),('4');
    2. Query OK, 4 rows affected (0.01 sec)
    3. Records: 4 Duplicates: 0 Warnings: 0
    4.  
    5.  
    6. admin@localhost : xiaoboluo 10:06:01> select * from test_load;
    7. +----+------+
    8. | id | test |
    9. +----+------+
    10. | 2 | 1 |
    11. | 4 | 2 |
    12. | 6 | null |
    13. | 8 | 4 |
    14. +----+------+
    15. 4 rows in set (0.00 sec)
  • 執行select …into outifile語句生成load data需要的資料文字檔案
    1. admin@localhost : xiaoboluo 10:12:41> select * from test_load into outfile "/tmp/test_load.txt";
    2. Query OK, 4 rows affected (0.01 sec)
    3.  
    4.  
    5. admin@localhost : xiaoboluo 10:12:42> system cat /tmp/test_load.txt;
    6. 2 1
    7. 4 2
    8. 6 null
    9. 8 4

2、read-uncommitted隔離級別

2.1. binlog_format=statement

  • 先在主庫中把binlog_format修改為statement,隔離級別修改為read-uncommitted,並重新整理主從庫binlog
    1. # 主庫
    2. admin@localhost : xiaoboluo 09:37:09> set binlog_format=statement;
    3. Query OK, 0 rows affected (0.00 sec)
    4.  
    5.  
    6. admin@localhost : xiaoboluo 09:39:24> set tx_isolation='read-uncommitted';
    7. Query OK, 0 rows affected (0.00 sec)
    8.  
    9.  
    10. admin@localhost : xiaoboluo 09:39:44> flush binary logs;
    11. Query OK, 0 rows affected (0.01 sec)
    12.  
    13.  
    14. # 從庫
    15. mysql> flush binary logs;
    16. Query OK, 0 rows affected (0.01 sec)
    17.  
    18.  
    19. mysql>
  • 主庫清空表test_load並執行load data語句
    1. admin@localhost : xiaoboluo 09:39:54> truncate test_load;
    2. Query OK, 0 rows affected (0.00 sec)
    3. admin@localhost : xiaoboluo 09:41:13> load data infile '/tmp/test_load.txt' into table test_load;
    4. ERROR 1665 (HY000): Cannot execute statement: impossible to write to binary log since BINLOG_FORMAT = STATEMENT and at least one table uses a storage engine limited to row-based logging. InnoDB is limited to row-logging when transaction isolation level is READ COMMITTED or READ UNCOMMITTED.
    5. Error (Code 1665): Cannot execute statement: impossible to write to binary log since BINLOG_FORMAT = STATEMENT and at least one table uses a storage engine limited to row-based logging. InnoDB is limited to row-logging when transaction isolation level is READ COMMITTED or READ UNCOMMITTED.
    6. Error (Code 1015): Can
  • 從以上結果中可以看到,在RU隔離級別下,binlog_format設定為statement不允許load data語句執行,從報錯資訊上可以看到,在RC和RU隔離級別下,binlog只能以row格式記錄,所以statement無法記錄binlog
  • 檢視主庫表test_load中的資料
    1. admin@localhost : xiaoboluo 09:41:52> select * from test_load;
    2. Empty set (0.00 sec)
  • 從以上結果中可以看到,表資料並沒有匯入成功,現在解析binlog檢視一下
    1. [root@5f1772e3-0c7a-4537-97f9-9b57cf6a04c2 binlog]# ll
    2. total 196
    3. -rw-r----- 1 mysql mysql 4916 May 4 00:11 mysql-bin.000014
    4.  
    5. -rw-r----- 1 mysql mysql 4557 May 4 00:12 mysql-bin.000015
    6. -rw-r----- 1 mysql mysql 5595 May 4 09:39 mysql-bin.000016
    7. -rw-r----- 1 mysql mysql 366 May 4 09:41 mysql-bin.000017
    8. -rw-r----- 1 mysql mysql 884 May 4 09:39 mysql-bin.index
    9. [root@5f1772e3-0c7a-4537-97f9-9b57cf6a04c2 binlog]# mysqlbinlog -vvv --base64-output=decode-rows mysql-bin.000017
    10. ......
    11. use `xiaoboluo`/*!*/;
    12. ......
    13. truncate test_load
    14. /*!*/;
    15. SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
    16. DELIMITER ;
    17. # End of log file
    18. ......
  • 從上面的結果中可以看到,解析binlog的結果中除了use db和truncate table語句之外,沒有其他的操作了,說明並沒有資料寫入,load data語句被mysql server直接拒絕執行了

2.2. binlog_format=mixed

  • 先在主庫中把binlog_format修改為mixed,隔離級別不要動,並重新整理主從庫binlog
    1. # 主庫
    2. admin@localhost : xiaoboluo 09:44:05> set binlog_format=mixed;
    3. Query OK, 0 rows affected (0.00 sec)
    4.  
    5.  
    6. admin@localhost : xiaoboluo 09:49:49> flush binary logs;
    7. Query OK, 0 rows affected (0.01 sec)
    8.  
    9.  
    10. # 從庫
    11. mysql> flush binary logs;
    12. Query OK, 0 rows affected (0.01 sec)
    13.  
    14.  
    15. mysql>
  • 主庫清空表test_load並執行load data語句
    1. admin@localhost : xiaoboluo 09:49:57> truncate test_load;
    2. Query OK, 0 rows affected (0.01 sec)
    3.  
    4.  
    5. admin@localhost : xiaoboluo 09:51:12> load data infile '/tmp/test_load.txt' into table test_load;
    6. Query OK, 4 rows affected (0.00 sec)
    7. Records: 4 Deleted: 0 Skipped: 0 Warnings: 0
  • 從以上結果中可以看到,在RU隔離級別下,binlog_format設定為mixed允許load data語句執行
  • 檢視主庫表test_load中的資料
    1. admin@localhost : xiaoboluo 09:51:24> select * from test_load;
    2. +----+------+
    3. | id | test |
    4. +----+------+
    5. | 2 | 1 |
    6. | 4 | 2 |
    7. | 6 | null |
    8. | 8 | 4 |
    9. +----+------+
    10. 4 rows in set (0.00 sec)
  • 從以上結果中可以看到,表資料匯入成功,現在解析binlog檢視一下
    1. [root@5f1772e3-0c7a-4537-97f9-9b57cf6a04c2 binlog]# ll
    2. total 196
    3. -rw-r----- 1 mysql mysql 4916 May 4 00:11 mysql-bin.000014
    4. -rw-r----- 1 mysql mysql 4557 May 4 00:12 mysql-bin.000015
    5. -rw-r----- 1 mysql mysql 5595 May 4 09:39 mysql-bin.000016
    6. -rw-r----- 1 mysql mysql 366 May 4 09:41 mysql-bin.000017
    7. -rw-r----- 1 mysql mysql 744 May 4 09:51 mysql-bin.000018
    8. -rw-r----- 1 mysql mysql 936 May 4 09:49 mysql-bin.index
    9. [root@5f1772e3-0c7a-4537-97f9-9b57cf6a04c2 binlog]# mysqlbinlog -vv --base64-output=decode-rows mysql-bin.000018
    10. # at 259
    11. #170504 9:51:12 server id 3306241 end_log_pos 354 CRC32 0xff83f0ce Query thread_id=155 exec_time=0 error_code=0
    12. use `xiaoboluo`/*!*/;
    13. ......
    14. truncate test_load
    15. ......
    16. BEGIN
    17. /*!*/;
    18. # at 501
    19. #170504 9:51:24 server id 3306241 end_log_pos 583 CRC32 0xfef1767a Rows_query
    20. # load data infile '/tmp/test_load.txt' into table test_load # 這裡可以看到load data語句在這裡,這裡是開啟了引數binlog_rows_query_log_events=ON之後記錄的原生sql,預設被加了註釋,主備複製不會執行
    21. # at 583
    22. #170504 9:51:24 server id 3306241 end_log_pos 643 CRC32 0x349b62a4 Table_map: `xiaoboluo`.`test_load` mapped to number 340
    23. # at 643
    24. #170504 9:51:24 server id 3306241 end_log_pos 713 CRC32 0x4ceacc10 Write_rows: table id 340 flags: STMT_END_F
    25. ### INSERT INTO `xiaoboluo`.`test_load` #這裡是用於主備複製的資料變更日誌,可以看到在mixed格式下被轉換為了row格式
    26. ### SET
    27. ### @1=2 /* INT meta=0 nullable=0 is_null=0 */
    28. ### @2='1' /* VARSTRING(300) meta=300 nullable=1 is_null=0 */
    29. ### INSERT INTO `xiaoboluo`.`test_load`
    30. ### SET
    31. ### @1=4 /* INT meta=0 nullable=0 is_null=0 */
    32. ### @2='2' /* VARSTRING(300) meta=300 nullable=1 is_null=0 */
    33. ### INSERT INTO `xiaoboluo`.`test_load`
    34. ### SET
    35. ### @1=6 /* INT meta=0 nullable=0 is_null=0 */
    36. ### @2='null' /* VARSTRING(300) meta=300 nullable=1 is_null=0 */
    37. ### INSERT INTO `xiaoboluo`.`test_load`
    38. ### SET
    39. ### @1=8 /* INT meta=0 nullable=0 is_null=0 */
    40. ### @2='4' /* VARSTRING(300) meta=300 nullable=1 is_null=0 */
    41. # at 713
    42. #170504 9:51:24 server id 3306241 end_log_pos 744 CRC32 0xfa76965b Xid = 1237
    43. COMMIT/*!*/;
    44. SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
    45. DELIMITER ;
    46. # End of log file
    47. /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
    48. /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/
  • 從上面解析binlog的結果中可以看到,資料寫入成功,load data語句被轉換為row格式記錄在binlog中

  • 檢視備庫中資料是否正確複製

    1. mysql> use xiaoboluo
    2. Reading table information for completion of table and column names
    3. You can turn off this feature to get a quicker startup with -A
    4.  
    5.  
    6. Database changed
    7. mysql> select * from test_load;
    8. +----+------+
    9. | id | test |
    10. +----+------+
    11. | 2 | 1 |
    12. | 4 | 2 |
    13. | 6 | null |
    14. | 8 | 4 |
    15. +----+------+
    16. 4 rows in set (0.00 sec)
  • 解析備庫binlog中是如何記錄的load data語句
    1. [root@e710d318-d5b4-4bc7-a606-d09f06ff5f5d binlog]# ll
    2. total 100
    3. -rw-r----- 1 mysql mysql 990 May 1 01:36 mysql-bin.000001
    4. -rw-r----- 1 mysql mysql 54766 May 4 00:02 mysql-bin.000002
    5. -rw-r----- 1 mysql mysql 21376 May 4 09:40 mysql-bin.000003
    6. -rw-r----- 1 mysql mysql 401 May 4 09:50 mysql-bin.000004
    7. -rw-r----- 1 mysql mysql 730 May 4 09:51 mysql-bin.000005
    8. -rw-r----- 1 mysql mysql 260 May 4 09:50 mysql-bin.index
    9. [root@e710d318-d5b4-4bc7-a606-d09f06ff5f5d binlog]# mysqlbinlog -vv --base64-output=decode-rows mysql-bin.000005
    10. ...
    11. #170504 9:51:12 server id 3306241 end_log_pos 354 CRC32 0xff83f0ce Query thread_id=155 exec_time=0 error_code=0
    12. use `xiaoboluo`/*!*/;
    13. ...
    14. truncate test_load
    15. ...
    16. BEGIN
    17. /*!*/;
    18. # at 487
    19. #170504 9:51:24 server id 3306241 end_log_pos 569 CRC32 0x67a31998 Rows_query
    20. # load data infile '/tmp/test_load.txt' into table test_load # 這裡可以看到load data語句在這裡,這裡是開啟了引數binlog_rows_query_log_events=ON之後記錄的原生sql,預設被加了註釋,主備複製不會執行
    21. # at 569
    22. #170504 9:51:24 server id 3306241 end_log_pos 629 CRC32 0x6420d19b Table_map: `xiaoboluo`.`test_load` mapped to number 301
    23. # at 629
    24. #170504 9:51:24 server id 3306241 end_log_pos 699 CRC32 0xf5fff0d8 Write_rows: table id 301 flags: STMT_END_F
    25. ### INSERT INTO `xiaoboluo`.`test_load` #這裡是備庫記錄的主庫binlog日誌,可以看到在mixed格式下被轉換為了row格式
    26. ### SET
    27. ### @1=2 /* INT meta=0 nullable=0 is_null=0 */
    28. ### @2='1' /* VARSTRING(300) meta=300 nullable=1 is_null=0 */
    29. ### INSERT INTO `xiaoboluo`.`test_load`
    30. ### SET
    31. ### @1=4 /* INT meta=0 nullable=0 is_null=0 */
    32. ### @2='2' /* VARSTRING(300) meta=300 nullable=1 is_null=0 */
    33. ### INSERT INTO `xiaoboluo`.`test_load`
    34. ### SET
    35. ### @1=6 /* INT meta=0 nullable=0 is_null=0 */
    36. ### @2='null' /* VARSTRING(300) meta=300 nullable=1 is_null=0 */
    37. ### INSERT INTO `xiaoboluo`.`test_load`
    38. ### SET
    39. ### @1=8 /* INT meta=0 nullable=0 is_null=0 */
    40. ### @2='4' /* VARSTRING(300) meta=300 nullable=1 is_null=0 */
    41. # at 699
    42. #170504 9:51:24 server id 3306241 end_log_pos 730 CRC32 0xd5a3bda4 Xid = 424
    43. COMMIT/*!*/;
    44. ...

2.3. binlog_format=row

  • 先在主庫中把binlog_format修改為row,隔離級別不要動,並重新整理主從庫binlog
    1. # 主庫
    2. admin@localhost : xiaoboluo 11:05:20> set binlog_format=row;
    3. Query OK, 0 rows affected (0.00 sec)
    4. admin@localhost : xiaoboluo 11:18:23> flush binary logs;
    5. Query OK, 0 rows affected (0.01 sec)
    6. # 從庫
    7. mysql> flush binary logs;
    8. Query OK, 0 rows affected (0.01 sec)
    9. mysql>
  • 主庫清空表test_load並執行load data語句
    1. admin@localhost : xiaoboluo 11:18:26> truncate test_load;
    2. Query OK, 0 rows affected (0.01 sec)
    3. admin@localhost : xiaoboluo 11:18:51> load data infile '/tmp/test_load.txt' into table test_load;
    4. Query OK, 4 rows affected (0.00 sec)
    5. Records: 4 Deleted: 0 Skipped: 0 Warnings: 0
  • 從以上結果中可以看到,在RU隔離級別下,binlog_format設定為row允許load data語句執行
  • 檢視主庫表test_load中的資料
    1. admin@localhost : xiaoboluo 11:18:57> select * from test_load;
    2. +----+------+
    3. | id | test |
    4. +----+------+
    5. | 2 | 1 |
    6. | 4 | 2 |
    7. | 6 | null |
    8. | 8 | 4 |
    9. +----+------+
    10. 4 rows in set (0.00 sec)
  • 從以上結果中可以看到,表資料匯入成功,現在解析binlog檢視一下
    1. [root@5f1772e3-0c7a-4537-97f9-9b57cf6a04c2 binlog]# ll
    2. total 196
    3. -rw-r----- 1 mysql mysql 4916 May 4 00:11 mysql-bin.000014
    4. -rw-r----- 1 mysql mysql 4557 May 4 00:12 mysql-bin.000015
    5. -rw-r----- 1 mysql mysql 5595 May 4 09:39 mysql-bin.000016
    6. -rw-r----- 1 mysql mysql 366 May 4 09:41 mysql-bin.000017
    7. -rw-r----- 1 mysql mysql 744 May 4 09:51 mysql-bin.000018
    8. -rw-r----- 1 mysql mysql 744 May 4 11:18 mysql-bin.000019
    9. -rw-r----- 1 mysql mysql 988 May 4 11:18 mysql-bin.index
    10. [root@5f1772e3-0c7a-4537-97f9-9b57cf6a04c2 binlog]# mysqlbinlog -vv --base64-output=decode-rows mysql-bin.000018
    11. # at 259
    12. #170504 11:18:51 server id 3306241 end_log_pos 354 CRC32 0x9d5985ad Query thread_id=155 exec_time=0 error_code=0
    13. use `xiaoboluo`/*!*/;
    14. ......
    15. truncate test_load
    16. ......
    17. BEGIN
    18. /*!*/;
    19. # at 501
    20. #170504 11:18:57 server id 3306241 end_log_pos 583 CRC32 0x0e8f7603 Rows_query
    21. # load data infile '/tmp/test_load.txt' into table test_load # 這裡可以看到load data語句在這裡,這裡是開啟了引數binlog_rows_query_log_events=ON之後記錄的原生sql,預設被加了註釋,主備複製不會執行
    22. # at 583
    23. #170504 11:18:57 server id 3306241 end_log_pos 643 CRC32 0xe1c5d4d0 Table_map: `xiaoboluo`.`test_load` mapped to number 341
    24. # at 643
    25. #170504 11:18:57 server id 3306241 end_log_pos 713 CRC32 0x31619df3 Write_rows: table id 341 flags: STMT_END_F
    26. ### INSERT INTO `xiaoboluo`.`test_load` #這裡是用於主備複製的資料變更日誌,可以看到在row格式下被轉換為了row格式
    27. ### SET
    28. ### @1=2 /* INT meta=0 nullable=0 is_null=0 */
    29. ### @2='1' /* VARSTRING(300) meta=300 nullable=1 is_null=0 */
    30. ### INSERT INTO `xiaoboluo`.`test_load`
    31. ### SET
    32. ### @1=4 /* INT meta=0 nullable=0 is_null=0 */
    33. ### @2='2' /* VARSTRING(300) meta=300 nullable=1 is_null=0 */
    34. ### INSERT INTO `xiaoboluo`.`test_load`
    35. ### SET
    36. ### @1=6 /* INT meta=0 nullable=0 is_null=0 */
    37. ### @2='null' /* VARSTRING(300) meta=300 nullable=1 is_null=0 */
    38. ### INSERT INTO `xiaoboluo`.`test_load`
    39. ### SET
    40. ### @1=8 /* INT meta=0 nullable=0 is_null=0 */
    41. ### @2='4' /* VARSTRING(300) meta=300 nullable=1 is_null=0 */
    42. # at 713
    43. #170504 11:18:57 server id 3306241 end_log_pos 744 CRC32 0x18b14b52 Xid = 1245
    44. COMMIT/*!*/;
    45. ......
  • 從上面解析binlog的結果中可以看到,資料寫入成功,load data語句被轉換為row格式記錄在binlog中

  • 檢視備庫中資料是否正確複製

    1. mysql> use xiaoboluo
    2. Database changed
    3. mysql> select * from test_load;
    4. +----+------+
    5. | id | test |
    6. +----+------+
    7. | 2 | 1 |
    8. | 4 | 2 |
    9. | 6 | null |
    10. | 8 | 4 |
    11. +----+------+
    12. 4 rows in set (0.00 sec)
    1. [root@5f1772e3-0c7a-4537-97f9-9b57cf6a04c2 binlog]# ll
    2. total 196
    3. -rw-r----- 1 mysql mysql 4916 May 4 00:11 mysql-bin.000014
    4.  
    5. -rw-r----- 1 mysql mysql 4557 May 4 00:12 mysql-bin.000015
    6. -rw-r----- 1 mysql mysql 5595 May 4 09:39 mysql-bin.000016
    7. -rw-r----- 1 mysql mysql 366 May 4 09:41 mysql-bin.000017
    8. -rw-r----- 1 mysql mysql 744 May 4 09:51 mysql-bin.000018
    9.  
    10. -rw-r----- 1 mysql mysql 744 May 4 11:18 mysql-bin.000019
    11. -rw-r----- 1 mysql mysql 988 May 4 11:18 mysql-bin.index
    12.  
    13.  
    14. [root@5f1772e3-0c7a-4537-97f9-9b57cf6a04c2 binlog]# mysqlbinlog -vv --base64-output=decode-rows mysql-bin.000018
    15. # at 259
    16. #170504 11:18:51 server id 3306241 end_log_pos 354 CRC32 0x9d5985ad Query thread_id=155 exec_time=0 error_code=0
    17. use `xiaoboluo`/*!*/;
    18. ......
    19. truncate test_load
    20. ......
    21. BEGIN
    22. /*!*/;
    23. # at 501
    24. #170504 11:18:57 server id 3306241 end_log_pos 583 CRC32 0x0e8f7603 Rows_query
    25. # load data infile '/tmp/test_load.txt' into table test_load # 這裡可以看到load data語句在這裡,這裡是開啟了引數binlog_rows_query_log_events=ON之後記錄的原生sql,預設被加了註釋,主備複製不會執行
    26. # at 583
    27. #170504 11:18:57 server id 3306241 end_log_pos 643 CRC32 0xe1c5d4d0 Table_map: `xiaoboluo`.`test_load` mapped to number 341
    28. # at 643
    29. #170504 11:18:57 server id 3306241 end_log_pos 713 CRC32 0x31619df3 Write_rows: table id 341 flags: STMT_END_F
    30. ### INSERT INTO `xiaoboluo`.`test_load` #這裡是用於主備複製的資料變更日誌,可以看到在row格式下被轉換為了row格式
    31. ### SET
    32. ### @1=2 /* INT meta=0 nullable=0 is_null=0 */
    33. ### @2='1' /* VARSTRING(300) meta=300 nullable=1 is_null=0 */
    34. ### INSERT INTO `xiaoboluo`.`test_load`
    35. ### SET
    36. ### @1=4 /* INT meta=0 nullable=0 is_null=0 */
    37. ### @2='2' /* VARSTRING(300) meta=300 nullable=1 is_null=0 */
    38. ### INSERT INTO `xiaoboluo`.`test_load`
    39. ### SET
    40. ### @1=6 /* INT meta=0 nullable=0 is_null=0 */
    41. ### @2='null' /* VARSTRING(300) meta=300 nullable=1 is_null=0 */
    42. ### INSERT INTO `xiaoboluo`.`test_load`
    43. ### SET
    44. ### @1=8 /* INT meta=0 nullable=0 is_null=0 */
    45. ### @2='4' /* VARSTRING(300) meta=300 nullable=1 is_null=0 */
    46. # at 713
    47. #170504 11:18:57 server id 3306241 end_log_pos 744 CRC32 0x18b14b52 Xid = 1245
    48. COMMIT/*!*/;
    49. ......
  • 解析備庫binlog中是如何記錄的load data語句
    1. [root@e710d318-d5b4-4bc7-a606-d09f06ff5f5d binlog]# ll
    2. total 100
    3. -rw-r----- 1 mysql mysql 990 May 1 01:36 mysql-bin.000001
    4. -rw-r----- 1 mysql mysql 54766 May 4 00:02 mysql-bin.000002
    5. -rw-r----- 1 mysql mysql 21376 May 4 09:40 mysql-bin.000003
    6. -rw-r----- 1 mysql mysql 401 May 4 09:50 mysql-bin.000004
    7. -rw-r----- 1 mysql mysql 730 May 4 09:51 mysql-bin.000005
    8. -rw-r----- 1 mysql mysql 730 May 4 11:18 mysql-bin.000006
    9. -rw-r----- 1 mysql mysql 312 May 4 11:18 mysql-bin.index
    10. [root@e710d318-d5b4-4bc7-a606-d09f06ff5f5d binlog]# mysqlbinlog -vv --base64-output=decode-rows mysql-bin.000009
    11. ......
    12. # at 259
    13. #170504 11:18:51 server id 3306241 end_log_pos 354 CRC32 0x9d5985ad Query thread_id=155 exec_time=0 error_code=0
    14. use `xiaoboluo`/*!*/;
    15. ......
    16. truncate test_load
    17. ......
    18. # at 487
    19. #170504 11:18:57 server id 3306241 end_log_pos 569 CRC32 0x97dd19e1 Rows_query
    20. # load data infile '/tmp/test_load.txt' into table test_load # 這裡可以看到load data語句在這裡,這裡是開啟了引數binlog_rows_query_log_events=ON之後記錄的原生sql,預設被加了註釋,主備複製不會執行
    21. # at 569
    22. #170504 11:18:57 server id 3306241 end_log_pos 629 CRC32 0x6bef4f90 Table_map: `xiaoboluo`.`test_load` mapped to number 302
    23. # at 629
    24. #170504 11:18:57 server id 3306241 end_log_pos 699 CRC32 0x5a86b7f1 Write_rows: table id 302 flags: STMT_END_F
    25. ### INSERT INTO `xiaoboluo`.`test_load` #這裡是備庫記錄的主庫binlog日誌,可以看到在row格式下被轉換為了row格式,與mixed格式下記錄的內容相同
    26. ### SET
    27. ### @1=2 /* INT meta=0 nullable=0 is_null=0 */
    28. ### @2='1' /* VARSTRING(300) meta=300 nullable=1 is_null=0 */
    29. ### INSERT INTO `xiaoboluo`.`test_load`
    30. ### SET
    31. ### @1=4 /* INT meta=0 nullable=0 is_null=0 */
    32. ### @2='2' /* VARSTRING(300) meta=300 nullable=1 is_null=0 */
    33. ### INSERT INTO `xiaoboluo`.`test_load`
    34. ### SET
    35. ### @1=6 /* INT meta=0 nullable=0 is_null=0 */
    36. ### @2='null' /* VARSTRING(300) meta=300 nullable=1 is_null=0 */
    37. ### INSERT INTO `xiaoboluo`.`test_load`
    38. ### SET
    39. ### @1=8 /* INT meta=0 nullable=0 is_null=0 */
    40. ### @2='4' /* VARSTRING(300) meta=300 nullable=1 is_null=0 */
    41. # at 699
    42. #170504 11:18:57 server id 3306241 end_log_pos 730 CRC32 0x983a4a33 Xid = 440
    43. COMMIT/*!*/;
    44. ......

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

相關文章