load data方式導入的數據不可以用binlog日志進行恢復，因為binlog裡面不產生insert sql語句 QQ群裡面有人問起這個問題: 用load data 導入數據的時候，在binlog文件中記錄的不是insert 語句,這樣的話，如果用load data 導入數據，當需要恢復數據庫的時候 binlog恢復就不行了 load data local infile '/root/table.txt' into table test.table ; 生成binlog 日志 然後用mysqlbinlog解析binlog日志，再more查看如下： # at 147405 #130718 3:15:02 server id 1 end_log_pos 147710 Query thread_id=97960 exec_time=0 error_code=0 use test/*!*/; SET TIMESTAMP=1374117302/*!*/; SET @@session.sql_mode=0/*!*/; /*!\C utf8 *//*!*/; SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/; create table `test`.`table`( `c1` varchar(20) , `c2` varchar(20) , `c3` varchar(20) , `c4` varchar(20) , `c5` varchar(20) , `c6` varchar(20) , `c7` varchar(20) , `c8` varchar(20) , `c9` varchar(20) ) /*!*/; # at 147710 #130718 3:17:14 server id 1 end_log_pos 147783 Query thread_id=98119 exec_time=2 error_code=0 SET TIMESTAMP=1374117434/*!*/; SET @@session.sql_mode=4194304/*!*/; BEGIN /*!*/; # at 147783 #130718 3:17:14 server id 1 end_log_pos 164190 #Begin_load_query: file_id: 1 block_len: 16384 # at 164190 #130718 3:17:14 server id 1 end_log_pos 180597 #Append_block: file_id: 1 block_len: 16384 # at 180597 #130718 3:17:14 server id 1 end_log_pos 197004 #Append_block: file_id: 1 block_len: 16384 # at 197004 #130718 3:17:14 server id 1 end_log_pos 213411 #Append_block: file_id: 1 block_len: 16384 # at 213411 #130718 3:17:14 server id 1 end_log_pos 229818 #Append_block: file_id: 1 block_len: 16384 # at 229818 #130718 3:17:14 server id 1 end_log_pos 246225 #Append_block: file_id: 1 block_len: 16384 # at 246225 #130718 3:17:14 server id 1 end_log_pos 262632 #Append_block: file_id: 1 block_len: 16384 # at 262632 #130718 3:17:14 server id 1 end_log_pos 279039 #Append_block: file_id: 1 block_len: 16384 # at 279039 #130718 3:17:14 server id 1 end_log_pos 295446 #Append_block: file_id: 1 block_len: 16384 # at 295446 #130718 3:17:14 server id 1 end_log_pos 311853 #Append_block: file_id: 1 block_len: 16384 # at 311853 #130718 3:17:14 server id 1 end_log_pos 328260 #Append_block: file_id: 1 block_len: 16384 # at 328260 #130718 3:17:14 server id 1 end_log_pos 344667 #Append_block: file_id: 1 block_len: 16384 # at 344667 #130718 3:17:14 server id 1 end_log_pos 361074 #Append_block: file_id: 1 block_len: 16384 # at 361074 #130718 3:17:14 server id 1 end_log_pos 377481 #Append_block: file_id: 1 block_len: 16384 # at 377481 #130718 3:17:14 server id 1 end_log_pos 393888 #Append_block: file_id: 1 block_len: 16384 # at 393888 #130718 3:17:14 server id 1 end_log_pos 410295 #Append_block: file_id: 1 block_len: 16384 # at 410295 #130718 3:17:14 server id 1 end_log_pos 426702 #Append_block: file_id: 1 block_len: 16384 # at 426702 #130718 3:17:14 server id 1 end_log_pos 443109 #Append_block: file_id: 1 block_len: 16384 # at 443109 #130718 3:17:14 server id 1 end_log_pos 459516 #Append_block: file_id: 1 block_len: 16384 # at 459516 #130718 3:17:14 server id 1 end_log_pos 475923 #Append_block: file_id: 1 block_len: 16384 # at 475923 #130718 3:17:14 server id 1 end_log_pos 492330 #Append_block: file_id: 1 block_len: 16384 # at 492330 #130718 3:17:14 server id 1 end_log_pos 508737 #Append_block: file_id: 1 block_len: 16384 # at 508737 #130718 3:17:14 server id 1 end_log_pos 525144 #Append_block: file_id: 1 block_len: 16384 # at 525144 #130718 3:17:14 server id 1 end_log_pos 541551 #Append_block: file_id: 1 block_len: 16384 # at 541551 #130718 3:17:14 server id 1 end_log_pos 557958 #Append_block: file_id: 1 block_len: 16384 # at 557958 #130718 3:17:14 server id 1 end_log_pos 574365 #Append_block: file_id: 1 block_len: 16384 # at 574365 #130718 3:17:14 server id 1 end_log_pos 590772 #Append_block: file_id: 1 block_len: 16384 # at 590772 #130718 3:17:14 server id 1 end_log_pos 607179 #Append_block: file_id: 1 block_len: 16384 # at 607179 #130718 3:17:14 server id 1 end_log_pos 623586 #Append_block: file_id: 1 block_len: 16384 # at 623586 #130718 3:17:14 server id 1 end_log_pos 639993 #Append_block: file_id: 1 block_len: 16384 # at 639993 #130718 3:17:14 server id 1 end_log_pos 656400 #Append_block: file_id: 1 block_len: 16384 # at 656400 #130718 3:17:14 server id 1 end_log_pos 672807 #Append_block: file_id: 1 block_len: 16384 # at 672807 #130718 3:17:14 server id 1 end_log_pos 689214 #Append_block: file_id: 1 block_len: 16384 # at 689214 #130718 3:17:14 server id 1 end_log_pos 705621 #Append_block: file_id: 1 block_len: 16384 # at 705621 #130718 3:17:14 server id 1 end_log_pos 722028 #Append_block: file_id: 1 block_len: 16384 # at 722028 #130718 3:17:14 server id 1 end_log_pos 738435 #Append_block: file_id: 1 block_len: 16384 # at 738435 #130718 3:17:14 server id 1 end_log_pos 754842 #Append_block: file_id: 1 block_len: 16384 # at 754842 #130718 3:17:14 server id 1 end_log_pos 771249 #Append_block: file_id: 1 block_len: 16384 # at 771249 #130718 3:17:14 server id 1 end_log_pos 787656 #Append_block: file_id: 1 block_len: 16384 # at 787656 #130718 3:17:14 server id 1 end_log_pos 804063 #Append_block: file_id: 1 block_len: 16384 # at 804063 #130718 3:17:14 server id 1 end_log_pos 820470 #Append_block: file_id: 1 block_len: 16384 # at 820470 #130718 3:17:14 server id 1 end_log_pos 836877 #Append_block: file_id: 1 block_len: 16384 # at 836877 #130718 3:17:14 server id 1 end_log_pos 853284 #Append_block: file_id: 1 block_len: 16384 # at 853284 #130718 3:17:14 server id 1 end_log_pos 869691 #Append_block: file_id: 1 block_len: 16384 # at 869691 #130718 3:17:14 server id 1 end_log_pos 886098 #Append_block: file_id: 1 block_len: 16384 # at 886098 #130718 3:17:14 server id 1 end_log_pos 902505 #Append_block: file_id: 1 block_len: 16384 # at 902505 #130718 3:17:14 server id 1 end_log_pos 918912 #Append_block: file_id: 1 block_len: 16384 # at 918912 #130718 3:17:14 server id 1 end_log_pos 935319 #Append_block: file_id: 1 block_len: 16384 # at 935319 #130718 3:17:14 server id 1 end_log_pos 951726 #Append_block: file_id: 1 block_len: 16384 # at 951726 #130718 3:17:14 server id 1 end_log_pos 968133 #Append_block: file_id: 1 block_len: 16384 # at 968133 #130718 3:17:14 server id 1 end_log_pos 984540 #Append_block: file_id: 1 block_len: 16384 # at 984540 #130718 3:17:14 server id 1 end_log_pos 1000947 #Append_block: file_id: 1 block_len: 16384 # at 1000947 #130718 3:17:14 server id 1 end_log_pos 1017354 #Append_block: file_id: 1 block_len: 16384 # at 1017354 #130718 3:17:14 server id 1 end_log_pos 1033761 #Append_block: file_id: 1 block_len: 16384 # at 1033761 #130718 3:17:14 server id 1 end_log_pos 1050168 #Append_block: file_id: 1 block_len: 16384 # at 1050168 #130718 3:17:14 server id 1 end_log_pos 1066575 #Append_block: file_id: 1 block_len: 16384 # at 1066575 #130718 3:17:14 server id 1 end_log_pos 1082982 #Append_block: file_id: 1 block_len: 16384 # at 1082982 #130718 3:17:14 server id 1 end_log_pos 1099389 #Append_block: file_id: 1 block_len: 16384 # at 1099389 #130718 3:17:14 server id 1 end_log_pos 1115796 #Append_block: file_id: 1 block_len: 16384 # at 1115796 #130718 3:17:14 server id 1 end_log_pos 1132203 #Append_block: file_id: 1 block_len: 16384 # at 1132203 #130718 3:17:14 server id 1 end_log_pos 1148610 #Append_block: file_id: 1 block_len: 16384 # at 1148610 #130718 3:17:14 server id 1 end_log_pos 1165017 #Append_block: file_id: 1 block_len: 16384 # at 1165017 #130718 3:17:14 server id 1 end_log_pos 1181424 #Append_block: file_id: 1 block_len: 16384 # at 1181424 #130718 3:17:14 server id 1 end_log_pos 1197831 #Append_block: file_id: 1 block_len: 16384 # at 1197831 #130718 3:17:14 server id 1 end_log_pos 1214238 #Append_block: file_id: 1 block_len: 16384 # at 1214238 #130718 3:17:14 server id 1 end_log_pos 1230645 #Append_block: file_id: 1 block_len: 16384 # at 1230645 #130718 3:17:14 server id 1 end_log_pos 1247052 #Append_block: file_id: 1 block_len: 16384 # at 1247052 #130718 3:17:14 server id 1 end_log_pos 1263459 #Append_block: file_id: 1 block_len: 16384 # at 1263459 #130718 3:17:14 server id 1 end_log_pos 1279866 #Append_block: file_id: 1 block_len: 16384 # at 1279866 #130718 3:17:14 server id 1 end_log_pos 1296273 #Append_block: file_id: 1 block_len: 16384 # at 1296273 #130718 3:17:14 server id 1 end_log_pos 1312680 #Append_block: file_id: 1 block_len: 16384 # at 1312680 #130718 3:17:14 server id 1 end_log_pos 1329087 #Append_block: file_id: 1 block_len: 16384 # at 1329087 #130718 3:17:14 server id 1 end_log_pos 1345494 ......... #Append_block: file_id: 1 block_len: 16384 # at 4298754 #130718 3:17:14 server id 1 end_log_pos 4315161 #Append_block: file_id: 1 block_len: 16384 # at 4315161 #130718 3:17:14 server id 1 end_log_pos 4331568 #Append_block: file_id: 1 block_len: 16384 # at 4331568 #130718 3:17:14 server id 1 end_log_pos 4347975 #Append_block: file_id: 1 block_len: 16384 # at 4347975 #130718 3:17:14 server id 1 end_log_pos 4364382 #Append_block: file_id: 1 block_len: 16384 # at 4364382 #130718 3:17:14 server id 1 end_log_pos 4380789 #Append_block: file_id: 1 block_len: 16384 # at 4380789 #130718 3:17:14 server id 1 end_log_pos 4397196 #Append_block: file_id: 1 block_len: 16384 # at 4397196 #130718 3:17:14 server id 1 end_log_pos 4413603 #Append_block: file_id: 1 block_len: 16384 # at 4413603 #130718 3:17:14 server id 1 end_log_pos 4430010 #Append_block: file_id: 1 block_len: 16384 # at 4430010 #130718 3:17:14 server id 1 end_log_pos 4446417 #Append_block: file_id: 1 block_len: 16384 # at 4446417 #130718 3:17:14 server id 1 end_log_pos 4462824 #Append_block: file_id: 1 block_len: 16384 # at 4462824 #130718 3:17:14 server id 1 end_log_pos 4479231 #Append_block: file_id: 1 block_len: 16384 # at 4479231 #130718 3:17:14 server id 1 end_log_pos 4495638 #Append_block: file_id: 1 block_len: 16384 # at 4495638 #130718 3:17:14 server id 1 end_log_pos 4512045 #Append_block: file_id: 1 block_len: 16384 # at 4512045 #130718 3:17:14 server id 1 end_log_pos 4528452 #Append_block: file_id: 1 block_len: 16384 # at 4528452 #130718 3:17:14 server id 1 end_log_pos 4544859 #Append_block: file_id: 1 block_len: 16384 # at 4544859 #130718 3:17:14 server id 1 end_log_pos 4561266 #Append_block: file_id: 1 block_len: 16384 # at 4561266 #130718 3:17:14 server id 1 end_log_pos 4577673 #Append_block: file_id: 1 block_len: 16384 # at 4577673 #130718 3:17:14 server id 1 end_log_pos 4594080 #Append_block: file_id: 1 block_len: 16384 # at 4594080 #130718 3:17:14 server id 1 end_log_pos 4610487 #Append_block: file_id: 1 block_len: 16384 # at 4610487 #130718 3:17:14 server id 1 end_log_pos 4626894 #Append_block: file_id: 1 block_len: 16384 # at 4626894 #130718 3:17:14 server id 1 end_log_pos 4643301 #Append_block: file_id: 1 block_len: 16384 # at 4643301 #130718 3:17:14 server id 1 end_log_pos 4659708 #Append_block: file_id: 1 block_len: 16384 # at 4659708 #130718 3:17:14 server id 1 end_log_pos 4676115 #Append_block: file_id: 1 block_len: 16384 # at 4676115 #130718 3:17:14 server id 1 end_log_pos 4692522 #Append_block: file_id: 1 block_len: 16384 # at 4692522 #130718 3:17:14 server id 1 end_log_pos 4708929 #Append_block: file_id: 1 block_len: 16384 # at 4708929 #130718 3:17:14 server id 1 end_log_pos 4725336 #Append_block: file_id: 1 block_len: 16384 # at 4725336 #130718 3:17:14 server id 1 end_log_pos 4735417 #Append_block: file_id: 1 block_len: 10058 # at 4735417 #130718 3:17:14 server id 1 end_log_pos 4735701 Execute_load_query thread_id=98119 exec_time=2 error_code=0 SET TIMESTAMP=1374117434/*!*/; LOAD DATA LOCAL INFILE '/tmp/SQL_LOAD_MB-1-0' INTO TABLE `table` FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\' LINES TERMINATED BY '\n' (`c1`, `c2`, `c3`, ` c4`, `c5`, `c6`, `c7`, `c8`, `c9`) /*!*/; # file_id: 1 # at 4735701 #130718 3:17:14 server id 1 end_log_pos 4735728 Xid = 297745 COMMIT/*!*/; DELIMITER ; # End of log file ROLLBACK /* added by mysqlbinlog */; /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; 確實沒有insert之類的sql語句，當用binlog日志進行恢復的時候，會報錯的，所以在生產環境上面，最好不要使用load data的方式進行數據導入。以免需要及時恢復數據的時候發生異常。 -- 察看binlog事件,會看到最後的load data local infile紀錄. [sql] view plaincopyprint? mysql> show binlog events in "mysql-bin.000001"; ...... | mysql-bin.000001 | 4708929 | Append_block | 1 | 4725336 | ;file_id=1;block_len=16384 | | mysql-bin.000001 | 4725336 | Append_block | 1 | 4735417 | ;file_id=1;block_len=10058 | | mysql-bin.000001 | 4735417 | Execute_load_query | 1 | 4735701 | use `test`; LOAD DATA LOCAL INFILE '/root/table.txt' IGNORE INTO TABLE `table` FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\' LINES TERMINATED BY '\n' (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`, `c7`, `c8`, `c9`) ;file_id=1 | | mysql-bin.000001 | 4735701 | Xid | 1 | 4735728 | COMMIT /* xid=297745 */ -- 再察看從庫數據也都同步復制過去了,再看從庫的binlog日志 # at 31990800 #130718 11:15:02 server id 1 end_log_pos 31991105 Query thread_id=97960 exec_time=4294967292 error_code=0 use test/*!*/; SET TIMESTAMP=1374117302/*!*/; SET @@session.sql_mode=0/*!*/; /*!\C utf8 *//*!*/; SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/; create table `test`.`table`( `c1` varchar(20) , `c2` varchar(20) , `c3` varchar(20) , `c4` varchar(20) , `c5` varchar(20) , `c6` varchar(20) , `c7` varchar(20) , `c8` varchar(20) , `c9` varchar(20) ) /*!*/; # at 31991105 #130718 11:17:14 server id 1 end_log_pos 31991169 Query thread_id=98119 exec_time=4 error_code=0 SET TIMESTAMP=1374117434/*!*/; SET @@session.sql_mode=4194304/*!*/; BEGIN /*!*/; # at 31991169 #130718 11:17:14 server id 1 end_log_pos 34088344 #Begin_load_query: file_id: 1 block_len: 2097152 # at 34088344 #130718 11:17:14 server id 1 end_log_pos 36185519 #Append_block: file_id: 1 block_len: 2097152 # at 36185519 #130718 11:17:14 server id 1 end_log_pos 36572432 #Append_block: file_id: 1 block_len: 386890 # at 36572432 #130718 11:17:14 server id 1 end_log_pos 36572723 Execute_load_query thread_id=98119 exec_time=4 error_code=0 SET TIMESTAMP=1374117434/*!*/; LOAD DATA LOCAL INFILE '/tmp/SQL_LOAD_MB-1-0' INTO TABLE `table` FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\' LINES TERMINATED BY '\n' (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`, `c7`, `c8`, `c9`) /*!*/; # file_id: 1 # at 36572723 #130718 11:17:14 server id 1 end_log_pos 36572750 Xid = 839475877 COMMIT/*!*/; -- 再去主庫從庫上面的tmp目錄察看臨時文件存在情況,果然看到SQL_LOAD_MB-1-0文件存在. [root@eanshlt2mydbc001db002 tmp]# ll -t /tmp/ |more total 2212772 -rw-r----- 1 root root 4581194 Jul 18 11:46 SQL_LOAD_MB-1-0
