load data方式導入的數據不可以用binlog日志進行恢復,因為binlog裡面不產生insert sql語句

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

發佈留言

發佈留言必須填寫的電子郵件地址不會公開。