loaddata方式导入的数据不可以用binlog日志进行恢复，因为binlo_MySQL

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